How to edit my timechart search to show distinct users per day and new users across all time per product?

[ahsanshah]

I am new to splunk. I am trying to create a timechart based report which shows me Distinct "Users" per day as well as "new" users who have accessed the system for the first time.

I can get the distinct users per day via the following:

index=bi "User * Logged" | eval Product=if(like(host,"agen%"),"Agency","Rate") | timechart span=1d dc(OBIEE_USER_NAME) by Product

How can I modify this to also show a column for "new/first time" users which spans across the time (not just for that day).

I tried using eventstats and some other options, but unable to figure it out. I would assume we can do this by enhancing the timechart.

[datasearchninja · Accepted solution]

Use streamstats to increment a counter when a user logs in, and evaluate a field with that value is 1 (The first time they log in). You can then sum that field in the timechart.

index=bi "User * Logged" | eval Product=if(like(host,"agen%"),"Agency","Rate") | streamstats count as logincount global=false by OBIEE_USER_NAME | eval newuserevent=case(logincount=="1", 1)  | timechart span=1d dc(OBIEE_USER_NAME) sum(newuserevent) by Product

[ahsanshah]

Thanks for the suggestion..I tried that and it does not seem to get the results I am expecting. I see slightly fewer totals in the "Sum" fields but the users seem to include those that have logged in before (on a prior day). I would assume if the same sets of users log in daily, they will never be counted in the subsequent SUMs based on the logic for newuservent. Its also possible I am missing something in my search that I need to include. The UNIQUE works well..but the NEW cumulative count does not seem to work even with streamstats.

[datasearchninja]

The graph is new users and distinct_users per product. IS that what you want? Or do you want new users for any product?

[ahsanshah]

I do need the New and Dictinct users PER product...that is correct. So for example, I modified the query to limit it to a specific use "cgm" who I know logs into the system every day

index=bi "User cgm Logged" | eval Product=if(like(host,"agen%"),"Agency","Rate") | streamstats count as logincount global=false by OBIEE_USER_NAME | eval newuserevent=case(logincount=="1", 1) | timechart span=1d dc(OBIEE_USER_NAME) sum(newuserevent) by Product

What I see here is that the SUM(NEWUSERVENTS) show as 1 only for the most RECENT DAY...I would think it would show it for the "First" day. Is Splunk defaulting to the most recent as "NEW USER" for some reason.

Also..I only see the SUM(NewUserEvent) value for one of the Products...Rate..not the other (Agency). In certain cases a userid can be the same across the two products but most often these are distinct user sets.

Not sure if that makes sense.

[ahsanshah]

As for my point ".I only see the SUM(NewUserEvent) value for one of the Products"...you can ignore that...I was using a userid that is only specific to Rate product.

The other questions are still valid. Thank you!

[ahsanshah]

Think I may have gotten it...added "reverse" prior to streamstat to ensure the order is correctly reflecting what is the "First" login which denotes a NEW user. Thanks for your help.