Software Management Group

Hi

I am using below SQL query to pull out computers with software installed report.

Can I add Domain, Operating System, OR how maximum table i can add to get single report

SELECT DISTINCT
vc.Guid [Guid],
vc.Name [Hostname],
[user],
arp.DisplayName [Sfoteware Name],
arp.DisplayVersion [Software Version]

FROM vComputer vc

JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

WHERE DisplayName LIKE 'Adobe Flash Player 10%' AND arp.InstallFlag=1

You can pull the report and it will show you require tab

Reports > All Reports, then expand Reports > Discovery and Inventory > Inventory > Cross-platform > Software/Applications > Installed Software or Installed Software by Computer.  For example, using the Installed Software report, type '%Adobe%' for the Company OR '%Lync%' for the Name.

I am aware about these report, but my requierment is different

We use something like this. Maybe this is what your looking for

SELECT vComputer.Name as PCNr,

dbo.Inv_AddRemoveProgram.DisplayName as 'Software',

vComputer.[OS Name],

vComputer.[User] as 'AD Account',

vUser.Surname as Naam,

vUser.[Given Name] as Voornaam

FROM dbo.vComputer INNER JOIN

dbo.Inv_AddRemoveProgram ON

dbo.vComputer.Guid = dbo.Inv_AddRemoveProgram._ResourceGuid

INNER JOIN

dbo.vUser ON dbo.vComputer.[User] = dbo.vUser.Name

WHERE dbo.Inv_AddRemoveProgram.DisplayName like 'SoftwareName'

and vComputer.Domain like 'yourdomain'

group by Inv_AddRemoveProgram.DisplayName,

vComputer.Name,

vComputer.[User],

vUser.[Given Name],

vUser.Surname,

vComputer.[OS Name]

order by vComputer.Name

SELECT DISTINCT
vc.Guid [Guid],
vc.Name [Hostname],
[user],

vc.Domain,
vc.OS Name,

arp.DisplayName [Software Name],
arp.DisplayVersion [Software Version]

FROM vComputer vc

JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

WHERE DisplayName LIKE 'Adobe Flash Player 10%' AND arp.InstallFlag=1

To add more details yourself, open SQL Management Studio and attach to your test Symantec DB. Right click on Views > New View. Past your query into the third pane down on the right and click the red ! exclamation mark icon near the top.

The top pane should now show vComputer (vc) and Inv_AddRemoveProgram (arp). You can click the boxes to select Domain and OS and see how the SQL changes and the effect on results.

You can add new tables and views and create joins yourself by dragging and dropping links to the appropriate GUID fields.
 

Be careful:

vc.OS Name schould be vc.[OS Name]

Good point, that's why I always use the View generator when I'm at work instead of trying to write freehand.
 

I wouldn't do a inner join on vuser as that will exclude data. Use left join.

this is a common mistake that happens with altiris reports and even with the views. I prefer to pull data directly from base tables instead of the views. 

Good point; the Views can be useful especially if you only want Active computers in a system using full Asset Management.
 

MetriX Dashboards software compliance dashboards may be able to get you the information you are looking for.  It is a great way to pull information out, as well as recieve cost saving information.  There is all types of information that we are able to pull out, not just compliance data.

If you would like to learn more and share with me some more of our issues, please reach out to me, and I would be glad to set-up a time to show you more.

www.metrixdashboards.com/solutions

rjschoenherr@novacoast.com

Lots of good help here. In the end, I've opted to split the points between Andy (for the simple query that satisfies the users request) and Fabian (for debugging it!).

Dashboard nice... will take a look sometime!