Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A couple of PDQ Inventory SQL Reports that group the Display Model and Display Serial Number fields so each computer only has 1 line.
SELECT
Computers.Name AS "Computer Name"
, Computers.Model AS "Computer Model"
, Computers.Manufacturer AS "Computer Manufacturer"
, Computers.SerialNumber AS "Computer Serial Number"
, Computers.Memory AS "Computer Memory"
, CPUs.ProcessorSummary AS "CPU Processor"
, Computers.OS AS "Computer O/S"
, Computers.OSVersion AS "Computer O/S Version"
, Computers.OSServicePack AS "Computer SP / Release"
-- GROUP_CONCAT usage lovingly stolen from this video: https://www.pdq.com/blog/webcast-recap-sql-made-simple-reports-in-inventory/
-- The || trick is adapted from: https://stackoverflow.com/a/29766435
, GROUP_CONCAT(Displays.Model || ' (' || Displays.SerialNumber || ')', ', ') AS "Display Models and Serial Numbers"
FROM
Computers
INNER JOIN
CPUs USING (ComputerId)
INNER JOIN
Displays USING (ComputerId)
WHERE
<ComputerFilter>
GROUP BY
Computers.ComputerId
ORDER BY
Computers.Name COLLATE NOCASE
<?xml version="1.0" encoding="utf-8"?>
<AdminArsenal.Export Code="PDQInventory" Name="PDQ Inventory" Version="17.1.0.0" MinimumVersion="3.1">
<Report>
<ReportDefinition name="Definition">
<Sql>SELECT
Computers.Name AS "Computer Name"
, Computers.Model AS "Computer Model"
, Computers.Manufacturer AS "Computer Manufacturer"
, Computers.SerialNumber AS "Computer Serial Number"
, Computers.Memory AS "Computer Memory"
, CPUs.ProcessorSummary AS "CPU Processor"
, Computers.OS AS "Computer O/S"
, Computers.OSVersion AS "Computer O/S Version"
, Computers.OSServicePack AS "Computer SP / Release"
-- GROUP_CONCAT usage lovingly stolen from this video: https://www.pdq.com/blog/webcast-recap-sql-made-simple-reports-in-inventory/
-- The || trick is adapted from: https://stackoverflow.com/a/29766435
, GROUP_CONCAT(Displays.Model || ' (' || Displays.SerialNumber || ')', ', ') AS "Display Models and Serial Numbers"
FROM
Computers
INNER JOIN
CPUs USING (ComputerId)
INNER JOIN
Displays USING (ComputerId)
WHERE
&lt;ComputerFilter&gt;
GROUP BY
Computers.ComputerId
ORDER BY
Computers.Name COLLATE NOCASE</Sql>
<ReportDefinitionTypeName>SqlReportDefinition</ReportDefinitionTypeName>
</ReportDefinition>
<Created>2019-07-30T17:29:35.0000000-06:00</Created>
<Description>For https://community.pdq.com/posts/12632-summarize-data-into-one-cell</Description>
<IsNew value="false" />
<Modified>2019-07-30T17:34:31.0000000-06:00</Modified>
<Name>Grouped Displays - Fancy</Name>
<Path>Reports\Grouped Displays - Fancy</Path>
<ReportFolderId value="1" />
<TypeName>SqlReport</TypeName>
<ReportType>SqlReport</ReportType>
</Report>
</AdminArsenal.Export>
SELECT
Computers.Name AS "Computer Name"
, Computers.Model AS "Computer Model"
, Computers.Manufacturer AS "Computer Manufacturer"
, Computers.SerialNumber AS "Computer Serial Number"
, Computers.Memory AS "Computer Memory"
, CPUs.ProcessorSummary AS "CPU Processor"
, Computers.OS AS "Computer O/S"
, Computers.OSVersion AS "Computer O/S Version"
, Computers.OSServicePack AS "Computer SP / Release"
-- GROUP_CONCAT usage lovingly stolen from this video: https://www.pdq.com/blog/webcast-recap-sql-made-simple-reports-in-inventory/
, GROUP_CONCAT(Displays.Model, ', ') AS "Display Models"
, GROUP_CONCAT(Displays.SerialNumber, ', ') AS "Display Serial Numbers"
FROM
Computers
INNER JOIN
CPUs USING (ComputerId)
INNER JOIN
Displays USING (ComputerId)
WHERE
<ComputerFilter>
GROUP BY
Computers.ComputerId
ORDER BY
Computers.Name COLLATE NOCASE
<?xml version="1.0" encoding="utf-8"?>
<AdminArsenal.Export Code="PDQInventory" Name="PDQ Inventory" Version="17.1.0.0" MinimumVersion="3.1">
<Report>
<ReportDefinition name="Definition">
<Sql>SELECT
Computers.Name AS "Computer Name"
, Computers.Model AS "Computer Model"
, Computers.Manufacturer AS "Computer Manufacturer"
, Computers.SerialNumber AS "Computer Serial Number"
, Computers.Memory AS "Computer Memory"
, CPUs.ProcessorSummary AS "CPU Processor"
, Computers.OS AS "Computer O/S"
, Computers.OSVersion AS "Computer O/S Version"
, Computers.OSServicePack AS "Computer SP / Release"
-- GROUP_CONCAT usage lovingly stolen from this video: https://www.pdq.com/blog/webcast-recap-sql-made-simple-reports-in-inventory/
, GROUP_CONCAT(Displays.Model, ', ') AS "Display Models"
, GROUP_CONCAT(Displays.SerialNumber, ', ') AS "Display Serial Numbers"
FROM
Computers
INNER JOIN
CPUs USING (ComputerId)
INNER JOIN
Displays USING (ComputerId)
WHERE
&lt;ComputerFilter&gt;
GROUP BY
Computers.ComputerId
ORDER BY
Computers.Name COLLATE NOCASE</Sql>
<ReportDefinitionTypeName>SqlReportDefinition</ReportDefinitionTypeName>
</ReportDefinition>
<Created>2019-07-30T16:57:04.0000000-06:00</Created>
<Description>For https://community.pdq.com/posts/12632-summarize-data-into-one-cell</Description>
<IsNew value="false" />
<Modified>2019-07-30T17:34:41.0000000-06:00</Modified>
<Name>Grouped Displays</Name>
<Path>Reports\Grouped Displays</Path>
<ReportFolderId value="1" />
<TypeName>SqlReport</TypeName>
<ReportType>SqlReport</ReportType>
</Report>
</AdminArsenal.Export>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.