Vidéos d'aide de Screencast

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

Created: 09 Oct. 2012 • Updated: 09 Oct. 2012 | 5 comments
l'image des SanjayDeo7x
+3 3 votes
Login to vote

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
 

Commentaires CommentairesAccéder au dernier commentaire

l'image des Neilon Ramires

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
+2
Login to vote
l'image des KalpeshParmar

Thanks Man!!!

Its Helpfull.

0
Login to vote
l'image des vinayak patil

Hi ,

Can we  add  few more columns and  fields ?

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

Please help if you can.

Regards

0
Login to vote
l'image des mlombardo

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.

0
Login to vote