SCCM SQL Queries 101

Here’s a collection of useful SQL queries you can use these in reports or PowerBI. Make sure you’re charting them as best you can, a picture paints a thousand words especially when you’re talking to management.

Total Clients

SELECT
COUNT(dbo.v_R_System.ResourceID)
FROM
dbo.v_R_System

Total Users

SELECT
COUNT(dbo.v_R_User.ResourceID)
FROM
dbo.v_R_User

By Domain

SELECT
       dbo.v_R_System.Full_Domain_Name0,
       COUNT(dbo.v_R_System.Name0)
FROM
       dbo.v_R_System
GROUP BY
       dbo.v_R_System.Full_Domain_Name0
ORDER BY
       dbo.v_R_System.Full_Domain_Name0

By Architecture

SELECT
dbo.v_R_System.Full_Domain_Name0,
COUNT(dbo.v_R_System.Full_Domain_Name0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Full_Domain_Name0

By Operating System

SELECT
dbo.v_R_System.operatingSystem0,
COUNT(dbo.v_R_System.operatingSystem0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.operatingSystem0

By Chassis Type

SELECT
CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Other'
WHEN '2' THEN 'Unknown'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low Profile Desktop'
WHEN '5' THEN 'Pizza Box'
WHEN '6' THEN 'Mini Tower'
WHEN '7' THEN 'Tower'
WHEN '8' THEN 'Portable'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Notebook'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Sub Notebook'
WHEN '15' THEN 'Space-Saving'
WHEN '16' THEN 'Lunch Box'
WHEN '17' THEN 'Main System Chassis'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
WHEN '20' THEN 'Bus Expansion Chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Rack Mount Chassis'
WHEN '24' THEN 'Sealed-Case PC'
ELSE 'Undefinded' END AS 'Chassis',
COUNT(dbo.v_R_System.Name0) AS [# Devices]

FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID

GROUP BY
dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0

ORDER BY
1

By Virtual/Physical

SELECT
CASE
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '0' THEN 'Physical'
WHEN dbo.v_R_System.Is_Virtual_Machine0 = '1' THEN 'Virtual'
ELSE 'Unknown'
END AS [Physical / Virtual],
COUNT(dbo.v_R_System.Name0) [# Devices]
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Is_Virtual_Machine0
ORDER BY
dbo.v_R_System.Is_Virtual_Machine0

 

By CCM Client Version

SELECT
dbo.v_R_System.Client_Version0,
COUNT(dbo.v_R_System.Client_Version0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Version0
ORDER BY
dbo.v_R_System.Client_Version0

By Client Health

--Variables
DECLARE @Now DateTime = GetDate()

--Add Data to Temp DB Table
IF object_id('tempdb..#TMP_ClientsHealthSum') IS NOT NULL
DROP TABLE #TMP_ClientsHealthSum

Select Distinct
Name0,
ResourceID,
LastOnline,
case
When DATEDIFF(dd,LastOnline,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastOnline,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastOnline,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastOnline,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastOnline,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastOnline',
--LastMPServerName,
LastHealthEvaluation,
case
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 0 and 7 then 'Past Week'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 8 and 14 then 'Last 2 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 15 and 21 then 'Last 3 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 22 and 29 then 'Last 4 Weeks'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 30 and 60 then 'Last 2 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) between 61 and 89 then 'Last 3 Months'
When DATEDIFF(dd,LastHealthEvaluation,@Now) >= 90 then 'Over 3 Months'
Else 'Never'
End As 'DaysSinceLastHealthEval',
LastHealthEvaluationResult,
IsActivePolicyRequest,
LastEvaluationHealthy,
ClientActiveStatus,
ClientState,
ClientStateDescription
INTO #TMP_ClientsHealthSum
FROM vSMS_R_System SYS
LEFT JOIN v_CH_ClientSummary vCS ON SYS.ItemKey = vCS.ResourceID
WHERE SYS.Obsolete0 = 0

Select * From #TMP_ClientsHealthSum

By Client Type

SELECT
CASE WHEN dbo.v_R_System.Client_Type0 = 0 THEN 'Legacy Client'
WHEN dbo.v_R_System.Client_Type0 = 1 THEN 'Computer'
WHEN dbo.v_R_System.Client_Type0 = 3 THEN 'Mobile'
ELSE 'Unknown' END AS [Client Type],
COUNT(dbo.v_R_System.Client_Type0)
FROM
dbo.v_R_System
GROUP BY
dbo.v_R_System.Client_Type0
ORDER BY
dbo.v_R_System.Client_Type0

 

 

 

 

 

If you want to only report on workstations or servers respectively you can append most of the above queries with the below WHERE statements.

WHERE
(dbo.v_R_System.operatingSystem0 NOT LIKE '%SERVER%') --Workstations
       (dbo.v_R_System.operatingSystem0 LIKE '%SERVER%') --Servers
Advertisements

SCCM Compliance – PowerShell version

One of the first things I always like to setup in compliance is a CI which checks for a minimum version of PowerShell. This is especially helpful when you’re writing advanced compliance scripts in PowerShell and they’re not running as expected across some of your environment. In my experience this is usually because some of the devices have an old version of PowerShell.

Compliance Settings:

Setting Type:

 Script

Data Type:

 Integer

Discovery script:

$PSVersionTable.PSVersion.Major

Remediation script:

I’m not using a Remediation script, better to just deploy Windows Management Framework across you’re environment and use compliance as validation.

Make sure that Run scripts by using the logged on user credentials is ticked.

Compliance Rules:

Rule Type:

Value

The value returned by the specified script:

Greater than or equal to

The following values:

 3

Report noncompliance if this setting instance is not found

Yes

Noncompliance severity for reports

 Critical

SCCM Compliance: Where to start

Compliance in SCCM is one of the most powerful and overlooked features, ultimately your imagination is the only limit to what it can do. There’s a whole range of ways you can use compliance but the most powerful is PowerShell. There’s a few things you want to do before you start building any Configuration item’s or baselines though.

  1. Change your PowerShell execution to Bypass in SCCM client settings
  2. Deploy Windows Management Framework 4.0 – Don’t reinvent the wheel just deploy it as an application using scripts ‘ wusa.exe Windows6.1-KB2819745-x64-MultiPkg.msu /quiet /norestart’
  3. Check PowerShell version across the board using compliance

To get started some great things to use compliance for include:

  1. SOE related settings
  2. Are core apps installed and healthy?
  3. Is AV running?
  4. Are your certificates installed on all systems?