SCCM Current Branch: Queries

Remember – if you get a syntax error while copy+pasting the query, delete and retype the quotes. These queries were taken from numerous sources.

Find Computers without App

select SMS_R_System.IPAddresses, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.LastLogonUserName, SMS_R_System.NetbiosName, SMS_R_System.Active, 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.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%Teams%")

Find Computers with App installed in Add/Remove Programs

select SMS_R_System.NetbiosName, 
 SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName 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 “Adobe Reader XI”

Find Computers in Active Directory 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 = "DomainFQDN/OU"

e.g. TORONTOIT.CO/Head Office/Workstations/Americas/Toronto

Find Users in Active Directory OU

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 = “TORONTOIT.CO/InsertOUHere/InsertOUHere”

Find Computers named Win10-XXX, e.g. Win10-Test, Win10-Prod

select SMS_R_System.NetbiosName from
 SMS_R_System
 WHERE
 SMS_R_System.NetbiosName = "WIN10%"

Find Computers with System Roles (e.g. Domain Controllers)

select
*
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.Roles like "%Domain_Controller%"

Find Computers with Service Installed (e.g. any service with Microsoft Exchange in the name)

select
*
from
SMS_R_System
inner join SMS_G_System_SERVICE
on SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_SERVICE.Name like “Microsoft Exchange%”

Find Computers with no client

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.Client is null

Find Computers that are Inactive

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_CH_ClientSummary
on SMS_G_System_CH_ClientSummary.ResourceId = SMS_R_System.ResourceId
where SMS_G_System_CH_ClientSummary.ClientActiveStatus = 0

Find computers that are Unknown computers (no client 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 where SMS_R_System.Unknown = 1

Find duplicate computers

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,
R.ResourceDomainORWorkgroup,R.Client from SMS_R_System as r
full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId
full join SMS_R_System as s2 on s2.Name = s1.Name
where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId

Once you have the collection, sort by Last Online timestamp and you’ll see half of the total # of computers in this collection that do not have a Last Online timestamp and most other details are missing for them, even if it shows the client is installed.  Then select them and delete.

Find Computers with Server OS

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%'

Find Computers with Windows Server 2012 OS

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Version = "6.2.9200" and SMS_G_System_OPERATING_SYSTEM.Name like "%server%"

Find Computers with Windows Server 2008 R2 OS

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.OperatingSystemNameandVersion like "%Server 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "%Server 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Server 6.1"

Find Computers with Windows Server 2008 OS

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 6.0%"

Find Computers with Windows Server 2003 OS

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 '%Server 5.2%'

Leave a Reply

Your email address will not be published. Required fields are marked *