PDQ Inventory Application Version Report

Comments

1 comment

  • Andy Langton

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

Please sign in to leave a comment.