Generate a lookup for EPS

[csprice]

Hello.

I have an index with traffic from 10 devices. I want to generate a lookup that contains the avg EPS over the course of 7 days utilizing tstats.

my current search - cobbled together from scouring answers:
| tstats count as COUNT where index=firewall earliest=-7d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host

kind of works, but spits out >50000 rows. What I'd like is a host - xxx eps looking over the past 7 days.

Any pointers would be appreciated

[csprice · Accepted solution]

Utilizing some suggestions here and a little more digging, what I came up with was the following:

| tstats count as COUNT where index=firewall earliest=-7d by host, _time span=1s
| stats avg(COUNT) as eps by host
| eval eps=round(eps,2)

This produced the table I was after that I could then output to a lookup.

[to4kawa]

| tstats count as COUNT where index=firewall earliest=-7d latest=-6d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host
| append [ | tstats count as COUNT where index=firewall earliest=-6d latest=-5d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]
| append [ | tstats count as COUNT where index=firewall earliest=-5d latest=-4d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]
| append [ | tstats count as COUNT where index=firewall earliest=-4d latest=3d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]
| append [ | tstats count as COUNT where index=firewall earliest=-3d latest=-2d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]
| append [ | tstats count as COUNT where index=firewall earliest=-2d latest=-1d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]
| append [ | tstats count as COUNT where index=firewall earliest=-1d by _time, host span=1s
| timechart span=1h max(COUNT) as eps by host]

hi, @csprice
will you stack append

[csprice]

@to4kawa Thank you for your response, but this essentially does the same as my initial search. I'm looking for output similar to the one Rich Galloway put out - just one that looks at the network devices inside the firewall index.

[richgalloway]

It's not as fast as tstats, but this query should do the job,

index=_internal source=*metrics.log group=per_index_thruput series=firewall earliest=-7d 
| bucket span=1h _time 
| stats max(eps) by host

[csprice]

Thank you Rich for your response. However, this isn't quite what I was after. This gave me a list of my splunk infrastructure and max eps.

I'm trying to look at my firewall index and pull out the hosts that are pushing data there. Then just keep track of their EPS. That way I can run a correlation search to check and see when/if their EPS falls off to create a notable in ES.