How to add "Last Polled" time to a Custom SWQL Query

How is it possible to add the polled time for a custom poller to this query below? I am unable to use the inbuilt SolarWinds widget as it displays unwanted information (chart values which are presented on a separate widget).

SELECT

  a.CustomPollerName AS Name,

  a.CurrentValue AS Value

FROM Orion.NPM.CustomPollerAssignment a

WHERE NodeID = '${NodeID}'

AND NOT a.CustomPollerName like 'Battery_Test_Minutes'
AND NOT a.CustomPollerName like 'External_Symmetry'
AND NOT a.CustomPollerName like 'Internal_Input'
AND NOT a.CustomPollerName like 'Internal_Symmetry'

ORDER BY CustomPollerName ASC

The image below shows the in built SolarWinds Widget, circled in Red is why I require this custom query to filter out the unwanted data within the table and in blue is what I want to add to my custom query. I have tried multiple ways but unfortunately have not been able to achieve it so far:

My custom query currently looks like this, hoping that it is just a reference I need to add within the query to add the additional column for time:

Thanks

Find more posts tagged with

swql query

datetime

Custom Query

UnDP

Accepted answers

All comments

wluther

@tominncle
There are probably other ways to do it, but I think you could add the DateTime value from the CustomPollerStatistics table. You should be able to do that with an implicit join, I believe.

a.CustomPollerName AS Name,
a.CurrentValue AS Value,
a.CustomPollerStatistics.DateTime AS [TimeStamp]

Try that and see if it pulls back anything that looks like what you'd expect. I can't check and test at the moment, but I think that might work.

tominncle

Hi,

Thanks for your input, I have tried and it does add the additional column however it is showing all the historical values also. We log some of these values to create reports for Voltage and Current so we cannot disable.

Is it possible to add a line to only show the last polled time in the database?

Thanks

adam.beedell

TOP 1
Order by time DESC
(Maybe Group by thing.ID)

On the node layer you can do stuff like this:

Select n.LastSystemUpTimePollUtc from orion.nodes n

It gets a bit more fiddily when there's more layers with different polling cycles being polled. Worst case though there should be a timestamp for anything you can graph, and anything you cant graph is almost certainly on the last main poll of the parent object

wluther

Just out of curiosity, does it show correct/similar data when you run this?

SELECT

  a.CustomPollerAssignment.CustomPollerName AS Name,
  a.CustomPollerAssignment.CurrentValue AS Value,
 a.DateTime

FROM Orion.NPM.CustomPollerStatusOnNode AS a

WHERE NodeID = ${NodeID}

AND NOT a.CustomPollerAssignment.CustomPollerName like 'Battery_Test_Minutes'
AND NOT a.CustomPollerAssignment.CustomPollerName like 'External_Symmetry'
AND NOT a.CustomPollerAssignment.CustomPollerName like 'Internal_Input'
AND NOT a.CustomPollerAssignment.CustomPollerName like 'Internal_Symmetry'

ORDER BY a.CustomPollerAssignment.CustomPollerName ASC

tominncle

Hello,

This approach is working exactly how I need it, thank you!

Is it possible to add a function to amend the full presented date to hours and minutes in relation to the server time. Currently the time is in UTC format, not a show stopper just a nice to have. I have looked at some date/time functions for SWQL but unsure on exactly where they should sit in relation to the above query.

Thanks Again!

tominncle

Thanks for your reply, some of the pollers run at different intervals depending on how much data we need to pull so I don't believe this solution would be an easy fit.