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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...