Video Screencast Help

Joining Installed Software to Monthly Summary Data

Created: 27 Apr 2012 | 3 comments
Kep's picture

Hi,

I'm struggling to create a report that links information in the Inv_InstalledSoftware table to usage data in the Inv_Monthly_summary table.  My goal is to create a report that shows the installed software on a computer and, if the application is metered, to show the last used date.  The columns would look like this...

Software Name | Computer Name | Installed Date | Date Last Used

It would seem like this would be easy because the console shows similar data when you navigate to Software > Usage Tracking and click on a metered application.  That said, I just can't figure out how pull it off.  I believe the tables/views involved are:

  • vRM_Computer_Item
  • Inv_InstalledSoftware
  • vSoftwareProduct
  • ResourceAssociation (links Inv_InstalledSoftware to vSoftwareProduct)
  • Inv_Monthly_Summary

If there is already built-in report for this, let me know (I admittedly could have missed it), but if not, any suggestions on building it would be appreciated.

Comments 3 CommentsJump to latest comment

Andrew Bosch's picture

I threw this together for you, it hasn't been fully tested for accurate results but should get you started.

 

 
SELECT sci.Name AS [Software Name], vc.Name AS [Computer Name], inst.InstallDate AS [Installed Date], usage.[Date Last Used]
FROM Inv_InstalledSoftware inst
JOIN vRM_Software_Component_Item sci
   ON sci.Guid = inst._SoftwareComponentGuid
JOIN vComputer vc
   ON vc.Guid = inst._ResourceGuid
JOIN ResourceAssociation ccf
   ON ccf.ParentResourceGuid = inst._SoftwareComponentGuid
   AND ccf.ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6' --component contains file
JOIN Inv_Software_Execution swe
   ON swe._ResourceGuid = ccf.ChildResourceGuid
   AND swe.IsMetered = 1
LEFT JOIN (SELECT _ResourceGuid, FileResourceGuid, DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), MAX([Last Start])) AS [Date Last Used]
             FROM vAMMonthlySummary
             GROUP BY _ResourceGuid, FileResourceGuid) usage
   ON usage.FileResourceGuid = ccf.ChildResourceGuid
   AND usage._ResourceGuid = vc.Guid
WHERE usage._ResourceGuid IS NOT NULL
AND inst.InstallFlag = 1

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

Kep's picture

A huge thanks for the sample code!  This gets me very close.  All the columns come through.  The only problem appears to be the number of records returned.  I'm getting ~5000 rows for an environment with ~3800 computers, so it would appear that a lot of records are missing.  Maybe something with one of the JOINs?

I'm going to play around with this a bit to see if I can't get it to work.

Kep's picture

I just wanted to post a followup on the final query I used for this.  The one caveat that I'll add here is that it only works when you are tracking one file per application in App Metering.  In other words, if your app has two EXEs and you are tracking usage on both, you can end up with double-entries for that app using this query.  This isn't a big deal in my environment, but your mileage may vary.

SELECT Distinct vc.Guid,sci.Name AS [Software Name], vc.Name AS [Computer Name], vc.[User] AS [User Name],inst.InstallDate AS [Installed Date],usage.[Date Last Used]
FROM Inv_InstalledSoftware inst
JOIN vRM_Software_Component_Item sci
   ON sci.Guid = inst._SoftwareComponentGuid
JOIN vComputer vc
   ON vc.Guid = inst._ResourceGuid
JOIN ResourceAssociation ccf
   ON ccf.ParentResourceGuid = inst._SoftwareComponentGuid
   AND ccf.ResourceAssociationTypeGuid = 'EABE86D3-AAFD-487A-AF63-5C95D7511AF6' --component contains file
JOIN Inv_Software_Execution swe
   ON swe._ResourceGuid = ccf.ChildResourceGuid

LEFT JOIN (SELECT _ResourceGuid, FileResourceGuid, DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), MAX([Last Start])) AS [Date Last Used]
             FROM vAMMonthlySummary
             GROUP BY _ResourceGuid, FileResourceGuid) usage
   ON usage.FileResourceGuid = ccf.ChildResourceGuid
   AND usage._ResourceGuid = vc.Guid
WHERE inst.InstallFlag = 1
Order By [Computer Name] ASC