Splunk Search

inline stats without subsearch

Communicator

This is a recurring problem for me in SPL. I want to assign some stats command results to a variable name and pop that into a table with the rest of the results. For instance, if my data look like this:

user    visits   action    url             
steve   3        blocked   www.porn.com 
steve   5        blocked   www.porn.com 
steve   23       blocked   www.porn.com 
steve   1        blocked   www.porn.com 
jack    4        allowed   www.gov.gov
jack    3        allowed   www.gov.gov
jack    1        allowed   www.gov.gov
jack    5        allowed   www.gov.gov

And all I want to do is list the max(visits) or avg(visits) or stdev(visits) (or all 3) for each combination of user, url and action, I want to do something like this:

index=proxy sourcetype=proxy earliest=-7d@d | stats avg(visits) as AVG by user | table user url AVG action

But that won't work because stats now only makes AVG and user available for the next pipe (in other words, url & action will be blank in the table). So, when I need all previous info + some sort of new calculated info available for the next section, I have started using eval, but eval doesn't have any stats capabilities. So what I'm looking for is something like this:

index=proxy sourcetype=proxy earliest=-7d@d | eval AVG = avg(visits) by user | table user url AVG action

But obviously, there is no such thing as eval x = avg(). Is there any way to get stats from some data without nuking the data (without using a subsearch?)

Subsearch is fine when you have tiny little data sets, but it's not feasible for this use case.

Tags (3)
0 Karma

Legend

You can do exactly what you want by using eventstats instead of stats. eventstats writes its results as a field in the original event rather than "consuming" its input and only leave its calculated fields as output. http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/Eventstats