Skip to content

Instantly share code, notes, and snippets.

@Colby-PDQ
Last active June 16, 2020 17:51
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Colby-PDQ/133fdcc7596d8908d6f43a195b9e02f2 to your computer and use it in GitHub Desktop.
Save Colby-PDQ/133fdcc7596d8908d6f43a195b9e02f2 to your computer and use it in GitHub Desktop.
Select 2 computers, then run this report. It will show you if an application only exists on one of them, or if each of them has a different version.
-- https://gist.github.com/Colby-PDQ/133fdcc7596d8908d6f43a195b9e02f2
SELECT
Computers.Name AS "Computer Name"
, Applications.Name AS "Application Name"
, Applications.Version AS "Application Version"
FROM
(
SELECT
Applications.ApplicationId
, COUNT(Applications.Name || Applications.Version) AS "ApplicationCount"
FROM
Applications
INNER JOIN
Computers USING (ComputerId)
WHERE
<ComputerFilter>
GROUP BY
Applications.Name || Applications.Version
) ApplicationCounts
INNER JOIN
Applications USING (ApplicationId)
INNER JOIN
Computers USING (ComputerId)
WHERE
<ComputerFilter>
AND
ApplicationCounts.ApplicationCount = 1
ORDER BY
Applications.Name COLLATE NOCASE
<?xml version="1.0" encoding="utf-8"?>
<AdminArsenal.Export Code="PDQInventory" Name="PDQ Inventory" Version="16.5.0.0" MinimumVersion="3.1">
<Report>
<ReportDefinition name="Definition">
<Sql>-- https://gist.github.com/Colby-PDQ/133fdcc7596d8908d6f43a195b9e02f2
SELECT
Computers.Name AS "Computer Name"
, Applications.Name AS "Application Name"
, Applications.Version AS "Application Version"
FROM
(
SELECT
Applications.ApplicationId
, COUNT(Applications.Name || Applications.Version) AS "ApplicationCount"
FROM
Applications
INNER JOIN
Computers USING (ComputerId)
WHERE
&lt;ComputerFilter&gt;
GROUP BY
Applications.Name || Applications.Version
) ApplicationCounts
INNER JOIN
Applications USING (ApplicationId)
INNER JOIN
Computers USING (ComputerId)
WHERE
&lt;ComputerFilter&gt;
AND
ApplicationCounts.ApplicationCount = 1
ORDER BY
Applications.Name COLLATE NOCASE</Sql>
<ReportDefinitionTypeName>SqlReportDefinition</ReportDefinitionTypeName>
</ReportDefinition>
<Created>2018-10-24T12:44:55.0000000-06:00</Created>
<Description>Select 2 computers, then run this report. It will show you if an application only exists on one of them, or if each of them has a different version.</Description>
<IsNew value="false" />
<Modified>2018-10-24T13:43:50.0000000-06:00</Modified>
<Name>Application Comparison with Version</Name>
<Path>Reports\Application Comparison with Version</Path>
<ReportFolderId value="1" />
<TypeName>SqlReport</TypeName>
<ReportType>SqlReport</ReportType>
</Report>
</AdminArsenal.Export>
@Colby-PDQ
Copy link
Author

Revision 2: Simplified the logic and cleaned up the output. Having separate columns looks a LOT nicer than tacking the version on with "---" :)

@Colby-PDQ
Copy link
Author

Revision 3: Added a link to this gist.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment