Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.

Trying to join Last Collection Date info to custom Hardware Inventory Search report

Created: 28 Dec 2012 • Updated: 31 Dec 2012 | 4 comments
This issue has been solved. See solution.

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%'
SELECT DISTINCT
mhco.TermDate as 'Warranty Expiration',
i.Name [Computer Name],
cs.[Identifying Number] [Serial Number],
i.[User],
i.[IP Address],
i.[MAC Address],
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

Comments 4 CommentsJump to latest comment

Andrew Bosch's picture

Inv_Inventory_Results is going to have 1 entry per Inventory policy per computer.  Which Inventory are you concerned about?  There is Full, Delta Software, Delta Hardware...

------------------------------------
Sr. Principal SQA Engineer
Symantec

Matt Damon's picture

Oh I didn't realize it seperated them out like that. Basically it's just to check to verify they're still active on our network. I would guess the Full would be best?

That "other" Matt Damon.

Andrew Bosch's picture

OK so I was slightly incorrect in my previous post.  Inv_Inventory_Result does not have one entry per Inventory policy, rather, it has one entry per agent that comes with Inventory (Inventory vs. Server Inventory vs. Application Metering).  

So, your JOIN should be this to make sure we only get the latest Inventory results:

 

LEFT JOIN dbo.Inv_Inventory_Results r    

 ON r.[_ResourceGuid] = i.Guid  

 AND r.Agent = 'Inventory Agent'

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION