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/c03e7eee22a73c9b1dcbe81aaa9b18e6 to your computer and use it in GitHub Desktop.
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.
-- 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
<?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
&lt;ComputerFilter&gt;
GROUP BY
Applications.Name
) 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-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>
@Colby-PDQ
Copy link
Author

Revision 2: Added COLLATE NOCASE to the ORDER BY clause to make it case insensitive.

@Colby-PDQ
Copy link
Author

Revision 3: Replaced instances of ON with USING.

@Colby-PDQ
Copy link
Author

Revision 4: Clarified the Description a bit.

@Colby-PDQ
Copy link
Author

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