Server Management - Systems Insight Manager
Showing results for 
Search instead for 
Did you mean: 

SQL Query to Show historic Drive Space Usage

Ben Hanson_1

SQL Query to Show historic Drive Space Usage

--Just posting this if anyone needs something like it.

select DATEADD(SECOND, Snapshot.CollectionDateTime / 1000, '19691231 20:00') AS CollectDate, R_LogicalDisks.DeviceName, R_LogicalDisks.Description, R_LogicalDisks.SizeMb, R_LogicalDisks.UsedMb, (R_LogicalDisks.SizeMb - R_LogicalDisks.UsedMb) AS FreeMB,R_LogicalDisks.UsedPercent from R_LogicalDisks
Left OUTER JOIN Snapshot on R_LogicalDisks.SnapshotID=SnapShot.SnapshotID
WHERE R_LogicalDisks.Description like '%Fixed%' AND NOT R_LogicalDisks.UsedMB is NULL AND NOT ((R_LogicalDisks.SnapShotID=-1) OR (R_LogicalDisks.SnapShotID is NULL)) AND DeviceKey in
(select devices.deviceKey from devices
LEFT OUTER JOIN notices ON devices.deviceKey = notices.deviceKey
LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey
LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0
LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey
LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey
LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey
LEFT OUTER JOIN LockdownStatus ON devices.deviceKey = LockdownStatus.deviceKey
LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6
JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '000000065b882db90000000400000007'
WHERE ( ( devices.productType = 1) ) )

order by R_LogicalDisks.DeviceName ASC, R_LogicalDisks.Description ASC, CollectionDateTime ASC
--order by R_LogicalDisks.UsedPercent DESC