Functions/Test-ECSWSUSDatabaseConnected.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
Function Test-ECSWSUSDatabaseConnected
    {
    <#
    .SYNOPSIS
    This tests that you are not only connected to the WSUS database, but also that we can query the wsus database
 
    .DESCRIPTION
    This tests that you are not only connected to the WSUS database, but also that we can query the wsus database
 
    .EXAMPLE
    There are no parameters, it's simply run the command as it is.
 
    Test-ECSWSUSDatabaseConnected
    #>

    [CmdletBinding()]
    

    Param
        (

        )

    Process
        {
        #########################################################################
        #Test connecton is defined

        Try
            {
            Write-Verbose "Testing if the global:ecswsusdatabaseconection var exists"
            $Shh = Get-Variable -Name ECSWSUSDatabaseConnection -ErrorAction Stop
            $VariableExists = $true
            $OverallStatus = $true
            }
        Catch
            {
            $VariableExists = $false
            $OverallStatus = $false
            }
        
        #END Test connecton is defined
        #########################################################################

        #########################################################################
        #Global Params

        $WSUSSQLServerName = $Global:ECSWSUSDatabaseConnection.WSUSSQLServerName
        Write-Verbose "WSUSSQLServerName = $($WSUSSQLServerName)"
        $WSUSDatabaseName = $Global:ECSWSUSDatabaseConnection.WSUSDatabaseName
        Write-Verbose "WSUSDatabaseName = $($WSUSDatabaseName)"
        $Credential = $Global:ECSWSUSDatabaseConnection.Credential
        
        #END Global Params
        #########################################################################

        #########################################################################
        #Executing SQL query to test the connection
        If ($OverallStatus -eq $true)
            {

            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
                Write-Verbose "Testing the following query:"
                Write-Verbose "$($SQLQueryToRun)"
            
                Invoke-Expression -Command $($SQLCommandToRun)
                $ExecutedTestQueryWithNoErrors = $true
                $OverallStatus = $true
                }
            Catch
                {
                $ExecutedTestQueryWithNoErrors = $false
                $OverallStatus = $false
                }

            #Making sure you had results, if not we're throwing an error
            
            $MeaureSQLQueryCount = $SQLQuery | Measure-Object | Select-Object -ExpandProperty count
            If ($MeaureSQLQueryCount -eq 1)
                {
                $TestQueryHadResults = $true
                $OverallStatus = $true
                }
            Else
                {
                $TestQueryHadResults = $false
                $OverallStatus = $false
                }
            }
        #END Executing SQL query
        #########################################################################

        #########################################################################
        #Output results

        New-Object PSObject -Property @{
            VariableExists = $VariableExists
            ExecutedTestQueryWithNoErrors = $ExecutedTestQueryWithNoErrors
            TestQueryHadResults = $TestQueryHadResults
            OverallStatus = $OverallStatus
            }
        
        #END Output results
        #########################################################################

        }
    }