Systems with Inventories < 14 Days

Posted on Updated on

This is a little SQL web report I created that identifies systems which have not reported hardware or software inventories within the last 14 days.

SELECT DISTINCT
    SYS.Netbios_Name0,
    HWSCAN.LastHWScan,
    SWSCAN.LastScanDate
FROM v_R_System SYS
LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
WHERE fcm.CollectionID='YourCollectionID' and (
    DATEDIFF(DAY, HWSCAN.LastHWScan, GETDATE()) > 14 or
    DATEDIFF(DAY, SWSCAN.LastScanDate, GETDATE()) > 14 or
    HWSCAN.LastHWScan is null or
    SWSCAN.LastScanDate is null )
ORDER BY SYS.Netbios_Name0

Note: you’ll want to change the collection ID to be the one you need.  Or modify the code to prompt for a collection would probably be even better still!

Advertisements

2 thoughts on “Systems with Inventories < 14 Days

    Christoph Voigt said:
    July 13, 2009 at 12:26 am

    Don’t forget to change the Collection ID ;)

    Nicolas Moseley responded:
    July 13, 2009 at 4:52 am

    Ooops, yes. LOL. I’ll add a note to the post as well. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s