topic Re: Generate a lookup for EPS in Splunk Search

Generate a lookup for EPS

csprice — Tue, 14 Jan 2020 19:44:40 GMT

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

Re: Generate a lookup for EPS

richgalloway — Tue, 14 Jan 2020 20:22:04 GMT

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

Re: Generate a lookup for EPS

csprice — Tue, 14 Jan 2020 20:30:48 GMT

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.

Re: Generate a lookup for EPS

to4kawa — Tue, 14 Jan 2020 21:24:42 GMT

| 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

Re: Generate a lookup for EPS

csprice — Wed, 15 Jan 2020 13:09:50 GMT

@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.

Re: Generate a lookup for EPS

csprice — Wed, 15 Jan 2020 15:14:47 GMT

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.