WQL

We have another WQL query here for you today to enable you to create a Configuration Manager collection based on whether a device is used as a domain controller.

Go ahead and create a new device collection and then use this query:

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

Save the query and you should now see a collection of all your domain controllers.

Just be careful with copying and pasting in case the formatting of the symbols comes out incorrectly. Check these first if your collection fails to import and devices.

More Queries

Our full range of SQL AND WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

If you are looking for a SQL query to pull back hardware inventory information and the SCCM client version, then you can use the below to pull all this information together.

SELECT
S.Name0 as Name,
S.Client_Version0 as [Cient Version],
OS.Caption0 as [Operating System],
CS.Manufacturer0 as Manufacturer,
CS.Model0 as Model,
PB.SMBIOSBIOSVersion0 as BIOS,
PR.Name0 as CPU,
SUM(PM.Capacity0) as Memory,
SUM(LD.Size0)as [Disc Size],
SUM(LD.FreeSpace0) as [Disc Free]
FROM
v_R_System S
join v_GS_OPERATING_SYSTEM OS on S.ResourceID = OS.ResourceID
join v_GS_PHYSICAL_MEMORY PM on S.ResourceID = PM.ResourceID
join v_GS_COMPUTER_SYSTEM CS on S.ResourceID = CS.ResourceID
join v_GS_LOGICAL_DISK LD on S.ResourceID = LD.ResourceID
join v_GS_PC_BIOS PB on S.ResourceID = PB.ResourceID
join v_GS_PROCESSOR PR on S.ResourceID = PR.ResourceID
WHERE LD.DeviceID0 = ‘C:’
GROUP
BY S.ResourceID, S.Name0, S.Client_Version0, OS.Caption0, CS.Manufacturer0, CS.Model0, PB.SMBIOSBIOSVersion0, PR.Name0

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.

If you are looking for a SCCM collection query for virtual machines in your environment, then you can use this query below:

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.ResourceId not in
(select SMS_R_SYSTEM.ResourceID 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.Model like "%Virtual%")