Server Quarterly Storage Usage details along with its Forecast

Hello all,

I am new to SolarWinds, I need your guidance. I have written an SQL script which gives three months Server average details along with its forecast. Could you please have a look and share some knowledge, if there is something I need to add, missed or something is wrong. My aim is to have a below details per Customer for top 10 server or the server which need to highlight :

I have managed to write the SQL script for Storage(Volume) Utilisation :

Declare @StartDate DateTime,@EndDate DateTime

Set @StartDate = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)
Set @EndDate = DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))

Select A.NodeID,A.InstanceId,A.Server_Name As 'Server_Name',A.InstanceCaption As 'InstanceCaption',A.SIZE_GB As 'Size /GB',A.Percent_Used As 'Percent_Used',A.USED_GB As 'Used /GB',A.FREE_GB As 'Free /GB'
,A.Month As 'Month',
ISNULL(Sum(A.DiskUsed),0) As 'DiskUsed', ISNULL(Sum(A.PercentDiskUsed),0) As 'PercentDiskUsed',
A.Volume_Type_ID,A.Volume_Type
,A.Customer As 'Customer',A.Location As 'Location',

CASE
WHEN A.DaysToWarningAvg < 0
Then 'Now'
ELSE
CASE
WHEN A.DaysToWarningAvg > 365
THEN '>1 Year'
ELSE
CASE
WHEN A.DaysToWarningAvg between 31 and 365
THEN convert(varchar(10),round(A.DaysToWarningAvg/31,0)) + ' Months'
ELSE

CASE
WHEN A.DaysToWarningAvg between 7 and 31
THEN convert(varchar(10),round(A.DaysToWarningAvg/7,0)) + ' Weeks'
ELSE
CASE
WHEN A.DaysToWarningAvg between 0 and 31
THEN convert(varchar(10),A.DaysToWarningAvg) + ' DAYS'
END
END
END
END
END as 'Warning - Avg',

CASE
WHEN A.DaysToCriticalAvg < 0
Then 'Now'
ELSE

CASE
WHEN A.DaysToCriticalAvg > 365
THEN '>1 Year'
ELSE
CASE
WHEN A.DaysToCriticalAvg between 31 and 365
THEN convert(varchar(10),round(A.DaysToCriticalAvg/31,0)) + ' Months'
ELSE

CASE
WHEN A.DaysToCriticalAvg between 7 and 31
THEN convert(varchar(10),round(A.DaysToCriticalAvg/7,0)) + ' Weeks'
ELSE
CASE
WHEN A.DaysToCriticalAvg between 0 and 31
THEN convert(varchar(10),A.DaysToCriticalAvg) + ' DAYS'
END
END
END
END
END as 'Critical - Avg',

CASE
WHEN A.DaysToCapacityAvg < 0
Then 'Now'
ELSE

CASE
WHEN A.DaysToCapacityAvg > 365
THEN '>1 Year'
ELSE
CASE
WHEN A.DaysToCapacityAvg between 31 and 365
THEN convert(varchar(10),round(A.DaysToCapacityAvg/31,0)) + ' Months'
ELSE

CASE
WHEN A.DaysToCapacityAvg between 7 and 31
THEN convert(varchar(10),round(A.DaysToCapacityAvg/7,0)) + ' Weeks'
ELSE
CASE
WHEN A.DaysToCapacityAvg between 0 and 31
THEN convert(varchar(10),A.DaysToCapacityAvg) + ' DAYS'
END
END
END
END
END as 'Capacity - Avg'

FROM
(
SELECT
ForecastThresholds.NodeID
,ForecastThresholds.InstanceId
,N.Caption AS 'Server_Name'
,ForecastThresholds.Volume_Type As 'Volume_Type'
,ForecastThresholds.Volume_Type_ID As 'Volume_Type_ID'
,ForecastThresholds.SIZE_GB As 'SIZE_GB'
,ForecastThresholds.Percent_Used As 'Percent_Used'
,ForecastThresholds.USED_GB As 'USED_GB'
,ForecastThresholds.FREE_GB As 'FREE_GB'
,ForecastThresholds.Caption as InstanceCaption
,ForecastThresholds.Month As 'Month'
,ForecastThresholds.DiskUsed
,ForecastThresholds.PercentDiskUsed
,(CASE WHEN (ForecastThresholds.WarningThreshold IS NULL OR ForecastThresholds.Aavg IS NULL OR ForecastThresholds.Bavg is NULL OR ForecastThresholds.Bavg=0 ) THEN NULL
WHEN (ForecastThresholds.Bavg < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.WarningThreshold) THEN NULL
ELSE Floor(((ForecastThresholds.WarningThreshold - ForecastThresholds.Aavg)/ForecastThresholds.Bavg - ndays)) END) AS DaysToWarningAvg

,(CASE WHEN (ForecastThresholds.CriticalThreshold IS NULL OR ForecastThresholds.Aavg IS NULL OR ForecastThresholds.Bavg is NULL OR ForecastThresholds.Bavg=0 ) THEN NULL
WHEN (ForecastThresholds.Bavg < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.CriticalThreshold) THEN NULL
ELSE Floor(((ForecastThresholds.CriticalThreshold - ForecastThresholds.Aavg)/ForecastThresholds.Bavg - ndays)) END) AS DaysToCriticalAvg

,(CASE WHEN (ForecastThresholds.Capacitythreshold IS NULL OR ForecastThresholds.Aavg IS NULL OR ForecastThresholds.Bavg is NULL OR ForecastThresholds.Bavg=0 ) THEN NULL
WHEN (ForecastThresholds.Bavg < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.Capacitythreshold) THEN NULL
ELSE Floor(((ForecastThresholds.Capacitythreshold - ForecastThresholds.Aavg)/ForecastThresholds.Bavg - ndays)) END) AS DaysToCapacityAvg

,(CASE WHEN (ForecastThresholds.WarningThreshold IS NULL OR ForecastThresholds.Apeak IS NULL OR ForecastThresholds.Bpeak is NULL OR ForecastThresholds.Bpeak=0 ) THEN NULL
WHEN (ForecastThresholds.Bpeak < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.WarningThreshold) THEN NULL
ELSE Floor(((ForecastThresholds.WarningThreshold - ForecastThresholds.Apeak)/ForecastThresholds.Bpeak - ndays)) END) AS DaysToWarningPeak

,(CASE WHEN (ForecastThresholds.CriticalThreshold IS NULL OR ForecastThresholds.Apeak IS NULL OR ForecastThresholds.Bpeak is NULL OR ForecastThresholds.Bpeak=0) THEN NULL
WHEN (ForecastThresholds.Bpeak < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.CriticalThreshold) THEN NULL
ELSE Floor(((ForecastThresholds.CriticalThreshold - ForecastThresholds.Apeak)/ForecastThresholds.Bpeak - ndays)) END) AS DaysToCriticalPeak

,(CASE WHEN (ForecastThresholds.Capacitythreshold IS NULL OR ForecastThresholds.Apeak IS NULL OR ForecastThresholds.Bpeak is NULL OR ForecastThresholds.Bpeak=0) THEN NULL
WHEN (ForecastThresholds.Bpeak < 0 AND ForecastThresholds.CurrentValue < ForecastThresholds.Capacitythreshold) THEN NULL
ELSE Floor(((ForecastThresholds.Capacitythreshold - ForecastThresholds.Apeak)/ForecastThresholds.Bpeak - ndays)) END) AS DaysToCapacityPeak
,N.Customer As 'Customer'

,N.Northgate_Support_Team As 'Team'

,N.City As 'Location'
FROM
(
SELECT Distinct cfc.NodeID
,v.[VolumeID] AS InstanceId
,v.Caption
,fm.[EntityType]
,fm.[Id] AS MetricId
,fm.[Name] AS MetricName
,V.VolumeType AS 'Volume_Type'
,V.VolumeTypeID As 'Volume_Type_ID'
,ROUND(V.VolumeSize/1073741824,0) AS 'SIZE_GB'
,ROUND(V.VolumePercentUsed,0) AS 'Percent_Used'
,ROUND(V.VolumeSpaceUsed/1073741824,0) AS 'USED_GB' --VolumeSpaceUsed is calculated in Bytes (/ by 1073741824 to get GB)
,ROUND(V.VolumeSpaceAvailable/1073741824,0) AS 'FREE_GB' --------VolumeSpaceAvailable is calculated in Bytes (/ by 1073741824 to get GB)
,ISNULL (fm.[ThresholdType], 0) AS ThresholdType
,DATENAME(MONTH, DateTime) As 'Month'
,ROUND(AVG(VolumeUsage.AvgDiskUsed)/1024/1024/1024,1) AS DiskUsed
,ROUND(AVG(VolumeUsage.PercentDiskUsed),1) AS PercentDiskUsed
,v.[VolumePercentUsed] AS CurrentValue
,Floor(( CASE WHEN (fcs.[WarningThreshold] IS NOT NULL) THEN fcs.[WarningThreshold]
WHEN (fm.[ThresholdType] IS NULL OR fm.[ThresholdType]=0) THEN ls.[GlobalWarningThreshold]
ELSE NULL END
)) AS WarningThreshold
,Floor(( CASE WHEN (fcs.[CriticalThreshold] IS NOT NULL) THEN fcs.[CriticalThreshold]
WHEN (fm.[ThresholdType] IS NULL OR fm.[ThresholdType]=0) THEN ls.[GlobalCriticalThreshold]
ELSE NULL END
)) AS CriticalThreshold
, ISNULL(fcs.[CapacityThreshold], ls.[GlobalCapacityThreshold]) AS Capacitythreshold
,(CASE WHEN fm.[Name] = N'Forecast.Metric.PercentDiskUsed' THEN cfc.[PercentDiskUsedAavg]
ELSE NULL END) AS Aavg
,(CASE WHEN fm.[Name] = N'Forecast.Metric.PercentDiskUsed' THEN cfc.[PercentDiskUsedBavg]
ELSE NULL END) AS Bavg
,(CASE WHEN fm.[Name] = N'Forecast.Metric.PercentDiskUsed' THEN cfc.[PercentDiskUsedApeak]
ELSE NULL END) AS Apeak
,(CASE WHEN fm.[Name] = N'Forecast.Metric.PercentDiskUsed' THEN cfc.[PercentDiskUsedBpeak]
ELSE NULL END) AS Bpeak
,(DATEDIFF(day, cfc.[Timestamp], CurrentTime)) AS ndays
FROM [dbo].[VolumeUsage_ForecastCoefficients] AS cfc WITH (NOLOCK)

CROSS JOIN (SELECT Id
,Name
,EntityType
,ThresholdType
,GETUTCDATE() AS CurrentTime
,NULL AS ThresholdName
FROM [dbo].[ForecastMetrics]
WHERE (EntityType = N'Orion.Volumes' AND [Name] = N'Forecast.Metric.PercentDiskUsed' )) fm
CROSS APPLY (SELECT
ISNULL((SELECT TOP 1 sts.CurrentValue FROM [dbo].[ForecastMetrics] AS fmm WITH (NOLOCK)
INNER JOIN [dbo].[Settings] AS sts WITH (NOLOCK) ON sts.[SettingID] LIKE fmm.[CriticalThresholdSettingID]
WHERE fmm.EntityType = N'Orion.Volumes'),0) GlobalCriticalThreshold,
ISNULL((SELECT TOP 1 sts.CurrentValue FROM [dbo].[ForecastMetrics] AS fmm WITH (NOLOCK)
INNER JOIN [dbo].[Settings] AS sts WITH (NOLOCK) ON sts.[SettingID] LIKE fmm.[WarningThresholdSettingID]
WHERE fmm.EntityType = N'Orion.Volumes'),0) GlobalWarningThreshold,
ISNULL((SELECT TOP 1 sts.CurrentValue FROM [dbo].[ForecastMetrics] AS fmm WITH (NOLOCK)
INNER JOIN [dbo].[Settings] AS sts WITH (NOLOCK) ON sts.[SettingID] LIKE fmm.[CapacityThresholdSettingID]
WHERE fmm.EntityType = N'Orion.Volumes'),100) GlobalCapacityThreshold
)ls
LEFT JOIN [dbo].[Volumes] AS v ON cfc.[VolumeID] = v.[VolumeID]
LEFT JOIN VolumeUsage ON (V.VolumeID = VolumeUsage.VolumeID)

AND ( Convert(Varchar, VolumeUsage.DateTime,112) Between Convert(Varchar, @StartDate,112) AND Convert(Varchar, @EndDate,112) )

LEFT JOIN ForecastCapacitySettings AS fcs ON v.[VolumeID] = fcs.[InstanceId] AND fcs.[MetricId] = fm.[Id]
Group by cfc.NodeID,v.[VolumeID],v.Caption,fm.[EntityType],fm.[Id],fm.[Name],V.VolumeType,V.VolumeTypeID,V.VolumeSize,v.[VolumePercentUsed],V.VolumeSpaceUsed,
V.VolumeSpaceAvailable,fm.[ThresholdType],fcs.[WarningThreshold],ls.[GlobalWarningThreshold],fcs.[CriticalThreshold],ls.[GlobalCapacityThreshold],
ls.[GlobalCriticalThreshold],fcs.[CapacityThreshold],cfc.[PercentDiskUsedAavg],cfc.[PercentDiskUsedBavg],cfc.[PercentDiskUsedApeak],cfc.[PercentDiskUsedBpeak],
cfc.[Timestamp], CurrentTime,DATENAME(MONTH, DateTime)
) AS ForecastThresholds
LEFT JOIN dbo.Nodes AS N with(nolock) ON N.NodeID =ForecastThresholds.NodeID
Where

N.Customer='Your Customer' and N.City in ('Required City(Location)')
--and ForecastThresholds.Bpeak>0 and ForecastThresholds.Bavg>0
) A
Group by A.NodeID,A.InstanceId,A.Server_Name,A.InstanceCaption,A.SIZE_GB,A.Percent_Used,A.USED_GB,A.FREE_GB,A.Month,A.Volume_Type,A.Customer,
A.Location,A.DaysToWarningAvg,A.DaysToCriticalAvg,A.DaysToCapacityAvg,A.Volume_Type_ID,A.Volume_Type

Your kind help will be really appreciated.

Cheers.