How do I report on percentage of machines in PDQ Inventory?

Comments

4 comments

  • Luke Nichols

    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
    Comment actions Permalink
  • Mike Chapman

    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
    Comment actions Permalink
  • Luke Nichols

    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
    Comment actions Permalink
  • Mike Chapman

    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
    Comment actions Permalink

Please sign in to leave a comment.