topic Search for variable Link value which changed and when it changed in Splunk Search

Search for variable Link value which changed and when it changed

atulitm — Sat, 06 Jun 2020 00:43:35 GMT

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

Re: Search for variable Link value which changed and when it changed

to4kawa — Mon, 11 May 2020 13:24:21 GMT

What's variable Link ?

Re: Search for variable Link value which changed and when it changed

richgalloway — Mon, 11 May 2020 14:03:30 GMT

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

Re: Search for variable Link value which changed and when it changed

atulitm — Mon, 11 May 2020 14:14:09 GMT

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"

Re: Search for variable Link value which changed and when it changed

atulitm — Mon, 11 May 2020 14:14:16 GMT

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 .

Re: Search for variable Link value which changed and when it changed

to4kawa — Mon, 11 May 2020 19:13:48 GMT

|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"

Re: Search for variable Link value which changed and when it changed

atulitm — Tue, 12 May 2020 09:51:24 GMT

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

Re: Search for variable Link value which changed and when it changed

atulitm — Mon, 18 May 2020 08:57:53 GMT

@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 .

Re: Search for variable Link value which changed and when it changed

to4kawa — Mon, 18 May 2020 09:05:54 GMT

I see , you do unaccepted

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

Re: Search for variable Link value which changed and when it changed

atulitm — Mon, 18 May 2020 09:09:52 GMT

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 .