Skip to main content

SCCM Queries

By


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

Labels:

Comments

  1. Willie AamesOctober 13, 2015 at 1:07 AM

    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

Post a Comment

Popular posts from this blog

SCCM Unknown computer not able to see Task Sequences after installing Current Branch 1702

By
Soon after installing SCCM CB 1702 we were unable to see Task Sequences deployed to the unknown collection. This issue was identified as a random system taking the GUID of the 'x64 Unknown Computer (x64 Unknown Computer)' record. As a result it was now a known GUID; as we were only deploying Task Sequences to the Unknown collection none were made available. 'x64 Unknown Computer (x64 Unknown Computer)' record 'x86 Unknown Computer (x86 Unknown Computer)' record To get the GUID of your unknown systems open SQL management studio and run the following command: --Sql Command to list the name and GUID for UnknownSystems record data select ItemKey, Name0,SMS_Unique_Identifier0 from UnknownSystem_DISC Using the returned GUID (SMS_Unique_Identifier0) we can find the hostname that has been assigned the 'x64 Unknown Computer (x64 Unknown Computer)' GUID by running the query below. --x64 Unknown Computers select Name0,SMS_Unique_Identifier0,Decommissioned0 from Sys...
170 comments
Read more

Windows 7 Offline files will not go Online when connected to network

By
Issue Several laptop users move between networks, domain, home, etc and when they attempt to access DFS shares explorer status is working offline.  The issue only resolves it self after a reboot. Connecting directly to the share works and i am able to ping network resources.  This behavior occurs for VPN users as well. Possible Causes "slow-link mode". In win7 (with default settings) a client will enter slow-link mode if the latency to the server is above 80ms. In slow-link mode all writes are made to the local cache and a background sync only happens every 6 hours.  Depending on your connection the default slow link detection speed is 64,000 bps On client computers running Windows 7 or Windows Server 2008 R2, a shared folder automatically transitions to the slow-link mode if the round-trip latency of the network is greater than 80 milliseconds, or as configured by the "Configure slow-link mode" policy. After transitioning a folder to the slow-link mode, Offline Fil...
152 comments
Read more

Sharepoint documents will not open in Word/Excel

By
Sharepoint documents will not open in Word/Excel Today I have been dealing with a very interesting Office 365 / SharePoint issue. It was reported that the Edit in Application button within SharePoint i.e. “Edit in Excel” “Edit in Word” is not working correctly and results in an empty Spreadsheet or empty Word document. Viewing and Editing within the browser is fully functional and the behaviour varies depending on computers within specific OU in active directory.  It is worth noting at this point the estate consisted of Office 2013 ProPlus installations running on Windows 8.1. The fact that identical systems with identical software versions patch to the same level, resulted in different behaviour could only mean Group Policy was different between them. I was quickly able to find the offending policy; " Block signing into Office: Enabled ". The screen shot above details a systems policy with the issue. With the Standard Workstation GPO's the policy "Block signing into...
11 comments
Read more