Tag Archives: reporting

[Powershell] Querying ServiceNow via REST

Compiling reports for ServiceNow is a pain with its built-in report builder, since by default it will only return 10,000 records.  Fortunately, they provide a REST API.  This script will page 1000 records at a time, both for ServiceNow record limitations, and avoiding HTTP timeouts.

$snUrl = "https://your-company.service-now.com"
$table = "table_you_want"
[int]$limit = 1000
$snApi = "api/now/v1"
$sncred = Get-Credential
[int]$numRecords = (Invoke-RestMethod -Uri $("$snUrl/$snApi/stats/$table" + "?sysparm_count=true") -Credential $sncred).result.stats.count
$numIterations = [int][math]::Ceiling(($numRecords / $limit))
$jsonTable = $null
for ($i=0; $i -lt $numIterations; $i++){
    $offset = $i * $limit
    $jsonTable += (Invoke-RestMethod -Uri $("$snUrl/$snApi/table/$table" + "?sysparm_offset=$offset&sysparm_limit=$limit") -Credential $sncred).result

[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

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