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

PDQ Inventory Application Version Report

I'm trying to create a report with installed application version and current version. I have multiple collections with $(AppVerxxx) that lists the  current version but the app summary report does not have that option.

 

Thank you

2

Comments

1 comment
Date Votes
  • I had a similar question, it looks like this is not a ready made report, but since the data exists you can make a SQL query to try and grab it.  I made a query that seems to work, if you want more applications you can just add more rows to the data retrieved, use this at your own discretion:

     


    --Create a temp table to copy the data from the applications table but cast ComputerId to an integer so the join is quick
    DROP TABLE IF exists ALApplications;

    CREATE TEMP TABLE "ALApplications" (
    "ApplicationId" integer NOT NULL PRIMARY KEY,
    "ComputerId" INTEGER,
    "Name" TEXT,
    "Publisher" TEXT,
    "Version" TEXT,
    "InstallDate" TEXT,
    "InstallDateSecondary" TEXT,
    "Uninstall" TEXT,
    "ApplicationRegistryKeyId" TEXT
    );

    --Put all the data from the appllcations table into the temp table
    insert into 'ALApplications' ( "ApplicationId","ComputerId", "Name","Publisher","Version","InstallDate","InstallDateSecondary","Uninstall","ApplicationRegistryKeyId")
    select * from '~Applications';

    --Create a table for the software we want to report on, the table has the following columns
    --AppNameVariable - The variable name that identifies the application name
    --AppVerVariable - The variable name that identifies the most recent version of the ApplicationId
    --AppCollectionVariable - The collection to use to scope the query to
    DROP TABLE IF exists ALReportApps;

    CREATE TEMP TABLE "ALReportApps" (
    "AppNameVariable" TEXT,
    "AppVerVariable" TEXT,
    "AppCollectionVariable" TEXT
    );

    insert into ALReportApps
    VALUES ('AppNameMozillaFirefox','AppVerMozillaFirefox','Collection Library\Applications\Internet Browsers\Mozilla Firefox\Mozilla Firefox 64-bit\Mozilla Firefox 64-bit (Old)');

    insert into ALReportApps
    VALUES ('AppNameGoogleChrome','AppVerGoogleChromeEnterprise','Collection Library\Applications\Internet Browsers\Chrome Enterprise\Chrome Enterprise (Old)');

    insert into ALReportApps
    VALUES ('AppName7Zip','AppVer7Zip','Collection Library\Applications\Utilities\Compression Utilities\7-Zip\7-Zip (Old)');


    SELECT
    Computers.Name as ComputerName
    ,ALApplications.Name as ApplicationName
    ,ALApplications.Version as InstalledVersion
    ,(select value from SystemVariables where name = ALReportApps.AppVerVariable) as CurrentVersion
    FROM
    Computers
    INNER JOIN
    CollectionComputers ON Computers.ComputerId = CollectionComputers.ComputerId
    INNER JOIN
    Collections ON CollectionComputers.CollectionId = Collections.CollectionId and ALReportApps.AppCollectionVariable = collections.Path
    Inner join ALApplications
    on computers.ComputerId = ALApplications.ComputerId
    LEFT OUTER JOIN ALReportApps
    WHERE
    Collections.Path in (select AppCollectionVariable from ALReportApps)
    AND
    CollectionComputers.IsMember = 1
    AND
    ALApplications.Name LIKE (SELECT '%' || (select replace ((select Value from SystemVariables where name= ALReportApps.AppNameVariable),'*','%')) ||'%')
    order by ComputerName

    1