Useful SCCM Disk Report

Recently I needed to write a custom SCCM Report in SQL Server Management Studio (SSMS) to report on hard drive info, including the computer name, user name, size, percentage free and amount of free disk space.  I hope this report saves you some time!

Also, if you’re seeing your devices listed as Null under FreeSpace0 it’s because you forgot to turn on Free Disk space collection under client settings.  Do that by following these steps!

SELECT DISTINCT
sys.Name0 AS 'Machine Name', cs.Model0 AS 'Model', cs.Manufacturer0 as 'Manufacturer',
sys.User_Name0 AS 'Last Logged on User', ld.Name0 AS 'Drive Letter',
  CASE
    when vdisk.Model0 like '%SSD%' then 'Known SSD Drive' + vdisk.Model0
    when vdisk.Model0 = 'LITEONIT LF-64M1S' or
    Vdisk.Model0 ='LITEONIT LFT-128M2S' then 'Known SSD Drive'
    else vdisk.Model0
  END
as 'Drive Type',
  CASE
   when se.ChassisTypes0 in ('3','4','6','7','15')then 'Desktop'
   when se.ChassisTypes0 in ('8','9','10','21')then 'Laptop'
   else 'SomethingTop'
  END
as 'DeviceType',
ld.Size0 as 'Total Drive Space on C: in MB',ld.FreeSpace0 as 'Free Hard Drive Space',((ld.FreeSpace0)/(CAST (ld.Size0 as Float))) as 'Percentage'
FROM v_R_System AS sys INNER JOIN
v_GS_COMPUTER_SYSTEM AS cs ON sys.ResourceID = cs.ResourceID INNER JOIN
v_GS_LOGICAL_DISK AS ld ON sys.ResourceID = ld.ResourceID INNER JOIN
v_GS_X86_PC_MEMORY AS mem ON sys.ResourceID = mem.ResourceID INNER JOIN
v_GS_SYSTEM_ENCLOSURE as SE on sys.ResourceID = se.ResourceID inner join
v_gs_Disk as vdisk on sys.resourceid = vdisk.resourceid
WHERE (vdisk.Model0 NOT LIKE '%USB%')
AND (vdisk.Model0 NOT LIKE '%SD MEMORY%')
AND (vdisk.Model0 <> 'SMART')
AND (sys.Active0 = 1) AND (sys.Decommissioned0 = 0)
AND (sys.SMBIOS_GUID0 IS NOT NULL) AND (ld.Name0 = 'C:')
and cs.Model0 <> 'Virtual Machine'
order by Percentage desc
/*Bonus fun queries below*/

--select distinct Manufacturer0 from v_GS_COMPUTER_SYSTEM
--select distinct ChassisTypes0 from v_GS_SYSTEM_ENCLOSURE
--select top 40 * from v_GS_LOGICAL_DISK where DeviceID0 = 'C:'

 

Advertisements