Splunk Search

Search for variable Link value which changed and when it changed

atulitm
Path Finder

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

Labels (1)
Tags (1)
0 Karma
1 Solution

to4kawa
Ultra Champion
|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"

View solution in original post

0 Karma

to4kawa
Ultra Champion
|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"
0 Karma

atulitm
Path Finder

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

0 Karma

atulitm
Path Finder

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

0 Karma

to4kawa
Ultra Champion

I see , you do unaccepted

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

0 Karma

atulitm
Path Finder

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 .

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

atulitm
Path Finder

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"

0 Karma

to4kawa
Ultra Champion

What's variable Link ?

0 Karma

atulitm
Path Finder

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 .

0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...