Today let's jump into the deep end of the reporting pool. We've compiled a few reports that often leave some of us scratching our heads when we try building them. These reports have come from actual requests from PDQ Inventory administrators around the globe.
Create a New Report. Make it a Standard (not SQL) Report. Add the following table/columns:
You will see that there are two tables for Local Groups. Honestly, I prefer to use Local Group Member when I pull any info related to group membership. Do not mix and match these groups in the same report. Anyway, there is undoubtedly more than one way to apply filters in this case. I’ll show you two options.
Compile a list of account names that are approved. We want to remove them from the results to cut down on noise in the returned rows.
This method uses fewer filters because we unlock the power of regular expressions. I am removing Domain Admins, Enterprise Admins, Quintana, and Administrator from the results since these accounts are approved. In this case, I use some RegEx special characters. ^ means start at the beginning of the string. $ means that is the end of the string. The pipe characters | is essential in this case as it means OR. The way to interpret the following RegEx is “do not return any account exactly named Domain Admins OR Enterprise Admins OR Quintana OR Administrator/.”
^Domain Admins$|^Enterprise Admins$|^quintana$|^Administrator$
If RegEx isn’t your thing, you can achieve the same results by using the following:
Let’s get to a tricky one, but first, we’ll set the table with an EASY one. The following screenshots show how you can have a report showing which computers have Google Chrome by version.
This report returns something like this:
Now here’s a tricky one. Now and then we’ll get a request to have a report showing computers with two or more specific applications. That part is easy. What gets a tad more difficult is when you want to DISPLAY the particular apps.
Here is a scenario. Find which computers have Chrome AND Firefox. OK, if all you want to do is report the machines that have both, this report is incredibly easy. Below are the filters you could use if you only want to return computer names.
Here is where it gets tricky. If you want this report to show both Chrome AND Firefox and their respective versions you will need to modify the filters quite a bit. Otherwise your report will return zero rows.
I understand the above example is not that intuitive. Below we have an example of getting a similar report with SQL. The only difference is that in the SQL report we were able to concatenate the two apps into one row. Thanks to Stephanie at PDQ.com for writing the SQL below.
SELECT C.Name as 'Computer Name', GROUP_CONCAT(A.Name, ',') as 'Apps' FROM Computers C JOIN Applications A USING (ComputerId) WHERE <ComputerFilter> AND C.name like '%' AND A.Name LIKE 'Mozilla Firefox%' OR A.Name LIKE 'Google Chrome%' GROUP BY C.Name HAVING COUNT(A.Name)>1