Splunk Search

Unique users by application over time periods in a timechart table

nickstone
Path Finder

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?

0 Karma
1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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
Explorer

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

0 Karma

bharanithippa
Explorer

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
0 Karma

somesoni2
Revered Legend

What's the seach you tried?

0 Karma

nickstone
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

Detecting Brute Force Account Takeover Fraud with Splunk

This article is the second in a three-part series exploring advanced fraud detection techniques using Splunk. ...

Buttercup Games: Further Dashboarding Techniques (Part 9)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Buttercup Games: Further Dashboarding Techniques (Part 8)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...