SCCM WQL Query: Dealing with X86 and X64 Systems

This one actually comes by request in the comments section of a previous post. I haven't bothered to post on this subject because I was of the opinion that plenty of other blogs had already covered this scenario. However, you asked so I suppose it doesn't hurt to go over how I address OS Architecture in my WQL queries. As with my WQL queries for Windows 8-2012 I just create a series of basic core queries that I then just "tweak" to fit my needs. Here they are:

Basic "OS Architecture" Queries (returns All Systems for specified architecture):

select distinct 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.SystemType = "x86-based PC"

select distinct 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.SystemType = "X64-based PC"

OS Architecture Queries filtered by OS Type (Client/Server):
Note how we look for any systems that are NOT servers to get our list of clients.

select distinct 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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption not like "%Server%"

select distinct 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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Server%"

OS Architecture Queries filtered by OS Version:

select distinct 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 inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.SystemType = "x86-based PC" and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows 7%"

In the above code just specify desired OS Architecture for "SMS_G_System_COMPUTER_SYSTEM.SystemType" and substitute desired OS version for "SMS_G_System_OPERATING_SYSTEM.Caption". For example:

• Use "%2000%" for Windows 2000
• Use "%Windows XP%" for Windows XP
• Use "%Server 2003%" for Windows 2003
• Use "%Vista%" for Windows Vista
• Use "%Serverr 2008%" for Windows 2008 Non-R2 (No, the extra "r" is not a typo)
• Use "%Windows 7%" for Windows 7
• Use "%Server 2008 R2%" for Windows Server 2008 R2
• Use "%Windows 8%" for Windows 8
• Use "%Server 2012%" for Windows 2012

Hope you guys find this useful, and if anyone knows a better method feedback is always welcome.

SQL Query to report SCCM client version and install status (extended)

I needed to extend the SQL query to report SCCM client version and install status that I wrote on July 22 to include systems where the client was Null. This is what I came up with:


select distinct SYS.Name0 as 'Machine Name', ISNULL(SYS.User_Name0, 'None') as 'Login ID', ISNULL(SYS.User_Domain0, 'None') as 'Domain', ISNULL(USR.Full_User_Name0, 'None') as 'Full Name', case when SYS.Client0 = 1 then 'Yes' when SYS.Client0 = 0 then 'No (or Inactive)' when SYS.Client0 is null then 'None' else convert(varchar(2), SYS.Client0) end as 'Client Installed', ISNULL(SYS.Client_Version0, 'None') as 'Client Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection CN on FCM.CollectionID = CN.CollectionID left outer join v_R_User USR on SYS.User_Name0 = USR.User_Name0 where CN.Name = 'All Systems' and ( SYS.Client0 = 0 or SYS.Client0 is null )

This is useful for tracking down those stale AD records.

SQL Query to report SCCM client version and install status

Put this together to generate a list of our SCCM clients and report on their install version and status:


select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', case when SYS.Client0 = 1 then 'Yes' when SYS.Client0 = 0 then 'No' else convert(varchar(2), SYS.Client0) end as 'Client Installed', SYS.Client_Version0 as 'Client Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection CN on FCM.CollectionID = CN.CollectionID join v_R_User USR on SYS.User_Name0 = USR.User_Name0 where CN.Name = 'All Systems'

SCCM WQL Query: Windows 8 and Server 2012 Systems

This weekend we'll be upgrading our SCCM 2012 server to SCCM 2012 SP1. In addition to a number of new features this update also adds support for Windows 8 and Server 2012 systems. To support these new OSes I've created new WQL queries to build collections for these operating systems. While I was doing that I took the opportunity to just clean up all my OS version collection queries. I came up with a system that (I feel) works for just about any OS version-specific deployment scenario you might face. It involves the use of 3 core queries that you just "tweak" to fit your needs. Here they are:

Basic "OS Type" Query (substitute "%Server%" for "%Workstation% for Server OSes):

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

OS version "LIKE" query (Further refines the above query by OS version):

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%" and SMS_R_System.OperatingSystemNameandVersion like "%6.2%"

OS version "NOT LIKE" query (Returns the opposite of the above 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.OperatingSystemNameandVersion like "%Server%" and SMS_R_System.OperatingSystemNameandVersion not like "%6.2%"

To to use these queries to create a collection for any OS version you need simply substitute the desired OS version number in the above code. For example:

• Use "%5.0%" for Windows 2000
• Use "%5.1%" for Windows XP
• Use "%5.2%" for Windows 2003
• Use "%6.0%" for Windows Vista-2008 Non-R2
• Use "%6.1%" for Windows 7-2008 R2
• Use "%6.2%" for Windows 8-2012

Using these three core queries I put together a fantastic set of OS Type and Version collections that (so far) have suited practically all my OS-specific deployment needs:

SCCM SQL Query: Multiple program versions in Add/Remove (with Publisher and full Username)

Management needed me to put together a SQL report for multiple versions of the same application. This particular case proved tricky because the logic involved including an "or" for the application versions and an "and" for the publisher in the "where" section. This query did the trick for me:

select distinct SYS.Name0 as 'Machine Name', SYS.User_Name0 as 'Login ID', SYS.User_Domain0 as 'Domain', USR.Full_User_Name0 as 'Full Name', ARP.DisplayName0 as 'Product Name', ARP.Publisher0 as 'Publisher', ARP.Version0 as 'Version' from v_R_System SYS join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID join v_Collection COL on FCM.CollectionID = COL.CollectionID join v_R_User USR on SYS.User_Name0 = USR.User_Name0 join v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID where COL.Name = 'All Systems' and ARP.Publisher0 = 'Solidworks Corporation' and ( ARP.DisplayName0 like 'SolidWorks 2010%' or ARP.DisplayName0 like 'SolidWorks 2011%' or ARP.DisplayName0 like 'SolidWorks 2012%' )
Hope you find it useful.

SCCM SQL Query: Disk Usage Reports

I can't take credit for all the SQL in this report, but I did tweak it enough to make it my own. Just change "FCM.CollectionID = 'SMS000DS'" to reflect the Collection ID of whatever Collection you want to run the report against. In my case I'm using the built-in collection for all servers:

 List all servers with Disk Usage Summary:

SELECT DISTINCT FCM.Name AS 'Computer Name', LDSK.DeviceID0 'Drive Letter', LDSK.Description0, LDSK.VolumeName0, LDSK.FreeSpace0 AS 'Free Space (MB)', LDSK.FreeSpace0/1024 AS 'Free Space (GB)', LDSK.FreeSpace0*100/LDSK.Size0 AS 'Free Space (%)', LDSK.Size0/1024 AS 'Total Space (GB)' FROM v_FullCollectionMembership FCM JOIN v_GS_LOGICAL_DISK LDSK on FCM.ResourceID = LDSK.ResourceID JOIN v_R_System SYS ON FCM.ResourceID = SYS.ResourceID WHERE LDSK.DriveType0 = 3 AND LDSK.Size0 > 0 AND FCM.CollectionID = 'SMS000DS' ORDER BY FCM.Name, LDSK.DeviceID0

SCCM SQL Query: List all systems by 'Computer Description'

Upper management made the decision to create a standardized convention for computers on our network based on the Computer Description field in Windows. They wanted a quick report to show what was already there for everything on the network. This query did the trick :

SELECT DISTINCT CS.Name0 AS 'Computer Name', CS.UserName0, OS.Description0 AS 'Computer Description' FROM v_GS_OPERATING_SYSTEM OS, v_GS_COMPUTER_SYSTEM CS WHERE CS.ResourceID = OS.ResourceID ORDER BY CS.Name0
Hope you find it useful.

SCCM SQL Query: Count known Service Accounts

This query helped me use SCCM to track down some service accounts that were running under the credentials of a recently terminated employee:

SELECT v_GS_SERVICE.StartName0 AS 'Service Account', COUNT(v_GS_SERVICE.StartName0) AS 'Count' FROM v_GS_SERVICE GROUP BY v_GS_SERVICE.StartName0 ORDER BY v_GS_SERVICE.StartName0
Very useful, especially since the services were failing to run because we suspended the account in AD as part of our term process! SCCM to the rescue...