How to Quickly Get Overall Compliance for ConfigMgr Software Update Deployments

If you have access to your ConfigMgr SQL database, you can quickly get a summary for the overall compliance of software updates deployments using the following PowerShell script, which you can run on your local machine.

It returns the results into the PowerShell GridView, allowing you to filter by deployment name, start time, deadline etc.

Enter your SQL server database and instance name at the top of the script.

Capture


<#

This script gets overall compliance summary data for software updates deployments from the ConfigMgr SQL database

#>

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Getting Software Updates Compliance Data
Write-host "Getting Software Updates Overall Compliance Data"
$query = "SELECT CI.AssignmentID `
,CI.AssignmentName `
,Description `
,CollectionID `
,StartTime `
,EnforcementDeadline `
,OverrideServiceWindows`
,RebootOutsideOfServiceWindows `
,NumCompliant*100/NumTotal as 'PercentCompliant' `
,NumTotal `
,NumHealthy`
,NumCompliant `
,NumNonCompliant `
,NumUnknown `
,NumFailed `
,NumPending `
,NumHealthyCompliant `
,NumHealthyFailed `
FROM v_UpdateDeploymentClientSummary UDCS `
inner join v_CIAssignment CI on UDCS.AssignmentID = CI.AssignmentID ORDER BY AssignmentName Desc"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$table | Out-GridView -Title "Software Updates Overall Compliance per Deployment"


# Close the connection
$connection.Close()

PXE Boot Fails After Computer Rename

Today I had an interesting PXE boot problem where a computer kept aborting the PXE boot. So I checked the SMSPXE.log and found that there were no advertisements for that machine:

Client boot action reply: <ClientIDReply><Identification Unknown=”0″ ItemKey=”16777684″ ServerName=””><Machine><ClientID/><NetbiosName/></Machine></Identification><PXEBootAction LastPXEAdvertisementID=”” LastPXEAdvertisementTime=”” OfferID=”” OfferIDTime=”” PkgID=”” PackageVersion=”” PackagePath=”” BootImageID=”” Mandatory=””/></ClientIDReply>
F0:1F:AF:6C:05:53, 4C4C4544-0047-5310-8034-C3C04F595931: no advertisements found
F0:1F:AF:6C:05:53, 4C4C4544-0047-5310-8034-C3C04F595931: No boot action. Aborted.
F0:1F:AF:6C:05:53, 4C4C4544-0047-5310-8034-C3C04F595931: Not serviced.

The machine was previously deployed with SCCM and we were rebuilding it. The computer was definitely in a collection with an OS Deployment advertisement that was working fine on other machines. It turns out that the computer had been renamed, and the computer record for the old name was still present in ConfiMgr. I ran the following SQL query to find the computer name of any records in the database using the MAC address of that machine, then I deleted the record from the ConfigMgr console. After this, the PXE boot worked fine 🙂

select sm.ItemKey, Netbios_Name0, MAC_Addresses0, GUID, si.SMBIOS_GUID0, Hardware_ID0
from System_MAC_Addres_ARR sm
inner join MachineIdGroupXRef mx on sm.ItemKey=mx.MachineID
inner join System_AUX_Info si on sm.ItemKey=si.ItemKey
where MAC_Addresses0='F0:1F:AF:6C:05:53'

ItemKey 16777684
Netbios_Name0 My-Computer-lt-05
MAC_Addresses0 F0:1F:AF:6C:05:53
GUID GUID:1dba29f3-350e-4a4c-a171-1e2ef1fe8f95
SMBIOS_GUID0 4C4C4544-0047-5310-8034-C3C04F595931
Hardware_ID0 2:84912D65A9328FFCBB0CA60D9EC952D9A21AA955

Re-running a ConfigMgr Task Sequence on Multiple Computers

Recently I deployed a very simple task sequence to all our laptop computers which installs a new WiFi profile.  However, on viewing the deployment reports I noticed a number of machines where the deployment was stuck in either the ‘running’ state, or ‘failed’.  Although the deployment is set to ‘Re-run if failed previous attempt’ it seems the task sequence will not re-run automatically, only if a new schedule is created.  I wanted a quick and easy way to simply trigger the deployment again on all the computers that needed it, so I modified a script I use for re-running a task sequence to run against all computers in a csv.

Here is the script.  You need PS remoting active in your environment.  Simply add the location of the csv that contains the computernames you want to re-run the deployment on (no header required), and the name of the task sequence, and the script will do the rest.  It’s a little crude at the moment, but it works!  After running this, my compliance figures for the deployment were immediately boosted, and only a handful of machines remained that required individual troubleshooting 🙂

$ComputerNames = Get-Content -Path C:\Script_Files\WiFi_Profile_Failures.csv
$TaskSequenceName = "WiFi Profile"

cls
foreach ($ComputerName in $ComputerNames)
{ 
# Test connectivity to the computer
if (Test-Connection -Quiet -Count 2 -ComputerName $ComputerName -ErrorAction SilentlyContinue)
{$Online = "Yes"}else{$Online = "No"}
if ($Online -eq "No")
{write-host "$ComputerName is not online!"}
if ($Online -eq "Yes")
{
 
$s = New-PSSession -ComputerName $ComputerName
Invoke-Command -Session $s -Argu $ComputerName,$TaskSequenceName -ScriptBlock `
{
param ($ComputerName,$TaskSequenceName)
write-host "Getting PackageID for '$TaskSequenceName' on $ComputerName"
 $PackageID = get-wmiobject -computername $ComputerName -query "SELECT * FROM CCM_SoftwareDistribution" -namespace "root\ccm\policy\machine\actualconfig" | where {$_.PKG_Name -like $TaskSequenceName} | Select PKG_PackageID
 $PackageID = $PackageID.PKG_PackageID
 write-host $PackageID -ForegroundColor Yellow
 write-host "Getting ScheduleID for '$TaskSequenceName' on $ComputerName"
 $ScheduleID = Get-WmiObject -computername $ComputerName -Namespace "root\ccm\scheduler" -Class ccm_scheduler_history | where {$_.ScheduleID -like "*$PackageID*"} | Select-Object ScheduleID 
 $ScheduleID = $ScheduleID.ScheduleID
 write-host $ScheduleID -ForegroundColor Yellow
 write-host "Getting AdvertisementID for '$TaskSequenceName' on $ComputerName"
 $AdvertisementID = get-wmiobject -computername $ComputerName -query "SELECT * FROM CCM_SoftwareDistribution" -namespace "root\ccm\policy\machine\actualconfig" | where {$_.PKG_Name -like $TaskSequenceName} | Select ADV_AdvertisementID
 $AdvertisementID = $AdvertisementID.ADV_AdvertisementID
 write-host $AdvertisementID -ForegroundColor Yellow
 write-host "Setting re-run behaviour"
 $a = get-wmiobject -computername $ComputerName -query "SELECT * FROM CCM_TaskSequence" -namespace "root\ccm\policy\machine\actualconfig" | Where {$_.ADV_AdvertisementID -like "*$AdvertisementID*"} 
 $a.ADV_RepeatRunBehavior='RerunAlways'
 $a.Put() | Out-Null
 write-host "Creating mandatory assignment"
 $a = get-wmiobject -computername $ComputerName -query "SELECT * FROM CCM_TaskSequence" -namespace "root\ccm\policy\machine\actualconfig" | Where {$_.ADV_AdvertisementID -like "*$AdvertisementID*"}
 $a.ADV_MandatoryAssignments=$True
 $a.Put() | Out-Null
 
 write-host "Triggering the schedule now!" -ForegroundColor Green
 Invoke-WmiMethod -ComputerName $ComputerName -Namespace ROOT\ccm -Class SMS_Client -Name TriggerSchedule -ArgumentList "$ScheduleID" | Out-Null
 }
 Remove-PSSession $s
 }
 }

Ping all the Computers in an AD OU

Here is a clean and simple PowerShell script to ping all the computer accounts in an Active Directory OU, and return the Name, IP Address and any errors into a csv file.

# Enter CSV file location
$csv = "C:\Script_Files\OUPing.csv"
# Add the target OU in the SearchBase parameter
$Computers = Get-ADComputer -Filter * -SearchBase "OU=Servers,DC=mydomain,DC=com" | Select Name | Sort-Object Name
$Computers = $Computers.Name
$Headers = "ComputerName,IP Address"
$Headers | Out-File -FilePath $csv -Encoding UTF8
foreach ($computer in $Computers)
{
Write-host "Pinging $Computer"
$Test = Test-Connection -ComputerName $computer -Count 1 -ErrorAction SilentlyContinue -ErrorVariable Err
if ($test -ne $null)
{
    $IP = $Test.IPV4Address.IPAddressToString
    $Output = "$Computer,$IP"
    $Output | Out-File -FilePath $csv -Encoding UTF8 -Append
}
Else
{
    $Output = "$Computer,$Err"
    $output | Out-File -FilePath $csv -Encoding UTF8 -Append
}
cls
}