Combining "stats dc(x)" with "stats dc(x) by y" in same search

[bryanfe]

I am having a ton of trouble expressing this query.

Suppose I have 1,000 distinct people, and 25 cities. Over a time period, each person might visit [0...n] cities.

I want a report which shows, for each city, what percentage of my unique people visited that city? Note that in this report, the percentages won't add up to 100%, since any person might visit more than one city in the time period.

This search gives me the # of unique people and returns one row, value of "1000":

eventtype=visit |stats dc(person) | rename dc(person) as "# Unique People"

This search shows # of unique people who visited each city and returns 25 rows:

eventtype=visit |stats dc(person) by city | rename dc(person) as "# Unique People" | sort -"# Unique People"

What I want is, in Search #2, instead of showing a # of people, I want to show the # of people divided by the total returned in Search #1 (which would be a percentage of the total unique people).

Can anyone help? I've played with append, appendcols, join, appendpipe.. I'm lost.

[martin_mueller]

totalPeople ends up empty because the first stats drops the person field, you need to swap things around a bit.

... | eventstats dc(person) as totalPeople | stats dc(person) as uniquePeople values(totalPeople) as totalPeople by city | eval percentage = 100*(uniquePeople/totalPeople)

[sideview]

UPDATED:

eventtype=visit | stats count by city person | eventstats dc(person) as totalPeople | stats dc(person) as visitors last(totalPeople) as totalPeople by city | eval percentage=100*(visitors/totalPeople) | table city percentage

[sideview]

Oops. I see the problem. I'll update.

[bryanfe]

Thank you. I tried this, but the "totalPeople" column ends up empty. Not sure why.