1. PDQ Deploy & Inventory Help Center
  2. Community
  3. PDQ Inventory
 

Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

Dynamic collection for PCs with non-standard software

Avatar
Rob Poling

I'd like to create a collection to look for PCs with software installed that doesn't match a list of standard applications. I'm not sure exactly how to go about creating one like that. Can anyone point me in the right direction?

0

Comments

4 comments
Date Votes
  • Avatar
    Monica KVH
    (Edited )

    You will probably be best served by a SQL report for this, rather than a collection right off the bat.

    Here's an example I put together to use as a baseline:

    select
        c.Name as [Computer Name],
        a.Name as [Application Name],
        a.Publisher as [Application Publisher]
    from Applications as a
        join Computers as c on c.ComputerId = a.ComputerId
    where a.Name not in ("", "", "") --quoted comma separated list goes here

    You will want to put ALL of the applications you consider as standard in the list.

    For example:

    where a.Name not in ("Mozilla Firefox%", "Google Chrome", "Adobe Flash%", "%Office%")

    % is used as a wildcard to catch various versions and types of applications, like Flash for IE, Flash for Firefox, and any kind of Office program (LibreOffice, Microsoft Office and related apps, etc). That way, you won't have to make a new item in the list for multiple branches of one application.

    Hope that helps!

    0
  • Avatar
    Monica KVH
    (Edited )

    I'm not the best with collections, but I *think* this is what you would want to do if you really want a dynamic collection:

    Of course, both the SQL report method AND the dynamic collection are going to take a while to get just right, depending on how many apps you consider to be "standard" for your environment.

    0
  • Avatar
    Rob Poling

    That does help. I'm going to play around with the SQL option for a while. Thanks!

    0
  • Avatar
    Margus Potter

    this is very quick and useful query, I added my own PC also to list to be safe side cause you know IT pc-s :P

    --

     

    select
    Computers.Name as 'Computer Name',
    Computers.Description as 'Description',
    CurrentUser as 'User',
    Applications.Name as 'Application Name',
    Applications.Publisher as 'Publisher',
    Applications.InstallDate as 'Installed Date'
    from Computers INNER JOIN Applications ON Applications.ComputerId=Computers.ComputerId

    where NOT Applications.Name LIKE '%7-Zip%'
    where NOT Applications.Nam... etc.

    AND NOT Applications.Publisher LIKE '%Realtek%'
    AND NOT Applications.Publi.... etc

    AND NOT Computers.Name LIKE '%MYPCNAME%'

    order by Applications.Name

    --

    0