Solved: Re: How do I get Unique users per day and per mont... - Page 2

pzhou07920 · ‎08-03-2016

Hi,

I have a field called "UserID" and a DateActive field. I'm looking to make a bar chart where each bar has a value equal to the average # of unique users per day in a month divided by the total # of active users of that month, for every month in the year (Lets call this value Stickiness). For example, if the month of Jan averages 3,000 unique active users per day and has 10,000 unique active users in the entire month of Jan, then I want the Stickiness for Jan to be 3,000/10,000 or .3.

I'm able to get both unique active users per day and unique active users per month in separate queries but am having trouble doing it together to do the division since I am using

timechart span=1(day or month) dc(UserID) and using eval _time=DateModified

So how can I obtain both the unique daily active user and unique monthly active user counts in a single query so that I can perform division on them? I also want to display the data using a bar graph that has one bar for the Stickiness of each month in a 12 month window? I'd normally use timechart but i'm not sure how for a more complex situation like this.

Might have worded it poorly, let me know so I can clarify.

somesoni2 · ‎08-03-2016

Give this a try

your base search | bucket span=1d _time | stats dc(UserID) as dailyUniq count(UserID) as DailyActive by _time | timechart span=1mon avg(dailyUniq) as DailyAvgUniqUsers sum(DailyActive) as TotalActive | eval Month=strftime(_time,"%B") | eval Stickiness=round(DailyAvgUniqUsers/TotalActive,2) | table Month Stickiness

Update#1
This should givev stickiness as ratio of Daily Average Uniq Users and Unique users for the month. Will be little slower than previous one.

your base search | bucket span=1d _time | eventstats dc(UserID) as dailyUniq by _time
| timechart span=1mon avg(dailyUniq) as DailyAvgUniqUsers dc(UserID) as MonthlyUniq | eval Month=strftime(_time,"%B") | eval Stickiness=round(DailyAvgUniqUsers/MonthlyUniq,2) | table Month Stickiness

View solution in original post

pzhou07920 · ‎08-05-2016

Seems like it is still in alphabetical order

pzhou07920 · ‎08-05-2016

didn't seem to work, order is alphabetical still

pzhou07920 · ‎08-04-2016

hmm yup, the average is now correct when I run the query you just gave me. Does this mean I'm excluding events by writing the _time field incorrectly?

We have to override the _time field because we have a database that stores timestamps of certain events, going back to December of 2015 when it was built. We just started using Splunk and indexed the entire database (from a flat file due to limitations) so the _time field for all events is the same. Going on the new database entries will be indexed each night but we want these charts to go on and only show the last 12 months worth of data.

I used a similar _time field override when doing the simple timecharts on the separate unique monthly and unique daily counts and those were fine.

thanks a ton for your help by the way, its much appreciated

How do I get Unique users per day and per month in one query and then divide them

Detecting Brute Force Account Takeover Fraud with Splunk

Buttercup Games: Further Dashboarding Techniques (Part 9)

Buttercup Games: Further Dashboarding Techniques (Part 8)

Are you a member of the Splunk Community?

How do I get Unique users per day and per month in one query and then divide them

Detecting Brute Force Account Takeover Fraud with Splunk

Buttercup Games: Further Dashboarding Techniques (Part 9)

Buttercup Games: Further Dashboarding Techniques (Part 8)