Last active
June 16, 2020 17:51
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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 | |
<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</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> |
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
Revision 2: Simplified the logic and cleaned up the output. Having separate columns looks a LOT nicer than tacking the version on with "---" :)