SQL for Wrong Version of Software

[Scott W. Allen]

I'm looking to create an SQL that shows machines with the wrong version of software installed. For examle, list all machines that have Adobe Reader installed, but NOT version 11.0.6.

 

I was working with the SQL from this article as a starting point: https://www-secure.symantec.com/connect/forums/altiris-ns-find-list-machines-without-installed-software

 

Thanks!

[andykn101]

Try:

SELECT     ai._ResourceGuid
FROM         dbo.vComputer AS i INNER JOIN
                      dbo.Inv_AddRemoveProgram AS ai ON ai._ResourceGuid = i.Guid
WHERE     (ai.DisplayName LIKE '%adobe reader%') AND (NOT (ai.DisplayVersion LIKE N'LIKE N11.0.6')) AND (ai.InstallFlag = 1)

This will give you PCs with Adobe Acrobat that aren't at 11.0.6. If you to include PCs that don't have any Adobe Acrobat you'd need to change it round to have something using a ...WHERE NOT IN (SELECT...

[Scott W. Allen]

It's not working. The SQL is showing machines with and without 11.0.6.

[Jeff Stanton]

Give this one a try...

select

vc.[name],

arp.[DisplayName],

arp.[DisplayVersion],

vc.[user]

from Inv_AddremoveProgram arp

left join vcomputer vc on vc.guid = arp._ResourceGuid

where [DisplayName] like 'adobe reader%'

and [displayversion] not like '11.0.06'

 

You can change the version as required.

Let me know if that works for you.

 

 

[Gavin Sanish]

Hi Swallen,

    Try with this

select * from Inv_AddRemoveProgram

where DisplayName like '%adobe Reader%' and DisplayVersion != '11.0.6'

[Jeff Stanton]

select

vc.[name],

arp.[DisplayName],

arp.[DisplayVersion],

vc.[user]

from Inv_AddremoveProgram arp

left join vcomputer vc on vc.guid = arp._ResourceGuid

where [DisplayName] like 'adobe reader%'

and [displayversion] not like '11.0.06'

 

Give this a try and see if that gives you what you need.

[Gavin Sanish]

Try with this query which contain the computer name as well

SELECT     dbo.Inv_AddRemoveProgram.DisplayVersion AS [Software name], dbo.Inv_AddRemoveProgram.DisplayName AS [Software Version],
                      dbo.vComputer.Name AS [Computer Name]
FROM         dbo.Inv_AddRemoveProgram LEFT OUTER JOIN
                      dbo.vComputer ON dbo.Inv_AddRemoveProgram._ResourceGuid = dbo.vComputer.Guid
WHERE     (dbo.Inv_AddRemoveProgram.DisplayName LIKE '%adobe Reader%') AND (dbo.Inv_AddRemoveProgram.DisplayVersion <> '11.0.6')

[Jeff Stanton]

I tried to post this a couple times, but it did not show up.  I'm hoping the third time is the charm...

select
vc.[name],
arp.[DisplayName],
arp.[DisplayVersion],
vc.[user]
from Inv_AddremoveProgram arp
left join vcomputer vc on vc.guid = arp._ResourceGuid
where [DisplayName] like 'adobe reader%'
and [displayversion] not like '11.0.06'

Hope this works for you.

Thanks!

[Scott W. Allen]

Thank you andykn101, Gavin Sanish and JMS97 for your help!