Use aggregate function latest for each minute to use timechart

[wvanloon]

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.

[arjunpkishore5 · Accepted solution]

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.

[wvanloon]

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?

[arjunpkishore5]

You're welcome.

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

[wvanloon]

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

[marycordova]

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

by id _time

[wvanloon]

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.