Kickin' those SUGs

You find out that some Software Update Groups (SUG) are low on compliant percentage, what to do? You can run the reports from SCCM console to see which clients are not fully compliant against specific SUG. Then you do some magic on the client and get the updates rolling again, right?

Well, sounds like too much work for me. Lets see what we can do with PowerShell...

Did some researching and found Eswar Koneti's @eskonr post about how to get Update Compliance status with SQL query. Nice! Now I took that SQL script, simplified it some and I was able to retrieve computers that need special care with updates.

Here's the script, ping me in Twitter @arisaastamoinen if you find it useful (or bugs...)

Cheers, Ari

 Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" 
 Set-Location "ABC:" # Set the current location to be the site code.
 # Which SUG we are checking?  
 $sug = 'Windows Updates Automatic Deployment 2017-06'  
 # Against which devices?  
 $devColl = Get-CMDeviceCollection -Name 'All Desktop and Server Clients'  
 <# Update statuses  
   Unknown = 0  
   Not Applicable = 1  
   Required = 2  
   Installed = 3  
 #>  
 $UpdateStatus = "2"  
 # Create a new collection where non-compliant clients will be added   
 $coll = New-CMDeviceCollection -Name 'Computers with Updates that need attention' -LimitingCollectionName 'All Systems' -Comment '2017-06-15 - Computers with Updates that need special attention' # -WhatIf  
 # or you can Get- the collection  
 $coll = Get-CMDeviceCollection -Name 'Computers with Updates that need attention'  
 # SQL specs  
 $SQLInstance = "mysqlserver\systemcenter"  
 $CMDatabase = "CM_X01"  
 # SQL Query returns computernames from a Collection whose Software Update Group status is x   
 $query = @"  
 Declare @Status nvarchar(255)  
 Declare @SUG nvarchar(255)  
 declare @devColl nvarchar(10)  
 set @Status='$UpdateStatus'  
 set @SUG = '$sug'  
 set @devColl = '$($devColl.CollectionID)'  
  select   
     RSys.name0 [Computername]  
  From v_Update_ComplianceStatusAll UCS  
      left join v_r_system RSys on UCS.resourceid=RSys.resourceid  
      left join v_FullCollectionMembership FCM on RSys.resourceid=FCM.resourceid  
      left join v_collection coll on coll.collectionid=FCM.collectionid  
      left join v_AuthListInfo LI on UCS.ci_id=li.ci_id  
  where li.title = @SUG and coll.collectionID = @devColl and UCS.status=@Status  
 "@  
 # Check results  
 # $query  
 # Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $CMDatabase -Query $query  
 $computer = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $CMDatabase -Query $query | select -ExpandProperty Computername  
 $computer | foreach {  
   $dev = Get-CMDevice -Fast -Name $_  
   Add-CMDeviceCollectionDirectMembershipRule -CollectionId $coll.CollectionID -ResourceId $dev.ResourceID # -WhatIf  
 }  
 # for fun, call Machine Policy refresh on the group  
 Invoke-CMClientNotification -DeviceCollectionId $coll.CollectionID -NotificationType RequestMachinePolicyNow  
 # now you can initiate client notifications from GUI but lets do it with PS/WMI  
 #Software Updates Deployment Evaluation Cycle  
 $strAction = "{00000000-0000-0000-0000-000000000108}"  
 $computer | foreach {  
   Invoke-Command -ComputerName $_ -ScriptBlock {  
     Invoke-WmiMethod -Namespace root\ccm -Class SMS_Client -Name TriggerSchedule -ArgumentList $Using:strAction   
   } -AsJob -Verbose  
 }  
 # Software Updates Scan Cycle  
 $strAction = "{00000000-0000-0000-0000-000000000113}"  
 $computer | foreach {  
   Invoke-Command -ComputerName $_ -ScriptBlock {  
     Invoke-WmiMethod -Namespace root\ccm -Class SMS_Client -Name TriggerSchedule -ArgumentList $Using:strAction   
   } -AsJob -Verbose  
 }