UPDATE: This is an update to yesterday's post which used dynamic SQL to show missing hardware inventory in ConfigMgr.
I noticed that we had like 95+ classes, but many of them had NO records for any clients whatsoever. To me, that indicates perhaps a class that doesn't have anything turned on for it and is thus a waste of space to have in the query. So I quickly wrote a little more to first find only those classes which have at least one row and only use those classes in the list of hardware class columns. Here's the updated code:
-- FIRST, FIND ONLY THOSE CLASSES THAT HAVE AT LEAST 1 RECORD.
-- NO NEED TO SHOW COLUMNS IF THE CLASS IS EMPTY...RIGHT?
DECLARE @STATEMENT
NVARCHAR(MAX)
-- GET TOP 1 FROM EACH CLASS (EMPTY CLASSES WON'T RETURN ROWS)
SELECT @STATEMENT
= COALESCE(@STATEMENT + '
UNION
ALL
','')+ 'SELECT TOP 1 '''+InvClassName+''' AS [View] FROM '+InvClassName
FROM dbo.v_GroupMap;
-- CREATE A TEMP TABLE TO HOLD THE CLASS NAMES
CREATE TABLE #tmpClassNames (
[Classes] VARCHAR(255)
)
-- PUT THE CLASSES INTO A TEMP TABLE
INSERT INTO #tmpClassNames
execute sp_executesql @statement;
-- CLEAR THE SQL STATEMENT SO WE CAN USE IT AGAIN
SET @STATEMENT
= NULL
-- BUILD THE COLUMNS THAT WILL HOLD THE INVENTORY CLASSES USING THE TEMP
TABLE ABOVE
SELECT @STATEMENT
= COALESCE(@STATEMENT+',
','')+'CASE
WHEN EXISTS (SELECT ResourceID FROM '+Classes+' WHERE ResourceID =
sys.ResourceID) THEN ''X'' END AS ['+Classes+']'
FROM
#tmpClassNames;
-- WRAP THAT STATEMENT WITH SOME OTHER CODE TO MAKE A COHERENT SQL
STATEMENT.
SET @STATEMENT
= '
SELECT
ResourceID,
Netbios_Name0,
'+@STATEMENT + '
FROM
dbo.v_R_System_Valid AS sys
ORDER
BY Netbios_Name0'
PRINT @STATEMENT
--JUST TO SEE IT :)
-- EXECUTE THE SQL AND SEE THE OUTPUT
EXECUTE sp_executesql @STATEMENT
-- CLEANUP
DROP TABLE #tmpClassNames