Michigan Endpoint Management User Group

Expand all | Collapse all

SQL for Wrong Version of Software

Jump to Best Answer
  • 1.  SQL for Wrong Version of Software

    Posted 04-28-2014 04:06 PM

    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!



  • 2.  RE: SQL for Wrong Version of Software

    Posted 04-28-2014 05:04 PM

    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...



  • 3.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 08:13 AM

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



  • 4.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 08:41 AM

    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.

     

     



  • 5.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 10:13 AM

    Hi Swallen,

        Try with this

    select * from Inv_AddRemoveProgram

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



  • 6.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 10:27 AM

    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.



  • 7.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 10:27 AM

    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')



  • 8.  RE: SQL for Wrong Version of Software
    Best Answer

    Posted 04-29-2014 10:38 AM

    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!



  • 9.  RE: SQL for Wrong Version of Software

    Posted 04-29-2014 11:23 AM

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