Unique users by application over time periods in a timechart table

[nickstone]

As a example, I have a search that calculates "Unique Users per Application" and this can be constrained to a particular timeframe with either a timerange picker or earliest/latest fields.

| stats dc(UserId) AS UserLogonCount BY ApplicationId | table ApplicationId UserLogonCount

What I would like to do is extend this to essentially be a timechart but in a table format that shows 1 day, 1 week, 1 month values etc.

The only way I can think of is to append multiple searches that use earliest/latest to define the different time periods, however this seen rather inefficient to me.

What other options do I have?

[somesoni2 · Accepted solution]

Give this a try

your base search with maximum timerange you have e.g. last 1 month | fields _time UserId ApplicationId
| eval TimePeriod=if(_time>=relative_time(now(),"-1d"),"Last_1_Day","")
| eval TimePeriod=if(_time>=relative_time(now(),"-1w"),TimePeriod." Last_1_Week",TimePeriod)
| eval TimePeriod=if(_time>=relative_time(now(),"-1mon"),TimePeriod." Last_1_Month",TimePeriod)
| makemv TimePeriod
| chart dc(UserId) over ApplicationId by TimePeriod

[bharanithippa]

Hi Somesoni2 - thanks for the solution, I am getting same values for all day, week, month with search of 1month, not sure what is missing, tried few options no luck, please help. Thanks, BK

[bharanithippa]

one from solution...

your base search with maximum timerange you have e.g. last 1 month | fields _time UserId ApplicationId
| eval TimePeriod=if(_time>=relative_time(now(),"-1d"),"Last_1_Day","")
| eval TimePeriod=if(_time>=relative_time(now(),"-1w"),TimePeriod." Last_1_Week",TimePeriod)
| eval TimePeriod=if(_time>=relative_time(now(),"-1mon"),TimePeriod." Last_1_Month",TimePeriod)
| makemv TimePeriod
| chart dc(UserId) over ApplicationId by TimePeriod

[somesoni2]

What's the seach you tried?

[nickstone]

Bingo! I had actually seen another post like this one, but I didnt know about relative_time where the other example used fixed dates.

Next job is to count the UserId but replace the value in the table with UserName instead

Thanks!