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