Splunk Search

How to only include business days (excl. Sat & Sun) in my following query?

Path Finder

Hi everyone! Recently, I got help on a query and it did what it was supposed to perfectly. Basically, I wanted to see:

1) How many days a user has logged on (doesn't matter how many times in a day, just one or more counts)
2) Average this over the number of days total that they've been in the system (today minus the days since the user first logged in ever)

Problem is, I forgot I only wanted to look at BUSINESS DAYS. So I wanted to see what the number of total business days the client has been in the system, minus Sat and Sun. I found a bunch of answers on the forum about how to do this, but it seems like every time I try to add it to my existing query, it doesn't do anything and results still includes weekends.

Here is my existing query:

| index search
| eventstats min(_time) as First_Login by client_user
| bin _time span=1d
| stats min(First_Login) as First_Login by _time client_user
| streamstats count as Active_Days by v1_client
| reverse
| dedup client_user
| eval First_Login=strptime(strftime(First_Login,"%Y/%m/%d"),"%Y/%m/%d")
| eval Duration_Since=round((_time-First_Login)/86400)
| eval Average_Activity = Active_Days/Duration_Since
| fillnull value=0 Average_Activity
| fieldformat First_Login=strftime(First_Login,"%Y/%m/%d")

Here's the query I found for excluding weekends:

| eval date_wday = tonumber(strftime(_time, "%w"))
| where date_wday>=1 AND date_wday<=5

How would I go about merging these 2?


Tags (1)
0 Karma


Try this ...

your search that gets all the logins 
| bin _time span=1d 

| rename COMMENT as "Create one record for each client_user for each day they logged in"
| stats count by client_user _time

| rename COMMENT as "Create one record for each client_user"
| stats min(_time) as mintime max(_time) as maxtime  count as Active_Days by client_user
| eval today=relative_time(now(),"@d")

| rename COMMENT as "calculate number of days"
| eval allDays  = ( today - mintime )/86400
| eval allWeeks = floor( allDays / 7 )
| eval startDay = tonumber(strftime(mintime,"%w"))
| eval endDay   = tonumber(strftime(today,"%w"))
| eval allWeekDays = 5 * AllWeeks + endDay - startDay + if(startDay>endDay,6,1)
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...