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

PDQ Inventory SQL Query

Avatar
Peter Brooks

I have contacted support but they don't support SQL queries and suggested I post here!

I have the following query

SELECT 
A.Name as 'Application Name', 
A.Version, 
COUNT(a.name) as 'Count', 
GROUP_CONCAT(C.computerID,', ') AS 'Computer ID' 
FROM Computers C
JOIN Applications A USING (ComputerID) 
WHERE A.Name IN ('Software Package 1', 'Package 2', 'etc') AND <ComputerFilter>
GROUP BY A.NAME 
ORDER BY COUNT

This works when it's run with against everything in Inventory however it doesn't work when run against a collection, I just receive

No such column: Computers.ComputerID SELECT

Could anyone advise as to what I'm doing wrong!?

Thanks

Pete

1

Comments

2 comments
Date Votes
  • Avatar
    Colby Bouma

    I did some experimenting, and it looks like <ComputerFilter> can't handle the Computers table being renamed. Also, I added Applications.Version to the GROUP BY clause so versions don't get swallowed.

    SELECT 
          Applications.Name as 'Application Name'
        , Applications.Version
        , COUNT(Applications.Name) as 'Count'
        , GROUP_CONCAT(Computers.ComputerID, ', ') AS 'Computer ID' 
    FROM
        Computers
    JOIN
        Applications USING (ComputerID) 
    WHERE
            Applications.Name IN (
                  'Microsoft Edge'
                , 'PDQ Deploy'
            )
        AND
            <ComputerFilter>
    GROUP BY
          Applications.Name
        , Applications.Version
    ORDER BY
        Count
    2
  • Avatar
    Peter Brooks

    That worked, thankyou!!

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post