Archive

Use aggregate function latest for each minute to use timechart

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

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

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

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

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

New Member

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

0 Karma

SplunkTrust
SplunkTrust

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

by id _time

0 Karma

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