This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

DISKS SPACE TREND Report with SQL Reporting Services

hcy01uk over 10 years ago

I created a Solarwinds Disk Usage Report using MS SQL reporting service. The SQL query should work for Solarwinds Report Writer too. The report will collect fixed disk usage (%) information for all nodes from 4 different days (yesterday(day1) and 2, 7 and 31 days ago). Then calculate the daily(day2-day1), weekly(day7-day1), monthly(daye31-day1) increase. If the usage is > 95%, or Daily Increase > 10%, or Weekly Increase > 20% or Monthly Increase > 30%, the information will be displayed. Because the calculation is only based on 4 days data, the results may not always 100% correct, but it should close the true picture which can be used to analyse the disk usage trend. Hope this helpful.

=========================================

Select TbDay1.NodeName, TbDay1.NodeID, TbDay1.DiskName, TbDay1.DiskID, TbDay1.Disk_Size,

TbDay1.Day1, TbDay1.Day1_DiskUsed, TbDay1.Day1_PercentDiskUsed,

TbDay2.Day2, TbDay2.Day2_DiskUsed, TbDay2.Day2_PercentDiskUsed,

Round(TbDay1.Day1_PercentDiskUsed - TbDay2.Day2_PercentDiskUsed,2) AS DailyIncrease,

TbDay7.Day7, TbDay7.Day7_DiskUsed, TbDay7.Day7_PercentDiskUsed,

Round(TbDay1.Day1_PercentDiskUsed - TbDay7.Day7_PercentDiskUsed,2) AS WeeklyIncrease,

TbDay30.Day30, TbDay30.Day30_DiskUsed, TbDay30.Day30_PercentDiskUsed,

Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed,2) AS MonthlyIncrease

FROM

(((

(SELECT

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

Volumes.VolumeID AS DiskID,

Volumes.Caption AS DiskName,

CONVERT(date,VolumeUsage.DateTime) AS Day1,

ROUND(AVG(VolumeUsage.DiskSize)/1024/1024/1024,1) AS Disk_Size,

ROUND(AVG(VolumeUsage.AvgDiskUsed)/1024/1024/1024,1) AS Day1_DiskUsed,

ROUND(AVG(VolumeUsage.PercentDiskUsed),1) AS Day1_PercentDiskUsed

FROM

(Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)) INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

WHERE

(Volumes.VolumeType ='Fixed Disk')

AND ( DateTime BETWEEN DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(hh, -22, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))))

GROUP BY CONVERT(date,VolumeUsage.DateTime), Nodes.NodeID, Volumes.VolumeID, Nodes.Caption, Volumes.Caption

) TbDay1

INNER JOIN

(SELECT

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

Volumes.VolumeID AS DiskID,

Volumes.Caption AS DiskName,

CONVERT(date,VolumeUsage.DateTime) AS Day2,

ROUND(AVG(VolumeUsage.DiskSize)/1024/1024/1024,1) AS Disk_Size,

ROUND(AVG(VolumeUsage.AvgDiskUsed)/1024/1024/1024,1) AS Day2_DiskUsed,

ROUND(AVG(VolumeUsage.PercentDiskUsed),1) AS Day2_PercentDiskUsed

FROM

(Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)) INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

WHERE

(Volumes.VolumeType ='Fixed Disk')

AND ( DateTime BETWEEN DATEADD(dd, -2, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(hh, -22, DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()))))

GROUP BY CONVERT(date,VolumeUsage.DateTime),Nodes.NodeID, Volumes.VolumeID, Nodes.Caption, Volumes.Caption

) TbDay2

ON (TbDay1.NodeID = TbDay2.NodeID AND TbDay1.DiskID = TbDay2.DiskID)

)

INNER JOIN

(SELECT

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

Volumes.VolumeID AS DiskID,

Volumes.Caption AS DiskName,

CONVERT(date,VolumeUsage.DateTime) AS Day7,

ROUND(AVG(VolumeUsage.DiskSize)/1024/1024/1024,1) AS Disk_Size,

ROUND(AVG(VolumeUsage.AvgDiskUsed)/1024/1024/1024,1) AS Day7_DiskUsed,

ROUND(AVG(VolumeUsage.PercentDiskUsed),1) AS Day7_PercentDiskUsed

FROM

(Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)) INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

WHERE

(Volumes.VolumeType ='Fixed Disk')

AND ( DateTime BETWEEN DATEADD(dd, -7, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(hh, -22, DATEADD(dd, -6, DATEDIFF(dd, 0, GETDATE()))))

GROUP BY CONVERT(date,VolumeUsage.DateTime),Nodes.NodeID, Volumes.VolumeID, Nodes.Caption, Volumes.Caption

) TbDay7

ON (TbDay7.NodeID = TbDay2.NodeID AND TbDay7.DiskID = TbDay2.DiskID)

)

INNER JOIN

(SELECT

Nodes.NodeID AS NodeID,

Nodes.Caption AS NodeName,

Volumes.VolumeID AS DiskID,

Volumes.Caption AS DiskName,

CONVERT(date,VolumeUsage.DateTime) AS Day30,

ROUND(AVG(VolumeUsage.DiskSize)/1024/1024/1024,1) AS Disk_Size,

ROUND(AVG(VolumeUsage.AvgDiskUsed)/1024/1024/1024,1) AS Day30_DiskUsed,

ROUND(AVG(VolumeUsage.PercentDiskUsed),1) AS Day30_PercentDiskUsed

FROM

(Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)) INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)

WHERE

(Volumes.VolumeType ='Fixed Disk')

AND ( DateTime BETWEEN DATEADD(dd, -31, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(hh, -22, DATEADD(dd, -30, DATEDIFF(dd, 0, GETDATE()))))

GROUP BY CONVERT(date,VolumeUsage.DateTime),Nodes.NodeID, Volumes.VolumeID, Nodes.Caption, Volumes.Caption

) TbDay30

ON (TbDay7.NodeID = TbDay30.NodeID AND TbDay7.DiskID = TbDay30.DiskID)

)

WHERE TbDay1.Day1_PercentDiskUsed > 95.0 OR Round(TbDay1.Day1_PercentDiskUsed - TbDay2.Day2_PercentDiskUsed,2) > 10.0 OR Round(TbDay1.Day1_PercentDiskUsed - TbDay7.Day7_PercentDiskUsed,2) > 20.0 OR Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed,2) > 30.0

ORDER BY TbDay1.NodeName, TbDay1.DiskName

0 trilobite_rex over 7 years ago

This could be nice for my environment, however it keeps knockin out my polling engine and the report never produces.
Any ideas of how to get around this?
I can run it from the server fine.
Cancel
Vote Up 0 Vote Down

Cancel
0 hcy01uk over 7 years ago

If you choose longer time range, The Query will search large amount data from tables, it will take long time to load results and slow down the polling engine. The latest NPM already has build in Volume, CPU & Memory capacity reporting functions.
Cancel
Vote Up 0 Vote Down

Cancel
0 hansons over 7 years ago

Hi hcy01uk,
just to let you know that this has saved me a lot of time, just wondered if you have setup any alerting off of the back of this for if the disk grows by x% then alert triggers ?
thanks again
Stuart
Cancel
Vote Up 0 Vote Down

Cancel
0 hcy01uk over 7 years ago

No alert is set-up in my environment. Just run as a weekly schedule task. You can set up some alerts if you have not many volumes monitored (or limited to certain nodes/volumes) otherwise run the report/alert against the whole monitored volumes frequently will cause some performance impact on database server.
Cancel
Vote Up 0 Vote Down

Cancel
0 jaxnoth over 5 years ago

Thanks so much for this!
I updated it to give current values if something changed as well as added a recommended value based upon allowing six months of growth:
SELECT TbDay1.NodeName
    ,TbDay1.DiskName
    ,'https://solarwinds.indwes.edu/Orion/NetPerfMon/VolumeDetails.aspx?NetObject=V:' + CAST(TbDay1.DiskID AS VARCHAR) DetailsURL
    ,TbDay1.VolumeSize AS Disk_Size
    ,TbDay1.VolumePercentUsed AS Day1_PercentDiskUsed
    ,TbDay1.VolumeSpaceAvailable
    ,TbDay2.Day2_PercentDiskUsed
    ,TbDay7.Day7_PercentDiskUsed
    ,TbDay30.Day30_PercentDiskUsed
    ,Round(TbDay1.Day1_PercentDiskUsed - TbDay2.Day2_PercentDiskUsed, 2) AS DailyIncrease
    ,Round(TbDay1.Day1_PercentDiskUsed - TbDay7.Day7_PercentDiskUsed, 2) AS WeeklyIncrease
    ,Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed, 2) AS MonthlyIncrease
    ,TbDay1.Day1_PercentDiskUsed + Round(TbDay1.Day1_PercentDiskUsed - TbDay7.Day7_PercentDiskUsed, 2) AS NextWeek
    ,TbDay1.VolumeSize + Round(TbDay1.VolumePercentUsed - TbDay30.Day30_PercentDiskUsed, 2) AS NextMonth
    ,CASE
        WHEN (Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed, 2) > 0)
            THEN TbDay1.VolumeSize + Round(Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed, 2) * 6 / 100 * TbDay1.VolumeSize, 0)
        ELSE TbDay1.Disk_Size
        END AS RecommendedSize
FROM (
    (
        (
            (
                SELECT Nodes.NodeID AS NodeID
                    ,Nodes.Caption AS NodeName
                    ,Volumes.VolumeID AS DiskID
                    ,Volumes.Caption AS DiskName
                    ,Volumes.VolumeSpaceAvailable
                    ,Volumes.VolumeSize
                    ,Volumes.VolumePercentUsed
                    ,CONVERT(DATE, VolumeUsage.DATETIME) AS Day1
                    ,ROUND(AVG(VolumeUsage.DiskSize), 1) AS Disk_Size
                    ,ROUND(AVG(VolumeUsage.AvgDiskUsed), 1) AS Day1_DiskUsed
                    ,ROUND(AVG(VolumeUsage.PercentDiskUsed), 1) AS Day1_PercentDiskUsed
                FROM (
                    Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)
                    )
                INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)
                WHERE (Volumes.VolumeType = 'Fixed Disk')
                    AND (
                        DATETIME BETWEEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
                            AND DATEADD(hh, - 22, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))
                        )
                GROUP BY CONVERT(DATE, VolumeUsage.DATETIME)
                    ,Nodes.NodeID
                    ,Volumes.VolumeID
                    ,Nodes.Caption
                    ,Volumes.Caption
                    ,Volumes.VolumeSpaceAvailable
                    ,Volumes.VolumeSize
                    ,Volumes.VolumePercentUsed
                ) TbDay1 INNER JOIN (
                SELECT Nodes.NodeID AS NodeID
                    ,Nodes.Caption AS NodeName
                    ,Volumes.VolumeID AS DiskID
                    ,Volumes.Caption AS DiskName
                    ,CONVERT(DATE, VolumeUsage.DATETIME) AS Day2
                    ,ROUND(AVG(VolumeUsage.DiskSize), 1) AS Disk_Size
                    ,ROUND(AVG(VolumeUsage.AvgDiskUsed), 1) AS Day2_DiskUsed
                    ,ROUND(AVG(VolumeUsage.PercentDiskUsed), 1) AS Day2_PercentDiskUsed
                FROM (
                    Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)
                    )
                INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)
                WHERE (Volumes.VolumeType = 'Fixed Disk')
                    AND (
                        DATETIME BETWEEN DATEADD(dd, - 2, DATEDIFF(dd, 0, GETDATE()))
                            AND DATEADD(hh, - 22, DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE())))
                        )
                GROUP BY CONVERT(DATE, VolumeUsage.DATETIME)
                    ,Nodes.NodeID
                    ,Volumes.VolumeID
                    ,Nodes.Caption
                    ,Volumes.Caption
                ) TbDay2 ON (
                    TbDay1.NodeID = TbDay2.NodeID
                    AND TbDay1.DiskID = TbDay2.DiskID
                    )
            ) INNER JOIN (
            SELECT Nodes.NodeID AS NodeID
                ,Nodes.Caption AS NodeName
                ,Volumes.VolumeID AS DiskID
                ,Volumes.Caption AS DiskName
                ,CONVERT(DATE, VolumeUsage.DATETIME) AS Day7
                ,ROUND(AVG(VolumeUsage.DiskSize), 1) AS Disk_Size
                ,ROUND(AVG(VolumeUsage.AvgDiskUsed), 1) AS Day7_DiskUsed
                ,ROUND(AVG(VolumeUsage.PercentDiskUsed), 1) AS Day7_PercentDiskUsed
            FROM (
                Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)
                )
            INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)
            WHERE (Volumes.VolumeType = 'Fixed Disk')
                AND (
                    DATETIME BETWEEN DATEADD(dd, - 7, DATEDIFF(dd, 0, GETDATE()))
                        AND DATEADD(hh, - 22, DATEADD(dd, - 6, DATEDIFF(dd, 0, GETDATE())))
                    )
            GROUP BY CONVERT(DATE, VolumeUsage.DATETIME)
                ,Nodes.NodeID
                ,Volumes.VolumeID
                ,Nodes.Caption
                ,Volumes.Caption
            ) TbDay7 ON (
                TbDay7.NodeID = TbDay2.NodeID
                AND TbDay7.DiskID = TbDay2.DiskID
                )
        ) INNER JOIN (
        SELECT Nodes.NodeID AS NodeID
            ,Nodes.Caption AS NodeName
            ,Volumes.VolumeID AS DiskID
            ,Volumes.Caption AS DiskName
            ,CONVERT(DATE, VolumeUsage.DATETIME) AS Day30
            ,ROUND(AVG(VolumeUsage.DiskSize), 1) AS Disk_Size
            ,ROUND(AVG(VolumeUsage.AvgDiskUsed), 1) AS Day30_DiskUsed
            ,ROUND(AVG(VolumeUsage.PercentDiskUsed), 1) AS Day30_PercentDiskUsed
        FROM (
            Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID)
            )
        INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)
        WHERE (Volumes.VolumeType = 'Fixed Disk')
            AND (
                DATETIME BETWEEN DATEADD(dd, - 31, DATEDIFF(dd, 0, GETDATE()))
                    AND DATEADD(hh, - 22, DATEADD(dd, - 30, DATEDIFF(dd, 0, GETDATE())))
                )
        GROUP BY CONVERT(DATE, VolumeUsage.DATETIME)
            ,Nodes.NodeID
            ,Volumes.VolumeID
            ,Nodes.Caption
            ,Volumes.Caption
        ) TbDay30 ON (
            TbDay7.NodeID = TbDay30.NodeID
            AND TbDay7.DiskID = TbDay30.DiskID
            )
    )
WHERE (
        TbDay1.Day1_PercentDiskUsed > 80.0
        AND Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed, 2) > 0
        )
    OR Round(TbDay1.Day1_PercentDiskUsed - TbDay2.Day2_PercentDiskUsed, 2) > 10.0
    OR Round(TbDay1.Day1_PercentDiskUsed - TbDay7.Day7_PercentDiskUsed, 2) > 20.0
    OR Round(TbDay1.Day1_PercentDiskUsed - TbDay30.Day30_PercentDiskUsed, 2) > 30.0
ORDER BY TbDay1.VolumePercentUsed DESC
Cancel
Vote Up 0 Vote Down

Cancel
0 Deltona over 4 years ago

Hi,
Thanks for this report! Could you please explain what purpose the NextMonth column has?
As far as I can tell, all it shows is the actual disk size.
Also, is the recommended size based on allowing 6 months of growth before this disk reaches 100 or 85%?
Cancel
Vote Up 0 Vote Down

Cancel