This Wiki Page serves as a quick repository for ConfigMgr 2007 and SMS 2003 queries. Edit the page to add your own, or just copy and paste to use in your own environment. Don't want to mess with adding to the Wiki yourself? Send your queries to
admin@myITforum.com and we'll post them for you - with attribution, of course!
Java Platform Query
This query returns: Name, Operating System and Version, Version of Java.
select distinct SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_ADD_REMOVE_PROGRAMS.Version, 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 "%Java 2 Runtime Environment%"
Prompted OU
Prompted OU based returns System and User info including user FULL NAME
select distinct SMS_R_System.Name, SMS_R_System.DirectoryLocation,
SMS_R_System.OperatingSystemNameandVersion,
SMS_R_System.DaysSinceADActivity, SMS_R_System.LastLogonUserName,
SMS_R_User.FullUserName, SMS_R_User.UserOUName from SMS_R_System full join
SMS_R_User on SMS_R_System.LastLogonUserName=SMS_R_User.UserName where
SMS_R_System.systemouname like ##PRM:SMS_R_System.systemouname##
Prompted OU(Directory Location)
Prompted OU(Directory Location) - Returns Last Logon User and OU
select distinct SMS_R_System.Name, SMS_R_System.DirectoryLocation,
SMS_R_System.OperatingSystemNameandVersion,
SMS_R_System.DaysSinceADActivity, SMS_R_System.LastLogonUserName,
SMS_R_User.FullUserName, SMS_R_User.UserOUName from SMS_R_System full join
SMS_R_User on SMS_R_System.LastLogonUserName=SMS_R_User.UserName where
SMS_R_System.DirectoryLocation like ##PRM:SMS_R_System.DirectoryLocation##
Prompted OU via System Disc
Prompted OU via System Disc -returns PC info
select distinct
SMS_R_System.*, SMS_G_System_OPERATING_SYSTEM.CSDVersion from SMS_R_System
inner join SMS_G_System_OPERATING_SYSTEM on
SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where
SMS_R_System.SystemOUName like ##PRM:SMS_R_System.SystemOUName##
All computers that are in a state of pending restart
Pasting this query as a new collection will create a collection containing all computers that are in a state of pending restart we can now advertise the reboot package to those computers. - Edan Yona
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
Clients Pending reboot
- Christopher Stauffer
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
Clients that failed to install an update
- Christopher Stauffer
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_UpdateComplianceStatus on SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid where SMS_UpdateComplianceStatus.LastEnforcementMessageID = 11
Clients waiting for another installation to complete
- Christopher Stauffer
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 = 3
Inventory for Spyware Query
- Michael Mott
LINK:
http://www.myitforum.com/myITWiki/SMSSpyware.ashx
Query for Newly Discovered Systems
- Aurélien BONNIN
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_SYSTEM
on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId
where
DATEDIFF(dd, SMS_G_System_SYSTEM.TimeStamp,GetDate()) < 30
Identifying Collections A Computer Is in
- Chris Nackers: This query will show you what collections a computer is a member of. Thanks to Todd and Brian on the
SCCM myITForum list.
Select
v_R_System.Name0, v_Collection.Name
From v_R_System
Left join v_FullCollectionMembership on v_FullCollectionMembership.ResourceId = v_R_System.ResourceId
Left join v_Collection on v_FullCollectionMembership.CollectionId = v_Collection.CollectionId
Where Name0 = computer you're looking for
Computers that have a laptop enclosure
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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where
SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ( "8", "9", "10", "14" )
Identfying Software based on Executables
- AndersonK
SELECT
VRS.Name0 as 'Computer Name',
VRS.User_Name0 as 'User Name',
SF.FileName as 'File Name',
SF.FileDescription as 'File Description',
SF.FilePath as 'File Path',
SF.ModifiedDate as 'Modified Date'
FROM
v_R_System VRS,v_GS_SoftwareFile SF
WHERE SF.ResourceID = VRS.ResourceId
AND SF.FileDescription = 'Google Talk'
ORDER BY VRS.Name0
OS and Service Pack with Count¶
- AndersonK
SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM,v_R_System
WHERE v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0