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)!