CODEWITHSUNDEEP
splunksplunk-query
Dev
Dev

Reputation: 1223

Output counts grouped by field values by for date in Splunk

I have a Splunk index named http_logs with the following fields:

I'm trying to use this data to create a table that looks like this:

Requester Today Yesterday Last Seven Days
Service 1 100 (2) 300 (4) 2000 (7)
Service 2 120 (3) 275 (3) 2400 (9)

...

As the table above shows, each column has two values:

  1. The number of http_logs with a status_code in the range of 200-299 for the time range (ie. today, yesterday, last seven days)
  2. The number of http_logs with a status_code outside of 200-299 for the time range (ie. today, yesterday, last seven days)

Currently, I have the following Splunk query:

index="http_logs"
earliest=-6d@d
latest=now
| fields requester status_code _time
| table requester

I don't know how to format a table while grouping fields with specific values though. Is this even possible with a Splunk query?

Upvotes: 0

Views: 5667

Answers (1)

RichG
RichG

Reputation: 9906

Splunk tables usually have one value in each cell. To put multiple values in a cell we usually concatenate the values into a single value.

To get counts for different time periods, we usually run separate searches and combine the results.

Note the use of sum instead of count in the stats commands. This is because the eval function always returns a value (0 or 1) and counting them would give the total number of results rather than the number of events that match the condition.

```Get today's events```
index=http_logs earliest=@d
| eval success=if(status_code>=200 status_code<=299, 1, 0)
```Count successes and failures```
| stats sum(eval(success=1)) as today_success, sum(eval(success=0)) as today_fail by requester
```Repeat for yesterday's events```
| append [ search index=http_logs earliest=-1d@d latest=@d
  | eval success=if(status_code>=200 status_code<=299, 1, 0)
  | stats sum(eval(success=1)) as yesterday_success, sum(eval(success=0)) as yesterday_fail by requester ]
```Repeat for the last 7 days' events```
| append [ search index=http_logs earliest=-7d@d latest=now
  | eval success=if(status_code>=200 status_code<=299, 1, 0)
  | stats sum(eval(success=1)) as sevenday_success, sum(eval(success=0)) as sevenday_fail by requester ]
```Put the results together```
| stats values(*) as * by requester
```Build the display values```
| eval Today = today_success . " (" . today_fail . ")"
| eval Yesterday = yesterday_success . " (" . yesterday_fail . ")"
| eval "Last Seven Days" = sevenday_success . " (" . sevenday_fail . ")"
| rename requester as Requester
```Display the results```
| table Requester Today Yesterday "Last Seven Days"

Upvotes: 1

Related Questions