How do I report on percentage of machines in PDQ Inventory?
I've been asked by management to produce some weekly reports on computers meeting or not meeting certain criteria. I've already got working collections and auto reports, but they want the results expressed as a percentage of computers rather than a total number or a list.
An example might be:
- Percentage of workstations running Windows 10 Pro release 1810 or later (supported)
- Percentage of workstations running Windows 10 Pro release earlier than 1810 (unsupported)
In this example, I would have a collection defined for the total of all workstations, a collection for the subtotal of Win10 workstations with a supported version and a collection for those on unsupported versions (actually I'd use a variable for the release number).
I can auto report as plain text files to my email or to a folder with the count for each, then work out the percentage by hand using (100/total)*subtotal and email it out to management.
What I'd like is to have an email sent from the server each week with the percentage figures in the body, but an attachment for each percentage would be fine.
I'm sure it's possible, but it'll require better SQL or Powershell skills than I have. Anyone fancy taking a shot at it?
Comments
Mike,
Try this for machines with service pack equal to or greater than 1810. I included only Windows 10 machines, you can remove the last AND in the WHERE clause to include non-Windows 10 machines. Also if you don't want all the decimal places in the percentage you can take off the ".0" from the end of the 100:
SELECT
Computers.OSServicePack,
(COUNT(Computers.OSServicePack)* 100.0 / (SELECT COUNT(*) FROM Computers)) AS 'Percentage',
COUNT(*) AS "Count"
FROM Computers
WHERE <ComputerFilter> AND Computers.OSServicePack >= 1810 AND Computers.OS = '10'
GROUP BY Computers.OSServicePack
Try this for machines with service pack less than 1810:
SELECT
Computers.OSServicePack,
(COUNT(Computers.OSServicePack)* 100.0 / (SELECT Count(*) FROM Computers)) AS 'Percentage',
COUNT(*) AS "Count"
FROM Computers
WHERE <ComputerFilter> AND Computers.OSServicePack < 1810 AND Computers.OS = '10'
GROUP BY Computers.OSServicePack
Thanks Luke, that works perfectly once I corrected my own mistake (it should be 1809 instead of 1810).
Can I substitute the value I'm comparing to Computers.OSServicePack with my @(OSVerWin10OldestReleaseServiced) custom variable?
Mike,
I'm not sure how to integrate PDQ variables into a SQL report-- to be honest, I don't work with PDQ variables much. Maybe someone from the PDQ staff can chime in on that point.
Thanks again Luke. I've really learned a lot of SQLite today, all starting with your query.
Giving back to the community, here's one for Office 365 ProPlus unsupported versions, using my custom variable for the *currently* oldest supported version (but without the percentage column added yet).
WITH Variables(o365ver) AS (SELECT CustomVariables.Value FROM CustomVariables WHERE CustomVariables.Name = 'AppVerOffice365OldestVersionServiced')
SELECT
Applications.Name AS "Application",
Applications.Version AS "Version",
COUNT(Computers.Name) AS "Count"
FROM Computers
JOIN Applications USING (ComputerID)
CROSS JOIN Variables
WHERE <ComputerFilter> AND Applications.Name LIKE 'Microsoft Office 365 ProPlus - en-us' AND CAST(SUBSTR(Applications.Version, 6) AS REAL) <= Variables.o365ver
GROUP BY Applications.Version;