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.
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!
- 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)
- 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.
- 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.
- Execute the SQL statement using the procedure sp_executeSQL
THE SQL CODE
WHEN EXISTS (SELECT ResourceID FROM '+InvClassName+' WHERE ResourceID =
sys.ResourceID) THEN ''X'' END AS ['+InvClassName+']'
-- (prepend to beginning of SQL statement)
-- (append to end of SQL statement)
= @STATEMENT + '
dbo.v_R_System_Valid AS sys
EXECUTE sp_executesql @STATEMENT
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