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 date_mday to date_month to go from daily uniques to monthly uniques.
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.
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.
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.