Trying to join Last Collection Date info to custom Hardware Inventory Search report
I'm trying to add "Last Collection Date" from the canned Computers that have not reported inventory in last N days report over to my custom created Hardware Inventory Search report. I successfully joined it and it matches up however now I'm seeing duplicates of some machines with two different Collection Dates.
I went to Manage > Computers and verified only one computer shows up there. Any other reports only show one as well.
When I go back to the canned Computers that have not reported inventory in last N days report and run it at 1 day looking for that specific computer it only shows up once so it's maybe something with how I'm joining the dbo.Inv_Inventory_Results?
Below is my SQL for the report:
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
mhco.TermDate as 'Warranty Expiration',
i.Name [Computer Name],
cs.[Identifying Number] [Serial Number],
cs.Manufacturer [System Manufacturer],
cs.Model [Computer Model],
i.[System Type] [Computer Type],
r.[Collection Time] [Last Collection Date],
CASE WHEN be._ResourceGuid IS NOT NULL THEN 'ENABLED' ELSE 'DISABLED' END [SMBIOS Enabled],
ltrim(rtrim(str([SMBIOS Major Version]))) + '.' + ltrim(rtrim(str([SMBIOS Minor Version])))[SMBIOS Version],
'ENABLED' [DMI Enabled],
'' [DMI Version]
FROM dbo.vComputer i
JOIN dbo.ScopeMembership sm
ON sm.[ResourceGuid] = i.Guid
AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))
LEFT JOIN dbo.Inv_AeX_AC_Identification d
ON d._ResourceGuid = i.Guid
JOIN dbo.vHWComputerSystem cs
ON i.Guid = cs._ResourceGuid
LEFT JOIN dbo.vSWBIOSElement be
ON be._ResourceGuid = i.Guid
LEFT JOIN dbo.vSWSMBIOSElement sbe
ON sbe._ResourceGuid = i.Guid
LEFT JOIN Inv_MHC_OwnedPC mhco
ON mhco.ServiceTag=cs.[Identifying Number]
LEFT JOIN dbo.Inv_Inventory_Results r
ON r.[_ResourceGuid] = i.Guid
WHERE i.Name LIKE '%Computer Name%'
AND cs.Manufacturer LIKE '%Manufacturer%'
AND cs.Model LIKE '%Model%'
ORDER BY i.Name ASC