Video Screencast Help

Altiris NS - Find a list of machines without Installed Software

Created: 11 Sep 2012 • Updated: 12 Sep 2012 | 8 comments
This issue has been solved. See solution.

Hello

Is there a query we can create using the Report Wizard that lists machines without a particular software installed? If not a wizard, does someone know how to create a SQl query for the same? I have NS 7.1 SP2

Thanks.

Comments 8 CommentsJump to latest comment

Andrew Bosch's picture

 

SELECT vc.*

FROM vRM_Software_Component_Item sci

JOIN Inv_InstalledSoftware inst

   ON inst._SoftwareComponentGuid = sci.Guid

   AND inst.InstallFlag = 1

LEFT JOIN vComputer vc

   ON vc.Guid = inst._ResourceGuid

WHERE LOWER(sci.Name) LIKE '%software name here in all lower case%'

AND vc.Guid IS NULL

------------------------------------
Sr. Principal SQA Engineer
Symantec

huseinm's picture

Hi Andrew

I created a computer report and copied this to the raw sql query window. I get a results windows with solid grey lines only and no computers listed?

Andrew Bosch's picture

Did you replace the 'software name here in all lower case' with something valid?  Like '%adobe%'?

------------------------------------
Sr. Principal SQA Engineer
Symantec

huseinm's picture

Yes for example I was looking for computers missing the Enterprise Vault plugin, so I typed '%ymantec enterprise vault%' and I get the grey screen with no results.

Andrew Bosch's picture

Argh, my bad.  I finally have access to SQL to test this out.  Use this one, instead:

SELECT *
FROM vComputer
WHERE Guid NOT IN (SELECT inst._ResourceGuid
                   FROM vRM_Software_Component_Item sci
                   JOIN Inv_InstalledSoftware inst
                      ON inst._SoftwareComponentGuid = sci.Guid
                      AND inst.InstallFlag = 1
                   WHERE LOWER(sci.Name) LIKE '%enterprise%vault%')

------------------------------------
Sr. Principal SQA Engineer
Symantec

SOLUTION
huseinm's picture

Thanks Andrew, this one works! Does this check all machines or only managed machine?

Andrew Bosch's picture

With the use of vComputer, it's only managed machines.  If you want all machines use vRM_Computer_Item.

------------------------------------
Sr. Principal SQA Engineer
Symantec