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

Parents
  • Since "under pressure" can mean different things to different devices, you might want to rely on the native Thresholds and their reflected status.

    Also, did you want this on a classic dashboard (called a 'view') or a Modern Dashboard?

    Why would you segregate Windows vs. Linux vs. Network Gear?  As far as Orion is concerned, they are all "nodes" and you can get the CPU, Memory, and Response time for each of them from the same place.

    You could do something like this in Modern Dashboards

  • Kevin,

    Thanks for your reply.  I am wanting to master modern dashboards and SWQL type dashboards.  What you showed is a great start.  Can you share your query?  Thanks  Matt

Reply Children
  • I can do you one better.  Here's the entire dashboard (as a JSON) which you can import using the Import-ModernDashboard PowerShell function in OrionSDK/func_ModernDashboards.ps1 at master · solarwinds/OrionSDK (github.com).

    Monitored Entities Needing Attention.json
    {
      "version": 1,
      "dashboards": [
        {
          "widgets": [
            {
              "unique_key": "a828ac68-786b-43d7-856f-ef465d7f8bfc",
              "location": {
                "x": 0,
                "y": 2,
                "cols": 7,
                "rows": 2
              },
              "reference": false
            },
            {
              "unique_key": "9c325418-b41b-4b04-bce7-ebfcfe4f565a",
              "location": {
                "x": 0,
                "y": 0,
                "cols": 7,
                "rows": 2
              },
              "reference": false
            }
          ],
          "parent": null,
          "unique_key": "96fe0902-3301-437e-9919-97e3d0de9615",
          "name": "Monitored Entities Needing Attention",
          "private": null
        }
      ],
      "widgets": [
        {
          "type": "table",
          "configuration": {
            "header": {
              "properties": {
                "title": "Volumes",
                "subtitle": "Status is not Up",
                "url": "",
                "description": "",
                "collapsible": false,
                "collapsed": true
              }
            },
            "table": {
              "providers": {
                "dataSource": {
                  "providerId": "TableSwqlDatasourceService",
                  "properties": {
                    "swql": "SELECT [Volumes].Node.Caption AS [Node]\n, [Volumes].Node.DetailsUrl AS [NodeDetailsUrl]\n     , CASE WHEN [Volumes].Type = 'Mount Point' THEN [Volumes].VolumeDescription\nELSE \nIsNull([Volumes].DeviceId, [Volumes].VolumeDescription) END AS [Name]\n     , [Volumes].Icon\n     , [Volumes].Status     \n     , [Volumes].Type\n     , [Volumes].VolumePercentUsed\n     , [Volumes].VolumeTypeIcon\n     , [Volumes].DiskQueueLength\n     , [Volumes].DiskTransfer\n     , [Volumes].DiskReads\n     , [Volumes].DiskWrites\n     , [Volumes].TotalDiskIOPS\n     , [Volumes].DetailsUrl\nFROM Orion.Volumes AS [Volumes]\nWHERE [Volumes].Type IN ( 'Fixed Disk', 'Mount Point', 'NetworkDisk', 'FixedDisk' )\n   AND [Volumes].Responding = 'Y'\n   AND [Status] <> 1\nORDER BY [Volumes].VolumePercentUsed DESC",
                    "dataFields": [
                      {
                        "id": "Node",
                        "label": "Node",
                        "dataType": "System.String"
                      },
                      {
                        "id": "NodeDetailsUrl",
                        "label": "NodeDetailsUrl",
                        "dataType": "System.String"
                      },
                      {
                        "id": "Name",
                        "label": "Name",
                        "dataType": "System.String"
                      },
                      {
                        "id": "Icon",
                        "label": "Icon",
                        "dataType": "System.String"
                      },
                      {
                        "id": "Status",
                        "label": "Status",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "Type",
                        "label": "Type",
                        "dataType": "System.String"
                      },
                      {
                        "id": "VolumePercentUsed",
                        "label": "VolumePercentUsed",
                        "dataType": "System.Single"
                      },
                      {
                        "id": "VolumeTypeIcon",
                        "label": "VolumeTypeIcon",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DiskQueueLength",
                        "label": "DiskQueueLength",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "DiskTransfer",
                        "label": "DiskTransfer",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "DiskReads",
                        "label": "DiskReads",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "DiskWrites",
                        "label": "DiskWrites",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "TotalDiskIOPS",
                        "label": "TotalDiskIOPS",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "DetailsUrl",
                        "label": "DetailsUrl",
                        "dataType": "System.String"
                      }
                    ],
                    "type": "hand-edit"
                  }
                },
                "adapter": {
                  "properties": {
                    "dataSource": {
                      "properties": {
                        "swql": "SELECT [Volumes].Node.Caption AS [Node]\n, [Volumes].Node.DetailsUrl AS [NodeDetailsUrl]\n     , CASE WHEN [Volumes].Type = 'Mount Point' THEN [Volumes].VolumeDescription\nELSE \nIsNull([Volumes].DeviceId, [Volumes].VolumeDescription) END AS [Name]\n     , [Volumes].Icon\n     , [Volumes].Status     \n     , [Volumes].Type\n     , [Volumes].VolumePercentUsed\n     , [Volumes].VolumeTypeIcon\n     , [Volumes].DiskQueueLength\n     , [Volumes].DiskTransfer\n     , [Volumes].DiskReads\n     , [Volumes].DiskWrites\n     , [Volumes].TotalDiskIOPS\n     , [Volumes].DetailsUrl\nFROM Orion.Volumes AS [Volumes]\nWHERE [Volumes].Type IN ( 'Fixed Disk', 'Mount Point', 'NetworkDisk', 'FixedDisk' )\n   AND [Volumes].Responding = 'Y'\n   AND [Status] <> 1\nORDER BY [Volumes].VolumePercentUsed DESC",
                        "dataFields": [
                          {
                            "id": "Node",
                            "label": "Node",
                            "dataType": "System.String"
                          },
                          {
                            "id": "NodeDetailsUrl",
                            "label": "NodeDetailsUrl",
                            "dataType": "System.String"
                          },
                          {
                            "id": "Name",
                            "label": "Name",
                            "dataType": "System.String"
                          },
                          {
                            "id": "Icon",
                            "label": "Icon",
                            "dataType": "System.String"
                          },
                          {
                            "id": "Status",
                            "label": "Status",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "Type",
                            "label": "Type",
                            "dataType": "System.String"
                          },
                          {
                            "id": "VolumePercentUsed",
                            "label": "VolumePercentUsed",
                            "dataType": "System.Single"
                          },
                          {
                            "id": "VolumeTypeIcon",
                            "label": "VolumeTypeIcon",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DiskQueueLength",
                            "label": "DiskQueueLength",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "DiskTransfer",
                            "label": "DiskTransfer",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "DiskReads",
                            "label": "DiskReads",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "DiskWrites",
                            "label": "DiskWrites",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "TotalDiskIOPS",
                            "label": "TotalDiskIOPS",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "DetailsUrl",
                            "label": "DetailsUrl",
                            "dataType": "System.String"
                          }
                        ],
                        "type": "hand-edit"
                      }
                    }
                  }
                }
              },
              "properties": {
                "configuration": {
                  "columns": [
                    {
                      "id": "column_0efee495-ea92-4858-9a40-4b00a594c4ae",
                      "formatter": {
                        "componentType": "EntityLinkFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "status": null,
                            "vendor": null,
                            "link": "NodeDetailsUrl",
                            "value": "Node"
                          },
                          "iconFormat": "noIcon",
                          "entityIcon": null
                        }
                      },
                      "isActive": true,
                      "label": "Node"
                    },
                    {
                      "id": "column_c3d159c2-687f-4b8d-97ff-3e28e0b4a2ec",
                      "label": "NodeDetailsUrl",
                      "isActive": false,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "NodeDetailsUrl"
                          }
                        }
                      }
                    },
                    {
                      "id": "column_7b49f959-e3b2-49c6-97a2-adbfa2ddd887",
                      "formatter": {
                        "componentType": "EntityLinkFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "status": "Status",
                            "vendor": null,
                            "link": "DetailsUrl",
                            "value": "Name"
                          },
                          "iconFormat": "status",
                          "entityIcon": null
                        }
                      },
                      "isActive": true,
                      "label": "Name"
                    },
                    {
                      "id": "column_c3998b18-dfab-438c-a1c5-16e361818965",
                      "label": "Status",
                      "isActive": false,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Status"
                          }
                        }
                      }
                    },
                    {
                      "id": "column_c31148a1-e7fe-46c8-ab65-5274271f1539",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Type"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "Type"
                    },
                    {
                      "id": "column_810f5f87-0e83-4cc4-a70f-9e1baee4a69f",
                      "formatter": {
                        "componentType": "ThresholdFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "VolumePercentUsed",
                            "instanceId": "",
                            "siteId": ""
                          },
                          "thresholdName": "Volumes.Stats.PercentDiskUsed",
                          "visualization": "barChart"
                        }
                      },
                      "isActive": true,
                      "label": "VolumePercentUsed"
                    },
                    {
                      "id": "column_76c9401d-bc47-4464-8fc3-4091a96f264e",
                      "label": "VolumeTypeIcon",
                      "isActive": false,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "VolumeTypeIcon"
                          }
                        }
                      }
                    },
                    {
                      "id": "column_e79c57a7-d2c1-4a60-bad1-1fc8391d5fce",
                      "label": "DetailsUrl",
                      "isActive": false,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "DetailsUrl"
                          }
                        }
                      }
                    }
                  ],
                  "sorterConfiguration": {
                    "sortBy": "column_810f5f87-0e83-4cc4-a70f-9e1baee4a69f",
                    "descendantSorting": true
                  },
                  "hasVirtualScroll": true,
                  "searchConfiguration": {
                    "enabled": false
                  }
                }
              }
            },
            "/": {
              "providers": {
                "refresher": {
                  "properties": {
                    "enabled": true,
                    "interval": 45,
                    "overrideDefaultSettings": false
                  }
                }
              }
            }
          },
          "subtitle": "Status is not Up",
          "description": "",
          "unique_key": "a828ac68-786b-43d7-856f-ef465d7f8bfc",
          "name": "Volumes",
          "private": false
        },
        {
          "type": "table",
          "configuration": {
            "header": {
              "properties": {
                "title": "Nodes under Pressure",
                "subtitle": "Ignores 'Up' and 'External' Devices",
                "url": "",
                "description": "",
                "collapsible": false,
                "collapsed": true
              }
            },
            "table": {
              "providers": {
                "dataSource": {
                  "providerId": "TableSwqlDatasourceService",
                  "properties": {
                    "swql": "SELECT [Nodes].Caption\n     , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1\nELSE [Nodes].CPULoad\nEND AS CPU\n     , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1\nELSE [Nodes].PercentMemoryUsed\nEND AS Memory\n     , [Nodes].PercentLoss\n     , [Nodes].Status\n     , [Nodes].StatusDescription\n     , [Nodes].DetailsUrl\nFROM Orion.Nodes\nWHERE [Nodes].Status NOT IN ( 1, 11 ) -- 1 = Up and 11 = External\nORDER BY Severity DESC",
                    "dataFields": [
                      {
                        "id": "Caption",
                        "label": "Caption",
                        "dataType": "System.String"
                      },
                      {
                        "id": "CPU",
                        "label": "CPU",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "Memory",
                        "label": "Memory",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "PercentLoss",
                        "label": "PercentLoss",
                        "dataType": "System.Double"
                      },
                      {
                        "id": "Status",
                        "label": "Status",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "StatusDescription",
                        "label": "StatusDescription",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DetailsUrl",
                        "label": "DetailsUrl",
                        "dataType": "System.String"
                      }
                    ],
                    "type": "hand-edit"
                  }
                },
                "adapter": {
                  "properties": {
                    "dataSource": {
                      "properties": {
                        "swql": "SELECT [Nodes].Caption\n     , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1\nELSE [Nodes].CPULoad\nEND AS CPU\n     , CASE WHEN [Nodes].ObjectSubType IN ( 'ICMP', 'External' ) THEN -1\nELSE [Nodes].PercentMemoryUsed\nEND AS Memory\n     , [Nodes].PercentLoss\n     , [Nodes].Status\n     , [Nodes].StatusDescription\n     , [Nodes].DetailsUrl\nFROM Orion.Nodes\nWHERE [Nodes].Status NOT IN ( 1, 11 ) -- 1 = Up and 11 = External\nORDER BY Severity DESC",
                        "dataFields": [
                          {
                            "id": "Caption",
                            "label": "Caption",
                            "dataType": "System.String"
                          },
                          {
                            "id": "CPU",
                            "label": "CPU",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "Memory",
                            "label": "Memory",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "PercentLoss",
                            "label": "PercentLoss",
                            "dataType": "System.Double"
                          },
                          {
                            "id": "Status",
                            "label": "Status",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "StatusDescription",
                            "label": "StatusDescription",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DetailsUrl",
                            "label": "DetailsUrl",
                            "dataType": "System.String"
                          }
                        ],
                        "type": "hand-edit"
                      }
                    }
                  }
                }
              },
              "properties": {
                "configuration": {
                  "columns": [
                    {
                      "id": "column_69ce58a5-a3e7-4746-acb7-79b6a8529520",
                      "label": "Node Name",
                      "isActive": true,
                      "formatter": {
                        "componentType": "EntityLinkFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "status": "Status",
                            "vendor": null,
                            "link": "DetailsUrl",
                            "value": "Caption"
                          },
                          "iconFormat": "status",
                          "entityIcon": null
                        }
                      }
                    },
                    {
                      "id": "column_5a7bdc8f-b9a6-4339-8ad6-d0d707995228",
                      "label": "CPU",
                      "isActive": true,
                      "formatter": {
                        "componentType": "ThresholdFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "CPU",
                            "instanceId": "",
                            "siteId": ""
                          },
                          "thresholdName": "Nodes.Stats.CpuLoad",
                          "visualization": "barChart"
                        }
                      }
                    },
                    {
                      "id": "column_b7737a78-db47-4ac2-9ae8-1babcf87b745",
                      "formatter": {
                        "componentType": "ThresholdFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Memory",
                            "instanceId": "",
                            "siteId": ""
                          },
                          "thresholdName": "Nodes.Stats.PercentMemoryUsed",
                          "visualization": "barChart"
                        }
                      },
                      "isActive": true,
                      "label": "Memory"
                    },
                    {
                      "id": "column_bcf96418-76c7-48ce-a295-37282885e01d",
                      "formatter": {
                        "componentType": "ThresholdFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "PercentLoss",
                            "instanceId": "",
                            "siteId": ""
                          },
                          "thresholdName": "Nodes.Stats.PercentLoss",
                          "visualization": "barChart"
                        }
                      },
                      "isActive": true,
                      "label": "PercentLoss"
                    },
                    {
                      "id": "column_8255b318-f588-43a3-b004-c173c917dfeb",
                      "label": "Status",
                      "isActive": false,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Status"
                          }
                        }
                      }
                    },
                    {
                      "id": "column_6fc56122-a268-4a61-b2e4-cb2a3e8d66bd",
                      "label": "Status Description",
                      "isActive": false,
                      "width": 300,
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "StatusDescription"
                          }
                        }
                      }
                    }
                  ],
                  "sorterConfiguration": {
                    "sortBy": "",
                    "descendantSorting": ""
                  },
                  "hasVirtualScroll": true
                }
              }
            }
          },
          "subtitle": "Ignores 'Up' and 'External' Devices",
          "description": "",
          "unique_key": "9c325418-b41b-4b04-bce7-ebfcfe4f565a",
          "name": "Nodes under Pressure",
          "private": false
        }
      ],
      "remove": null
    }
    

    This dashboard doesn't use any Custom Properties, so it should work for anyone.  It was created on Orion Platform 2020.2.6 HF1 (current version as of today).

  • Also - THWACK MVP  is doing the same thing now (re: Dashboards) and writing about it in a 3 part series starting with Modern Dashboarding: Introduction to SWQL Studio.

  • So I am not seeing how to do the Import-ModernDashboard PowerShell function . I see this doc but I am not seeing how to open the invoke tab   the documentation.solarwinds.com/.../core-fusion-dashboard-import-export.htm

  • Open PowerShell on your system.

    Install the SwisPowerShell module if not already installed.

    if ( -not ( Get-Command -Name Connect-Swis -ErrorAction SilentlyContinue ) ) {
        Install-Module -Name SwisPowerShell -Confirm:$false
    }

    This will install the module from the PowerShell Gallery.

    Build a connection to your Orion Server.

    $SwisConnection = Connect-Swis -Hostname "myOrionServer.domain.local" -Username "myUsername" -Password "myComplexPassword"

    Load the functions from the script.

    # This line must start with a single period and then the path to the downloaded PS1 file
    . C:\Path\To\Place\Where\I\Downloaded\func_ModernDashboards.ps1

    Now the functions are loaded in memory.  You can use the function to import the JSON file.

    Import-ModernDashboard -SwisConnection $SwisConnection -Path "C:\Path\To\Dashboard.json"
     

    Basically this says "using my current connection to SolarWinds import the JSON file as a Modern Dashboard."

  • Thanks so much for your help Kevin,  excited to get this going.  I am getting this error on the last step 


    PS C:\Users\adminmatm> Import-ModernDashboard -SwisConnection $SwisConnection -Path "C:\Download\import\Dashboard.json"
    Get-SwisData : No DNS entries exist for host https.
    At C:\Download\export\moderndash.ps1:214 char:23
    + ... ashboards = Get-SwisData -SwisConnection $SwisConnection -Query $Swql ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Get-SwisData], EndpointNotFoundException
    + FullyQualifiedErrorId : System.ServiceModel.EndpointNotFoundException,SwisPowerShell.GetSwisData

    Import-ModernDashboard : File content of 'C:\Download\import\Dashboard.json' does not match JSON format
    At line:1 char:1
    + Import-ModernDashboard -SwisConnection $SwisConnection -Path "C:\Down ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Import-ModernDashboard

  • For the SWIS hostname, you shouldn't include the http:// or https:// - just the name or IP address.

  • Ughhh,  well I am a pain today I guess.   Lastly getting this error.  
    PS C:\Users\adminmatm> Import-ModernDashboard -SwisConnection $SwisConnection -Path "C:\Download\import\dashboard.json"
    Import-ModernDashboard : File content of 'C:\Download\import\dashboard.json' does not match JSON format
    At line:1 char:1
    + Import-ModernDashboard -SwisConnection $SwisConnection -Path "C:\Down ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Import-ModernDashboard 

    I did paste it into json format checker and it passed

  • 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.