cancel
Showing results for 
Search instead for 
Did you mean: 
designerfx
Level 16

Easy SWQL Volume Query (for widget/dashboard)

Standard MVP collab with  dgsmith80​​ , mesverrum​​ , m_roberts​ birthed a tiny little volume baby. So, here's a query and a search string to filter for some volumes, based on them being >80% full and <5GB free.  This one's a two part query for the search vs the regular part

SELECT FullName AS [Node]
,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node] --this gives nodes a status icon
,v.Node.DetailsURL as [_LinkFor_Node] --this lets us show node information on hover from [Node]
,ROUND(VolumeSize/1073741824,2) as Vol_GB --volume storage deals in 1024, this divides by GB
,ROUND(VolumeSpaceAvailable/1073741824,2)  AS GB_Free
,Caption AS [Volume] --this shows caption and the detailsURL info
,DetailsURL as [_LinkFor_Volume] --this lets us show volume information on hover from [Volume]
,v.Node.IP
,ROUND(VolumePercentAvailable,2) as Avail_Percent
,ROUND(VolumePercentUsed,2) as [UsedPercent]
,CASE
     WHEN v.VolumePercentUsed > v.ForecastCapacity.CriticalThreshold THEN '/Orion/images/StatusIcons/Small-Critical.gif'
    WHEN v.VolumePercentUsed > v.ForecastCapacity.WarningThreshold THEN '/Orion/images/StatusIcons/Small-Warning.gif'
    ELSE '/Orion/images/StatusIcons/Small-Up.gif'
    END AS [_IconFor_UsedPercent]
--incorporate some volume forecasting
,ROUND(VolumeSpaceUsed/1073741824,2) as GB_Used
--uncomment the following line below via removing the -- to then customize and add a custom property of your choosing
--,v.Node.CustomProperties.Device_Class as Application
FROM Orion.Volumes as V
Where Caption LIKE '%\%' AND (VolumePercentUsed > 80 AND VolumeSpaceAvailable < 5368709120)
ORDER BY VolumePercentUsed ASC

to turn this into a searchable SWQL query, you need to add the following as a search window.

SELECT FullName AS [Node]
,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node]
,v.Node.DetailsURL as [_LinkFor_Node]
,ROUND(VolumeSize/1073741824,2) as Vol_GB
,ROUND(VolumeSpaceAvailable/1073741824,2)  AS GB_Free
,Caption AS [Volume]
,DetailsURL as [_LinkFor_Volume]
,v.Node.IP
,ROUND(VolumePercentAvailable,2) as Avail_Percent
,ROUND(VolumePercentUsed,2) as [UsedPercent]
,CASE
     WHEN v.VolumePercentUsed > v.ForecastCapacity.CriticalThreshold THEN '/Orion/images/StatusIcons/Small-Critical.gif'
    WHEN v.VolumePercentUsed > v.ForecastCapacity.WarningThreshold THEN '/Orion/images/StatusIcons/Small-Warning.gif'
    ELSE '/Orion/images/StatusIcons/Small-Up.gif'
    END AS [_IconFor_UsedPercent]
,ROUND(VolumeSpaceUsed/1073741824,2) as GB_Used
--uncomment the following line below via removing the -- to then customize and add a custom property of your choosing
--,v.Node.CustomProperties.Device_Class as Application
FROM Orion.Volumes as V
-- the first WHERE adds the search query below
WHERE (Caption LIKE '%${SEARCH_STRING}%' OR v.Node.Caption LIKE '%${SEARCH_STRING}%') AND (Caption LIKE '%\%' AND (VolumePercentUsed > 80 AND VolumeSpaceAvailable < 5368709120))
--replace above version with below version if you have a custom property you want to filter, you can change Device_Class to be any custom property you want to search
--WHERE (Caption LIKE '%${SEARCH_STRING}%' OR v.Node.Caption LIKE '%${SEARCH_STRING}%' OR v.Node.CustomProperties.Device_Class LIKE '%${SEARCH_STRING}%') AND (Caption LIKE '%\%' AND (VolumePercentUsed > 80 AND VolumeSpaceAvailable < 5368709120))
ORDER BY VolumePercentUsed ASC

So what this means is anything that I put into the first

WHERE 

and I specify the match as

LIKE '%${SEARCH_STRING}%' 

will then search the results of the above query and filter accordingly. So the way I've set this up, you can search the name of the the node, the name of the volume, or a defined custom property. Here's how it looks:

pastedImage_6.png

So if I search XHG or Exchange, C: or D:, it will filter accordingly.

commented out the special custom properties, fixed a typo

0 Kudos
3 Replies
olessandr
Level 8

Re: Easy SWQL Volume Query (for widget/dashboard)

There was an error processing the request.

0 Kudos
designerfx
Level 16

Re: Easy SWQL Volume Query (for widget/dashboard)

olessandr​ you need to define a custom property here, line 18 above, 17 in search string or remove the search part

v.Node.CustomProperties.Device_Class as Application --replace with custom property of your choosing

so v.node.customproperties.yourcustomproperty

and also in lines 17 & 20 within the search query.

WHERE (Caption LIKE '%${SEARCH_STRING}%' OR v.Node.Caption LIKE '%${SEARCH_STRING}%' OR v.Node.CustomProperties.Device_Class LIKE '%${SEARCH_STRING}%') AND (Caption LIKE '%\%' AND (VolumePercentUsed > 80 AND VolumeSpaceAvailable < 5368709120)) 

designerfx
Level 16

Re: Easy SWQL Volume Query (for widget/dashboard)

olessandr​ I missed a comma on v.Node.CustomProperties.Device_Class as Application - this should be

,v.Node.CustomProperties.Device_Class as Application