PDQ.com mobilePDQ.com desktop
Support

Head Scratching Reports in PDQ Inventory

Shane CorellianShane Corellian
Stephanie WarnerStephanie Warner
·

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.

Find Unapproved Domain Accounts in Local Administrators Groups.

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

Ready to get started?

Take our 14-day Free Trial.
This round is on us!

Don't miss the next post!

Using PowerShell to Install Printers

When installing printers, we will need to do the four things; Add Driver to the Store, Install the Driver, Create Printer Port, and Install the Printer