Continuing on with my mission to destroy eyeballs everywhere (or, at least those foolish enough to gaze into the stupidity of my blog, moooo-ha ha ha haaaa!)… I’m on a theme of poking a stick at SQL to wake up the sleep ConfigMgr tiger underneath (oh, wait, it’s the other way around, I think).
This query dumps out a table of devices associated in the ConfigMgr database with a particular Active Directory Site Name.
USE CM_PS1; SELECT DISTINCT dbo.v_R_System.ResourceID, dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0, dbo.v_GS_COMPUTER_SYSTEM.SystemType0, dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 FROM dbo.v_R_System LEFT OUTER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID WHERE dbo.v_R_System.AD_Site_Name0 = 'Default-First-Site-Name' ORDER BY Name0
To beat this dead horse even more, you can fetch a list of the distinct/unique AD site names first, and then start up a coal-fired iteration loop to churn through each one, and query the machines associated with it.
USE CM_PS1; SELECT DISTINCT AD_Site_Name0 FROM dbo.v_R_System WHERE AD_Site_Name0 IS NOT NULL
However, if you want to show devices which are not associated with an AD Site Name, you’d want to include those which are assigned NULL. But rather than bending over and taking a NULL up the you-know-what in your report outputs, which is ugly, believe me, you can substitute a new (string value) name in place of it. This is easiest with the COALESCE function, but you can do this several other ways as well…
USE CM_PS1; SELECT DISTINCT COALESCE(AD_Site_Name0, 'NONE') AS AD_Site_Name FROM dbo.v_R_System
Just remember to handle the “NONE” value as IS NULL, when pulling the list of machines for the matching condition.
Now, if you just want to tally up the sum total of devices per AD Site Name, you can use another simple query with a GROUP BY clause, and toss in a teaspoon of that smelly COALESCE sauce for garnishing…
USE CM_PS1; SELECT DISTINCT COALESCE(AD_Site_Name0, 'NONE'), COUNT(*) AS Devices FROM dbo.v_R_System GROUP BY AD_Site_Name0
You don’t necessarily have to include the “USE CM_PS1;” statement every time. If you’re running these queries inside SQL Management Studio, you can either change the database drop-down selector (top-left in the ribbon menu), or use the USE statement once at the top of the query window. After that, it should “stick” for all subsequent queries.