topic Re: Does the use of the stats command on a field clear that field's value? in Splunk Search

Does the use of the stats command on a field clear that field's value?

Michael — Wed, 14 Jan 2015 18:40:20 GMT

I'm trying to find visitors (IP addresses) to my web site that present with more than one UserAgent. (i.e., Baidu is known to change it's UserAgent in mid-stream...).

This works:

host=webserver GET  | stats dc(useragent) AS num_agents by clientip |  where num_agents>1 | table clientip,num_agents

And this works:

host=webserver GET  | transaction clientip | table clientip,useragent

But, if I stats the useragent field, it all of a sudden empties it... So, the below works -- but the values in the column for useragent is empty:

host=webserver GET | transaction clientip | stats dc(useragent) AS num_agents by clientip |  where num_agents>1 | table clientip,useragent

Thots?

thanks!

Re: Does the use of the stats command on a field clear that field's value?

chanfoli — Wed, 14 Jan 2015 18:56:02 GMT

In short, yes. stats works on the results you provide and only returns the fields that you specify. You might try adding values(useragent) to your stats command.

More info on stats here:

http://docs.splunk.com/Documentation/Splunk/6.2.1/Search/Usethestatscommandandfunctions

I would add that I am not sure of your intended use of transaction in this search. It looks to me like the net effect with transaction defaults would be to group all of your events with the same client ip (up to 1000) in the search into a single result and add potentially meaningless fields, depending on the data in your logs. It may also slow down your search quite a bit.

Re: Does the use of the stats command on a field clear that field's value?

chanfoli — Wed, 14 Jan 2015 19:38:13 GMT

Here is a search I was playing with which might get you closer to what you appear to be looking for, without the transaction overhead and possible confusion.

GET clientip=* | stats dc(useragent) AS num_agents, values(useragent) by clientip | sort -num_agents

Re: Does the use of the stats command on a field clear that field's value?

MuS — Wed, 14 Jan 2015 19:47:35 GMT

Just to be precise: stats does not clear any field's value - but any field not provided with a stats command is not available afterwards in the search pipeline. Have a look at the eventstats command http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/Eventstats which handle that.

Re: Does the use of the stats command on a field clear that field's value?

Michael — Wed, 14 Jan 2015 20:49:49 GMT

Fantastic, this is perfect, provides exactly what I was asking for. Thank you!

No, reading the docs yet again on stats doesn't do much for me, that's why there's Q&A places like this ... 😉

Award Points to Chanfoli!

Re: Does the use of the stats command on a field clear that field's value?

Michael — Wed, 14 Jan 2015 20:51:36 GMT

"Just to be precise: stats does not clear any field's value - but any field not provided with a stats command is not available afterwards in the search pipeline."

Thanks!