Splunk Search
Highlighted

How to combine multiple stats distinct counts into one search?

Communicator

I have these 3 stats count searches, but I would like to combine them as one and create a table.

 host ="web*" source="Access" "men" | stats dc(id) by city
 host ="web*" source="Access" "women" | stats dc(id) by city
 host ="web*" source="Access" | stats dc(id) by city

My objective is to have the number for men, women, and total (men+women)

thanks,

0 Karma
Highlighted

Re: How to combine multiple stats distinct counts into one search?

Esteemed Legend

Like this:

host ="web*" source="Access" | eval type = case(searchmatch("men"), "All,Men", searchmatch("women"), "All,Women", true(), "All,Neither") | makemv delim="," type | chart dc(id) over city BY type

View solution in original post

Highlighted

Re: How to combine multiple stats distinct counts into one search?

Communicator

thank you so very much, quick question, do you mind if you can explain to me what's the purpose of "Neither"? also the total count for (men+women) by each city rather than total (men/women total. sorry I think my question was wrong earlier.

0 Karma
Highlighted

Re: How to combine multiple stats distinct counts into one search?

Esteemed Legend

OK, I tweaked my answer a bit. I realized that since we are using dc instead of count, that using addtotals is invalid. So I switched to creating a value for type called All that will allow us to dc all events. As far as "Neither", there may be some events that have neither "men" nor "women" so this captures those. It is quite possible that "Neither" will always be zero but it is good to check, just in case. Also, I had the 2 fields' ( city and type ) positions swapped so the table was built backwards. I fixed it and it should look like what you are desiring now.

0 Karma
Highlighted

Re: How to combine multiple stats distinct counts into one search?

Communicator

thank you so much again, perfect outcome and really good lesson.

0 Karma