Capture Status Change

[vishaltaneja070]

I have data like this:
Status              EndTime        StartTime
Pending           25-06-2020      24-06-2020
Pending           24-06-2020      23-06-2020
New              23-06-2020      22-06-2020
Pending          22-06-2020      21-06-2020
Pending           21-06-2020      20-06-2020
OLD               20-06-2020      19-06-2020
OLD              19-06-2020      18-06-2020
NEW              18-6-2020       17-06-2020

I need to capture the date change and start and end time of Status change. So output should be like:

Pending 25-06-2020 23-06-2020
New 23-06-2020 22-06-2020
Pending 22-06-2020 20-06-2020
OLD  20-06-2020 18-06-2020
NEW     18-6-2020     17-06-2020

can somebody please help?

[bowesmana · Accepted solution]

Try this

| makeresults
| eval _raw="STATUS ENDTIME STARTTIME
Pending 25-06-2020 24-06-2020
Pending 24-06-2020 23-06-2020
New 23-06-2020 22-06-2020
Pending 22-06-2020 21-06-2020
Pending 21-06-2020 20-06-2020
OLD 20-06-2020 19-06-2020
OLD 19-06-2020 18-06-2020
NEW 18-6-2020 17-06-2020"
| multikv
| fields - _raw _time
| eval start=strptime(STARTTIME, "%d-%m-%Y"), end=strptime(ENDTIME, "%d-%m-%Y")
| streamstats reset_on_change=t min(start) as start max(end) as end by STATUS
| stats min(start) as start by STATUS, end
| sort - end
| eval start=strftime(start, "%F"), end=strftime(end, "%F")

You example data setup  is up to the fields - _raw _time

Hope this helps