1

I have event logs in a Splunk index. I want to get the number of a) successful requests and b) failed requests based on statusCode over the last week. However, I want to show the number of each for each day.

From my understanding, I can execute a query for yesterday using something like this:

index="my_index" 
sourcetype="*" 
_raw="*execTime*" 
earliest=-1d@d
latest=now 
| fields _time requestUrl statusCode
| stats
  count(eval(statusCode<200 OR statusCode>299)) as failures
  count(eval(statusCode>199 AND statusCode<300)) as successes
  by requestUrl 
| table requestUrl failures successes

This will give me the total success and failures for each request url for yesterday. I could copy this query and change the earliest field value for each day. However, it seems like there are two better ways:

  1. Save this query as a report and pass the earliest value in as a parameter. This would require seven separate search requests (one for each day) or
  2. Run one query that gets all of the events for a week. Then, run queries against the results of that query. One query for each day. The thinking is that this would reduce the set of events that need to be evaluated.

The latter seems more efficient. However, I don't know if it's possible. My question is, is it possible, if so how? Is it actually more efficient?

Thank you

Developer
  • 89
  • 1
  • 4

1 Answers1

0

Yes, it can be done both ways, but it's easier to run a single query for the week and let Splunk separate them by day.

index="my_index" sourcetype="*" _raw="*execTime*" earliest=-7d@d latest=@d 
| fields _time requestUrl statusCode
```Separate the events into days by rounding _time to the beginning of the day```
| bin span=1d _time
```Count events by day and statusCode```
| stats
  count(eval(statusCode<200 OR statusCode>299)) as failures
  count(eval(statusCode>199 AND statusCode<300)) as successes
  by _time,requestUrl 
| table _time requestUrl failures successes

BTW, the construct earliest=-1d@d latest=now searches both yesterday and today. To search only yesterday, use earliest=-1d@d latest=@d.

RichG
  • 9,063
  • 2
  • 18
  • 29
  • Thank you. Is there a way to get the group number from the `bin` command? Right now, the `_time` in the table is some number (seconds?). – Developer May 26 '22 at 18:39
  • _time **is** the group number from `bin`. It's supposed to be converted a human-readable date automatically, but if that's not happening then format it manually using `| fieldformat _time=strpftime(_time, "%Y-%m-%d")`. – RichG May 26 '22 at 18:45