Splunk Search

How to compute the field value a time interval earlier?

yshen
Communicator

I want to compute the change in temperature for each location in a given interval, say, 15 minutes, or 30 minutes. I figure that streamstats might capture the temperature value at the beginning of such time interval, using time_window to specify the interval length. But, however, the following example surprises me.
The temperature readings for Pleasonton are collected every 15 minutes, thus the following query:
| makeresults
| eval _raw="time_ Location Temperature
2021-08-23T03:04:05.000-0700 Pleasonton 185
2021-08-23T03:04:20.000-0700 Pleasonton 86
2021-08-23T03:04:35.000-0700 Pleasonton 87
2021-08-23T03:04:50.000-0700 Pleasonton 89"
| multikv forceheader=1
| eval _time=strptime(time_,"%Y-%m-%dT%H:%M:%S.%3N%z")
| fields _time Location Temperature
| sort _time
| streamstats earliest(Temperature) as previous_temp earliest(_time) as previous_time by Location time_window=5m
| convert ctime(previous_time)
I’d expect the following, as with the interval 5 minutes from an event, there is no other event, but the current one.
_time	Location	Temperature	_raw	previous_temp	previous_time
2021-08-23 03:04:05 Pleasonton 185 2021-08-23T03:04:05.000-0700 Pleasonton 185 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:20 Pleasonton 86 2021-08-23T03:04:20.000-0700 Pleasonton 86 86 08/23/2021 03:04:20.000000
2021-08-23 03:04:35 Pleasonton 87 2021-08-23T03:04:35.000-0700 Pleasonton 87 87 08/23/2021 03:04:35.000000
2021-08-23 03:04:50 Pleasonton 89 2021-08-23T03:04:50.000-0700 Pleasonton 89 89 08/23/2021 03:04:50.000000
but this is actually what I get:
_time	Location	Temperature	_raw	previous_temp	previous_time
2021-08-23 03:04:05 Pleasonton 185 2021-08-23T03:04:05.000-0700 Pleasonton 185 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:20 Pleasonton 86 2021-08-23T03:04:20.000-0700 Pleasonton 86 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:35 Pleasonton 87 2021-08-23T03:04:35.000-0700 Pleasonton 87 185 08/23/2021 03:04:05.000000
2021-08-23 03:04:50 Pleasonton 89 2021-08-23T03:04:50.000-0700 Pleasonton 89 185 08/23/2021 03:04:05.000000
All taking the earliest event's temperature, which is beyond 5 minutes from any subsequent events.How can I query to get the temperature at the beginning of the time period?
 
Labels (1)
Tags (1)
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...