Splunk Search

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

sharonmok
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?

Thanks!

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...