SCCM Report: Asset Overview

One of the first things people want to start seeing once they have SCCM in place is richer detail on all of the devices that are being managed. I’ve written this report to provide an overview of all devices.

One key thing to call out is the way I structure reports like this is based around creating a temp table and then joining multiple queries together through the ‘UPDATE’ statement, this allows you to on the fly manage multiple values on sub queries to ensure you’re getting the desired data.

Capture.PNG

 DECLARE @TempTable TABLE(
Hostname varchar (100),
CCMClient varchar (10),
OperatingSystem varchar (100),
Manufacturer varchar (100),
Model varchar (100),
Chassis varchar (100),
Serial varchar (100),
IsVirtual varchar (100),
CPU ntext,
CPUCores int,
CPULogical int,
RAM int,
VolumeSize_C int,
VolumeFree_C int
)

INSERT INTO @TempTable (Hostname, OperatingSystem,CCMClient, IsVirtual)

SELECT
dbo.v_R_System.Name0,
dbo.v_R_System.operatingSystem0,
CASE WHEN dbo.v_R_System.Client0=1 THEN 'Yes' ELSE 'No' END,
CASE WHEN dbo.v_R_System.Is_Virtual_Machine0=1 THEN 'Yes' ELSE 'No' END
FROM
dbo.v_R_System

UPDATE @TempTable
SET Manufacturer = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 IS NOT NULL)
)

UPDATE @TempTable
SET Model = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Model0 IS NOT NULL)
)

UPDATE @TempTable
SET Chassis = (
SELECT DISTINCT
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'
FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0!='12')
)

UPDATE @TempTable
SET Serial = (
SELECT DISTINCT
dbo.v_GS_PC_BIOS.SerialNumber0
FROM
dbo.v_GS_PC_BIOS
INNER JOIN dbo.v_R_System ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PC_BIOS.SerialNumber0 IS NOT NULL)
)

UPDATE @TempTable
SET CPU = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.Name0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.Name0 IS NOT NULL)
)

UPDATE @TempTable
SET CPUCores = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfCores0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfCores0 IS NOT NULL)
)

UPDATE @TempTable
SET CPULogical = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0 IS NOT NULL)
)

Update @TempTable
SET VolumeSize_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.Size0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.Size0 IS NOT NULL) AND
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)

Update @TempTable
SET VolumeFree_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.FreeSpace0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)

Update @TempTable
SET RAM = (
SELECT
SUM(dbo.v_GS_PHYSICAL_MEMORY.Capacity0)/1024
FROM
dbo.v_GS_PHYSICAL_MEMORY
INNER JOIN dbo.v_R_System ON dbo.v_GS_PHYSICAL_MEMORY.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName)
)

SELECT *
FROM
@TempTable

ORDER BY
Hostname

Or you can download the .rdo here and install. One note using this report you must have the OperatingSystem attribute being discovered by AD System Discovery.

If you have questions or want to see extra detail in these reports feel free to comment below and I can assist in extending.

Advertisements

SCCM SQL Queries: Software Update Compliance by Software Update Group

This is particularly useful for charting compliance of your environment over a period of time by each Software Update Group, I’ve split it out into the total compliance, servers and workstations so that they can individually be charted. The table cane modified as well in the declared @TempTableVariable table and using the ‘UPDATE’ statement.

If charting, use a line graph to chart each of the individual columns as values.Feel free to ask questions if you need a hand getting this working 😉


Declare @TempTableVariable TABLE(
SoftwareUpdateGroup VARCHAR(500),
TotalCompliance INT,
ServerCompliance INT,
WorkstationsCompliance INT)

INSERT INTO @TempTableVariable (SoftwareUpdateGroup)
SELECT
dbo.v_AuthListInfo.Title
FROM
dbo.v_AuthListInfo
ORDER BY
dbo.v_AuthListInfo.Title

--Total Compliance
UPDATE @TempTableVariable SET TotalCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title)

--Server Compliance
UPDATE @TempTableVariable SET ServerCompliance=(
SELECT
round((CAST(SUM (
CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
FROM
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id
WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

--Workstations Compliance
UPDATE @TempTableVariable SET WorkstationsCompliance=(
SELECT
round((CAST(SUM ( CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) AS FLOAT)/COUNT(*) )*100,2) AS 'Success %'
From
v_Update_ComplianceStatusAll UCS
INNER JOIN v_r_system sys on ucs.resourceid=sys.resourceid
INNER JOIN v_UpdateCIs on ucs.resourceid=v_UpdateCIs.CI_ID
INNER JOIN v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
INNER JOIN v_collection coll on coll.collectionid=fcm.collectionid
INNER JOIN v_AuthListInfo LI on ucs.ci_id=li.ci_id

WHERE
v_UpdateCIs.IsDeployed =1 AND
v_UpdateCIs.IsExpired=0 AND
v_UpdateCIs.InUse=1 AND
v_UpdateCIs.IsTombstoned=0 AND
v_UpdateCIs.IsEnabled=1 AND
sys.operatingSystem0 NOT LIKE '%SERVER%' AND
(li.title=SoftwareUpdateGroup)
GROUP BY
li.title )

SELECT
SoftwareUpdateGroup,
TotalCompliance,
ServerCompliance,
WorkstationsCompliance

FROM
@TempTableVariable
ORDER BY
SoftwareUpdateGroup 

Getting started with PowerBI

thumbnail

PowerBI has been around for a while now, it’s being constantly being developed by a great team over at Microsoft. It also proves to be a great tool for visualising large sets of data which makes it ideal for carving up everything in your SCCM DB.

I’ve built a number of dashboards for presenting dynamic data of your environment on 55″ LCD TVs. It’s a great way to present data and gain visibility of any problems you might be having in your environment

First thing you’re going to want to do is go and pick up the latest copy of PowerBI from here. Then you can simply create queries like you would for a normal SQL report, make sure that the account you’re connecting with has read rights to your CM DB though.

If you’re not too crash hot on building SQL queries or want something up and running pretty quick then you can’t go past this solution template for System Center Configuration Manager

 

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