How to edit my search to sum up the count of hosts per group for each account?

[ks2211]

Hi All,

I'm pretty new to Splunk so still learning my way around everything.

Running a search like this results in the following table below:

index=my table | stats count(Host) by Account, Group | table Account, Group, Host

Account | Group | Host
A | 1 | abc.com
A | 1 | def.com
A | 1 | ghi.com
A | 2 | abcd.com
A | 2 | abcde.com
B | 1 | foo.com
B | 1 | bar.com
B | 3 | foobar.com

I want to have a table that will display the results from above like this (basically it looks by Account then by Group and then sums up the count of Host's under this one account and group

Account | Group | Host (count) <--Note: "(count)" after "Host" is optional
A | 1 |3
A | 2 |2
B | 1 |2
B | 3 |1

Any suggestions on how to construct this search?

Thanks

[AaronAltonKinro · Accepted solution]

I believe that the | table bit is redundant. the output of a stats command is in tabular format already. You'd only need to use it if you actually want to reorder the columns to match the table you mentioned. So either of these should work:

 index=my table | stats count(Host) by Account, Group | table Account, Group, count(Host)

 index=my table | stats count(Host) by Account, Group

The only difference between your search command and mine is that after the stats count pipe, I'm referring to the aggregated field as count(Host).

I'm new too, so this may not be the best way to do it, but it seems to do what you're looking for.

[ks2211]

Well I want to get an actual summed count of the Hosts by account and group. Doing the 2nd query in your comment, it just gives me a similar output as the first table in my original post

Edit: Nevermind, I am an idiot. I had some other fields that were unique to the Host in my query and this caused the screwniness.

Thanks for the help!

[AaronAltonKinro]

No probs. I read your comment before the edit and I was pretty sure I was going crazy 😉

Glad it worked out for you.

[DeronJensen]

I think you can just use the "|" to another stats:
index=my table | stats count(Host) by Account, Group | rename count(Host) AS Hosts | stats count(Group) by Account, Hosts | ...