In PDQ Inventory you can also create custom SQL reports. SQL (Structured Query Language) can be used to create advanced reports that you may not be able make using the Basic report tools provided in the Basic Report window. For example, you can use SQL to aggregate data via the HAVING clause. Enterprise mode is required to create and save changes to reports.
A good understanding of SQL is required to use this feature. This topic does not include an in-depth procedure for creating SQL reports; instead, it provides you an overview of the process and links to additional resources. We recommend you watch the video below, and because PDQ Inventory uses SQLite for its database, see the webpage SQL As Understood by SQLite at sqlite.org to refresh your SQLite knowledge.
SQL Reports are created and edited in the SQL Report window, using the Define Report button. This window contains the report details and the SQL and Auto Reports tabs where you specify basic report details, add the data you want in your report, and determine whether your reports are automatically run on a schedule (Enterprise mode required). You can also manage aspects of reports on the main console. For information about managing and editing reports, see Managing Reports.
You can run reports and view report data in the SQL Report window, using the Run Report button. To see how reports will look when output, view the report in the Print Preview window. For details, see Running Reports and Previewing and Printing Reports.
VIDEO: SQL Snippets: Using SQL Reports in PDQ Inventory
SQL Snippets: Using SQL Reports
(https://youtu.be/TTlmA4mZvC8)
This section contains the following topics:
Creating a SQL Report
The SQL Report window is similar to the Basic Report window except that you define the content of your reports using SQL statements. You use the same tools for your SQL reports to run, preview, print, export, select collections to run against, and to attach auto reports as for Basic reports. For details, see Running Reports and Print Preview window.
The SQL Tab
Table and Column Names
The SQL tab includes Tables and Columns lists on the left. Double-click a table name to insert it into the SQL panel of the Report window. To insert a column, select a table name, then double-click a column.
The <ComputerFilter> Identifier
<ComputerFilter> is a special identifier within the SQL that indicates the filter clause for the computer collection source. If a report includes the Computers table, we recommend you include this identifier so the report can be run against a specific collection. Without it, the report always runs against all computers in the database.
NOTE: Do not include <ComputerFilter> if the Computers table isn't included in your SQL. If you do, it will result in an error when the report is run.
SQL Examples
The default code included when you start a new report, shown in the graphic above, creates a single column report listing all the computers in your database and their AD description (if available).
The follow are examples of SQL reports:
1.The SQL query below will display all columns in the Computers table for each computer in the selected collection:
SELECT * from Computers
WHERE <ComputerFilter>
2.The SQL query below will display all applications on each computer in the selected collection:
SELECT Computers.Name as "Computer Name", Applications.Name as "Application Name"
FROM Computers
INNER JOIN Applications USING (ComputerID)
WHERE <ComputerFilter>
3.The query below will display all computers that have more than 50 applications and it would order them from the highest number of installed applications to the lowest
SELECT computers.name as Computer, count(applications.name) as Apps
FROM computers
INNER JOIN applications on computers.computerid = applications.computerid
WHERE <computerfilter>
GROUP BY computers.name HAVING count(applications.name) > 50
ORDER BY count(applications.name) DESC
The Auto Reports Tab
You can associate SQL reports to Auto Reports just like Basic reports (Enterprise mode required). For details, see Part 4: Managing Auto Reports in Creating Basic Reports and Creating Auto Reports.
More SQL Report Help
For assistance in formulating your SQL statements and reports, visit the PDQ Support Forums.
VIDEO: SQL Snippets: Using the WHERE Clause to Extract Data
SQL Snippets: Using the WHERE Clause
(https://youtu.be/oeBPO5ZlKsM)
VIDEO: SQL Snippets: Utilizing All 6 Major SQL Clauses
SQL Snippets: All 6 Major SQL Clauses
(https://youtu.be/fe29z2g3lRc)
© 2020 PDQ.com Corporation. All rights reserved.
PDQ.com is a trademark of PDQ.com Corporation. All other product and company names are the property of their respective owners.
Help Version: 18.4.0.0