Last active
June 16, 2020 17:51
-
-
Save Colby-PDQ/c03e7eee22a73c9b1dcbe81aaa9b18e6 to your computer and use it in GitHub Desktop.
Select 2 computers, then run this report. It will show you which applications only exist on one of them.
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/c03e7eee22a73c9b1dcbe81aaa9b18e6 | |
SELECT | |
Computers.Name AS "Computer Name" | |
, Applications.Name AS "Application Name" | |
FROM | |
( | |
SELECT | |
Applications.ApplicationId | |
, COUNT(Applications.Name) AS "ApplicationCount" | |
FROM | |
Applications | |
INNER JOIN | |
Computers USING (ComputerId) | |
WHERE | |
<ComputerFilter> | |
GROUP BY | |
Applications.Name | |
) 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/c03e7eee22a73c9b1dcbe81aaa9b18e6 | |
SELECT | |
Computers.Name AS "Computer Name" | |
, Applications.Name AS "Application Name" | |
FROM | |
( | |
SELECT | |
Applications.ApplicationId | |
, COUNT(Applications.Name) AS "ApplicationCount" | |
FROM | |
Applications | |
INNER JOIN | |
Computers USING (ComputerId) | |
WHERE | |
<ComputerFilter> | |
GROUP BY | |
Applications.Name | |
) 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-04-26T16:31:07.0000000-06:00</Created> | |
<Description>Select 2 computers, then run this report. It will show you which applications only exist on one of them.</Description> | |
<IsNew value="false" /> | |
<Modified>2018-10-24T13:45:13.0000000-06:00</Modified> | |
<Name>Application Comparison</Name> | |
<Path>Reports\Application Comparison</Path> | |
<ReportFolderId value="1" /> | |
<TypeName>SqlReport</TypeName> | |
<ReportType>SqlReport</ReportType> | |
</Report> | |
</AdminArsenal.Export> |
Revision 3: Replaced instances of ON with USING.
Revision 4: Clarified the Description a bit.
Revision 5: 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: Added COLLATE NOCASE to the ORDER BY clause to make it case insensitive.