none
Total Disk Space vs. Disk Space Used

    Question

  • I'm not a SQL person so I was wondering if anyone has a report which can show me Total Disk Space vs Disk Space used on all servers?
    Have seen reports which show each separetly, but not together.
    Thanks.

    Tuesday, January 26, 2010 12:39 PM

Answers

  • This seems to do the trick! :)

    SELECT

     

    CollectionID as 'Collection ID',

    v_R_System

    .Netbios_Name0 [System Name],

    v_GS_COMPUTER_SYSTEM

    .Model0 as Model,

    SUM

     

    (v_GS_LOGICAL_DISK.Size0) AS [Total Disk Space:],

    SUM

     

    (v_GS_LOGICAL_DISK.FreeSpace0) AS [Total Free Space:],

    SUM

     

    (v_GS_LOGICAL_DISK.Size0)-SUM (v_GS_LOGICAL_DISK.FreeSpace0) AS [Total Used:]

    FROM

    v_R_System

     

    join v_GS_PC_BIOS on v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID

     

    join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID

     

    join v_GS_LOGICAL_DISK on v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID

     

    join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID=v_R_System.ResourceID

    AND

    v_FullCollectionMembership

    .CollectionID=@CollectionID

    where

    v_GS_COMPUTER_SYSTEM

    .Model0 like '%'

    and

    v_GS_LOGICAL_DISK

    .DeviceID0 like '%'

    GROUP

     

    BY

    CollectionID

    ,

    --v_R_System.ResourceID, -- no need to group this column as we are not selecting this in the query

    v_R_System

    .Netbios_Name0,

    --v_GS_LOGICAL_DISK.FreeSpace0, -- no need to group this column as we are not selecting this in the query

    v_GS_COMPUTER_SYSTEM

    .Model0

    ORDER

    by

     

    v_R_System.Netbios_Name0

    • Marked as answer by Lats Wednesday, January 27, 2010 3:51 PM
    Wednesday, January 27, 2010 3:50 PM

All replies

  • Check out the V_GS_Logical_Disk view
    In it you will see FreeSpace0 as well as Size0.  So this will give you Total disk space and the Disk space used.  You just need to do some subtraction on it to get the Disk Space = (Size - Freespace)


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    Tuesday, January 26, 2010 2:31 PM
    Moderator
  • Below is the query I have found and modified which shows me Total Disk Size on both C: and D: drives as well as how much is free.
    I would like to add a third column to show me Used Disk Space C: and D: drives.
    Using the formula above, how and where can I place that:

    SELECT

     

    --CollectionID as 'LDN001F2',

    v_R_System

    .Netbios_Name0 [System Name],

    v_GS_COMPUTER_SYSTEM

    .Model0 as Model,

    SUM

     

    (CASE WHEN v_GS_LOGICAL_DISK.DeviceID0 ='C:'

     

    THEN v_GS_LOGICAL_DISK.Size0 ELSE 0 END) AS [Total Disk Size C:],

    SUM

     

    (CASE WHEN v_GS_LOGICAL_DISK.DeviceID0 ='C:'

     

    THEN v_GS_LOGICAL_DISK.FreeSpace0 ELSE 0 END) AS [Free Space C:],

    SUM

     

    (CASE WHEN v_GS_LOGICAL_DISK.DeviceID0 ='D:'

     

    THEN v_GS_LOGICAL_DISK.Size0 ELSE 0 END) AS [Total Disk Size D:],

    SUM

     

    (CASE WHEN v_GS_LOGICAL_DISK.DeviceID0 ='D:'

     

    THEN v_GS_LOGICAL_DISK.FreeSpace0 ELSE 0 END) AS [Free Space D:]

    from

     

    v_R_System

    join

     

    v_GS_PC_BIOS on v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID

    join

     

    v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID

    join

     

    v_GS_LOGICAL_DISK on v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID

    join

     

    v_FullCollectionMembership on v_FullCollectionMembership.ResourceID=v_R_System.ResourceID

    AND

     

    v_FullCollectionMembership.CollectionID='LDN001F2'

    where

     

    v_GS_COMPUTER_SYSTEM.Model0 like '%'

    and

     

    v_GS_LOGICAL_DISK.DeviceID0 in ('C:','D:')

    GROUP

     

    BY

    CollectionID

    ,

    v_R_System

    .Netbios_Name0,

    v_R_System

    .ResourceID,

    v_GS_COMPUTER_SYSTEM

    .Model0

    order

     

    by v_R_System.Netbios_Name0

    Wednesday, January 27, 2010 10:15 AM
  • This seems to do the trick! :)

    SELECT

     

    CollectionID as 'Collection ID',

    v_R_System

    .Netbios_Name0 [System Name],

    v_GS_COMPUTER_SYSTEM

    .Model0 as Model,

    SUM

     

    (v_GS_LOGICAL_DISK.Size0) AS [Total Disk Space:],

    SUM

     

    (v_GS_LOGICAL_DISK.FreeSpace0) AS [Total Free Space:],

    SUM

     

    (v_GS_LOGICAL_DISK.Size0)-SUM (v_GS_LOGICAL_DISK.FreeSpace0) AS [Total Used:]

    FROM

    v_R_System

     

    join v_GS_PC_BIOS on v_R_System.ResourceID=v_GS_PC_BIOS.ResourceID

     

    join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID

     

    join v_GS_LOGICAL_DISK on v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID

     

    join v_FullCollectionMembership on v_FullCollectionMembership.ResourceID=v_R_System.ResourceID

    AND

    v_FullCollectionMembership

    .CollectionID=@CollectionID

    where

    v_GS_COMPUTER_SYSTEM

    .Model0 like '%'

    and

    v_GS_LOGICAL_DISK

    .DeviceID0 like '%'

    GROUP

     

    BY

    CollectionID

    ,

    --v_R_System.ResourceID, -- no need to group this column as we are not selecting this in the query

    v_R_System

    .Netbios_Name0,

    --v_GS_LOGICAL_DISK.FreeSpace0, -- no need to group this column as we are not selecting this in the query

    v_GS_COMPUTER_SYSTEM

    .Model0

    ORDER

    by

     

    v_R_System.Netbios_Name0

    • Marked as answer by Lats Wednesday, January 27, 2010 3:51 PM
    Wednesday, January 27, 2010 3:50 PM