SQL Help

Hi,

Yesterday i sent a help request to pdq support asking for help with SQL Reports. The responded back today saying they were unable to help with any SQL matters. Maybe someone with more SQL knowledge than myself can help? I pasted my request to them below in its entirety...Thanks!

Im new to SQL. Very new.

I need to build a report that the Basic Inventory report doesn’t seem to handle.

Im looking to create a report that shows specific specs of our PC’s

Computer Name
CPU Model
Cores
Total CPUs
Logical Cores
Memory in GB
Hyper Threaded Cores
Network Adapter Speed
Disk Size
OS Name
OS Version
OS Service Pack
GPU Model
# of GPU’s

Ive successfully created a SQL report that does everything I need, except the GPU.
In Inventory, it seems the right table.column to use is “ HardwareDevices.Name”

But that lists EVERY piece of hardware in the machine, so I need to filter it to only what I want. All our GPU’s are “Nvidia GeForce ….” So I created a select statement that does this:

Select HardwareDevices.Name from HardwareDevices WHERE HardwareDevices.Name LIKE "NVIDIA GeForce%"

This works perfectly and returns back exactly (and only what I need).

But now I need it to combine those results in the same report as everything else. All the other ones look like this…


select
                                Computers.Name as "Computer Name",
                                CPUs.Name as "CPU Model",
                                CPUs.PhysicalCores as "Physical Cores",
                                CPUs.TotalProcessors as "Number of CPU's",
                                CPUs.LogicalCores as "Hyper Threading Cores",
                                Computers.[Memory] / 1073741824 as "Memory GB",
                                NetworkAdapters.ConnectionSpeed as "Network Speed",
                                LogicalDisks.[Size] / 1073741824 as "Disk Size GB",
                                DiskDrives.PhysicalDiskType as "Disk Type",
                                Computers.OSName as "OS Version",
                                Computers.OSServicePack as "OS Service Pack"
from Computers

                INNER JOIN CPUs ON Computers.ComputerId = CPUs.ComputerId
                INNER JOIN DiskDrives ON Computers.ComputerId = DiskDrives.ComputerId
                INNER JOIN NetworkAdapters ON Computers.ComputerId = NetworkAdapters.ComputerId
                INNER JOIN LogicalDisks ON Computers.ComputerId = LogicalDisks.ComputerId
                INNER JOIN HardwareDevices ON Computers.ComputerId = HardwareDevices.ComputerId



Both of these parts work just as I need …separately.

But im having some trouble trying to combine the two sections. I’ve tried the UNION and UNION ALL command. At first, it said the two were uneven, so I added null columns to the HardwareDevices like this:

Select HardwareDevices.Name, ‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,‘ ‘,  from HardwareDevices WHERE HardwareDevices.Name LIKE "NVIDIA GeForce%"

That MIGHT have worked, but it crashes PDQ every time (Screen captures attached of error). So maybe that’s still not right? Can any of you SQL guys help?

Here is what my code looks like in its entirety. Remember, the code seems to run without a problem, but the PDQ application crashes.

select
                                HardwareDevices.Name, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '
                from 
                                HardwareDevices WHERE HardwareDevices.Name LIKE "NVIDIA GeForce%"
                UNION ALL
                select
                                Computers.Name as "Computer Name",
                                CPUs.Name as "CPU Model",
                                CPUs.PhysicalCores as "Physical Cores",
                                CPUs.TotalProcessors as "Number of CPU's",
                                CPUs.LogicalCores as "Hyper Threading Cores",
                                Computers.[Memory] / 1073741824 as "Memory GB",
                                NetworkAdapters.ConnectionSpeed as "Network Speed",
                                LogicalDisks.[Size] / 1073741824 as "Disk Size GB",
                                DiskDrives.PhysicalDiskType as "Disk Type",
                                Computers.OSName as "OS Version",
                                Computers.OSServicePack as "OS Service Pack"
                from Computers


                
                INNER JOIN CPUs ON Computers.ComputerId = CPUs.ComputerId
                INNER JOIN DiskDrives ON Computers.ComputerId = DiskDrives.ComputerId
                INNER JOIN NetworkAdapters ON Computers.ComputerId = NetworkAdapters.ComputerId
                INNER JOIN LogicalDisks ON Computers.ComputerId = LogicalDisks.ComputerId
                INNER JOIN HardwareDevices ON Computers.ComputerId = HardwareDevices.ComputerId

Thanks all! 😃