Splunk Search

Use aggregate function latest for each minute to use timechart

wvanloon
New Member

My data looks like this:

_time:11/5/1912:41:00
ID: 123
Value:10

For each minute I want to know the last value that was known in that minute. How can I achieve this?
The ID is not updated every minute, so if the latest value for a given id at the moment 12:41 is 10 I want the same number for 12:42.

I've tried something like this:
index="" | bin span=1m _time | streamstats current=f window=1440 last(value) AS last_value by id

But I have the feeling I am missing a part.

Thanks in advance.

0 Karma
1 Solution

arjunpkishore5
Motivator

I think what you're looking for is this.

index = someindex
| fields _time, value, id
| timechart span=1m latest(value) as value by id limit=0
| filldown

If this id has never had a value in the selected time range, then it won't show up.

View solution in original post

0 Karma

arjunpkishore5
Motivator

I think what you're looking for is this.

index = someindex
| fields _time, value, id
| timechart span=1m latest(value) as value by id limit=0
| filldown

If this id has never had a value in the selected time range, then it won't show up.

View solution in original post

0 Karma

wvanloon
New Member

Thanks this is the beginning for what I was looking for.

After this search I want to be able to count the occurrence of each value by time, any idea?

0 Karma

arjunpkishore5
Motivator

You're welcome.

The occurrence, is this going to be a separate search? or you want to append to the results of this search?

0 Karma

wvanloon
New Member

The best way would be to append to this result. But a seperate search would be fine too.

0 Karma

marycordova
SplunkTrust
SplunkTrust

Does adding _time at the end of your by id clause help?

by id _time

0 Karma

wvanloon
New Member

No that is not working. The problem is that if you group by id and _time it will not find the latest value before that time if the value for that minute doesn't exist.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.