Below query is working fine But i want to include date wise success and failures for comparison.
(index="x") AND (host="y") status > 199 AND status < 400
| stats count as Success BY application, methodPath | join methodPath type=left
[search (index="x") AND (host="y") status > 400
| stats count as Failures BY application, methodPath] | table application, methodPath, Success, Failures | sort by Success DESC | sort by Failures DESC
My table should like.
application, methodPath, Success(3/09/2018), Failures(3/09/2018), Success(3/10/2018), Failures(3/10/2018) etc.
Or application, methodPath, 3/09/2018 -Success, Failures, 3/10/2018 Success, Failures.
Any better way also welcome.
Thank you.
Hey guru 89044,
You can try this:
index="X" AND HOST="Y" status=*
| bin _time span=1d
| eval time=strftime(_time,"%d-%m-%Y")
| eval newfield=application.+":".+methodPath
| chart useother=f sum(eval(if(status>199 AND status<400,1,0))) as success sum(eval(if(status>400,1,0))) as failure OVER newfield by time
| rex field=newfield "(?<application>[^\:]+)\:(?<methodPath>.*)"
| fields- newfield
| table application methodPath *
| rename failure:* as *:failure success:* as *:success
Let me know if this helps!!
Hey guru 89044,
You can try this:
index="X" AND HOST="Y" status=*
| bin _time span=1d
| eval time=strftime(_time,"%d-%m-%Y")
| eval newfield=application.+":".+methodPath
| chart useother=f sum(eval(if(status>199 AND status<400,1,0))) as success sum(eval(if(status>400,1,0))) as failure OVER newfield by time
| rex field=newfield "(?<application>[^\:]+)\:(?<methodPath>.*)"
| fields- newfield
| table application methodPath *
| rename failure:* as *:failure success:* as *:success
Let me know if this helps!!
Cool!!!!!!!!!! at least I am getting the results way i want. What time its considering while filtering the counts? is it From midnight to midnight? What parameter i need to change in the query itself to get x days(5 days) result for comparison? it takes lot of time, is it due to eval ? what does | fields- newfield does?
Thank you.
Thanks!
@deepashri_123 earliest=-5d@d latest=@d
should be added in the base search before the bin command.
Also stats can be used upfront after span=1d and eval can be performed on aggregated data per day to make it faster.
Adding limit=0
or some max upper limit(if known) to chart command would be safer bet.
Finally, as per the output in the request rex for field segregation is not required:
index="X" AND HOST="Y" status="*" application=* methodPath=*
| bin _time span=1d
| stats count(eval(status>199 AND status<300)) as Success count(eval(status>299 AND status<600)) as Failure by application methodPath _time
| eval key=application.":".methodPath
| eval time=strftime(_time,"%Y/%m/%d")
| fields - _time application methodPath
| chart limit=0 useother=f sum(Success) as Success sum(Failure) as Failure over key by time
| rename "Failure: *" as "*(Failure)"
| rename "Success: *" as "*(Success)"
PS: Changing time format to %Y/%m/%d
instead of %d/%m/%Y
so that output is sorted correctly for string time.
Hey@niketnilay,
I agree with you , stats should have been used , and i was trying to implement that itself but that was not giving the desired output. If this query gives desired output to guru89044 than the problem will be solved.
@deepashri_123 @niketnilay
great. Thank you very much guys. I modified original query a bit as per niketnilay. Worked fine.
@guru89044, glad you found it working. Do up vote the comment if it helped!
done. I was not aware there is option of upvote for comment also thank you.
@deepashri_123 @niketnilay
May i know how to get the same result for 2 different date ranges?
i,e Success and failures count from march 1st to march 5th and from mar 6th to Mar10th.
Thank you.
Since you do not have time on x-axis and do not want time overlay could you please explain reason for the dates as to why 1st to 5th and 6th to 10th?
You can use date_wday!="sunday"
along with earliest
and latest
time selector.
@niketnilay sure. I am testing X release for A duration and y release for B duration. Now i want to compare the count of failures of X release vs Y release.
Also please let me know if any better tutorial available for splunk.
Thank you.
@guru89044, sorry I missed this question, you should check out following blog for overlaying time series...
https://www.splunk.com/blog/2012/02/19/compare-two-time-ranges-in-one-report.html
You would want to use multisearch command or appendcol command instead of append.
You should also check out timewrap command available from Splunk 6.5 and above.
For learning Splunk you can check out Splunk Fundamentals course and some of other free elearning courses on Splunk Education. Splunk Docs and Splunk Dev are good documentation and tutorial sites. You can also check out Splunk How To channel on Youtube and Splunk Blogs.
@guru89044
Did you try this query?
There is no need for that join, you can do that in one shot:
(index="x") AND (host="y")
| stats sum(eval(if(status > 199 AND status < 400,1,0))) as Success, sum(eval(if(status>400,1,0))) as Failures BY application, methodPath
| sort by Success DESC
| sort by Failures DESC
Let me know if it worked for you
sorry. No. My ask is to find the date wise failures and success. Please let me how to add date wise success and failures in table format in the dashboard. Thanks for query without join but failure/success count of my query vs your query didn't match.
(index="x") AND (host="y")
| bucket _time
| stats sum(eval(if(status > 199 AND status < 400,1,0))) as Success, sum(eval(if(status>400,1,0))) as Failures BY application, methodPath, _time
no..it didn't work. Why the result(success and failure count) of your query is not matching with my query? Also time taken by your query to display the result is more. Thanks