In episode 1, we explained how SQL reports can be utilized in PDQ Inventory and PDQ Deploy. Today, we’ll discuss customizing SQL reports and how they can optimize your report results by using the ‘WHERE’ clause.
Recap of episode 1:
We previously learned how a reports folder was created and a SQL report was built and saved in our new folder named, “SQL Reports”. We Double-clicked the report to re-run it, then it automatically ran in a new window. Then, we right-clicked to edit the saved report and clicked “Define Report” or pressed “Ctrl F8” on the keyboard. Tables of the database were found in the upper box on the left-hand panel. Columns of the table populated in the box below once the table was selected.
Diving deeper into the WHERE clause:
Add the ‘OSServicePack’ column from the ‘Computers’ table to build onto the last report,Click ‘Computers’ in the tables panel box. —> Scroll to ‘OSServicePack’ in the columns panel box.
Make sure you’ve added a comma after the ‘IPAddress’ column in the SELECT statement and pressed ‘Enter’ on the keyboard to go to the next line (for organization).
Double click ‘OSServicePack’ and it will automatically add itself to the SELECT statement. —> Run the Report!
Since ‘OSName’ is a longer explanation of what is necessary for this report, the ‘OS’ column is more relevant. Erase the ‘Name’ portion from ‘OSName’ for ‘Computers.OS’ instead of adding an additional column.
Narrow down results by customizing SQL reports:
The WHERE clause:
Show all computers whose OS is 10
This is done specifically with the WHERE clause. We simply add the following command to our query to narrow down results to show only computers whose OS is 10,
WHERE Computers.OS = ‘10’ AND <ComputerFilter>
In this case, single quotes are used around the 10 because we may not be sure if the data type for that column is “integer” or “varchar” (various characters). If it is integer, the quotes are unnecessary. If it is varchar, the quotes are required. The <ComputerFilter> must be kept in the WHERE clause using an ‘AND’ operator to narrow it down by collection source. In our example, we only filter on ALL computers in which we can keep the <ComputerFilter> as is or even remove it if desired. However, to narrow down by specified collection source, the ‘AND <ComputerFilter>’ script must remain.
Show all computers whose OS is 10 and service pack is 1607
Filter even further in the WHERE clause using another ‘AND’ operator. Keep the same syntax and add the Computers.OSServicePack condition in the middle of the other conditions. The syntax should look like so:
WHERE Computers.OS = ‘10’ AND Computers.OSServicePack = 1607 AND <ComputerFilter>
In this case, single quotes are not necessary around 1607 because the data type is “integer” and does not require quotations around them. In comparison to ‘10’, this condition needs single quotes because its data type is “varchar”, meaning that both numbers and letters may be used in that column. Our query will not show letters in that column because of the filter. However, if you were to erase the filter and run:
You will notice the results from the Computers. OS column includes both letters and numbers.
Results: A report with a WHERE clause using two ‘AND’ operators for three filter.
Query:
Report:
Summarizing
Using the WHERE clause allows you to narrow down results by customizing SQL reports. Filter even further in the WHERE clause using additional operators such as ‘AND’ or ‘OR’. It’s important to note when single quotes are used or not. In our example, we did use single quotes around the ’10’ because we were uncertain if the data type for the column was “integer” or “varchar”. Remember, if it is integer, the quotes are unnecessary, but if it is varchar, the quotes are required. You can also watch our video of this tutorial by clicking here.
