How do you compare the last two recent events for different devices sending data to Splunk?

[hypePG]

Hey,

i have different devices that are sending temperature data to my Splunk instance. For alarming, I want to compare the temperature data of the last two measurements that were sent. Ideally, I want to do this for all devices at once. So, my goal is to create a table like this:

deviceid             last_temp        second_last_temp      difference 
xxxxx                  25                     20                      5
xxxxx2                35                     18                     17

Based on the calculation of the difference, I want to configure my alarming...

The events look like this:

{ "deviceId": "4D3F7A", "time": 1542800341, "data": "9e46544000808f41", "duplicate": false, "categoryId":"5bb366f22c9fbb00da468aee",  "temperature" : "17.9375" }

I probably just have "a knot in my brain" right now and can't get to a solution.

Thanks in advance.

Max

[woodcock · Accepted solution]

Like this:

index=YouShouldAlwaysSpecifyAnIndex source=DeviceTemperature 
| reverse
| streamstats current=false last(temperature) as second_last_temp by deviceId
| eval difference = temperature - second_last_temp
| reverse
| rename temperature AS last_temp
| table _time deviceId last_temp second_last_temp difference

[inventsekar]

source=DeviceTemperature 
| streamstats current=false last(second_last_temp) as new_temp last(last_temp) as old_temp by Device 
| eval difference=new_temp - old_temp
| table _time Device new_time old_time difference

(approximate query, edits may be needed 😉 )

streamstats example from splunk blogs -
https://www.splunk.com/blog/2013/10/31/streamstats-example.html

[hypePG]

thanks for your superfast answer. i checked the streamstats command and its probably the command i am looking for. but i am still a little bit confused. how do i get "second_last_temp" and "last_temp"? i dont have those fields. thats one of the problems i need to solve.

regards max