SCCM Device collection Query

by

SCCM Device collection Query based on the Device name.
SELECT * FROM SMS_R_System 
WHERE SMS_R_System.Name IN ("DeviceName1", "DeviceName2", "DeviceName3")
For devices whose names contain certain patterns or partial matches, you can use multiple LIKE conditions:
SELECT * FROM SMS_R_System 
WHERE SMS_R_System.Name LIKE "%PartialName1%"
OR SMS_R_System.Name LIKE "%PartialName2%"
OR SMS_R_System.Name LIKE "%PartialName3%"

SCCM Device collection Query for removing inactive devices based on the number of days.

select SMS_R_SYSTEM.ResourceID, 
       SMS_R_SYSTEM.ResourceType, 
       SMS_R_SYSTEM.Name, 
       SMS_R_SYSTEM.SMSUniqueIdentifier, 
       SMS_R_SYSTEM.ResourceDomainORWorkgroup
from SMS_R_System 
where DATEDIFF(day, SMS_R_SYSTEM.LastLogonTimestamp, GETDATE()) > 30

Leave a Comment