My boss is wanting me to create a custom widget for a dynamic top 10 list of "nodes that need attention" like nodes with CPU > 95, and memory > 95 and disk > 95% etc. Any ideas on how to achieve this with SWQL?

Smart nodes list  I imagine multiple widgets for windows servers, Linux servers, Network devices.  For items that are in duress and only those items.

Top Replies

  • That error message comes from this part of func_ModernDashboards.ps1.  See Everything you wanted to know about exceptions for details about how PowerShell handles exceptions, and pay attention to the $PSItem automatic variable that gives you details about the exception.  If you tweak the script to print that out in the catch block, you will likely get some additional details that will help you understand what went wrong.  Maybe something like this:

    Write-Error -Message "Error processing'$( $File.Fullname )': $PSItem"

  • Just out of curiosity, what version of PowerShell are you running? I ask because I ran into the same/similar issue as you, with the "Does not match JSON format". I then realized, out of bad habits, I had been running it in the old PowerShell ISE. I opened up a PowerShell 7 prompt, reran the same commands, and everything worked perfectly.

  • Thanks for your reply ,  I am running version 5.1.   Let me upgrade to 7 and test

  • Looks like I need to add a #Requires line for PowerShell 7 to be more thorough.

    Hey  - I added this as Pull Request #299.

  • Thanks!  I approved and merged the pull request.

  • Thanks all for your input.  I have it working.  I do end up with unmanaged devices and servers that I would like to remove from the list as we have a number of volumes called docshares that are full for a reason and we create new volumes.  

  • For the upper, edit the data source to exclude Unmanaged.

    SELECT [Nodes].Caption
         , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1
    ELSE [Nodes].CPULoad
         , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1
    ELSE [Nodes].PercentMemoryUsed
    END AS Memory
         , [Nodes].PercentLoss
         , [Nodes].Status
         , [Nodes].StatusDescription
         , [Nodes].DetailsUrl
    FROM Orion.Nodes
    WHERE [Nodes].Status NOT IN ( 1, 11 ) -- 1 = Up and 11 = External
      AND [Nodes].Unmanaged != 1 -- Skip unmanaged devices
    ORDER BY Severity DESC

    For the lower, you can ignore anything with 'docshare' in the name.

    SELECT [Volumes].Node.Caption AS [Node]
    , [Volumes].Node.DetailsUrl AS [NodeDetailsUrl]
         , CASE WHEN [Volumes].Type = 'Mount Point' THEN [Volumes].VolumeDescription
    IsNull([Volumes].DeviceId, [Volumes].VolumeDescription) END AS [Name]
         , [Volumes].Icon
         , [Volumes].Status     
         , [Volumes].Type
         , [Volumes].VolumePercentUsed
         , [Volumes].VolumeTypeIcon
         , [Volumes].DiskQueueLength
         , [Volumes].DiskTransfer
         , [Volumes].DiskReads
         , [Volumes].DiskWrites
         , [Volumes].TotalDiskIOPS
         , [Volumes].DetailsUrl
    FROM Orion.Volumes AS [Volumes]
    WHERE [Volumes].Type IN ( 'Fixed Disk', 'Mount Point', 'NetworkDisk', 'FixedDisk' )
       AND [Volumes].Responding = 'Y'
       AND [Status] <> 1
       AND [Volumes].Caption NOT LIKE '%docshare%' -- skip the docshare volumes
    ORDER BY [Volumes].VolumePercentUsed DESC

  • This also reminds me of an old sql dashboard I remember being on here that assigned various weights to different kinds of bad conditions and then made a table of nodes sorted by how unhappy they were.   Something like node down would be 1000 points,  high cpu might be 200.  I've also built swql dashboard for ops teams that had a ton of case logic embedded and unions where it would set different messages and include extra info on each node depending on the issues being detected.   It had all devices grouped up by where they fell in the data flow with edge firewalls at the top and going down through the aggregation layers until bottom being top of rack switches.  If no issues it would just display something like "edge firewalls are all ok" if not then it would list out falling conditions for devices in that category.   Was pretty useful for the Noc at a big ISP.

  • Thanks for all of the input guys.  My new Dashboard(s) is starting to take shape.  So one dash I would like to add is for volumes with less then 2 weeks forecast.  I found this query from  (Thanks Michael) and made a couple of small changes, and I am wondering how I convert/join etc to take the node ID to convert to actual node name.  

    My Query:

    SELECT VolumesForecastCapacity.DisplayName, VolumesForecastCapacity.NodeID, VolumesForecastCapacity.CurrentValue, VolumesForecastCapacity.DaysToCapacityAvg FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

    LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

    LEFT JOIN Orion.VolumesCustomProperties p ON p.VolumeID = VolumesForecastCapacity.InstanceID

    LEFT JOIN Orion.Volumes v ON v.VolumeID = VolumesForecastCapacity.InstanceID

    WHERE VolumesForecastCapacity.InstanceCaption != 'Virtual Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'Physical Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'Real Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'Memory Buffers'

    AND VolumesForecastCapacity.InstanceCaption != 'Cached Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'Shared Virtual Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'Shared Real Memory'

    AND VolumesForecastCapacity.InstanceCaption != 'swap space'

    AND VolumesForecastCapacity.DaysToCapacityAvg <= 14

    AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

    AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

    AND Status = 1
    AND Caption Not Like '%VM%'
    AND Caption Not Like 'MCPITSOLRWNDS2'
    AND Caption Not Like '%perf%'
    AND Caption Not Like '%test%'
    AND Caption Not Like '%Stage%'
    AND Caption Not Like '%DEV%'
    AND Caption Not Like '%QA%'
    AND Caption Not Like '%FS0%'

    Which gives me this:

  • You can get rid of all of those joins if you just use the Navigation Properties (shown bound in red below).

    Orion.VolumesForecastCapacity connects to Orion.Volumes, which connects to Orion.Nodes.

    I'm pretty sure that will give you what you need:

    SELECT [VForcast].Volume.Node.Caption AS [Node Caption]
         , [VForcast].DisplayName
         , [VForcast].CurrentValue
         , [VForcast].Volume.Size
         , [VForcast].DaysToWarningAvg
         , [VForcast].DaysToCriticalAvg
         , [VForcast].DaysToCapacityAvg
         , [VForcast].Volume.Type
    FROM Orion.VolumesForecastCapacity AS [VForcast]
    WHERE [VForcast].DaysToCapacityAvg <= 100000
         AND [VForcast].Volume.Type IN (
              'Fixed Disk'
              , 'FixedDisk'
              , 'Mount Point'
              , 'Network Disk'
    ORDER BY [VForcast].DaysToCapacityAvg

    Obviously change out the DaysToCapacityAvg number in the WHERE clause to something more reasonable.  I used 100000 to guarantee results in my environment.