SCCM All Computers, their OU and last Hardware Scan

A quick Report to return all of the computers, the OU they are in and the last time they had  a Hardware scan.

First we create a temporary table to hold the 1st pass results, then we query that table looking for the longest OU for each computer ( this way we only get 1 record per computer returned, otherwise each level of the OU a computer is a member of will be returned)

CREATE TABLE #OU_TEMP (
ResourceID int not null,
Hostname varchar(50),
OU varchar(255),
LastHWScan datetime,
)

INSERT INTO #OU_TEMP (ResourceID, Hostname, OU, LastHWScan)
(SELECT v_R_System.ResourceID,
	   Netbios_name0,
	   System_OU_Name0,
	   LastHWScan
 FROM v_R_System
 INNER JOIN v_RA_System_SystemOUName
   ON v_R_System.ResourceID = v_RA_System_SystemOUName.ResourceID
 INNER JOIN v_GS_WORKSTATION_STATUS
   ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
)

SELECT
  Hostname,
  OU,
  LastHWScan AS [Last Hardware Scan (UTC)]
FROM #OU_TEMP AS A
WHERE LEN(OU) >=
( SELECT MAX(len(OU))
  FROM #OU_TEMP
  WHERE ResourceID=A.ResourceID
)
AND NOT EXISTS
( SELECT 1
  FROM #OU_TEMP
  WHERE ResourceID=A.ResourceID
  AND LEN(OU)=LEN(A.OU)
  AND OU < A.OU
)
ORDER BY Hostname

DROP TABLE #OU_TEMP

2 thoughts to “SCCM All Computers, their OU and last Hardware Scan”

  1. For a single machine the edit should be easy enough.

    Just add a var called @machinename and in the 1st SELECT Statement add a WHERE Clause:
    WHERE Netbios_name0 like @machinename

    To limit to numbers per pages you’ll need to create an SSRS report in VS BI 2008 and limit your table to X results where x is the number per page.

    I can’t do that as I don’t have access to any datasources / SCCM servers currently. But if you open up some of the other SSRS reports I’ve done for SCCM / ConfigMgr then you could quite easily edit one of those. Patch Compliance report might be a good one to look at.

  2. Really cool this one

    Can you make one with a prompt per machine or per collection? I have more than 3000 machines and the results can not get in 1 screen.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This blog is kept spam free by WP-SpamFree.