Functions/Connect-ECSWSUSDatabase.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Function Connect-ECSWSUSDatabase
    {
    <#
    .SYNOPSIS
    This establishes a global connection variable to be used by other ECSWSUS cmdlets. This to reduce the amount of parameters needed per command
 
    .DESCRIPTION
    This establishes a global connection variable to be used by other ECSWSUS cmdlets. This to reduce the amount of parameters needed per command
 
    .PARAMETER WSUSSQLServerName
    This is the name of the SQL server and SQL instance your WSUS database is hosted on.
 
    .PARAMETER WSUSDatabaseName
    This is the name of the WSUS database you're querying.
 
    .PARAMETER Credential
    If you want to connect using SQL auth, use "Get-Credential" to store your SQL auth username and password in a PS object
 
    .EXAMPLE
    This example gets any computer with wks in the name with a timeout of 100 seconds and uses SQLAuth
 
    Connect-ECSWSUSDatabase -WSUSSQLServerName asi-sql-01 -Credential $cred
 
    .EXAMPLE
    This example gets any computer with wks and pc in the name with not timeout and uses passthru auth
    Connect-ECSWSUSDatabase -WSUSSQLServerName asi-sql-01
 
 
 
    #>

    [CmdletBinding()]
    

    Param
        (
        #############################################################
        #WSUSSQLServerName

        [Parameter(
            ParameterSetName = "PassthruAuth",
            Mandatory = $True,
            HelpMessage="Enter the name of your WSUS SQL server or SQLServer\Instance name"
            )]

        [Parameter(
            ParameterSetName = "SQLAuth",
            Mandatory = $True,
            HelpMessage="Enter the name of your WSUS SQL server or SQLServer\Instance name"
            )]

        [ValidateNotNullorEmpty()]
        [String]$WSUSSQLServerName,

        #############################################################
        #WSUSDatabaseName

        [Parameter(
            ParameterSetName = "PassthruAuth",
            Mandatory=$false
            )]

        [Parameter(
            ParameterSetName = "SQLAuth",
            Mandatory=$false
            )]

        [ValidateNotNullorEmpty()]
        [String]$WSUSDatabaseName = "SUSDB",


        #############################################################
        #Credential

        [Parameter(
            ParameterSetName = "SQLAuth",
            Mandatory=$True
            )]
        [ValidateNotNullorEmpty()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]
        $Credential = $null
        )

    Process
        {
        
        #########################################################################
        #Dynamic Parameters

        #$ModuleRootPath = Split-Path -Path $PSScriptRoot
        
        #END Dynamic Parameters
        #########################################################################

        #########################################################################
        #Executing SQL query to test the connection
        Try
            {
            #Simple test SQL query to run
            $SQLQueryToRun = "SELECT TOP (1) fileid FROM [$($WSUSDatabaseName)].[sys].[sysfiles]"

            #Formulating base command
            $SQLCommandToRun = '$SQLQuery' + " = Invoke-Sqlcmd -ServerInstance $WSUSSQLServerName -Database $WSUSDatabaseName -AbortOnError" + ' -Query $SQLQueryToRun' + ' -ErrorAction "Stop"'

            #Checking if you wanted PassThru or SQL Auth
            If ($Credential -ne $null)
                {
                Write-Verbose "AuthenticationType = SQLAuth"
                $SQLCommandToRun = $SQLCommandToRun  + ' -Credential $Credential'
                }
            Else
                {
                Write-Verbose "AuthenticationType = Passthru"
                }

            #Running test query
            Invoke-Expression -Command $($SQLCommandToRun)

            #Making sure you had results, if not we're throwing an error
            $MeaureSQLQueryCount = $SQLQuery | Measure-Object | Select-Object -ExpandProperty count
            If ($MeaureSQLQueryCount -ge 1)
                {
                $Global:ECSWSUSDatabaseConnection = New-Object PSObject -Property @{
                    WSUSSQLServerName = $($WSUSSQLServerName)
                    WSUSDatabaseName = $($WSUSDatabaseName)
                    Credential = $Credential
                    }
                }
            Else
                {
                Throw "No test query results, it's likely the connection failed"
                }

            }
        Catch
            {
            Throw "Something went wrong with the SQL query, exception message = $($_.Exception.Message)"
            }

        #END Executing SQL query
        #########################################################################
        }
    }