Adding a Collection ID prompt to “Computers not inventoried recently…”

There was a question asked on the Technet forum today on how to make the builtin report in SCCM 2007 “Computers not inventoried recently (in a specified number of days)” to be filtered per Collection and prompt for a CollectionID.

The updated report looks like this, the changes made are in bold letters:

SELECT INST.SMS_Installed_Sites0, SYS.Netbios_Name0, SYS.User_Domain0, SYS.User_Name0, @varcollection as 'Collection', SYS.Resource_Domain_OR_Workgr0, HWSCAN.LastHWScan, DateDiff(Day,

CASE WHEN IsNULL(HWSCAN.LastHWScan,'1/1/1980') > IsNULL(SWSCAN.LastScanDate,'1/1/1980')

THEN HWSCAN.LastHWScan

ELSE SWSCAN.LastScanDate

END

,GetDate()) AS C053,

SWSCAN.LastScanDate, SWSCAN.LastCollectedFileScanDate

FROM v_R_System SYS

LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID

LEFT OUTER JOIN v_RA_System_SMSInstalledSites INST ON SYS.ResourceID = INST.ResourceID

Left join v_FullCollectionMembership FCM on FCM.ResourceID = SYS.ResourceID

WHERE

DateDiff(Day,

CASE WHEN IsNULL(HWSCAN.LastHWScan,'1/1/1980') > IsNULL(SWSCAN.LastScanDate,'1/1/1980')

THEN HWSCAN.LastHWScan

ELSE SWSCAN.LastScanDate

END

,GetDate()) >= @variable

AND Client0=1 and FCM.CollectionID = @varcollection

Order By INST.SMS_Installed_Sites0, SYS.Netbios_Name0

To implement it either change the report or paste this in a new report and create to Prompts, one for Days called variable and one for CollectionID called varcollection.
query

There is no prompt query for the original Report ID:74 as it is days it asks for, the prompt query for CollectionID can be found here:

begin

if (@__filterwildcard = '')

select distinct CollectionID, Name from v_Collection order by Name

else

select distinct CollectionID, Name from v_Collection

WHERE CollectionID like @__filterwildcard

order by Name

end

Add a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.