topic stats value count by in two different result in Splunk Search

stats value count by in two different result

elbywong — Thu, 12 Jul 2018 03:12:41 GMT

I am using two spl which the result are different, but I think the spl is the same. Can anyone help?

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

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

The result is attached.

Re: stats value count by in two different result

kamlesh_vaghela — Thu, 12 Jul 2018 08:03:04 GMT

@elbywong

In your first search you have used stats command to count events useragent and to get useragent as Agents Used. This will gives useragent wise total event count. Here we will get the proper count of events.

As per my suggestion, if you remove the values(useragent) as "Agents Used" and rename the useragent field then it will give the same result with proper approach.

like

index=main sourcetype=access_combined_wcookie
|stats count as "Times Used" by useragent 
| rename useragent as "Agents Used"
|table "Agents Used", "Times Used"

In your second search, you have written values(useragent) as "Agents Used", if you execute below search then it will give you multivalued Agents Used field.

Try:

index=main sourcetype=access_combined_wcookie
|stats values(useragent) as "Agents Used"

Here we are losing total events for particular Agents and gives you a single field with multiple agents. And, as we have used stats
command, we will not able to take the events or count of events which can be used in next stats command which is representing count of events by useragent.

Next Stats:

|stats count("Agents Used") as "Times Used" by "Agents Used"
|table "Agents Used", "Times Used"

count("Agents Used") as "Times Used" by "Agents Used"
which gives you a total number of "Agents Used" in "Times Used". And it is an invalid count of agents.

If you want a count of events per Agent then I suggest below search.

index=main sourcetype=access_combined_wcookie
|stats count as "Times Used" by useragent 
| rename useragent as "Agents Used"
|table "Agents Used", "Times Used"

Thanks

Re: stats value count by in two different result

danielwysockiar — Thu, 12 Jul 2018 12:53:59 GMT

The results are different, because those SPL count something completly different.
Look at the first SPL:

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

count counts how many times a unique useragent appeared in searched events and I suppose that is what you want to achieve

...but in the second SPL:

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

count counts how many useragents were listed by previous command, because it is piped....

Look at the "Statistics" tab on the screen you attached: It says 2 and the result of SPL for each line is also 2.

Do a simple test and compare:

index=main sourcetype=access_combined_wcookie
|stats values(useragent) as x count by useragent

and

 index=main sourcetype=access_combined_wcookie
|stats values(useragent) as x
| stats count(x) by x

Regards