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.
