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

  • 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'
              )
    -- Your requests begin here
         AND [VForcast].Volume.Node.Vendor = 'Windows'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%VM%'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%perf%'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%test%'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%stage%'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%DEV%'
         AND [VForcast].Volume.Node.Caption NOT LIKE '%QA%'
    -- Your requests end here
    ORDER BY [VForcast].DaysToCapacityAvg

    It looks like you have a bunch of QA/Stage/Test things in your Orion Infrastructure.  Have you considered setting up a Node-based Custom Property called "ActiveState" and have "Test", "Stage", "Production", etc. as options?  Then you could just use that as the filter instead of this laundry list of Caption matching.

  • Thanks for all your help Kevin,  Yes I am aware of the active state but I have created a number of complex alerts based up tags.  and actually using tags instead of captions is better.  I was just not sure of the correct variable.

  • If you have a "tag" for a custom property you can reference that instead (fi you like).

    [VForcast].Volume.Node.CustomProperties.<Custom Property Name> = 'Custom Property Value'

  • Its awesome trying to think of new dashlets.  I would like to create a netflow ingress and egress KPI dashboard showing zoom traffic.  Here is what I have so far:

    SELECT
    Hostname, sourcedomain
    ,destinationhostname
    ,egressbytes
    ,ingressbytes
    --COUNT (ingessbytes) as Total
    From Orion.Netflow.FlowsByHostname
    --count ([data].[ingessbytes]) AS [Number]
    WHERE TimeStamp > (GETUTCDATE() - 0.08)
    AND Hostname = 'dynamic-acs-72-23-16-226.zoominternet.net'
    AND sourcedomain = 'zoominternet.net'

    Does anyone know how I add the totals and convert to MB for a KPI widget?

  • What does this return for you?

    SELECT ( SUM(IngressBytes)  / 1024 / 1024 ) AS [Ingress over the last 24 hours]
         , ( SUM(EgressBytes)  / 1024 / 1024 ) AS [Egress over the last 24 hours]
    From Orion.Netflow.FlowsByHostname
    WHERE TimeStamp > ( GETUTCDATE() - 1 )
    AND Hostname = 'dynamic-acs-72-23-16-226.zoominternet.net'
    AND sourcedomain = 'zoominternet.net'

  • Thanks Kevin,

    Works like a champ.  It was giving me timeout for 24 hours but with this it works like a champ.

    WHERE TimeStamp > (GETUTCDATE() - 0.08) 

    When I am done with these Dashboards I would be happy to share my dashboard(s) with queries.  I am sure some others out there would like to duplicate.  

    This one is great for Netflow.  I imagine creating an entire dashboard of KPI dashboards for Netflow to breakdown traffic for chosen domains and interfaces.  I think management at my company will like that.

    Thanks again

  • SELECT ROUND( ( SUM(IngressBytes)  / 1024 / 1024 ), 2)  AS [Ingress over the last 24 hours]
         , ROUND( ( SUM(EgressBytes)  / 1024 / 1024 ), 2)  AS [Egress over the last 24 hours]
    From Orion.Netflow.FlowsByHostname
    WHERE TimeStamp > ( GETUTCDATE() - 1 )
    AND Hostname = 'dynamic-acs-72-23-16-226.zoominternet.net'
    AND sourcedomain = 'zoominternet.net'

  • Once you are done, you can just export the Dashboards as JSON and don't need to include the queries - they will be included inside the export.

  • So similar to last one I need to be able to show specific interfaces in a table and convert to MBs.  Not sure how/where to insert that in this query.

    SELECT I.NodeID, I.InterfaceID, I.Node.Caption AS Node, I.Caption AS Interface, DATETRUNC('day', I.Traffic.ObservationTimestamp) AS [Day],

    AVG(I.Traffic.InAveragebps) AS InAvgBps, AVG(I.Traffic.OutAveragebps) AS OutAvgBps,

    MIN(I.Traffic.InMinbps) AS InMinBps, AVG(I.Traffic.OutMinbps) AS OutMinBps,

    MAX(I.Traffic.InMaxbps) AS InMaxBps, AVG(I.Traffic.OutMaxbps) AS OutMaxBps

    FROM Orion.NPM.Interfaces I

    WHERE I.Traffic.ObservationTimestamp > (GETUTCDATE() - 0.08)
    AND InterfaceID=6609

    GROUP BY I.NodeID, I.InterfaceId, I.Node.Caption, I.Caption, DATETRUNC('day', I.Traffic.ObservationTimestamp)

Reply
  • So similar to last one I need to be able to show specific interfaces in a table and convert to MBs.  Not sure how/where to insert that in this query.

    SELECT I.NodeID, I.InterfaceID, I.Node.Caption AS Node, I.Caption AS Interface, DATETRUNC('day', I.Traffic.ObservationTimestamp) AS [Day],

    AVG(I.Traffic.InAveragebps) AS InAvgBps, AVG(I.Traffic.OutAveragebps) AS OutAvgBps,

    MIN(I.Traffic.InMinbps) AS InMinBps, AVG(I.Traffic.OutMinbps) AS OutMinBps,

    MAX(I.Traffic.InMaxbps) AS InMaxBps, AVG(I.Traffic.OutMaxbps) AS OutMaxBps

    FROM Orion.NPM.Interfaces I

    WHERE I.Traffic.ObservationTimestamp > (GETUTCDATE() - 0.08)
    AND InterfaceID=6609

    GROUP BY I.NodeID, I.InterfaceId, I.Node.Caption, I.Caption, DATETRUNC('day', I.Traffic.ObservationTimestamp)

Children