index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1
| eval status = if(like(_raw, "%down%"), 1 , 0)
| stats count
| eval status=if(count==0, "up", status)
| table status
This seems to be a simple query but for some reason it really does not like me.
I am wanting to return a default value of "up" if there is no results found.
Long Explanation:
I am creating a dashboard that will tell us if an interface on a router is down. Since our Index is large and is no position to be adjusted I am only checking for changes in the log over the last 30 days. At the end of the 30 days the dashboard will produce "No results found" because there has been no events in the last 30 days that matches the search. Because of this I want to setup a default value that will return.
I have tried:
fillnull
| eval noResults = if(searchmatch("NBRCHANGE"),1,0)
| stats count as myCount sum(noResults) AS noResults
| eval noResults=if(myCount=="0",0,noResults)
| eval status = case(noResults=="0", "first result returned", noResults!="0", if(like(_raw, "%down%"), "second result returned", "third result returned"))
| table status
The issue with your search is that the line | stats count
is eliminating all fields except the newly-created field named count
. Thus, on the next line when you reference the field status
as a default value for non-matches, there is no value in status
. How about this instead:
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1
| eval status = if(like(_raw, "%down%"), 1 , 0)
| stats count max(status) AS status
| eval status=if(count==0, "up", status)
| table status
Note that I replaced | stats count
with | stats count max(status) AS status
, which preserves the field status
so you can still reference it in the next line.
The above suggestion preserves all of the original logic, while forwarding the value of status
through the call to stats
. However, if I read your requirements correctly, I suspect you'll want to change it to this:
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1
| eval status = if(like(_raw, "%down%"), 1 , 0)
| stats count max(status) AS status
| eval status=if(count==0 OR status==0, "up", "down")
| table status
This will display "down" if either the base search returns no results or the base search returns a result that contains "down" in the _raw
event. It will display "up" if the base search returns a result that does not contain "down" in the _raw
event.
The issue with your search is that the line | stats count
is eliminating all fields except the newly-created field named count
. Thus, on the next line when you reference the field status
as a default value for non-matches, there is no value in status
. How about this instead:
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1
| eval status = if(like(_raw, "%down%"), 1 , 0)
| stats count max(status) AS status
| eval status=if(count==0, "up", status)
| table status
Note that I replaced | stats count
with | stats count max(status) AS status
, which preserves the field status
so you can still reference it in the next line.
The above suggestion preserves all of the original logic, while forwarding the value of status
through the call to stats
. However, if I read your requirements correctly, I suspect you'll want to change it to this:
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1
| eval status = if(like(_raw, "%down%"), 1 , 0)
| stats count max(status) AS status
| eval status=if(count==0 OR status==0, "up", "down")
| table status
This will display "down" if either the base search returns no results or the base search returns a result that contains "down" in the _raw
event. It will display "up" if the base search returns a result that does not contain "down" in the _raw
event.
The first query solves it for me. You are correct I will change it but the stats count killing all my fields was my issue.
Thank you very much for the help!
Try like this
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| head 1 | stats count values(_raw) as raw
| eval status = case(count=0,"up", like(raw, "%down%"),"down",1=1,"down")
| table status
Something like -
index=ios host=1.1.0.2 src_ip="1.2.2.1" "NBRCHANGE"
| stats count
| eval type=if(count = 0 ,"up","down")
The only problem is if there is a value I need to evaluate if it states up or down. If there is no results then I can assume it is up.