Splunk dashboard search parameter- How to get exact date?

[Teemanny]

I have a dashboard that shows the APIs called over a particular period and get the success rate based on the response status, but I need to also get the success rate of that API over the past two weeks (last week, last two weeks) on the exact date, but I've been having issues getting that. This is my current search parameter. Any help on how to get this will be appreciated.

Index="Main"  |  stats count(eval(in(response_status, "200", "201", "202","203","204","205",....,"307","308", "0") and severity="Audit")) AS Success_count, count(eval(in(response_status,"400",...,"451"))) AS Backend_4XX, count(eval(in(response_status, "0") and severity="Exception")) AS L7_Error, count(eval(in(response_status, "500",...,"511"))) AS Backend_5XX BY API | eval Total = Success_count + (Backend_4XX + Backend_5XX + L7_Error), Success_Rate=round(Success_count/Total*100,2) | Table API Total Success_count L7_Error Backend_4XX Backend_5XX Success_Rate | sort API | search Success_Rate=*

[Teemanny]

I am new to Splunk and kind of need help in getting this done. How do I add the time command?

[ITWhisperer]

Index="Main" | bin _time span=1w |  stats count(eval(in(response_status, "200", "201", "202","203","204","205",....,"307","308", "0") and severity="Audit")) AS Success_count, count(eval(in(response_status,"400",...,"451"))) AS Backend_4XX, count(eval(in(response_status, "0") and severity="Exception")) AS L7_Error, count(eval(in(response_status, "500",...,"511"))) AS Backend_5XX BY API _time

[Teemanny]

Thank you for the response. So, I want to have data for this week and the same set of data for the exact time last week, how do I get the add the field for last week

[ITWhisperer]

How are you defining the time range for your search currently?

[Teemanny]

I currently use the default time element in the Splunk search but figured that won't work. I am currently trying to use the Splunk search to get the success rate for today and last week. I have been stuck on getting this done for a few days. How do I incorporate the _time and get the data for this week and last week in the same data table. I want to have Success_count and Success_count_lastweek in the same table.

[ITWhisperer]

The timeframe for the search is available to the search through the addinfo command, also the timeframe for the search can be modified by the search, so combining these two, you could do something like this

<your initial search> [| makeresults | addinfo | eval row=mvrange(0,2) | mvexpand row | eval earliest=info_min_time - (7 * 24 * 60 * 60 * row) | eval latest=info_max_time - (7 * 24 * 60 * 60 * row) | table earliest latest]

[Teemanny]

It failed to display a result, plus I want the result to show in the table below beside the current valuesas Success_count lastweek L7_Errorlastweek and others.

P.S: The screenshot currently shows the data I get now

[ITWhisperer]

Please share the full search you used for that table.

Also, you should look at using timewrap to get 7 days earlier

[Teemanny]

index="l7" | rename service_resolutionUri as API | stats count(eval(in(response_status, "200", "201", "202","203","204","205","206","207","208","226","300","301","302","303","304","305","306","307","308", "0") and severity="Audit")) AS Success_count, count(eval(in(response_status,"400","401","402","403","404","405","406","407","408","409","410","411","412","413","414","415","416","417","418","421","422","423","424","425","426","426","428","429","431","451"))) AS Backend_4XX, count(eval(in(response_status, "0") and severity="Exception")) AS L7_Error, count(eval(in(response_status, "500","501","502","503","504","505","506","507","508","510","511"))) AS Backend_5XX BY API | eval Total = Success_count + (Backend_4XX + Backend_5XX + L7_Error), Success_Rate=round(Success_count/Total*100,2) | table API Total Success_count L7_Error Backend_4XX Backend_5XX Success_Rate

[ITWhisperer]

Try something like this (not 100% on whether I have the syntax right):

index="l7" [| makeresults | addinfo | eval row=mvrange(0,2) | mvexpand row | eval earliest=info_min_time - (7 * 24 * 60 * 60 * row) | eval latest=info_max_time - (7 * 24 * 60 * 60 * row) | table earliest latest]
| bin _time span=1w
| rename service_resolutionUri as API | stats count(eval(in(response_status, "200", "201", "202","203","204","205","206","207","208","226","300","301","302","303","304","305","306","307","308", "0") and severity="Audit")) AS Success_count, count(eval(in(response_status,"400","401","402","403","404","405","406","407","408","409","410","411","412","413","414","415","416","417","418","421","422","423","424","425","426","426","428","429","431","451"))) AS Backend_4XX, count(eval(in(response_status, "0") and severity="Exception")) AS L7_Error, count(eval(in(response_status, "500","501","502","503","504","505","506","507","508","510","511"))) AS Backend_5XX BY API _time
| eval Total = Success_count + (Backend_4XX + Backend_5XX + L7_Error), Success_Rate=round(Success_count/Total*100,2) | table _time API Total Success_count L7_Error Backend_4XX Backend_5XX Success_Rate
| timewrap 1w

[Teemanny]

Thank you for your help. I want the API field to remain same while the other field changes. The current display API last week and times out after a few minutes

[ITWhisperer]

Can you share the exact query which is timing out?

Can you open the search in a separate window and show what time values are being used?

[Teemanny]

The API name should remain same while other values have an append of today and last week and it should produce more than one result.

The idea is I'm trying to get the APIs called this moment and show the total,success_count,success_rate..... for today and exact same data for this time last week.

[ITWhisperer]

If you want more than one result, remove the timewrap command

[Teemanny]

I didn't get the values for last week

[ITWhisperer]

But you got two rows?

[Teemanny]

No. I didn't...it reverted to the data I had previously, not showing the data for last week

[ITWhisperer]

Please can you show the search covering more than 1 week, which has time binned by week, and stats generated by time which is not showing more than one row.

If there is only one row, please can you ensure that there is actually data for the previous week under the search conditions available.

[ITWhisperer]

Your initial stats command has no time element to it so any notion of when the events happened has been lost. you could try preceding it with a bin command to allocate the events to different time bins and base your stats on that (as well as API)

[ManishS]

Based on the frequency of the result set, we can decide on span time in the bin command to project the relevant time in the results.