- 1. Show all computer names, OS names, IP addresses, and if they’re online or not. Show how to filter the data for just the computers in a specific collection source.
- 2. Show computers with more than one monitor; the count and descriptions of those monitors.
- 3. Show computers that have more than 1 version of an application installed on them
- 4. Last successful heartbeat for computers that are online and offline
- 5. Applications on Computer A that are NOT on Computer B and Computer C
The SQL reports feature in PDQ Inventory is a great tool to utilize if you need specific data and understand how to retrieve it using SQLite Code.
1. Show all computer names, OS names, IP addresses, and if they’re online or not. Show how to filter the data for just the computers in a specific collection source.
SELECT
Name AS ‘Computer Name’,
OSName,
IPAddress,
IsOnline
FROM Computers
WHERE <ComputerFilter>Note: The <ComputerFilter> in the WHERE clause works with the ‘Select Collection Source’ button above and is automatically placed in the query. The button looks like:
Choose a collection and keep the ‘WHERE <ComputerFilter>’ to run against a specific Collection Source. If you delete the <ComputerFilter> from the WHERE clause or do not select a collection source, the query will run against ALL computers.
2. Show computers with more than one monitor; the count and descriptions of those monitors.
SELECT
C.Name AS ‘Name’,
COUNT(C.Name) AS ‘Count’,
GROUP_CONCAT(D.Description, ', ') AS 'Displays'
FROM COMPUTERS C JOIN DISPLAYS D USING (ComputerId)
GROUP BY C.Name
HAVING COUNT(C.Name) > 1
ORDER BY COUNT(C.Name) DESC3. Show computers that have more than 1 version of an application installed on them
SELECT
C.Name AS ‘Computer Name’,
A.Name AS ‘Application Name’,
A.Version,
COUNT(A.Name) AS ‘Count’,
GROUP_CONCAT(A.Name, ‘,’) AS ‘App Names’
FROM Computers C
JOIN Applications A USING (ComputerId)
WHERE A.Name LIKE ‘PDQ%’
GROUP BY C.Name
HAVING COUNT(A.Name) > 1
ORDER BY COUNT(A.Name) DESCNote: You can change the application name in the WHERE clause from ‘PDQ%’ to whatever you desire. The ‘%’ sign in the WHERE clause is used as a wildcard. For this example, the results will include ALL applications whose name begins with ‘PDQ’. The wildcard, ‘%’, will include all results with anything listed in the application name after ‘PDQ’.
4. Last successful heartbeat for computers that are online and offline
SELECT Computers.Name AS 'Computer Name',
Computers.LastIsOnlineTime,
ComputerScans.SuccessfulScanDate
FROM Computers
JOIN ComputerScans USING (ComputerId)
ORDER BY ComputerScans.SuccessfulScanDate DESC
LIMIT 10Note: You can set the limit for results to any number.
5. Applications on Computer A that are NOT on Computer B and Computer C
Example: New Employee A gets hired and sysadmin needs to know what programs to deploy onto new computer. Runs report from Employee B and C's computer to find out, etc.
SELECT
C.Name AS ‘Computer’,
A.Name as 'Application',
A.Version AS 'Version'
FROM Computers C
JOIN Applications A USING (ComputerId)
WHERE C.Name LIKE 'Jerry%'
AND A.Name NOT IN (SELECT A.Name from Applications A
WHERE A.ComputerId IN (SELECT ComputerId from Computers WHERE Name LIKE 'GUINNESS%'))
AND A.Name NOT IN (SELECT A.Name from Applications A
WHERE A.ComputerId IN (SELECT ComputerId from Computers WHERE Name LIKE 'TinyRick%'))
*Great example of how PDQ Deploy and PDQ Inventory work better together!
Stephanie is a big time math nerd, avid snowboarder, and overall winter lover. She enjoys the torture of running and traveling to work Ragnar Relay races. From shredding the gnar to getting far too invested in murder/crime documentaries, you'll also find her petting all the doggos. She was an employee of PDQ.
Related articles
