Name: All sccm clients requiring a reboot
Query: 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 AS sms_r_system
inner join SMS_UpdateComplianceStatus as c on c.machineid=sms_r_system.resourceid
where c.LastEnforcementMessageID = 9
____________________________________________________________________________________________________________________________
Name: All SCCM client computers in a specific site
Description : show all SCCM client computers in a specific Active directory site , in this case the site name is “India”
Query : 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.ADSiteName =’India’ and client=1
—————————————————————————————————————————–
Name: SCCM 2007 R3 client
Description : All SCCM clients with R3 version
Query : 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 “4.00.6487.2187″ or SMS_R_System.ClientVersion like “4.00.6487.2157″
_________________________________________________________________________________________
Name: All windows servers with client install
Description : show all SCCM servers with sccm client installed on them
Query :
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 OperatingSystemNameAndVersion like ‘Microsoft Windows NT%Server%’ and client=1
_________________________________________________________________________________________
Name : All Windows 7 Computers
Query :
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 OperatingSystemNameandVersion like ‘%Workstation 6.1%’
_________________________________________________________________________________________
Name : Users in a specific OU
Query :
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.UserOUName = “Idit.local/ISRAEL/USERS”
______________________________________________________________________________________________________
Name : Computers in a specific OU
Query :
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=”Idit.local/Israel/Computers/Accounting”
_________________________________________________________________________________________________
Name : Lync installed
Description: Collection That determine the workstations that have Lync installed (you can write any programs from “Add remove Program”)
Query:
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.ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE ‘%Lync%’)
______________________________________________________________________________________________________________________
Name: Computers with no SCCM client
Description: Collection that shows all computers that don’t have SCCM client installed
Query:
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 Client = 0 or Client is null
______________________________________________________________________________________________________________________
Name: Windows Server 2008
Description: Collection that shows all discovered Windows Server 2008
Query:
select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainOrWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.NetbiosName from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like “%Server 6.0%”
_____________________________________________________________________________________________________________________
Name: Windows 7
Description: Collection that shows all discovered Windows 7
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_R_System.OperatingSystemNameandVersion like “%Workstation 6.1%” or
SMS_R_System.OperatingSystemNameandVersion like “%Windows 7%
____________________________________________________________________________________________________________________
Name: Find Mac
Description: Query to mac address
select SMS_R_System.Name from SMS_R_System where SMS_R_System.MACAddresses like ##PRM:SMS_R_System.MACAddresses##
Sr. Cloud Solution Architect at Microsoft, I’m passionate about helping customers succeed by building secure, scalable, and innovative cloud solutions – with a strong focus on AI, DevOps practices, and end-to-end security. With a proven track record in the IT and services industry, I serve as a trusted advisor, partnering closely with organizations to guide them through digital transformation and maximize the value of their cloud investments.
My role combines technical leadership with project ownership – from designing modern architectures to leading cross-functional implementations that drive real business outcomes. I also manage and empower strategic partners, working hand-in-hand with them to ensure project success and deliver measurable business impact for customers.
