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.
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
