PDQ Inventory - Device Counts SQL Report

Eddie,

This works for me in my environment but I only have one server (the PDQ server itself):

SELECT (SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Desktops',
(SELECT COUNT(Computers.OSName) AS 'Server' FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers'

Luke , this works for me. Thank you very much ! 

Okay.... So that does work but if I want to filter in another set of Data and call it "Virtual Servers" using another column I get an error showing "No Such Column".  I essentially added another line and changed Computers.OSName to DiskDrives.Model     <--- DiskDrives.Model is indeed at column at least to my understanding? 

SELECT (SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) AS 'Server' FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(DiskDrives.Model) AS 'Virtual Servers' FROM Computers WHERE DiskDrives.Model LIKE "%Virtual%" AND <computerfilter>) AS 'Virtual Servers'

Disregard, I was able to fix this by using the "Computers.Model" entry. 

SELECT (SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) AS 'Server' FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(Computers.Model) AS 'Virtual Server' FROM Computers WHERE Computers.Model LIKE "%Virtual%" AND <computerfilter>) AS 'Virtual Servers'

That's because you aren't looking at the "Computers" table in your new column, you're only looking at the "DiskDrives" table. You should also pull the <computerfilter> since you're not looking at computers, like so:

SELECT
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(DiskDrives.Model) FROM DiskDrives WHERE DiskDrives.Model LIKE "%Virtual%") AS 'Virtual Servers'

That being said, this will return all virtual machines as a "virtual server" even if it's something like a virtual Windows 10 machine. It will also return a count for every virtual drive found, not just each computer containing a virtual drive, so if a VM has two drives you will show it as two virtual servers. I would recommend looking at the "Computers.SystemFamily" field instead, since all my virtual machines return that field as "Virtual Machine", like so:

SELECT
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily LIKE "%Virtual Machine%" AND <computerfilter>) AS 'Virtual Servers'

Luke once again thank you for the insight and useful information. I understand that portion better now. Did you have any metric for "Physical Servers" that is the next step and final piece to my report. I am looking into this myself now but if you had insight it would be appreciated once again. Thanks!

Eddie

I would just select the inverse of virtual servers, like this:

SELECT
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily LIKE "%Virtual Machine%" AND Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Virtual Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily NOT LIKE "%Virtual Machine%" AND Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Physical Servers'

Thanks Luke. I have tried NOT LIKE "%Virtual Machine%" and it appears to return all physical devices. (Workstations and Servers) 

Using Computers.Chassis     - "Rack Mount Chassis" seems to work but not sure if every Physical Server will reflect the Rack Mount Chassis metric. 

SELECT (SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily LIKE "%Virtual Machine%" AND <computerfilter>) AS 'Virtual Servers',
(SELECT COUNT(Computers.Chassis) FROM Computers WHERE Computers.Chassis LIKE "%Rack Mount Chassis%" AND <computerfilter>) AS 'Physical Servers'

Eddie,

If you look at my last SQL query you will see that I added a second filter to the WHERE clause that filters by both Computers.SystemFamily and Computers.OSName. That should alleviate the issue you are describing where it returns every physical device:

SELECT
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName NOT LIKE "%Server%" AND <computerfilter>) AS 'Workstations',
(SELECT COUNT(Computers.OSName) FROM Computers WHERE Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily LIKE "%Virtual Machine%" AND Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Virtual Servers',
(SELECT COUNT(Computers.SystemFamily) FROM Computers WHERE Computers.SystemFamily NOT LIKE "%Virtual Machine%" AND Computers.OSName LIKE "%Server%" AND <computerfilter>) AS 'Physical Servers'

Luke, 

         Thank you for your patience, you have helped me immensely. Everything is working as expected. 

Eddie