Tag Archives: SCCM

[Powershell] SCCM Client Health “Super Query”

I was asked to create a report on devices in AD and SCCM, and report on whether or not AD devices were in the SCCM database, and if so, what their health status was. 99% of the credit goes to Trevor Jones for the SQL portion, which I made a couple tweaks to.  The SQL query is part of his excellent client health spreadsheet, which can be found here.  Huge thank you to Trevor, who gave me permission to republish his query!

Simply define your domain(s), SCCM database server(s), and the time threshold that constitutes activity (when client was last active in AD).

For this script, you will need the RSAT tools, the SQL Server Powershell Provider, and Join-Object by RamblingCookieMonster.

$arrDomains = @("sub1.domain.com","sub2.domain.com","sub3.domain.com")
$arrSccmDbs = @("sccm1.domain.com","sccm2.domain.com")
$timeThreshold = 30

#Unfortunately Arraylists do not work with Join-Object. 
#$arrAD = New-Object -TypeName System.Collections.Arraylist
#$arrSccm = New-Object -TypeName System.Collections.Arraylist
$arrAD = @()
$arrSccm = @()
$daysAgo = $(get-date).AddDays($timeThreshold * -1)
foreach ($domain in $arrDomains) {
    $strSearchBase = 'DC=' + $($domain -replace '\.',',DC=')
    $arrAD += Get-ADComputer -Filter * -SearchBase $strSearchBase -Server $domain -Properties Name, SID, description, OperatingSystem, OperatingSystemVersion, lastLogonDate, lastLogonTimeStamp, lastLogon, whenChanged, Enabled `
    | Select Name, @{N='SID'; E={$_.SID.Value}}, description, OperatingSystem, OperatingSystemVersion, @{N='LastLogonTimeStamp'; E={[DateTime]::FromFileTime($_.LastLogonTimeStamp)}}, @{N='LastLogon'; E={[DateTime]::FromFileTime($_.LastLogon)}}, whenChanged, Enabled `
    | ? {($_.OperatingSystem -notlike "*Server*" -and $_.OperatingSystem -like "*Windows*") -and ($_.LastLogonTimeStamp -ge $daysAgo -or $_.LastLogon -ge $daysAgo -or $_.whenChanged -ge $daysAgo) -and $_.Enabled}
$query = "select
sys.SID0 as 'SID',
sys.Name0 as 'Computer Name',
bios.SerialNumber0 as 'Serial Number',
sys.User_Name0 as 'User Name',
case when summ.ClientActiveStatus = 0 then 'Inactive'
       when summ.ClientActiveStatus = 1 then 'Active'
       end as 'ClientActiveStatus',
max(summ.LastActiveTime) AS LastActiveTime,
case when summ.IsActiveDDR = 0 then 'Inactive'
       when summ.IsActiveDDR = 1 then 'Active'
       end as 'IsActiveDDR',
case when summ.IsActiveHW = 0 then 'Inactive'
       when summ.IsActiveHW = 1 then 'Active'
       end as 'IsActiveHW',
case when summ.IsActiveSW = 0 then 'Inactive'
       when summ.IsActiveSW = 1 then 'Active'
       end as 'IsActiveSW',
case when summ.ISActivePolicyRequest = 0 then 'Inactive'
       when summ.ISActivePolicyRequest = 1 then 'Active'
       end as 'ISActivePolicyRequest',
case when summ.IsActiveStatusMessages = 0 then 'Inactive'
       when summ.IsActiveStatusMessages = 1 then 'Active'
       end as 'IsActiveStatusMessages',
case when LastHealthEvaluationResult = 1 then 'Not Yet Evaluated'
       when LastHealthEvaluationResult = 2 then 'Not Applicable'
       when LastHealthEvaluationResult = 3 then 'Evaluation Failed'
       when LastHealthEvaluationResult = 4 then 'Evaluated Remediated Failed'
       when LastHealthEvaluationResult = 5 then 'Not Evaluated Dependency Failed'
       when LastHealthEvaluationResult = 6 then 'Evaluated Remediated Succeeded'
       when LastHealthEvaluationResult = 7 then 'Evaluation Succeeded'
       end as 'Last Health Evaluation Result',
case when LastEvaluationHealthy = 1 then 'Pass'
       when LastEvaluationHealthy = 2 then 'Fail'
       when LastEvaluationHealthy = 3 then 'Unknown'
       end as 'Last Evaluation Healthy',
case when summ.ClientRemediationSuccess = 1 then 'Pass'
       when summ.ClientRemediationSuccess = 2 then 'Fail'
       else ''
       end as 'ClientRemediationSuccess',
from v_CH_ClientSummary summ
inner join v_R_System sys on summ.ResourceID = sys.ResourceID
inner join v_GS_PC_BIOS bios on bios.ResourceID = sys.ResourceID
where sys.SID0 is not null --and summ.LastActiveTime > DATEADD(DAY, $("-"+$timeThreshold), GETDATE())
group by sys.SID0, sys.Name0, bios.SerialNumber0, sys.User_Name0, summ.ClientStateDescription, summ.ClientActiveStatus, summ.IsActiveDDR, summ.IsActiveHW, summ.IsActiveSW, summ.ISActivePolicyRequest, summ.IsActiveStatusMessages, summ.LastOnline,summ.LastDDR, summ.LastHW, summ.LastSW, summ.LastPolicyRequest, summ.LastStatusMessage, summ.LastHealthEvaluation, summ.LastHealthEvaluationResult, summ.LastEvaluationHealthy, summ.ClientRemediationSuccess, summ.ExpectedNextPolicyRequest"
foreach ($sccmDb in $arrSccmDbs) {
    $db = $null
    $db = (Invoke-Sqlcmd -query "select name from sys.databases" -ServerInstance $sccmDb -Database "master" | ? {$_.name -like "CM_*"}).name
    $arrSccm += Invoke-Sqlcmd -Query $query -ServerInstance $sccmDb -Database $db `
    | Select -Property SID, "Computer Name", "User Name", "Serial Number", ClientStateDescription, ClientActiveStatus, LastActiveTime, IsActiveDDR, IsActiveHW, IsActiveSW, ISActivePolicyRequest, IsActiveStatusMessages, LastOnline, LastDDR, LastHW, LastSW, LastPolicyRequest, LastStatusMessage, LastHealthEvaluation, LastHealthEvaluationResult, LastEvaluationHealthy, ClientRemediationSuccess, ExpectedNextPolicyRequest
$arrHealth = Join-Object -Left $arrAD -Right $arrSccm -LeftJoinProperty SID -RightJoinProperty SID -Type AllInLeft | Sort-Object -Property LastActiveTime -Descending | Sort-Object -Property SID -Unique

[SCCM] Deploy to Machines Based on User Distribution List Membership (Without Using User-Device Affinity)

I recently came across a unique situation where it was necessary to deploy applications to a machine, based on the owner’s membership in a particular DL in exchange, but without the use of user-device affinity.  Query is below.

SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
SMS_G_System_COMPUTER_SYSTEM.UserName IN (SELECT UniqueUserName FROM SMS_R_User WHERE UserGroupName = "Domain\\MailingList")