Splunk Search

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

Explorer

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.

Tags (3)
0 Karma
1 Solution

Motivator

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

View solution in original post

Motivator

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

View solution in original post

Explorer

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.

0 Karma

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

0 Karma

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Motivator

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.

0 Karma

Explorer

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

0 Karma

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

Explorer

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

0 Karma

Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Explorer

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

0 Karma

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Motivator

@guru89044
Did you try this query?

0 Karma

Influencer

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

0 Karma

Explorer

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.

0 Karma

Influencer
 (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

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!