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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...