Splunk Search

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

bryanfe
New Member

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.

Tags (1)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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

0 Karma

bryanfe
New Member

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...