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

[Michael]

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!

[chanfoli · Accepted solution]

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.

[MuS]

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.

[Michael]

"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!

[chanfoli]

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

[Michael]

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!