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
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