Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrade.
Please accept our apologies in advance for any inconvenience this might cause.

Report for Software not installed

Created: 06 Oct 2011 • Updated: 07 Oct 2011 | 4 comments
This issue has been solved. See solution.

I have been banging my head for a couple of days sifting through tables,filters, SQL queries and trying to modify reports and have gotten nowhere so maybe someone here can help me.

I am trying to create a report that will tell me if a particular piece of software is not installed.  I have tried reversing a variety of reports that will tell me what computers have an application installed but have failed miserably.  The Audit Software Search report that will give me the polar opposite of what I need but I need a distinct list of computers that do not have the application.

Any guidance would be appreciated.

SMP 7.1 with Inventory and Asset Management installed. 

 

Thanks!

Comments 4 CommentsJump to latest comment

mclemson's picture

If you had a report like this that was showing the proper list of computers with Adobe Flash Player 10:

SELECT vc.Guid,vc.Name, arp.DisplayName,arp.DisplayVersion

FROM vComputer vc

JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

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

 

Then this report would show computers without Adobe Flash Player 10:

 

SELECT DISTINCT vc.Guid,vc.Name

FROM vComputer vc

WHERE vc.Guid NOT IN

(SELECT arp._ResourceGuid FROM Inv_AddRemoveProgram arp WHERE DisplayName LIKE 'Adobe Flash Player 10%' AND arp.InstallFlag=1)

 

Does that make sense?  You're taking the logic from the WHERE clause and putting it into a select statement that just pulls GUIDs, and then you're selecting the GUIDs from vComputer that do not exist in the list of GUIDs you pulled using this logic.  You can apply this method to any query, or you can use the queries above as your template.

Does this help?

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

SOLUTION
LaurennT's picture

Yes, Mike it does very much.  Everything I found was very complex and led to a long processing time, I currently have less than 100 computers in the CMDB so I needed a simpler query.  This will definitely get me moving in the right direction.

Next step will be setting it up to only query a certain site, but the big overreaching need right now is which computers do not have X mission essential applications installed.

 

Yesterdat I became very adept at writing queries that were all different but spit out the exact opposite results of what I needed consistently.

 

Thanks

mclemson's picture

I'm glad that helped.  I also came across this KB this morning quite by accident, which has a very similar query example:
http://www.symantec.com/docs/HOWTO30272

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

LaurennT's picture

Don't you love how you can dig and dig for hours then randomly find exactly what you needed yesterday? 

Everything I found was far more complex, but I learned a lot.

 

Ended up using:

select Name from vComputerEx

where Guid not in

(

select _ResourceGuid from Inv_AddRemoveProgram

where DisplayName like '%App1%'

or DisplayName like '%App2%'

)

and IsManaged = 1

and ([OS Name]not like '%server%')

order by Name

Where App1 and App2 were the names of 2 applications we use that do the same thing, however you only have one or the other installed and never both.