This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

API Poller Metrics SWQL

KMSigma.SWI over 1 year ago

I was helping someone elsewhere and thought I should share this here.

SELECT [Metrics].ValueToMonitor.ApiPoller.Node.DisplayName AS [Node Name]
     , [Metrics].ValueToMonitor.ApiPoller.Node.DetailsUrl AS [Node Name_Link]
     , [Metrics].ValueToMonitor.ApiPoller.DisplayName AS [Poller Name]
     , [Metrics].ValueToMonitor.ApiPoller.DetailsUrl AS [Poller Name_Link]
     , [Metrics].ValueToMonitor.DisplayName AS [Metric Name]
     , [Metrics].ValueToMonitor.DetailsUrl AS [Metric Name_Link]
     , AVG([Metrics].AvgMetric * 1.0) AS [Average (Last 24 Hours)]
     , MIN([Metrics].MinMetric * 1.0) AS [Minimum (Last 24 Hours)]
     , MAX([Metrics].MaxMetric * 1.0) AS [Maximum (Last 24 Hours)]
     , [Metrics].Status AS [Current Status]
FROM Orion.APIPoller.ValueToMonitor.Metrics AS [Metrics]
-- The above is just too big, so only give me the last 1 day
WHERE [Metrics].ObservationTimestamp >= GETUTCDATE() - 1 -- "right-now" minum 1 (day)
-- Filter for a specific API Poller by name
-- AND [Metrics].ValueToMonitor.ApiPoller.DisplayName = 'Pi-Hole Status'
-- Filter for a specific API Poller Metric by name match
-- AND [Metrics].ValueToMonitor.DisplayName LIKE 'Query%'
GROUP BY [Metrics].ValueToMonitor.ApiPoller.Node.DisplayName
       , [Metrics].ValueToMonitor.ApiPoller.Node.DetailsUrl
       , [Metrics].ValueToMonitor.ApiPoller.DisplayName
       , [Metrics].ValueToMonitor.ApiPoller.DetailsUrl
       , [Metrics].ValueToMonitor.DisplayName
       , [Metrics].ValueToMonitor.DetailsUrl
       , [Metrics].Status
ORDER BY [Metrics].ValueToMonitor.ApiPoller.Node.DisplayName
       , [Metrics].ValueToMonitor.ApiPoller.DisplayName
       , [Metrics].ValueToMonitor.DisplayName

This query connects the metric to the API poller, and then back to the assigned node. I used this example to build a very simple modern dashboard.

In my environment, I only have the one poller setup, so that's all it returns. I left a bunch of comments in the above to provide some other ways to filter it. I'm happy to answer questions.

sum_giais over 1 year ago

slick, love it!
Cancel
Vote Up 0 Vote Down

Cancel

mmayer5823 over 1 year ago

Thanks Kevin, So how would I get just the latest value if I want to use this as a KPI?

I used this query for getting 1 specific pollerID

SELECT [Metrics].ValueToMonitorId
, [Metrics].ObservationTimestamp
, [Metrics].AvgMetric
, [Metrics].Status
, [Metrics].ValueToMonitor.DisplayName AS [Metric Name]
, [Metrics].ValueToMonitor.ApiPoller.DisplayName AS [Poller Name]
, [Metrics].ValueToMonitor.ApiPoller.Node.DisplayName AS [Node Name]
FROM Orion.APIPoller.ValueToMonitor.Metrics AS [Metrics]
-- The above is just too big, so only give me the last 1 day
--WHERE [Metrics].ObservationTimestamp >= GETUTCDATE() - 1
WHERE ValueToMonitorId = 100
--WHERE [Metrics].ValueToMonitor.ApiPoller.DisplayName = 'Android Phone Usage - User Count'
-- ORDER BY [Metrics].ObservationTimestamp DESC

KMSigma.SWI over 1 year ago in reply to mmayer5823

This is a common ask and I find most people have a tendency to try and overthink it.

Order by the date descending and select only the top 1.

SELECT TOP 1 [Metrics].AvgMetric
           , [Metrics].Status
           , [Metrics].ValueToMonitor.DisplayName AS [Metric Name]
           , [Metrics].ValueToMonitor.ApiPoller.DisplayName AS [Poller Name]
           , [Metrics].ValueToMonitor.ApiPoller.Node.DisplayName AS [Node Name]
FROM Orion.APIPoller.ValueToMonitor.Metrics AS [Metrics]
WHERE ValueToMonitorId = 100
ORDER BY [Metrics].ObservationTimestamp DESC

mmayer5823 over 1 year ago in reply to KMSigma.SWI

Nice!!! Works like a champ. You da man. Even in a kilt
Cancel
Vote Up 0 Vote Down

Cancel