Hi,
I have dataset in the following format
Name,Status,Timestamp
ABC,F, 04/24/2025 15:30:03
ABC, R, 04/24/2025 15:15:01
I need to be able to only display / render the latest status for a given name
My output should like the following since the status as of 04/24/2025 15:30:03 is the most recent status.
ABC,F, 04/24/2025 15:30:03
Appreciate your help.
It worked for certain cases but please see the following
For the following data records,
ABC,F, 04/24/2025 15:30:03
ABC, R, 04/24/2025 15:15:01
ABC, F, 04/25/2025 15:50:00
ABC, R, 04/25/2025 15:25:00
The solution should be as follows - i.e. latest status by day has to be captured.
ABC,F, 04/24/2025 15:30:03
ABC, F, 04/25/2025 15:50:00
I think you have your answer in other posts, but this is a good indication of asking the right question - including the "by day" also is an important point 🙂
You didn't answer @bowesmana 's question about whether your sample is from an index or a lookup table. I will assume that they come from events. In this case, it is unnecessary to extract _time inline. You can use latest as @bowesmana and @ITWhisperer suggested, or you can simply use dedup to get the latest events before further processing:
| eval day = strftime(_time, "%F")
| dedup day Name
Given this dataset
Name | Status | _raw | _time |
ABC | F | ABC,F, 04/25/2025 15:50:00 | 2025-04-25 15:50:00 |
ABC | R | ABC,R, 04/25/2025 15:25:00 | 2025-04-25 15:25:00 |
ABC | F | ABC,F, 04/24/2025 15:30:03 | 2025-04-24 15:30:03 |
ABC | R | ABC,R, 04/24/2025 15:15:01 | 2025-04-24 15:15:01 |
The above will give you
Name | Status | _raw | _time | day |
ABC | F | ABC,F, 04/25/2025 15:50:00 | 2025-04-25 15:50:00 | 2025-04-25 |
ABC | F | ABC,F, 04/24/2025 15:30:03 | 2025-04-24 15:30:03 | 2025-04-24 |
Here is a full emulation of your mock data
| makeresults
| eval _raw="Name,Status,Datestamp
ABC,F, 04/24/2025 15:30:03
ABC,R, 04/24/2025 15:15:01
ABC,F, 04/25/2025 15:50:00
ABC,R, 04/25/2025 15:25:00"
| multikv forceheader=1
| eval _time = strptime(Datestamp, "%m/%d/%Y %T")
| fields - Datestamp linecount
| sort - _time
``` data emulation above ```
| makeresults format=csv data="Name,Status,Timestamp
ABC,F, 04/24/2025 15:30:03
ABC, R, 04/24/2025 15:15:01
ABC, F, 04/25/2025 15:50:00
ABC, R, 04/25/2025 15:25:00"
| eval _time = strptime(Timestamp, "%m/%d/%Y %T")
| bin _time as _day span=1d
| stats latest(*) as * by _day Name
I am guessing this is data in a lookup file rather than event data - if you have event data you would already have a time stamp in the event which may or may not be the same as Timestamp.
However, in your specific example, assuming no _time field, the just parse the Timstamp field and use stats latest to get the latest, i.e.
| makeresults format=csv data="Name,Status,Timestamp
ABC,F, 04/24/2025 15:30:03
ABC, R, 04/24/2025 15:15:01"
| eval _time = strptime(Timestamp, "%m/%d/%Y %T")
| stats latest(*) as * by Name
It worked for certain cases but please see the following
For the following data records,
ABC,F, 04/24/2025 15:30:03
ABC, R, 04/24/2025 15:15:01
ABC, F, 04/25/2025 15:50:00
ABC, R, 04/25/2025 15:25:00
The solution should be as follows - i.e. latest status by day has to be captured.
ABC,F, 04/24/2025 15:30:03
ABC, F, 04/25/2025 15:50:00
Hi @bsreeram
If you want it splitting by Name and day so you get the latest per Name AND day then you can use a timechart
| timechart span=1d latest(*) as *
🌟 Did this answer help you? If so, please consider:
Your feedback encourages the volunteers in this community to continue contributing.