SWQL Needed for disk volume usage - Modern Dashbord

jasonford1971

Hi - need some SWQL for

1 - disk volume usage for modern dashboard

2 - Which widget would be the best to use

thanks

Find more posts tagged with

Accepted answers

bourlis

Sorry that's my bad!!!! I forgot to add the "where" statement.

This should do the trick.

FROM Orion.Volumes AS v
where v.Node.CustomProperties.CoreProductionSystem LIKE '%YES%'
with nolock

All comments

LeBeauUK

Hi @jasonford1971 it all depends on what you want it to display you can do a straight forward table or you can build a Perfstack and put it in the Modern Dashboard.

bourlis

Hi Jasonford1971, while I agree with LebeauUK I would be very careful with the PerStack option. Personally, I would only suggest that if you want to show of 4-5 drives, any more than that then you could potentially see a web performance degradation at least on that page/tab.

Here's my 3 standard modern drive performance widgets that I build for all of my customers. Side note, there are a few variances like disposition & system drive versus non-system drives. But all-in-all my customers love these.

Here's my standard production/system drive query:

SELECT
v.Node.Caption AS [System Name]
--**remove the below line if you don't care about system/non-system drives.**
,CONCAT(v.DeviceId,'/') AS [Drive]
, round(v.DiskQueueLength * 1.0 ,3) as [Disk Queue Length]
, round(v.DiskTransfer * 1.0 ,3) as [Disk Transfer]
, round(v.DiskReads * 1.0 ,3) as [Disk Reads]
, round(v.DiskWrites * 1.0 ,3) as [Disk Writes]
, v.Node.DetailsUrl AS LinkForNode
, v.DetailsUrl
, v.Icon
, v.StatusIcon

FROM Orion.Volumes AS v

WHERE 1=1
    and (v.DeviceId = 'C:')
    --**If you use custom properties for you teams replace lines below with your own custom property.
    --**the script below covers systems with or without a responsible team custom property.
    and (v.Node.CustomProperties.Responsible_Team = 'Server_OPS' or v.Node.CustomProperties.Responsible_Team is NULL)
    --**The lines below cover disposition based off of our custom properties.
    and (v.Node.CustomProperties._Disposition = 'PROD' 
    or v.Node.CustomProperties._Disposition is NULL)
with nolock

Here's what this widget looks like:

Here's my widget for drive space, again I'm focused on the system drive, production systems and systems supported by a certain team.

SELECT
v.Node.Caption AS [System Name]
,CONCAT(v.DeviceId,'/') AS [Drive]
,CASE WHEN V.size >= 1099511627776 THEN CONCAT(ROUND(V.size/1099511627776,2), ' TB')
    WHEN V.size >= 1073741824 THEN CONCAT(ROUND(V.size/1073741824,2), ' GB')
    WHEN V.size >= 1048576 THEN CONCAT(ROUND(V.size/1048576,2),' MB')
    WHEN V.size >= 1024 THEN CONCAT(ROUND(V.size/1024,2),' KB')
    Else '0' END as [VolumeSize]
,CASE WHEN v.VolumeSpaceUsed >= 1099511627776 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1099511627776,2), ' TB')
    WHEN v.VolumeSpaceUsed >= 1073741824 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1073741824,2), ' GB')
    WHEN v.VolumeSpaceUsed >= 1048576 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1048576,2),' MB')
    WHEN v.VolumeSpaceUsed >= 1024 THEN CONCAT(ROUND(v.VolumeSpaceUsed/1024,2),' KB')
    Else '0' END as [Space Used]
,CASE WHEN v.VolumeSpaceAvailable >= 1099511627776 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1099511627776,2), ' TB')
    WHEN v.VolumeSpaceAvailable >= 1073741824 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1073741824,2), ' GB')
    WHEN v.VolumeSpaceAvailable >= 1048576 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1048576,2),' MB')
    WHEN v.VolumeSpaceAvailable >= 1024 THEN CONCAT(ROUND(v.VolumeSpaceAvailable/1024,2),' KB')
    Else '0' END as [Space Available]
,CONCAT(round(V.VolumePercentAvailable,1),'%') AS [Percent Available]
,ROUND(VolumePercentUsed, 2) AS [Percent Used]
, CONCAT (v.PercentDiskUsedThreshold.Level1Value,' %') as [Warning % Threshold]
, CONCAT (v.PercentDiskUsedThreshold.Level2Value,' %')  as [Critical % Threshold]
, '/Orion/images/ForecastingIcons/volumeutilization.png' AS [_IconFor_Volume]
, '/NetPerfMon/images/Volumes/' + v.icon AS [_IconFor_VolumeSize]
, '/Orion/images/StatusIcons/Small-' + v.StatusLED AS [_IconFor_Percent Used]
, v.Node.DetailsUrl AS LinkForNode
, v.DetailsUrl AS LinkForVolume

FROM Orion.Volumes AS v
WHERE 1=1
    and (v.DeviceId = 'C:')
    and (v.Node.CustomProperties.Responsible_Team = 'Server_OPS' 
    or v.Node.CustomProperties.Responsible_Team is NULL)
    and (v.Node.CustomProperties._Disposition = 'PROD' 
    or v.Node.CustomProperties._Disposition is NULL)
with nolock

and here's what this widget looks like:

Lastly, for the hat-trick I supply this forecast widget

SELECT
N.Caption as [System Name]
,N.DetailsUrl as [Node Link]
,N.Volumes.DetailsUrl as [Drive Link]
,CONCAT(n.Volumes.DeviceId,'/') AS [Drive]
,CASE 
    WHEN F.DaysToWarningAvg is NULL THEN tostring('Not Calculated') 
    WHEN F.DaysToWarningAvg <= 0 then tostring('Now')
    WHEN F.DaysToWarningAvg >= 1825 THEN tostring('> 5 Years') 
    WHEN F.DaysToWarningAvg >= 365 THEN tostring('> 1 Year')
    WHEN F.DaysToWarningAvg >= 90 THEN tostring('> 90 Days')
    WHEN F.DaysToWarningAvg >= 60 THEN tostring('> 60 Days')
    WHEN F.DaysToWarningAvg >= 30 THEN tostring('> 30 Days')
    ELSE tostring(tostring(F.DaysToWarningAvg) + ' Days') 
    END AS [Warning]
, CASE 
    WHEN  F.DaysToCriticalAvg is NULL THEN tostring('Not Calculated') 
    WHEN F.DaysToCriticalAvg <= 0 then tostring('Now') 
    WHEN F.DaysToCriticalAvg >= 1825 THEN tostring('> 5 Years') 
    WHEN F.DaysToCriticalAvg >= 365 THEN tostring('> 1 Year')
    WHEN F.DaysToCriticalAvg >= 90 THEN tostring('> 90 Days')
    WHEN F.DaysToCriticalAvg >= 60 THEN tostring('> 60 Days')
    WHEN F.DaysToCriticalAvg >= 30 THEN tostring('> 30 Days')
    ELSE tostring(tostring(F.DaysToCriticalAvg) + ' Days') 
    END AS [CRITICAL]
, CASE
    WHEN F.DaysToCapacityAvg is NULL THEN tostring('Not Calculated') 
    WHEN F.DaysToCapacityAvg <= 0 then tostring('Now') 
    WHEN F.DaysToCapacityAvg >= 1825 THEN tostring('> 5 Years') 
    WHEN F.DaysToCapacityAvg >= 365 THEN tostring('> 1 Year')
    WHEN F.DaysToCapacityAvg >= 90 THEN tostring('> 90 Days')
    WHEN F.DaysToCapacityAvg >= 60 THEN tostring('> 60 Days')
    WHEN F.DaysToCapacityAvg >= 30 THEN tostring('> 30 Days')
    ELSE tostring(tostring(f.DaysToCapacityAvg) + ' Days')
    END  as [AT CAPACITY]

FROM orion.forecastcapacity F

left join Orion.Nodes N on f.NodeID = n.NodeID

WHERE 1=1
    and ((N.CustomProperties.ITD_Responsible_Team = 'Server_Ops'
    or N.CustomProperties.ITD_Responsible_Team is NULL)
    and (N.CustomProperties._Disposition = 'PROD')
     or N.CustomProperties._Disposition is NULL)
    and ((N.Volumes.DeviceId BETWEEN 'C' and 'Z') 
    and (F.DaysToCapacityAvg is NOT NULL))

order by F.DaysToCapacityAvg ASC
with nolock

which looks like this:

I hope these help. If you want I can this view and post it on resource share.

jasonford1971

thanks for replies - very much appreciated, are the above table widgets ?

bourlis

Yes, widgets for a modern dashboard. This is what it looks like when put all together.

jasonford1971

Have tried the above and the SWQL is not working - Provided SWQL query is not valid is the error being displayed