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.

PDQ Inventory SQL Query

Avatar
Brooks, Pete

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
    Brooks, Pete

    That worked, thankyou!!

    0