Computer Info SQL Report


Today, Kris asked what I've done recently with PDQ Deploy/Inventory.

Probably the most involved thing I've done lately is build a SQL report that compiles all the basic computer information in one handy-dandy place. Plus, you can export the results how you see fit, turn it into a really pretty color-coded Excel spreadsheet, or just ignore the results and run screaming away from SQL because you think query languages are scarier than running out of champagne on Mimosa Monday. (Which happened to me once. But it was a Saturday.)

The query should be pretty self-explanatory. I already did the math for getting sensible RAM and hard drive size numbers. Using "select distinct" may or may not be necessary for you--I had a couple odd duplicate results without using distinct.

Run it, or run away from it. Either way, let me know your thoughts! :)

select distinct
    c.Name as [Computer Name],
    c.SerialNumber as [Computer Serial#],
    c.Chassis as [Computer Type],
    c.OS as [Operating System],
    c.OSArchitecture as [OS Bitness],
    cpu.Name as [CPU Model],
    c.Memory / 1073741824  as [Installed RAM (GB)],
    d.Model as [Drive Model],
    d.Size / 1000000000 as [Drive Size (GB)],
    disp.Manufacturer as [Monitor Brand],
    disp.Model as [Monitor Model],
    disp.SerialNumber as [Monitor Serial#]
from Computers as c
    join DiskDrives as d on d.ComputerID = c.ComputerID
    join CPUs as cpu on cpu.ComputerID = c.ComputerID
    join Displays as disp on disp.ComputerID = c.ComputerID
login to comment

Hi Just wanne say that this is Super.

The only thing I have problem with is if I select lets say all computer with Windows 7 and run this report It will still show every computer in my AD.

In the top of the report is saying 24 computer but the report has 64 computer in it.

Can I correct this in any way ?

Thanks. Busser Melander.

login to comment