PDQ Inventory - Device Counts SQL Report

Comments

11 comments

  • Luke Nichols

    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'

    2
    Comment actions Permalink
  • Eddie Dones

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

    1
    Comment actions Permalink
  • Eddie Dones

    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'

    1
    Comment actions Permalink
  • Eddie Dones

    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'

    1
    Comment actions Permalink
  • Luke Nichols

    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'

    2
    Comment actions Permalink
  • Eddie Dones

    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

    0
    Comment actions Permalink
  • Luke Nichols

    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'

    0
    Comment actions Permalink
  • Eddie Dones

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

    0
    Comment actions Permalink
  • Eddie Dones

    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'

    0
    Comment actions Permalink
  • Luke Nichols

    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'

    1
    Comment actions Permalink
  • Eddie Dones

    Luke, 

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

     

    Eddie

    0
    Comment actions Permalink

Please sign in to leave a comment.