cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

TC18: Custom Queries from Tips & Tricks

Customizing Orion

pastedImage_0.png

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

pastedImage_1.png

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

pastedImage_4.png

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

pastedImage_8.png

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

Comments

Am not getting any CBQOS data in the box.

pastedImage_0.png

It requires NTA 12.3 or later and NTA 4.4 or later... oh - and you have to have CBQoS setup in NetFlow to capture this information.

Am using NTA 12.3 i think am not sending the CbQos data to solarwinds

I have not enabled the cbqos mib

snmp mib persist cbqos

Are you getting anything in the CBQoSStatistics table?  Did you check the CBQoS ckeckbox in Manage NetFlow Sources and CBQoS Polling area of the NTA Admin settings?

Am not receiving am CbQos data in NTA admin settings but in all the device Qos configured it's not sending that data to solarwinds.

Then you need to check the boxes to collect CBQoS.

pastedImage_0.png

Then you can confirm it by looking at NetFlow Sources on the NetFlow Summary Page.

pastedImage_1.png

Am CBQOS data not collecting in NTA manage NTA and cbqos page but in all the devices cbqos configured.

i Need to export cbqos MIB with snmp then i will get.

Hi KMSigma​ - this is truly awesome. I already have a view setup just for this stuff.

Though if I may, could I suggest a few changes to the poller performance swql?

I have SAM but, as it turns out only one of my additional pollers is actually polling any applications / components so an INNER join causes this to yield that poller as the only result. Changing line 34 to a LEFT join resolved this. While it's not all encompassing, I thought some may find it useful to see the polling rate (at least I do lol.) To add that in at least with my limited query knowledge I copied and edited a couple lines. Unfortunately since it's the same field just a different row it's not that easy it seems to add another value from that table without another INNER join of that same table.

Add after line 23:

, [EP2].PropertyValue AS [Polling Rate]

Add after line 28:

INNER JOIN Orion.EngineProperties AS [EP2]

ON [E].EngineID = [EP2].EngineID 

AND [EP2].PropertyName = 'Orion.Standard.Polling'

Hope this helps...

Thanks again!

I love that you found this little tidbit.  This is why we share stuff on this community.  Can I make a suggestion?

I think it would be best if you publish a new document (like this one) and then write up the differences that you found.  Then you can just link back to this one saying something like "this was good, but I made some changes which I think are better.  What do you think?"

Hi,

Finally CISCO confirmed there is a hardware limitation not able to monitor CBQOS

Comment by CISCO:

All the platforms that uses MQC like the QoS Model (3650, 3850 and C9K) could be affected by the limitation of counters for QoS monitoring software crash.

The other platforms like 3560, 3750, 4500, C6k (6500 or 6800), and all platforms that use MLS like QoS Model have no this particular limitation.

I hope that you didn't have to wait since October to get that answer, but at least there's an answer.

Absolutely can do that superb!

the capacity stuff report, needs any changes to be done or pasting will just work fine for any environment?

The Capacity Stuff resource should just work if you copy the code.  I don't think I see any customizations that were made.  But you should probably give it a better name.

Thanks 🙂

Version history
Revision #:
1 of 1
Last update:
‎10-18-2018 03:12 PM
Updated by: