Welcome to SQL snippets!
Writing SQL queries and building SQL reports on any platform may not always be a simple task. The SQL Snippets series is designed to help teach, assist, and bridge some gaps of knowing what you want in a SQL report and getting the desired results. We’ll be demonstrating examples using both of our products PDQ Deploy and PDQ Inventory, as well as best practices outside of our products.
Click here to follow along with the video of Episode 1 related to this blog.
For best practices, start by creating a new ‘Reports’ folder within PDQ Inventory to save all SQL reports in. This helps maintain organization within the console.
Right-click ‘Reports’ → ‘New’ → ‘Report Folder’ → Insert a name for your folder.
OR
Click ‘Report’ tab up top → ’New Report Folder’ → Insert a name for your folder.
Name the SQL report folder and it will automatically open in the console/portal. It will also appear in the left-hand panel under ‘Reports’. Start creating and saving reports for future reference.
Building SQL reports in PDQ Inventory
On the tabs at the top:
Report → New Report → SQL Report
This will now open a report editor where the queries are built. By default, the editor will auto-populate a simple ‘SELECT’ statement querying the ‘Name’ and ‘Description’ columns from the ‘Computers’ table. You will also notice the ‘Computers.Name’ column has an alias of ‘Computer Name’ as opposed to ‘Name’.
We know we’re extracting data from the ‘Computers’ table simply because of the ‘FROM’ statement.
“SELECT <columns> FROM <table> WHERE <ComputerFilter>”
Notice on the left-hand side of the reports window is a square box with a list of all tables in the PDQ Inventory database. As soon as you click on a table, you will see the columns of the table populate in the box directly below. This will help build your queries and even drop in your selections when double clicking on the columns throughout the process.
For this example, we edit the default query by getting rid of the ‘Description’ column and adding the ‘OSName’ column instead. Click —> ‘Run Report’. In return, we receive a report with a list of all the computers in our network along with the operating system for each individual computer.
Important: When selecting columns in a SQL query, all columns MUST be separated with a comma EXCEPT for the last column before the ‘FROM’ command.
To edit a report that has already run, click —> ‘Define Report’ on the top panel, and it will open the query builder again with the latest query run so there’s no need to start from scratch.
We decide we need to know more than just the operating system of the computers and want the IP address they are connecting to as well. Easy.
Adding an additional column
Add the ‘IPAddress’ Column
Adding one more column means placing a comma at the end of the second to the last column. Make sure the last column included in the ‘SELECT’ statement does NOT have a comma at the end of it. Since the ‘IPAddress’ column is added after the ‘OSName’ column, we manually edit the query by adding a comma after ‘OSName’ —> Hit ‘Enter’ on the keyboard to drop to the next line. For best practices in organization —> find the ‘IPAddress’ column in the lower left-hand list box (assuming the ‘Customers’ table is still highlighted in the ‘Tables’ list box above) —> Double Click ‘IPAddress’. It should auto-populate where the cursor on the query builder is located.
Run the Updated Report Including the ‘IPAddress’ Column:
Results:
“Understanding the “WHERE” clause using <ComputerFilter>”:
In SQL, the ‘WHERE’ clause is used to extract data that falls into precise filters/conditions. In PDQ Inventory, we have built in a proprietary “WHERE <ComputerFilter>” condition that auto-populates upon creating a new report. This can be removed or kept, but there is a reason it has been built into our product. The “<ComputerFilter>” in the ‘WHERE’ clause signifies the computers query is being run against. If it is left alone/unchanged or even completely deleted, the query will run against ALL computers on your network. If you’re unsure of how to determine this, you can:
Click on “Select Collection Source” at the top:
Select from your collections:
By default, a query will run against ALL COMPUTERS, the first collection listed. However, if you choose a specific collection source, the ‘WHERE’ clause will essentially read:
“WHERE Collection Source = ‘Lab Computers’”
Tying it all together
Choosing ‘Lab Computers’ (the specific collection source), the query will only extract data from 281 computers as opposed to 471 computers (in given example above).
The greatest part? You don’t have to type that all out! Follow the steps above to select a collection source, or leave it untouched to run your queries against all computers!
Remember: Deleting the WHERE clause entirely will automatically run against ALL COMPUTERS when you click “Run Report”. However, if you customize the WHERE clause with your own syntax and include the ‘AND’ operator to keep <ComputerFilter> in the script, the custom query can still run against a specific collection source. Therefore, this is a very important step for extracting data and receiving accurate results!
