Asset Management Suite

 View Only
Back to Library

Customized Report "Computer with Software Installed, Usage, Installed and Last Use" 

Oct 09, 2012 12:56 PM

Some costomers require a report with "Computer with software installed, usage,installed and last use" which not in default reports. Below is the SQL query of the report and report in xml format.

Select Distinct adrp.DisplayName,vms.[Last Start] as [Last Run],vc.Name, vc.[User],adrp.InstallDate,
case when vms.[Run Count]>= 1 then 'Yes' else 'No' end usage from Vcomputer vc 
inner join inv_addremoveprogram adrp on vc.Guid = adrp._resourceguid
inner join vammonthlysummary vms on adrp._resourceguid = vms._resourceguid
inner join Inv_InstalledSoftware ins on vms._ResourceGuid = ins._ResourceGuid
where ins.InstallFlag = 1 and adrp.DisplayName like 'Adobe reader 8%'
 and vms.[Last Start] =(SELECT max(vms.[Last Start]) FROM vammonthlysummary vms where vms._ResourceGuid = vc.Guid)
 order by vc.Name
 

Statistics
0 Favorited
3 Views
2 Files
0 Shares
0 Downloads
​
Attachment(s)
Download All
xml file
Computer with Software Installed and Usage.xml   25 KB   1 version
Uploaded - Feb 25, 2020
 
Download
jpg file
Reportrequired.jpg   183 KB   1 version
Uploaded - Feb 25, 2020
 
Download

Tags and Keywords

Comments

haroldvm89
Oct 03, 2017 03:49 PM

Very useful report, and works with version 8.1 RU2.

Thanks for sharing.

RodrigoPinheiro
Jun 05, 2017 10:38 AM

Hi,

Could you post this solution again? 

The link above is not available.

Migration User
Jun 16, 2016 10:24 AM

Right

Migration User
May 28, 2014 07:27 AM

Useful query!

Migration User
May 13, 2014 03:56 PM

The SQL provided aboe was not accurate in my 7.5 environment. The "Last Used" data did not matched the data provided in the Silverlight console view.  I've published a picker report here: https://www-secure.symantec.com/connect/downloads/computers-software-installed-report.

Migration User
Apr 19, 2014 10:55 AM

Hi ,

Can we  add  few more columns and  fields ?

i.e. Purchase Quantity,Location and balance (installed - Purchase)

 

Please help if you can.

 

Regards

Migration User
Nov 21, 2013 07:35 AM

Thanks Man!!!

Its Helpfull.

Migration User
Aug 12, 2013 04:28 PM

Adjust the query. This is a correct query.

 

SELECT DISTINCT adrp.DisplayName,vms.[Last Start] AS [Last Run],vc.Name, vc.[User],adrp.InstallDate,
CASE WHEN vms.[Run Count]>= 1 THEN 'Yes' ELSE 'No' END usage FROM Vcomputer vc 
LEFT JOIN Inv_AddRemoveProgram adrp ON vc.Guid = adrp._resourceguid
LEFT JOIN vAMMonthlySummary vms ON adrp._resourceguid = vms._resourceguid
  AND vms.[Last Start] = (SELECT max(vms.[Last Start]) FROM vAMMonthlySummary vms WHERE vms._ResourceGuid = vc.Guid)
LEFT JOIN Inv_InstalledSoftware ins ON vms._ResourceGuid = ins._ResourceGuid
WHERE adrp.DisplayName LIKE 'Adobe reader 8%'
ORDER BY vc.Name

Related Entries and Links

No Related Resource entered.