Tag Archives: SQL

[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',
summ.ClientStateDescription,
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',
summ.LastOnline,
summ.LastDDR,
summ.LastHW,
summ.LastSW,
summ.LastPolicyRequest,
summ.LastStatusMessage,
summ.LastHealthEvaluation,
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',
summ.ExpectedNextPolicyRequest
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
Advertisements

[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.

SELECT
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
FROM
SMS_R_System INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON
SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
WHERE
SMS_G_System_COMPUTER_SYSTEM.UserName IN (SELECT UniqueUserName FROM SMS_R_User WHERE UserGroupName = "Domain\\MailingList")

Spiceworks Report – Tickets Closed Last Month by Technician

This report includes tickets that were assigned to nobody, and also gives a grand total. It does not include technicians who did not close any tickets the previous month unfortunately. There is no need to edit the report; it automatically gets the statistics for the month prior to the running of the report.

select Name, Count from (

select u.first_name || " " || u.last_name as 'Name', count(*) as 'Count'
  from tickets t, users u
  where t.closed_at between date('now','start of month','-1 month') and date('now','start of month')
  and t.status = "closed"
  and t.assigned_to = u.id
  group by Name
  
union
  
select "(Nobody)" as Name, count(*) as 'Count'
  from tickets t
  where t.closed_at between date('now','start of month','-1 month') and date('now','start of month')
  and t.status = "closed"
  and t.assigned_to is NULL
  
union
  
select "z_GRAND TOTAL" as Name, count(*) as 'Count'
  from tickets t
  where t.closed_at between date('now','start of month','-1 month') and date('now','start of month')
  and t.status = "closed"
  
)
  
group by Name
order by [Name] ASC