topic Re: How to include date wise success and failures for comparison in table format in the dashboard? in Splunk Search

How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Tue, 13 Mar 2018 12:27:12 GMT

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.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

tiagofbmm — Tue, 13 Mar 2018 13:17:16 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Tue, 13 Mar 2018 14:49:27 GMT

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.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

tiagofbmm — Tue, 13 Mar 2018 14:55:10 GMT

 (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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

deepashri_123 — Wed, 14 Mar 2018 08:57:06 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Wed, 14 Mar 2018 10:16:14 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

deepashri_123 — Wed, 14 Mar 2018 10:20:18 GMT

@guru89044
Did you try this query?

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Wed, 14 Mar 2018 11:03:09 GMT

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.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

deepashri_123 — Wed, 14 Mar 2018 11:53:38 GMT

The time period is for each day where bin _time span=1d
To get 5 days you can add earliest=-5d@d latest=@d after bin command.
i have created a newfield to display desired result, fields -newfield removes that field once the output is achieved.
Please accept the answer and upvote if this worked for you!!

Thanks!

Re: How to include date wise success and failures for comparison in table format in the dashboard?

niketn — Wed, 14 Mar 2018 13:00:33 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

deepashri_123 — Thu, 15 Mar 2018 05:08:07 GMT

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.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Thu, 15 Mar 2018 07:46:09 GMT

@deepashri_123 @niketnilay
great. Thank you very much guys. I modified original query a bit as per niketnilay. Worked fine.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

niketn — Thu, 15 Mar 2018 08:13:02 GMT

@guru89044, glad you found it working. Do up vote the comment if it helped!

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Thu, 15 Mar 2018 08:35:18 GMT

done. I was not aware there is option of upvote for comment also thank you.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Thu, 15 Mar 2018 10:08:47 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

niketn — Thu, 15 Mar 2018 13:58:35 GMT

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.

Re: How to include date wise success and failures for comparison in table format in the dashboard?

guru89044 — Fri, 16 Mar 2018 05:02:32 GMT

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

Re: How to include date wise success and failures for comparison in table format in the dashboard?

niketn — Tue, 03 Apr 2018 02:51:32 GMT

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