MFfn7

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

Notes:

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.

Cheers!

 

 

 

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s