SCCM Collection queries

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%

____________________________________________________________________________________________________________________