Functions/Get-ECSWSUSUpdateReport.ps1
|
Function Get-ECSWSUSUpdateReport { <# .SYNOPSIS This report will give you a list of the update status for a given update across your wsus environment .DESCRIPTION This report will give you a list of the update status for a given update across your wsus environment .PARAMETER UpdateIDObject This is the update id GUID object that you'll pipe from a command such as Get-ECSWSUSUpdate .PARAMETER IncludeNonApplicableUpdates By default, we only grab applicable updates. There may be a reason i can't forsee thatto see ALL updates for a given computer, using this switch if that's the case. .PARAMETER UpdateApprovalAction This is to influence whether you only want to report on updates that are approved, not approved or any status. We default to any. .EXAMPLE This example gets the status of a singular update Get-ECSWSUSUpdateReport -UpdateIDObject "dddcf9cd-6eee-4e16-8bd8-85117ed9b7de" -UpdateApprovalAction Install .EXAMPLE This example, we'll get a list of updated pipelined from get-ecswsusudpate Get-ECSWSUSUpdate | Select-Object -First 5 -ExpandProperty updateid | Get-ECSWSUSUpdateReport -UpdateApprovalAction Install #> [CmdletBinding()] Param ( ############################################################# #UpdateIDObject [Parameter( ValueFromPipelineByPropertyName = $true, ValueFromPipeline = $true, Mandatory=$true )] [ValidateNotNullorEmpty()] $UpdateIDObject, ############################################################# #IncludeNonApplicableUpdates [Parameter(Mandatory=$false)] [Switch]$IncludeNonApplicableUpdates = $false, ############################################################# #UpdateApprovalAction [Parameter(Mandatory=$false)] [ValidateSet("All","Install","NotApproved")] [String]$UpdateApprovalAction = "All" ) Process { ######################################################################### #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 ######################################################################### ######################################################################### #Test connection state Write-Verbose "Testing the WSUS database connectivity before querying WSUS" $TestWSUSConnectionState = Test-ECSWSUSDatabaseConnected If ($TestWSUSConnectionState.OverallStatus -eq $false) { Throw "Either you are not connected to the WSUS database, or we had an error. Please run Test-ECSWSUSDatabaseConnected to determine the detailed status" } #END Test connection state ######################################################################### Foreach ($UpdateObject in $UpdateIDObject) { ######################################################################### #Define dynamic SQL query #Creating two different base queries based on whether we're doing a detailed report or a summary report only $SQLQueryToRun = @" Use $($WSUSDatabaseName) DECLARE @UpdateId uniqueidentifier; SET @UpdateId = '$($UpdateObject)'; SELECT [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[UpdateId] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[ComputerTargetId] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[State] ,CASE WHEN State = 0 THEN 'Unknown' WHEN State = 1 THEN 'NotApplicable' When State = 2 THEN 'NotInstalled' WHEN State = 3 THEN 'Downloaded' WHEN State = 4 THEN 'Installed' WHEN State = 5 THEN 'Failed' WHEN State = 6 THEN 'InstalledPendingReboot' Else 'No Match' end as FriendlyState ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[RevisionNumber] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[DefaultTitle] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[DefaultDescription] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[ClassificationId] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[ArrivalDate] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[CreationDate] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[IsDeclined] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[IsWsusInfrastructureUpdate] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[MsrcSeverity] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[PublicationState] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[UpdateType] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[UpdateSource] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[KnowledgebaseArticle] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[SecurityBulletin] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[InstallationCanRequestUserInput] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[InstallationRequiresNetworkConnectivity] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[InstallationImpact] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[InstallationRebootBehavior] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateApproval].[Action] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTarget].[Name] ,[$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTargetGroup] .[Name] as TargetGoupName FROM [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo] --Find the matching computer target inner join [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTarget] on [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTarget].[ComputerTargetId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[ComputerTargetId] --Find the approval ID to see whether the update is approved or not inner join [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateEffectiveApprovalPerComputer] on ( [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateEffectiveApprovalPerComputer].[ComputerTargetId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[ComputerTargetId] and [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateEffectiveApprovalPerComputer].[UpdateId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[UpdateId] ) --Link the update approval ID's for the effective computer approval inner join [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateApproval] on ( [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateApproval].[UpdateApprovalId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateEffectiveApprovalPerComputer].[UpdateApprovalId] ) --Show the computer target group, so we know the frinedly name of where the approval comes from. inner join [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTargetGroup] on ( [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vComputerTargetGroup].[ComputerTargetGroupId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateApproval].[ComputerTargetGroupId] ) --Get update details inner join [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate] on ( [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdate].[UpdateId] = [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[UpdateId] ) where [$($WSUSDatabaseName)].[PUBLIC_VIEWS].[vUpdateInstallationInfo].[UpdateId] = @UpdateId and State != 1 "@ #Applicable Updates only If ($IncludeNonApplicableUpdates -eq $true) { Write-Verbose "IncludeNonApplicableUpdates = $true" } Else { Write-Verbose "IncludeNonApplicableUpdates = $false" $SQLQueryToRun += @" and [PUBLIC_VIEWS].[vUpdateInstallationInfo].[State] != 1 "@ } #Approved Updates only Write-Verbose "UpdateApprovalAction = $($UpdateApprovalAction)" If ($UpdateApprovalAction -ne "All") { $SQLQueryToRun += @" and [PUBLIC_VIEWS].[vUpdateApproval].[Action] = '$($UpdateApprovalAction)' "@ } #END Define dynamic SQL query ######################################################################### ######################################################################### #Executing SQL query Try { #Formulating base command $SQLCommandToRun = '$SQLQuery' + " = Invoke-Sqlcmd -ServerInstance $WSUSSQLServerName -Database $WSUSDatabaseName -AbortOnError" + ' -Query $SQLQueryToRun' + ' -ErrorAction "Stop"' #Executing command Write-Verbose "Executing the following command: $($SQLCommandToRun)" Write-Verbose "The following query is being executed:" Write-Verbose $SQLQueryToRun #Running 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 -eq 0) { Throw "No results querying computer target $($UpdateObject.FullDomainName)" } } Catch { Throw "Something went wrong with the SQL query, exception message = $($_.Exception.Message)" } #END Executing SQL query ######################################################################### ######################################################################### #Output Report $SQLQuery #End Output Report ######################################################################### } } } |