• State Not Answered
  • Locked Locked
  • Replies 1 reply
  • Subscribers 17 subscribers
  • Views 829 views
  • Users 0 members are here
Options
Related
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.

How do I compare latest polled and next to latest polled values in SWQL?

dkeyser

I'm trying to subtract the previous value from the current value to get a delta of change but I'm struggling on figuring out how to do this. I tried to start with a query to get the top two rows by DateTime DESC so I'd get both rows I'm interested in. Now I'm trying to figure out how to get access to the second row value only. I feel like there's something I'm missing and any help would be greatly appreciated.

What I'm starting with:

SELECT TOP 2 NodeID, InterfaceID, DateTime, InAveragebps
FROM Orion.NPM.InterfaceTraffic
Where InterfaceID = 342 and NodeID = 70
Order by DateTime DESC

Kicking around the idea of summing the data and then subtracting the value for the max row to get the value for the second row out but there has to be a better way...

Parents
  • 0

    This seems to work for me

    SELECT top 1 it.NodeID
, it.InterfaceID
, it.DateTime
, it.InAveragebps
, it2.DateTime as previousDateTime
, it2.InAveragebps as previousAverageBps
, it.InAveragebps - it2.InAveragebps as Change

FROM Orion.NPM.InterfaceTraffic it
join orion.npm.InterfaceTraffic it2 on it.DateTime > addminute(-(1.1*it.Interface.StatCollection),getutcdate()) and it.InterfaceID=it2.InterfaceID and it2.DateTime < it.DateTime and it2.DateTime > addminute(-(2.1*it.Interface.StatCollection),it.DateTime)

where it.interfaceid = 96636

order by previousdatetime desc

Reply
  • 0

    This seems to work for me

    SELECT top 1 it.NodeID
, it.InterfaceID
, it.DateTime
, it.InAveragebps
, it2.DateTime as previousDateTime
, it2.InAveragebps as previousAverageBps
, it.InAveragebps - it2.InAveragebps as Change

FROM Orion.NPM.InterfaceTraffic it
join orion.npm.InterfaceTraffic it2 on it.DateTime > addminute(-(1.1*it.Interface.StatCollection),getutcdate()) and it.InterfaceID=it2.InterfaceID and it2.DateTime < it.DateTime and it2.DateTime > addminute(-(2.1*it.Interface.StatCollection),it.DateTime)

where it.interfaceid = 96636

order by previousdatetime desc

Children
No Data

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.