Hello,

I"m trying to provide a report to our SW Asset Mgmt team that includes several fields, namely:

SerialNumber, CompName, SoftwareProductName, SWProductManufacturer, SW Product "Version", "Detail" version, LastUsed, RunCount

I used the built-in Software Usage report and managed to get this far:

SELECT DISTINCT

i.Name [Computer], chassis.[Serial Number],

vsps.CompanyName,

f.Name [Software_Product],

MAX (a.[Last Start]) [Last Start],

SUM(a.[RunCount]) AS RunCount,

CASE WHEN a._ResourceGuid IS NULL THEN 'No'

ELSE 'Yes' END AS 'Used [Yes | No]'

FROM dbo.vSoftwareProduct f

JOIN vSoftwareProductSearch vsps on f.Guid = vsps.Guid

JOIN ResourceAssociation ra

ON ra.ParentResourceGuid = f.Guid

AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'

JOIN vSoftwareComponent sc

ON sc.Guid = ra.ChildResourceGuid

JOIN dbo.Inv_InstalledSoftware iis

ON iis._SoftwareComponentGuid = ra.ChildResourceGuid

AND iis.InstallFlag = 1

JOIN dbo.vComputer i

ON i.Guid = iis._ResourceGuid

JOIN Inv_HW_Chassis chassis ON i.Guid = chassis._ResourceGuid

LEFT JOIN Inv_Software_Product_Usage spu

ON spu._ResourceGuid = f.Guid

LEFT JOIN (SELECT DISTINCT ms.FileResourceGuid, ms._ResourceGuid, ra12.ParentResourceGuid [ParentResourceGuid],

MAX ([Last Start]) [Last Start], SUM(ms.[Run Count]) AS RunCount FROM dbo.Inv_Software_Execution se

JOIN dbo.vAMMonthlySummary ms

ON ms.FileResourceGuid = se._ResourceGuid

JOIN dbo.ResourceAssociation ra11

ON ra11.ChildResourceGuid = se._ResourceGuid

JOIN ResourceAssociation ra12

ON ra12.ChildResourceGuid = ra11.ParentResourceGuid

AND ra12.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'

WHERE se.IsMetered = 1

GROUP BY ms.FileResourceGuid, ms._ResourceGuid, ra12.ParentResourceGuid

) a

ON a.ParentResourceGuid = ra.ParentResourceGuid

AND a._ResourceGuid = i.Guid

AND a.[Last Start] BETWEEN GETDATE() - spu.UsageCount and GETDATE()

WHERE i.[System Type] LIKE 'Win%'

AND chassis.[Serial Number] IS NOT NULL and chassis.[Serial Number] <> 'None'

GROUP BY

i.[Name], chassis.[Serial Number],

i.[Domain], vsps.CompanyName,

f.Name,

CASE WHEN a._ResourceGuid IS NULL THEN 'No' ELSE 'Yes' END

At this point I'm trying to figure out how to get the Version field out of the Software Product (the "9.x" for example in the top of the SW Product configuration screen), along with the actual installed version from Add/Remove Programs, and the binary used for tracking execution.  I just can't dig through the db anymore at the moment.

Anyone have any ideas (I'm looking at you, @Andrew Bosch)!