Version 2

    Customizing Orion

    During our session, Dez and I showed off a few custom queries that used the SolarWinds Query Language.  These are the kinds of things you need to leverage the superpower of the Orion Platform - customizations!

     

    I drafted all of them using the SWQL Studio, which is part of the SolarWinds Orion SDK.  The benefit of using the SDK is to ease your way into getting a better understanding of the SolarWinds Orion API.  There are many, many things that you can do with the API and the SDK is your first steps into comprehension.

     

    In our environment, we setup a new view where we could drop these custom queries.  You can call it what you want, but we called it the Scratch Pad.  These are the queries that we put on that page:

     

    Poller Performance

    SELECT        --CASE   
                  --   WHEN [N].Caption IS NULL THEN [E].ServerName  
                  --   ELSE CONCAT('<a href="', [N].DetailsUrl, '">', [N].[Caption], '</a>')  
                  --  END AS [Caption]
                  [N].Caption  
                  , [E].ServerType  
                  , [N].DetailsUrl AS [_LinkFor_Caption]  
                  , CONCAT('/NetPerfMon/images/Small-', [N].StatusLED) AS [_IconFor_Caption]
                  , ROUND([N].SystemUpTime / 60 / 60 / 24, 2 ) AS [Uptime (Days)]  
                  , MinuteDiff([E].KeepAlive, GETUTCDATE()) AS [Last Checkin (Minutes)]  
                  , [E].Elements AS [Monitored Elements]  
                  , [E].Nodes AS [Monitored Nodes]  
                  , [E].Interfaces AS [Monitored Interfaces]  
                  , [E].Volumes AS [Monitored Volumes]  
                  , [E].Pollers AS [Monitored UnDP]  
                  -- Comment out the follow line if you don't own SAM  
                  , [SAM].ComponentCount AS [Monitored Components]  
                  , [E].EngineVersion  
                  , CONCAT([E].WindowsVersion, '/', [E].ServicePack ) AS [OS/SP]  
                  , [N].CPULoad AS [CPU %]  
                  , [N].PercentMemoryUsed AS [Mem %]  
                  , [E].PollingCompletion AS [Polling Completion %]
                  , [EP].PropertyValue AS [Total Job Weight]
                    
    FROM Orion.Engines AS [E]
    INNER JOIN Orion.EngineProperties AS [EP]
      ON [E].EngineID = [EP].EngineID
     AND [EP].PropertyName = 'Total Job Weight'  
    -- Use of LEFT JOIN so that we can show Engines even if we aren't monitoring them... but we should be monitoring them  
    LEFT JOIN Orion.Nodes AS [N]  
      ON [E].IP = [N].IP_Address  
    -- Comment out the follow block if you don't own SAM  
    -- [BEGIN] SAM Information...  
    INNER JOIN ( SELECT COUNT([AA].ComponentID) AS ComponentCount  
                      , [N].EngineID  
    FROM Orion.APM.Component AS [AA]  
    INNER JOIN Orion.APM.Application AS [A]  
       ON [AA].ApplicationID = [A].ApplicationID  
    INNER JOIN Orion.Nodes AS [N]  
       ON [A].NodeID = [N].NodeID  
    WHERE [AA].Disabled = 'False'  
    GROUP BY [N].EngineID ) AS [SAM]  
       ON [SAM].EngineID = [E].EngineID  
    -- [END] SAM Information  
    ORDER BY [E].ServerType DESC, [N].Caption 

     

    The first query gives you details on all of your SolarWinds Orion Polling Engines.  This works on all environments, but is especially suited for environments with Network Performance Monitor and Server & Application Monitor.  If you don't have Server & Application Monitor, you can just comment out lines 34-43 to skip that bit of data.

     

    Capacity Stuff

    Custom SWQL Query

    SELECT [OFC].InstanceCaption AS [Node Name]
                   , CONCAT('/NetPerfMon/Images/Vendors/', [N].VendorIcon) AS [_IconFor_Node Name]
                   , CASE
                        WHEN [OFC].MetricName = 'Forecast.Metric.CpuLoad' THEN '% CPU'
                        ELSE '% Mem'
                     END AS [Metric]
                   , [OFC].CurrentValue AS [Current Value]
                   , ROUND([OFC].Aavg,2) AS [CALCULATED AVG]
                   , ROUND([OFC].Bavg,2) AS [AVG GROWTH]
                   , [OFC].DaysToWarningAvg AS [DAYS TO WARN THRESHOLD]
                   , [OFC].DaysToCriticalAvg AS [DAYS TO CRIT THRESHOLD]
                   , [OFC].DaysToCapacityAvg AS [DAYS TO 100% USAGE]
                   , [OFC].DetailsUrl AS [_LinkFor_Node Name]
    FROM Orion.ForecastCapacity  AS [OFC]
    INNER JOIN Orion.Nodes AS [N]
      ON [OFC].NodeID = [N].NodeID
    WHERE [OFC].EntityType = 'Orion.Nodes'
          AND [OFC].Aavg > 0
          AND [OFC].Bavg > 0
          --AND [OFC].InstanceCaption LIKE 'EAST%'
    ORDER BY [OFC].InstanceCaption, [OFC].MetricName

     

    Search SWQL Query

    SELECT [OFC].InstanceCaption AS [Node Name]
                   , CONCAT('/NetPerfMon/Images/Vendors/', [N].VendorIcon) AS [_IconFor_Node Name]
                   , CASE
                        WHEN [OFC].MetricName = 'Forecast.Metric.CpuLoad' THEN '% CPU'
                        ELSE '% Mem'
                     END AS [Metric]
                   , [OFC].CurrentValue AS [Current Value]
                   , ROUND([OFC].Aavg,2) AS [CALCULATED AVG]
                   , ROUND([OFC].Bavg,2) AS [AVG GROWTH]
                   , [OFC].DaysToWarningAvg AS [DAYS TO WARN THRESHOLD]
                   , [OFC].DaysToCriticalAvg AS [DAYS TO CRIT THRESHOLD]
                   , [OFC].DaysToCapacityAvg AS [DAYS TO 100% USAGE]
                   , [OFC].DetailsUrl AS [_LinkFor_Node Name]
    FROM Orion.ForecastCapacity  AS [OFC]
    INNER JOIN Orion.Nodes AS [N]
      ON [OFC].NodeID = [N].NodeID
    WHERE [OFC].EntityType = 'Orion.Nodes'
          AND [OFC].Aavg > 0
          AND [OFC].Bavg > 0
          AND [OFC].InstanceCaption LIKE '%${SEARCH_STRING}%'
    ORDER BY [OFC].InstanceCaption, [OFC].MetricName

     

    Yeah - "Capacity Stuff" isn't the most clever or inventive name, but I couldn't think of anything better.

     

    This query shows the capacity information for monitored systems in your environment.  These are frequently on Node, Interface, and Volume Detail pages, but I wanted to see all of them in the same place for a quick view.  Now I can determine where I need to spend my time in the near future.

     

    CBQoS Drops

    -- Displayed:
    --  Node (NetFlowNodeURL as HTML)
    --  Interface (NetFlowInterfaceUrl as HTML)
    --  Class (NetFlowClassUrl AS HTML)
    --  Direction
    --  Total Bytes (TotalBytes)
    --  Average Bit Rate (AvgBitRate)
    SELECT [N].[Caption] AS [NodeName]
         , [I].[IfName] AS [InterfaceName]
         , [ClassMap].[Name] AS [Class]
         , [DirDesc].[DirectionName] AS [Direction]
         , SUM([Stats].Bytes) AS [Total Bytes]
         , AVG([Stats].BitRate ) AS [BPS]
         , CONCAT('/Orion/TrafficAnalysis/NetflowNodeDetails.aspx?NetObject=NN:', [Policy].NodeID, ';T:Last%2024%20Hours;FD:', [DirDesc].DirectionName) AS [_LinkFor_NodeName]
         , CONCAT('/Orion/TrafficAnalysis/NetflowInterfaceDetails.aspx?NetObject=NI:', [Policy].InterfaceID, ';T:Last%2024%20Hours;FD:', [DirDesc].DirectionName) AS [_LinkFor_InterfaceName]
         , CONCAT('/Orion/TrafficAnalysis/NetflowCBQoSDetails.aspx?NetObject=CCM:', [Policy].PolicyID, ';I:', [Policy].InterfaceID, ';T:Last%2024%20Hours;FD:', [DirDesc].DirectionName) AS [_LinkFor_Class]
    FROM Orion.Netflow.CBQoSPolicyMetric AS [Metric]
    INNER JOIN Orion.Netflow.CBQoSStatistics AS [Stats]
    ON [Metric].PolicyID = [Stats].PolicyID
    INNER JOIN Orion.Netflow.CBQoSPolicy AS [Policy]
    ON [Metric].PolicyID = [Policy].PolicyID
    INNER JOIN Orion.Nodes AS [N]
    ON [Policy].NodeID = [N].NodeId
    INNER JOIN Orion.NPM.Interfaces AS [I]
    ON [Policy].InterfaceID = [I].InterfaceID
    INNER JOIN Orion.Netflow.CBQoSDirectionDescription AS [DirDesc]
    ON [Policy].DirectionID = [DirDesc].DirectionID
    INNER JOIN Orion.Netflow.CBQoSClassMap AS [ClassMap]
    ON [Policy].ClassMapID = [ClassMap].ClassMapID
    INNER JOIN Orion.Netflow.CBQoSPolicyMap AS [PolicyMap]
    ON [Policy].PolicyMapID = [PolicyMap].PolicyMapID
    WHERE [Metric].StatisticsName = 'Drops'
      AND ( [Stats].Bytes + [Stats].BitRate + [Stats].ClassUtilization <> 0 )
      AND ( [Stats].TimeStamp >= GETUTCDATE() - 1 )
      --AND ( ( [Stats].TimeStamp >= ${FromTime} )
      --  AND ( [Stats].TimeStamp <= ${ToTime} ) )
    GROUP BY [NodeName]
           , [InterfaceName]
           , [Class]
           , [Direction]
           , [Policy].NodeID
           , [Policy].InterfaceID
           , [Policy].PolicyID

     

    This one is for the network nerds.  Class-Based Quality of Service is the way that you shape data moving around your environments.  This is typical for Voice-over-IP and Video-over-IP networks.  But there are acceptable limits and knowing if you are above these limits will help you tune your policies.  This one was a little more complex to create, but the ideas are all the same.  Using the _LinkFor_<FieldName> makes this query clickable.

     

    Custom queries are great and they offer you the ability to get all that monitoring data out of SolarWinds into a format that is easy for you to consume.

     

    There is other goodness in our Tips & Tricks session, so please peruse it for your pleasure.