Important Notice: On February 29th, this community will be put into read-only mode. All existing posts will remain but customers will be unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

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?

 

1

Comments

4 comments
Date Votes
  • 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

    2
  • 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?

    1
  • 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.

    1
  • 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;

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post