Sunday, September 13, 2015

SCCM Queries



Is Installed Query : Internet Explorer 11

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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and SMS_G_System_SoftwareFile.FilePath like "%Program Files%" and SMS_G_System_SoftwareFile.FileVersion like "11%"

Or via Applications installed (Control Panel)

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "CtrlWork%"





Search by Network Name or Last logon username


_Search by Network Name:


select Name, IPAddresses, LastLogonUserName, MACAddresses, ResourceId, ResourceType from  SMS_R_System where Name like ##PRM:SMS_R_System.Name## order by Name, IPAddresses, LastLogonUserName, MACAddresses




_Search by Last Logon Username:


select Name, LastLogonUserName, IPAddresses, MACAddresses, ResourceId, ResourceType from  SMS_R_System where LastLogonUserName like ##PRM:SMS_R_System.LastLogonUserName## order by Name, LastLogonUserName, IPAddresses, MACAddresses




_Search Application AddRemove Name:


select SMS_R_System.Name, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName, SMS_R_System.MACAddresses, SMS_R_System.ResourceId, SMS_R_System.ResourceType 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 ##PRM:SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName## order by SMS_R_System.Name, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName, SMS_R_System.MACAddresses




_Search Active Directory Security Groups:


(Contains data only from AD Security Group Discovery, not NT User Group Discovery.)

select Name, UsergroupName, WindowsNTDomain, NetworkOperatingSystem, AgentName, AgentSite, AgentTime, ResourceId, ResourceType, UniqueUsergroupName from sms_r_usergroup where AgentName = "SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT"




_Package ID:


select packageid, name from sms_package




_Collection ID:


select collectionid, name from sms_collection

_AD User group Collection


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.UserGroupName = "<Domain>\\<AD Group>"

1 comment:

  1. I was wondering if its possible to query sccm for a computer's model, vpn network and serial number. I would be using this to set the computer description which i have a script currently able to do. The device name would be supplied and SCCM would return the serial number and model

    ReplyDelete