topic Re: Stats Sum question in Splunk Search

Stats Sum question

palisetty — Wed, 30 Sep 2020 03:30:15 GMT

I have sum (field) which has been piped into stats sum of another field, Not sure what is happening here. Kindly help me understand.

sum(field) {| stats sum(sc_bytes) as Bandwidth by s_host}

Re: Stats Sum question

niketn — Thu, 02 Jan 2020 13:51:50 GMT

@palisetty what are you trying to do? What is your complete query? Do you get any error or is your output not as expected? What does your data look like and what is the expected output. For the community to assist you better please provide as much details as possible.

Re: Stats Sum question

richgalloway — Thu, 02 Jan 2020 13:55:33 GMT

The stats command filters fields to only those referenced in the command. In the case of stats sum(field) the only field available to later commands is sum(fields). The sc_bytes and s_host fields are removed (as are all others). Consider using eventstats, instead.

Re: Stats Sum question

palisetty — Wed, 30 Sep 2020 03:30:26 GMT

I am sorry for the discomfort. Actually, I got that bit from Quizlet. Let us take the proper query, I have an issue with multiple stats functions within a Search Component. I don't understand what is going on.

index=main sourcetype=access_combined_wcookie | stats values(useragent) as "Agents used" count as "Times used" by useragent

Re: Stats Sum question

woodcock — Thu, 02 Jan 2020 15:22:52 GMT

Not exactly true; there is also the BY field, namely userAgent.

Re: Stats Sum question

woodcock — Thu, 02 Jan 2020 15:24:57 GMT

The search is idiotic, as is any search of thts format:

... | stats anyfunc(byField) BY byField

Re: Stats Sum question

palisetty — Wed, 30 Sep 2020 03:30:31 GMT

@gcusello @woodcock @richgalloway
I am sorry for the discomfort. Actually, I got that bit from Quizlet.

Let us take the proper query, I have an issue with multiple stats functions within a Search Component. I don't understand what is going on.

index=main sourcetype=access_combined_wcookie | stats values(useragent) as "Agents used" count as "Times used" by useragent

I have rewritten it so that everyone can see it. I don't want to use eval command as I have no idea about it.

Re: Stats Sum question

niketn — Wed, 30 Sep 2020 03:35:02 GMT

@palisetty if you are preparing for any Splunk Certification exam, I would recommend you to go through the course content and refer to Splunk Documentation for all your queries, understanding and additional reading. Splunk Docs have all the required examples and explanations which you require for Certification and in fact for actual development usage.

In my opinion quizlet is a inappropriate way to learn Splunk as it gives questions based on memory and half baked scenarios which may actually be incorrect. Learning from such source will lead to incomplete and incorrect knowledge which would be disastrous.

So coming to your follow up details, seems like you are interested in multiple aggregations within stats command.

If you are going through stats command you should refer to documentation for the same as well to understand it in depth: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats

You can also refer to Splunk Documentation for all the available/applicable aggregation functions here:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Aggregatefunctions

Best way to learn through these examples is to try them out using Splunk's _internal index data on local machine or ingest Splunk's tutorial data as mentioned in the Fundamental course as well as on Splunk Documentation for Search Tutorial: https://docs.splunk.com/Documentation/Splunk/latest/SearchTutorial/GetthetutorialdataintoSplunk

In your question the example has been created based on same tutorial data where data has been uploaded to main (default) index with default sourcetype (pre-defined) access_combined_wcookie. (explained in Search documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchTutorial/Aboutthetutorialdata)

index=main sourcetype=access_combined_wcookie 
| stats values(useragent) as "Agents used" count as "Times used" by useragent

In the above stats function by useragent defines the key field for aggregation i.e. each row in the result of stats function will have unique value for each useragent present.

The values() stats aggregation function gives unique values of specific field. Which implies you will have two columns useragent and Agents used with same values as both are unique per row. So, your SPL seems to be performing stats (transformation) for a non-realistic scenario.

Coming to my point, if your use case is to get the access count through various user agents, you could have performed the following:

index=main sourcetype=access_combined_wcookie
| stats count as "Times used" by useragent
| rename useragent as "Agents used"