Missing Node Name in Volume Capacity Forecast Report

For some reason they made some weird choices with the Volume forecast tables so they don't join up as easily as one would hope in the GUI.

This is the custom SWQL query I use for keeping an eye on disk volumes, hopefully you can adjust it for what you need

SELECT n.caption as [Node]

, v.Caption as [Volume]

, round(v.VolumePercentUsed,1) as [Space Used]

,round(v.volumespaceavailable/1073741824,1) as [Free GB]

,case when f.DaysToCapacityPeak is null then 'No Forecast'

when f.DaysToCapacityPeak<0 then 'Full'

when f.DaysToCapacityPeak<91 then tostring(f.DaysToCapacityPeak)

when f.DaysToCapacityPeak>90 then '>90 Days'

end as [Days til Full],

v.DetailsUrl as [_linkfor_Volume]

, n.DetailsUrl as [_linkfor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]

, case when fc.WarningThreshold is null and v.VolumePercentUsed > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') then '/Orion/images/StatusIcons/Small-Critical.gif'

when fc.WarningThreshold is null and v.VolumePercentUsed > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') then '/Orion/images/StatusIcons/Small-Warning.gif'

when v.VolumePercentUsed>f.CriticalThreshold then '/Orion/images/StatusIcons/Small-Critical.gif'

when v.VolumePercentUsed>f.WarningThreshold then '/Orion/images/StatusIcons/Small-Warning.gif'

end as [_Iconfor_Space Used]

,'/Orion/images/StatusIcons/Small-' + v.StatusIcon AS [_IconFor_Volume]

from orion.Volumes v

join orion.nodes n on n.nodeid=v.nodeid

left join Orion.ForecastCapacitySettings fc on fc.InstanceId=v.VolumeID and fc.metricid=3

left join Orion.ForecastCapacity f on f.InstanceId = v.VolumeID and f.EntityType='Orion.volumes'

where (v.volumetype like 'fixed%' or v.volumetype like '%network%' or v.volumetype like '%mount%') and v.volumesize>500000000

and v.volumepercentused > case when fc.WarningThreshold is null then (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning')

else fc.CriticalThreshold end

--and v.customproperties.SupressVolume=0

--and v.FullName like '%${SEARCH_STRING}%'

order by currentvalue desc

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

thanks mesverrum,

     can you add total disk size & disk used percentage column.??? And filter nodes by nodes custom properties. That will really help. !!

SELECT n.caption as [Node]

, v.Caption as [Volume]

, round(v.VolumePercentUsed,1) as [Space Used %]

,round(v.volumesize/1073741824,1) as [Total GB]

,round(v.volumespaceavailable/1073741824,1) as [Free GB]

,case when f.DaysToCapacityPeak is null then 'No Forecast'

when f.DaysToCapacityPeak<0 then 'Full'

when f.DaysToCapacityPeak<91 then tostring(f.DaysToCapacityPeak)

when f.DaysToCapacityPeak>90 then '>90 Days'

end as [Days til Full],

v.DetailsUrl as [_linkfor_Volume]

, n.DetailsUrl as [_linkfor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]

, case when fc.WarningThreshold is null and v.VolumePercentUsed > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Error') then '/Orion/images/StatusIcons/Small-Critical.gif'

when fc.WarningThreshold is null and v.VolumePercentUsed > (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning') then '/Orion/images/StatusIcons/Small-Warning.gif'

when v.VolumePercentUsed>f.CriticalThreshold then '/Orion/images/StatusIcons/Small-Critical.gif'

when v.VolumePercentUsed>f.WarningThreshold then '/Orion/images/StatusIcons/Small-Warning.gif'

end as [_Iconfor_Space Used %]

,'/Orion/images/StatusIcons/Small-' + v.StatusIcon AS [_IconFor_Volume]

from orion.Volumes v

join orion.nodes n on n.nodeid=v.nodeid

left join Orion.ForecastCapacitySettings fc on fc.InstanceId=v.VolumeID and fc.metricid=3

left join Orion.ForecastCapacity f on f.InstanceId = v.VolumeID and f.EntityType='Orion.volumes'

where (v.volumetype like 'fixed%' or v.volumetype like '%network%' or v.volumetype like '%mount%') and v.volumesize>500000000

and v.volumepercentused > case when fc.WarningThreshold is null then (Select CurrentValue AS [col1] FROM Orion.Settings WHERE SettingID = 'NetPerfMon-DiskSpace-Warning')

else fc.CriticalThreshold end

and n.customproperties.[yourproperty]='yourcommenthere'

--and v.FullName like '%${SEARCH_STRING}%'

order by currentvalue desc

Keep an eye out for the line near the end for the custom property filter

and n.customproperties.[yourproperty]='yourcommenthere'

I'm running it from web reports, by selecting "report>custom  table> then selection method as SWQL".. but while clicking on preview its showed "* Query is not valid".

This was written to use in the custom query resource, custom table is a little fussy about some of the more advanced swql maneuvers.

thanks.. It worked..

but not fully, i have custom property on 5 nodes, they all showing capacity forecast on summary page, but with your code, only 1 node came with only 1 volume mentioned.

The report the way I use it has several filters such as only showing disks above 500 mb, with the volume type of fixed/mount/network, that are above their warning threshold or the global warning threshold.  You should probably just remove the where conditions until your stuff shows up.  The most generic version that still checks the custom property would be like this:

where n.customproperties.[yourproperty]='yourcommenthere'

I think, now its on my own.. appreciate you help. thanks