Skip to main content desktop
Try Now

Webcast Recap: SQL made Simple, Reports in Inventory

Renzo TrujiloRenzo Trujilo

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

SQL made Simple

What is the difference between a basic and SQL report?

What is a SQL report?

SQL report basic syntax example.

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

Using the built-in Computer filter.

Writing an efficient SQL report.

Aliasing tables and concatenating columns.

Grouping, counting and wildcards.

Where to get shared reports?

Can you query the database directly?


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.

3). Do you have downloads for common reports?

We don’t have a report library where you can download templates from, but people share their reports on the community website, PDQ Reddit and some of our blog posts.

4). 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 1 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.

Don't miss the next post!

How to Block the Windows 11 Upgrade

Windows 11 is here, but that doesn't mean you have to use it. Discover several different ways to block Windows 11 from installing on your computers.

© 2021 Corporation
  • PDQ Deploy ®
  • PDQ Inventory ®
  • Pricing
  • Downloads
  • Licensing
  • Buy