Below is the SQL code needed to retrieve all of the collections from ConfigMgr. This is useful when creating custom reports that need to be scoped to a specific collection. The query below selects the CollectionName and CollectionID columns and sorts the CollectionName column alphabetically.

SELECT        CollectionName, CollectionID
FROM            v_Collections
ORDER BY CollectionName

Results from the query on my test system.

The last hardware scan is stored in the view v_GS_WORKSTATION_STATUS. When joined with either v_R_System_Valid or v_R_System, you can retrieve the last hardware scan for a computer.

The SQL code below will return the last hardware scan date for all computers in the ConfigMgr system.

SELECT        v_R_System_Valid.Netbios_Name0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM            v_GS_WORKSTATION_STATUS INNER JOIN
                         v_R_System_Valid ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_System_Valid.ResourceID

If you need to return the last hardware scan for a single computer you can use the query below to filter by the computer name. In this example, we filtered on computer ‘pc01’.

SELECT        v_R_System_Valid.Netbios_Name0, v_GS_WORKSTATION_STATUS.LastHWScan
FROM            v_GS_WORKSTATION_STATUS INNER JOIN
                         v_R_System_Valid ON v_GS_WORKSTATION_STATUS.ResourceID = v_R_System_Valid.ResourceID
WHERE        (v_R_System_Valid.Netbios_Name0 = N'pc01')