SQL Historical Volume Utilisation Script / Report

I have been working on a SQL script that will pull the monthly average volume utilisation of a selected node for the past 6 months. What I have so far only covers a single month by matching the timestamp and I can't work out how to cover multiple months in the same script.

SELECT vd.[NodeID],vd.VolumeID,v.volumedescription
,ROUND((AVG(vd.DiskSize)/1024/1024/1024),0) AS SizeGB
,ROUND((AVG(vd.AvgDiskUsed)/1024/1024/1024),0) AS UsedGB
FROM [SolarWindsOrion].[dbo].[VolumeUsage_CS_Daily_hist] vd
INNER JOIN Volumes v on v.volumeid = vd.volumeid
WHERE
TIMESTAMP like '2024-12%' and
v.volumetype='Fixed Disk'
AND vd.NODEID=[xxxx]
GROUP BY vd.VolumeID,vd.NodeID,vd.DiskSize,v.volumedescription

I know this can be done in NPM reports but I intend to use the data in SQL Reporting

If anyone could point me in the right direction, it would be much appreciated.

Thanks

Find more posts tagged with

capacity report

volume utilization

sql query

Accepted answers

All comments

stuartd

<span class="hljs-keyword">Have you tried:</span>

<span class="hljs-keyword">WHERE</span> TIMESTAMP <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">MONTH</span>, <span class="hljs-number">-6</span>, GETDATE())

<span class="hljs-keyword"></span>

ja18

Yes but that would average over 6 months. What I need is a month by month average for each volume

stuartd

Ahh sorry - didn't see that bit about separate months.

Then I can only suggest adding data blocks (in a report) for each month you want and adjust your script to grab the month 6, then 5, etc.

ja18

My fault, I didn't include it in the initial request, thanks for the suggestion