Client Management Suite

Can anyone assist in creating a report that gives us a list of all Laptop models with current hard drive size and type?

------

I can see

select * from dbo.vHWLogicalDevices

where dbo.vHWLogicalDevices.[Manufacturer] like '%(Standard disk drives)'

shows all of the HD models

------

select * from dbo.vHWComputerSystem

shows all of the computer models. 

---------------

I see that IT analytics can give a report of computer model and the drive size, but no physical disk type (hard drive model).

I don't see a common key to marry them together in a report.

I need the report to show

Computer Model, Laptop HD model, HD size.

Thanks in advance for your time!

anyone have any report or sql query suggestions?

I'm also trying to find out if the hard drive is an SSD or spindle drive. thanks,

Hi,

did you check default report "Computer Summary"?

See attached screenshot.

You can filter all computers by Domain or Computer name field and then Group them by any of available report column.

Or you can try this a little bit modified Query from "Computer Summary" report

SELECT DISTINCT i.Guid,
i.Name [PC Name],
sn1.Manufacturer [Manufacturer],    
sn1.[Model] [Computer Model],        
cpu.[Max Clock Speed (Mega-hertz)] [CPU Frequency],  
cpu.[Family] [CPU Type],      
sn1.[Total Physical Memory (Bytes)] / (1024 * 1024) [Memory (MB)],      
ISNULL (i.[OS Name],os.[Name]) [Operating System],      
d.[OS Version] [OS Version],                 
ld.[Free Space (Bytes)] [Free Space(MB)],    
sn1.[Identifying Number] [Serial Number],
hw.[Interface Type] [HDD Type],
hw.[Max Media Size (Kilobytes)]

FROM dbo.vComputer i      
    
    LEFT JOIN dbo.Inv_AeX_AC_Identification d
        ON d._ResourceGuid = i.Guid
    LEFT JOIN dbo.vHWPhysicalMemory m1    
        ON  m1.[_ResourceGuid] = i.Guid       
    JOIN dbo.vHWComputerSystem sn1    
        ON  sn1.[_ResourceGuid] = i.Guid       
    LEFT JOIN dbo.Inv_HW_Processor cpu   
        ON cpu.[_ResourceGuid] = i.[Guid]
    LEFT JOIN  (SELECT SUM ([Free Space (Bytes)] / (1024 * 1024)) [Free Space (Bytes)], _ResourceGuid
        FROM dbo.vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid) ld
        ON i.Guid = ld._ResourceGuid
    LEFT JOIN dbo.vOSOperatingSystem os
        ON os._ResourceGuid = i.Guid
    JOIN dbo.Inv_HW_Storage hw
        ON hw.[_ResourceGuid] = i.Guid WHERE [Media Type] = 29

Thanks,

IP.

Thanks for your help.  We don't need a list of every computer.  Just every laptop model and the HD type (SSD/spindle etc...)  Any way to query if it is an SSD and report on that?

I dont know of a way to get HD type off hand, perhaps using the model of the HD you can lookup to see the HD type?? This query might help you to narrow down your search to laptops.

Select distinct vc.Name, ct.[Chassis Type], hw.[Max Media Size (Kilobytes)], hwld.Description, hwld.Manufacturer, hwld.Model, hwld.[Device ID]

from dbo.vHWLogicalDevices hwld

Join vComputer vc on vc.Guid = hwld._ResourceGuid

Join vAC_ChassisType ct on ct.Guid = vc.Guid

Join dbo.Inv_HW_Storage hw on hw.[_ResourceGuid] = vc.Guid

Where hwld.[Manufacturer] like '%(Standard disk drives)'

And ct.[Chassis Type] like 'laptop'

And hwld.[Device ID] not like '%USB%'

And hw.[Media Type] = 29

order by vc.Name

Below Query will give the SSD disks information

select distinct idf.name[Computer name],cs.manufacturer[Manufacturer],cs.model[Model],ld.model[SSD Serial Number],ld.description from vHWLogicalDevices LD

inner join inv_aex_ac_identification idf on idf._resourceguid =LD._resourceguid

inner join vHWComputerSystem cs on cs._resourceguid = idf._resourceguid

inner join vHWChassis hc on hc._resourceguid = idf._resourceguid

where ld.description = 'Disk drive' and ld.model like '%SSD%'

and hc.[Chassis Package Type] in (9,10,14)