Number2 Blog - myITforum

My fellow ConfigMgr MVP Torsten Meringer hit me up with an interesting question this morning.  He wanted to identify those machines that are missing hardware inventory.  He mentioned the v_GroupMap view that shows you all of the hardware inventory classes (which I shamefully admit I didn't know about until he pointed it out) and manually wrote a query to show which classes are missing from which machines.  This is a pretty good idea, actually...I've done the same thing, only by writing a few exception reports where I pull all machines that are missing a particular class (For example, a report like "Machines without records in Asset Intelligence" or "Machines without records in V_GS_Computer_System") but his idea of a single report that just listed them all in a matrix was much cleaner.

Now, being the bright guy he is, he knew there was a more dynamic way of using that v_GroupMap view to write a query that shows all machines and which classes they have and don't have.  But, just because you're a great ConfigMgr engineer doesn't mean you're a SQL guy who can squeeze out dynamic SQL for something like this, so he enlisted my help.  Here's what I came up with.

OVERVIEW

I will quickly give you a background of what we're going to do, but I'm not going to go into a full primer of Dynamic SQL...in case you were wondering :)

NOTE:  Since we're talking System Center Configuration Manager 2007  (ConfigMgr 2007) I'm going to assume you have SQL 2005+.   If you don't, then pony up a couple of bucks and upgrade already!

Basic Steps:

1. Declare a variable that will hold the SQL statement that we're going to generate on the fly.  The sp_executeSQL procedure that will run the dynamic SQL statement requires that you pass it a unicode string, so declare the variable as NVARCHAR(MAX)
2. Using string concatenation, pull all of the inventory classes from v_GroupName and build the columns that will show the existence of records in each inventory class.
3. Now concatenate that string with some other strings to make one big coherent SELECT statement that will get all computers from v_R_System_Valid and show which of those classes they have or are missing.
4. Execute the SQL statement using the procedure sp_executeSQL

THE SQL CODE

--STEP 1

DECLARE @STATEMENT NVARCHAR(MAX)

--STEP 2

SELECT @STATEMENT = COALESCE(@STATEMENT+',

   ','')+'CASE WHEN EXISTS (SELECT ResourceID FROM '+InvClassName+' WHERE ResourceID = sys.ResourceID) THEN ''X'' END AS ['+InvClassName+']'

  FROM dbo.v_GroupMap;

--STEP 3

-- (prepend to beginning of SQL statement)

SET @STATEMENT = '

SELECT

   ResourceID,

   Netbios_Name0,

   '+@STATEMENT;

-- (append to end of SQL statement)

SET @STATEMENT = @STATEMENT + '

FROM

   dbo.v_R_System_Valid AS sys

ORDER BY Netbios_Name0'

--STEP 4

 EXECUTE sp_executesql @STATEMENT

THE OUTPUT

Any NULL values indicate that machine doesn't have any records for the class shown in the column header, and an 'X' indicates there is at least 1 record.

Well, I hope that gives you something to think about.

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog