Splunk Search

Using searches to get a percentage or, Daily uniques as a percentage of monthly uniques.

Path Finder

I can easily come up with searches to find the number of daily unique users and monthly unique users. But, someone wants to see it as a percentage, for example on Monday X% of monthly visitors logged in. So, I'm essentially looking for a way to have the count of search A be the numerator and the count of search B be the denominator.

Here's the search base I use:

index=main hostname=*PROD extra.user_id=* NOT extra.csv.cohort_id=* NOT extra.user_id=no_user  NOT (extra.template=VerifyEmailAddress OR extra.template=LostPassword)| dedup extra.user_id date_mday |timechart count

I just change the datemday to datemonth to go from daily uniques to monthly uniques.

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Something like this:

index=main hostname=*PROD extra.user_id=* NOT extra.csv.cohort_id=* NOT extra.user_id=no_user  NOT (extra.template=VerifyEmailAddress OR extra.template=LostPassword)
| rename extra.user_id as extra_user_id
| eventstats dc(extra_user_id) as monthly_distinct_users by date_month date_year
| timechart span=1d dc(extra_user_id) as daily_distinct_users avg(monthly_distinct_users) as monthly_distinct_users
| eval percentage = round(daily_distinct_users * 100 / monthly_distinct_users, 2) . "%"

First I rename the field, because having a dot in a field name is asking for trouble - for example, eval will interpret that as the concatenation of two fields extra and user_id.
Second, calculate the monthly distinct count of users.
Third, calculate the daily distinct count of users and carry over the monthly dc.
Last, calculate percentage for each day.

View solution in original post

SplunkTrust
SplunkTrust

Something like this:

index=main hostname=*PROD extra.user_id=* NOT extra.csv.cohort_id=* NOT extra.user_id=no_user  NOT (extra.template=VerifyEmailAddress OR extra.template=LostPassword)
| rename extra.user_id as extra_user_id
| eventstats dc(extra_user_id) as monthly_distinct_users by date_month date_year
| timechart span=1d dc(extra_user_id) as daily_distinct_users avg(monthly_distinct_users) as monthly_distinct_users
| eval percentage = round(daily_distinct_users * 100 / monthly_distinct_users, 2) . "%"

First I rename the field, because having a dot in a field name is asking for trouble - for example, eval will interpret that as the concatenation of two fields extra and user_id.
Second, calculate the monthly distinct count of users.
Third, calculate the daily distinct count of users and carry over the monthly dc.
Last, calculate percentage for each day.

View solution in original post

Path Finder

That seems to do it enough that I think I can take it from here. I may change it to look back 30 days rather than hard month boundaries. Awesome though, and thanks for the tip on the dot in a field name. We log in JSON and the dot carries over from that.

0 Karma