30 Useful SCCM SQL Queries

Estimated reading time: 3 minutes

I’ve been creating a lot of collections with our SCCM recently, so I thought I would share the ones we thought were most useful:

Show all computers in SCCM:

SELECT * FROM v_R_System

Show all computers with a specific name:

SELECT * FROM v_R_System WHERE Name0 = 'computername'

Show all computers with a specific operating system:

SELECT * FROM v_R_System WHERE Operating_System_Name_and0 LIKE '%Windows 10%'

Show all computers with a specific IP address:

SELECT * FROM v_R_System WHERE IP_Addresses0 LIKE '10.10.10.%'

Show all installed applications on a specific computer:

SELECT * FROM v_Add_Remove_Programs WHERE ResourceID = '123456'

Show all computers with a specific application installed:

SELECT * FROM v_Add_Remove_Programs WHERE DisplayName0 LIKE '%applicationname%'

Show all computers with a specific user logged on:

SELECT * FROM v_R_User WHERE UserName0 LIKE '%username%'

Show all software updates with a specific severity:

SELECT * FROM v_Update WHERE Severity = 'Critical'

Show all software updates with a specific release date:

SELECT * FROM v_Update WHERE DatePosted >= '2022-01-01'

Show all collections with a specific name:

SELECT * FROM v_Collection WHERE Name LIKE '%collectionname%'

Show all deployments with a specific name:

SELECT * FROM v_DeploymentInfo WHERE DeploymentName LIKE '%deploymentname%'

Show all clients with a specific status:

SELECT * FROM v_ClientInfo WHERE LastDDR <> '1900-01-01 00:00:00.000'

Show all clients that have not reported in a certain number of days:

SELECT * FROM v_R_System WHERE DATEDIFF(day, LastDDR, GETDATE()) > 7

Show all clients that have not received a specific update:

SELECT * FROM v_UpdateComplianceStatus WHERE UpdateCI_UniqueID = '123456' AND ComplianceState <> 1

Show all clients that have not received any updates in a certain number of days:

SELECT * FROM v_UpdateScanStatus WHERE LastScanTime < DATEADD(day, -30, GETDATE())

Show all clients with a specific version of the SCCM client installed:

SELECT * FROM v_ClientVersion WHERE Version = '5.00.8968.1000'

Show all software update groups with a specific name:

SELECT * FROM v_AULevel WHERE Title LIKE '%updategroupname%'

Show all software update groups that have not been deployed:

SELECT * FROM v_AULevel WHERE DeploymentID IS NULL

Show all software update groups with a specific software update included:

SELECT * FROM v_AULevel WHERE UpdateID = '123456'

Show all software updates with a specific language:

SELECT * FROM v_Update WHERE Language = 'en-us'

Show all software updates that are expired:

SELECT * FROM v_Update WHERE Expired = 1

Show all software updates that are superseded:

SELECT * FROM v_Update WHERE IsSuperseded = 1

Show all software updates that have been declined:

SELECT * FROM v_Update WHERE IsDeclined = 1

Show all software updates that have not been approved:

SELECT * FROM v_Update WHERE Approval = 0

Show all software updates that have been approved for deployment:

SELECT * FROM v_UpdateDeploymentSummary WHERE DeploymentState = 2

Show all software update deployments with a specific name:

SELECT * FROM v_UpdateDeploymentSummary WHERE DeploymentName LIKE '%deploymentname%'

Show all software update deployments that have not completed:

SELECT * FROM v_UpdateDeploymentSummary WHERE DeploymentState = 1

Show all software update deployments that have failed:

SELECT * FROM v_UpdateDeploymentSummary WHERE DeploymentState = 3

Show all software update deployments with a specific software update included:

SELECT * FROM v_UpdateDeploymentSummary WHERE UpdateID = '123456'

Show all software update deployments with a specific collection:

SELECT * FROM v_UpdateDeploymentSummary WHERE CollectionName LIKE '%collectionname%'

These are just a few examples of SCCM SQL queries that you can use to get information from your SCCM database. Depending on your specific needs, you may need to modify these queries or create your own custom queries.