How to compare past 30 days vs today?

[alakhotia]

I want to have a table or chart where I can see the failure % of the past 30 days, vs. today, and output the difference too.

So the table should have:

Success over past 30 days, failure  over past 30 days, failure % over past 30 days, total over past 30 days, success today, failure today, failure % today, total today, failure % today minus failure % over past 30 days.

So far I have something like this, for only the past 30 day fields.

earliest=-30days

| eval status=case('httpReturnCode' == 200,"Success", 'httpReturnCode' != 200, "Invalid")

| stats count(eval(status="Success")) as Success, count(eval(status="Invalid")) as Failure by loggingObject.methodName | eval Failure(%)=(Failure/(Success + Failure)) * 100 | eval Total = Success + Failure

[Tom_Lundie]

Here is a run-anywhere example to get you started. I wasn't sure if you wanted to retain the method. Also this does not include the "today" results in the "past 30 days".

| makeresults count=100 
| eval zero_to_three=round(tonumber(substr(tostring(random()),-1))/3,0) 
| eval _time = _time - (zero_to_three * 10 * 86400) 
| fields - zero_to_three 
| eval httpReturnCode=if(tonumber(substr(tostring(random()),-1)) >= 5, 200, 404) 
| eval loggingObject.methodName=if(tonumber(substr(tostring(random()),-1)) >= 5, "GET", "POST") 
| eval status=case('httpReturnCode' == 200,"Success", 'httpReturnCode' != 200, "Invalid") 
| eval today = if(_time >= relative_time(now(),"@d"), "today", "past_30_days") 
| eval status=case('httpReturnCode' == 200,"Success", 'httpReturnCode' != 200, "Invalid") 
| stats count(eval(status="Success")) as Success, count(eval(status="Invalid")) as Failure by loggingObject.methodName, today
| eval Failure(%)=(Failure/(Success + Failure)) * 100 
| eval Total = Success + Failure
| chart sep="_during_" values(*) as * over loggingObject.methodName by today
| eval "Failure(%)_difference" = 'Failure(%)_during_today' - 'Failure(%)_during_past_30_days'

P.S. Using special-chars in Splunk fields can make commands a pain (note the use of single-quotes to get that last eval working). It might be worth swapping "(%)" with "pct".

[alakhotia]

Thanks.

When I pasted your query above, it said to move makeresults to the beginning.

I did.

I also removed the loggingObject.methodName from what you pasted, because I already had it from before the portion I pasted.

Here's what I have:

makeresults count=100

[some other terms, omitted here]

| eval zero_to_three=round(tonumber(substr(tostring(random()),-1))/3,0)
| eval _time = _time - (zero_to_three * 10 * 86400)
| fields - zero_to_three

| eval status=case('loggingObject.httpReturnCode' == 200,"Success", 'loggingObject.httpReturnCode' != 200, "Invalid")
| eval today = if(_time >= relative_time(now(),"@d"), "today", "past_30_days")
| eval status=case('loggingObject.httpReturnCode' == 200,"Success", 'loggingObject.httpReturnCode' != 200, "Invalid")
| stats count(eval(status="Success")) as Success, count(eval(status="Invalid")) as Failure by loggingObject.methodName, today
| eval Failure(%)=(Failure/(Success + Failure)) * 100
| eval Total = Success + Failure
| chart sep="_during_" values(*) as * over loggingObject.methodName by today
| eval "Failure(%)_difference" = 'Failure(%)_during_today' - 'Failure(%)_during_past_30_days'

This doesn't produce anything.

Also what is zero_to_three and _time evaluating?  

[Tom_Lundie · Accepted solution]

Ah sorry, I've mislead you with my answer.

The run-anywhere example can be copied and pasted directly into Splunk (without making any changes). It's designed to help others who come across this in future and also helps others to help you by providing a fake result set to play with.

That being said, lots of the lines in my search are used to generate those fake results in a way that matches your use-case. However, the section that you will be interested in, is as follows:

| eval status=case('httpReturnCode' == 200,"Success", 'httpReturnCode' != 200, "Invalid") 
| eval today = if(_time >= relative_time(now(),"@d"), "today", "past_30_days") 
| eval status=case('httpReturnCode' == 200,"Success", 'httpReturnCode' != 200, "Invalid") 
| stats count(eval(status="Success")) as Success, count(eval(status="Invalid")) as Failure by loggingObject.methodName, today
| eval Failure(%)=(Failure/(Success + Failure)) * 100 
| eval Total = Success + Failure
| chart sep="_during_" values(*) as * over loggingObject.methodName by today
| eval "Failure(%)_difference" = 'Failure(%)_during_today' - 'Failure(%)_during_past_30_days'

Try appending this to your base search.

[alakhotia]

Thank you.

How do I tell it to ignore the time range in the drop down?  It seems I have to select last 30 days in the preset, for it to accurately calculate the errors in the last 30 days, and today.

If I only select today, then it will have nothing in the past 30 days.

Or if I select last 7 days, then it will calculate the last 7 days, not 30 days.

Also I had to remove the makeresults from my query.

It might be useful to tie it to the value that's used in the drop town, if I add it to a panel and use the shared time picker.  But for now, I want to understand how I can have it ignore the time range in the drop down.

I think the end goal, once it is added to a panel, will be to use the time range in the shared time picker.  Although the field names will be a little mis leading, with the "_past_30_days" part, and I would probably change it to "_past_X_days".

[alakhotia]

Nevermind on having it ignore the time range in the drop down.

I think this is fine to use the drop down.

Thank you!