Webcast Recap: SQL made Simple, Reports in Inventory

Renzo Trulijo headshot
Renzo Trujilo|Updated February 1, 2021
Webcast Recap
Webcast Recap

Last week on PDQ Live, we went through the SQL report builder in PDQ Inventory, with examples tricks and tips.

SQL made simple

the difference between a basic and SQL report
What is a SQL report
basic syntax example

SQL report advanced syntax example. Computer ID as the primary key for joining tables.

Computer ID as the primary key
Using the built-in Computer filter
Writing an efficient SQL report
Aliasing tables
Grouping, counting and wildcards
Where to get shared reports
Can you query the database

Q&A:

1) Is the schema for the database available?

We don’t have one, but any column name that ends in id is most likely going to be a primary or foreign key. Like computerid which maps back to the computer table. We will look into providing a schema or entity-relationship diagram.

2) Any tips to help remember SQL terms?

Start practicing with the basics, get familiar with select, from, where and joins. We have some helpful SQL Reports available in PDQ Inventory as well.

3) Can the SQL database be queried from PowerBI?

You can query the database directly, but keep in mind that SQLite is not a relational database, when you query the database from within PDQ Inventory console there are some processes in place to prevent conflicts. We definitely don’t recommend writing to the database directly.

Bonus content:

Last 10 computers with successful scan:

SELECT Computers.Name AS 'Computer Name',
Computers.LastIsOnlineTime,
ComputerScans.SuccessfulScanDate
FROM Computers
JOIN ComputerScans USING (ComputerId)
ORDER BY ComputerScans.SuccessfulScanDate DESC
LIMIT 10Computers with more than 1 application:

Computers with more than one application:

SELECT
C.Name AS 'Computer Name',
A.Name As 'Application Name',
A.Version,
COUNT(A.Name) AS 'Count',
GROUP_CONCAT(A.Name, ',') AS 'Application 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) DESC

Computers with multiple monitors:

SELECT
Computers.Name AS "Name",
COUNT(Computers.Name) AS "Count",
GROUP_CONCAT(Displays.Description, ',') AS "Displays"
FROM Computers
JOIN Displays USING (ComputerId)
GROUP BY Computers.Name
HAVING COUNT(Computers.Name)1
ORDER BY COUNT(Computers.Name) DESC

This weeks swag winners:

  • Jeff T.

  • Mary M.

Renzo Trulijo headshot
Renzo Trujilo

Renzo is a QA Engineer at PDQ.

Related articles