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

SCCM Collection: Dynamically Identifying Users that have had their mailbox migrated to O365

One of the challenges I’ve recently had is identifying users that have had their mailbox migrated to Office365. This becomes quite a task if like me you’re using InTune integrated with SCCM but need separate mail profiles for mobile users. Now this isn’t an issue if you’re happy to manually maintain an AD group which can then be referenced by SCCM, however I want this to dynamically be updated and not rely on Humans.

John Bailey, has written an excellent article on some key AD attributes which can be used to identify whether the mailbox is on-prem or in O365. For my environment I can simply extend my Active Directory User Discovery to include the attribute ‘msExchRecipientDisplayType’ and then use a WQL query to identify the users that have been migrated.

Common values:

Value

Object Type

1

User Mailbox

2

Linked Mailbox

4

Shared Mailbox

2048

Dynamic Distribution Group

2147483648

Remote Mailbox (O365)

The query for where the mailbox has been migrated to Office365:

SELECT *
FROM
SMS_R_User
WHERE
SMS_R_User.msExchRecipientTypeDetails = 2147483648

Most used WQL Queries

Here are some of my most used WQL Queries for collection creation.

User is in AD group

SELECT
SMS_R_USER.ResourceID,
SMS_R_USER.ResourceType,
SMS_R_USER.Name,
SMS_R_USER.UniqueUserName,
SMS_R_USER.WindowsNTDomain
FROM
SMS_R_User
WHERE
SMS_R_User.UserGroupName = "DOMAIN\\GROUP"

Device is in AD Group

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 
WHERE
SMS_R_System.SystemGroupName = "DOMAIN\\GROUP"

Device is in OU

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 
WHERE 
SMS_R_System.SystemOUName = "DOMAIN.LOCAL/OU/OU"

Devices where the Primary User is in a specific AD Group

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 
JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName 
JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName 
WHERE 
SMS_UserMachineRelationship.Types=1 AND 
SMS_R_User.UserGroupName="DOMAIN\\USER GROUP"

Specific Application is installed

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId 
INNER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId 
WHERE 
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Name from Add/Remove Programs" OR 
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Name from Add/Remove Programs"

Specific Application is installed but has not been used in >= 90 days

I always limit this collection with a Specific Application is installed collection.

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_CCM_RECENTLY_USED_APPS on SMS_G_System_CCM_RECENTLY_USED_APPS.ResourceID = SMS_R_System.ResourceId
WHERE
SMS_G_System_CCM_RECENTLY_USED_APPS.ExplorerFileName = "SameEXEasMetering.EXE" AND
DATEDIFF(day, LastUsedTime, GETDATE()) > 90 order by SMS_R_System.Name

By Architecture

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.SystemType = "x86-based PC/X64-based PC"

All Workstations

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 
WHERE
SMS_R_System.operatingSystem NOT LIKE "%Server%"

All Servers

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
WHERE
SMS_R_System.operatingSystem LIKE"%Server%"

Clients with specific CCM version

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
WHERE
SMS_R_System.ClientVersion LIKE "5.00.8325%"