How to get count of events for each field?

[Yaichael]

In the following query, I'm trying to display the count of events for each field (bar) from a single field (foo).

foo=* bar=* | stats values(bar) as Clients count as Amount by foo | eventstats sum(Amount) as AmountByClient by foo | stats sum(Amount) AS "Total Count", list(foo) as Clients list(AmountByClient) as "Client Count" by bar

On each foo field, I can get all the bars and stat the total amount of events for that group of bars, but I will like to get the count for each bar individually.

Thanks for the help!

[somesoni2 · Accepted solution]

How a bout this

foo=* bar=* | stats count as Amount by foo bar |  eventstats sum(Amount) as AmountByClient by foo | stats sum(Amount) AS "Total Count", list(foo) as Clients list(AmountByClient) as "Client Count" by bar

[Yaichael]

Thanks for the reply, someoni2.

Using your answer as reference, I reconfigured my query as follows:

foo=* bar=* | stats count as Amount by bar foo | eventstats sum(Amount) as AmountByClient by foo | stats sum(Amount) AS "Total Count" list(bar) as Clients list(AmountByClient) as "Client Count" by foo

but each "Client Count" is being reflected as "Total Count". I played around with it, but I'm still getting unwanted results.

[somesoni2]

In my query, I'm using bar in the last stats, whereas you're using foo. Try with bar. Or if you want to group by foo in the last, then try like this (swapping bar with foo)

foo=* bar=* | stats count as Amount by foo bar |  eventstats sum(Amount) as AmountByClient by bar | stats sum(Amount) AS "Total Count", list(bar) as Clients list(AmountByClient) as "Client Count" by foo

[Yaichael]

Found the error! I was missing foo in the eventstats command.

Thanks for the help!

foo=* bar=* | stats count as Amount by bar foo | eventstats sum(Amount) as AmountByClient by bar foo | stats sum(Amount) AS "Total Count" list(bar) as Clients list(AmountByClient) as "Client Count" by foo

[somesoni2]

Well, the aggregation done on eventstats and the stats just before that is same, so they're kind of redundant. I would just use like this

foo=* bar=* | stats count as AmountByClient  by bar foo | stats sum(AmountByClient) AS "Total Count" list(bar) as Clients list(AmountByClient) as "Client Count" by foo

[Yaichael]

Yeah, you're right.

Thanks for the suggestion!