Search for variable Link value which changed and when it changed

[atulitm]

Date="8 May 2020" Link="X" Status="UP"
Date="9 May 2020" Link="Y" Status="DOWN"
Date="10 May 2020" Link="X" Status="UP"
Date="11 May 2020" Link="X" Status="DOWN"
Date="12 May 2020" Link="Y" Status="UP"

I am getting logs on daily basis in above format and data . I am looking to find variable Link whose Status went down but never came up and on which date it went DOWN . Can someone please help with same , thanks

[to4kawa · Accepted solution]

|makeresults
| eval _raw="Date=\"8 May 2020\" Link=\"X\" Status=\"UP\"
Date=\"9 May 2020\" Link=\"Y\" Status=\"DOWN\"
Date=\"10 May 2020\" Link=\"X\" Status=\"UP\"
Date=\"11 May 2020\" Link=\"X\" Status=\"DOWN\"
Date=\"12 May 2020\" Link=\"Y\" Status=\"UP\"
Date=\"13 May 2020\" Link=\"X\" Status=\"DOWN\""
| multikv noheader=t 
| kv
| table Date Link Status


| eval Date=strptime(Date,"%d %B %Y")
| fieldformat Date=strftime(Date,"%F")
| sort Link Date
| streamstats current=f last(Status) as prev by Link
| streamstats count(eval(Status!=prev)) as changed by Link
| eventstats last(changed) as session by Link
| where changed==session
| stats min(Date) as start max(Date) as end values(Status) as Status by session Link
| where Status="DOWN"
| convert ctime(start) ctime(end) timeformat="%F"

[atulitm]

This works as expected with few changes for my other need . Thank you !!

[atulitm]

@to4kawa above query works but i see issue being that streamstats reaches limits as number of logs are more than 10000 so it doesnt work . is there any workaround for same thanks .

[to4kawa]

I see , you do unaccepted

I don't know your problem, I only answer your question.

[atulitm]

I unaccepted because it doesn't resolve the issue completely but thats true it resolve the original question . i will raise another question for corresponding issue then . No problem , accepted it solution for original query .

[richgalloway]

This may help. It takes the most recent Status value and throws away anything not "DOWN".

index=foo
| stats latest(Date) as Date, latest(Status) as Status by Link
| where Status="DOWN"
| table Date Status

[atulitm]

Actually this i already tried this but this shows last logs which mean which is down but not up but it doesnt show when it went down . For example it below case , Link X went down on 11 May but log on 13 May shows its still down .

Date="9 May 2020" Link="Y" Status="DOWN"
Date="10 May 2020" Link="X" Status="UP"
Date="11 May 2020" Link="X" Status="DOWN"
Date="12 May 2020" Link="Y" Status="UP"
Date="13 May 2020" Link="X" Status="DOWN"

[to4kawa]

What's variable Link ?

[atulitm]

Date="9 May 2020" Link="Y" Status="DOWN"
Date="10 May 2020" Link="X" Status="UP"
Date="11 May 2020" Link="X" Status="DOWN"
Date="12 May 2020" Link="Y" Status="UP"
Date="13 May 2020" Link="X" Status="DOWN"

Variable Link is shows in Logs above with below requirement :
For example variable Link "X" went down on 10th May but log on 13th May shows its still down .
and query should not show Link Y as output because it went down on 9th May but last logs shows its up now as in last log . Hope this clarifies what i am looking for thanks .