How to calculate the number of hours per month subtracting weekends?

[jcioffari]

I'm looking to calculate the number of hours per month (minus Saturday and Sunday). Is there straightfoward way to do this?

Thanks

[to4kawa]

| gentimes start=01/01/2019 end=01/01/2020 increment=1d 
| eval week=substr(starthuman,1,3), month=substr(starthuman,5,3) 
| stats count(eval(in(week,"Sat","Sun"))) as weekdays_count 
,count(eval(in(week,"Mon","Tue","Wed","Thu","Fri"))) as onduty_count 
,count as month_count by month

gentime has month and week.
Simply, count them.

[jcioffari]

Thanks. This is what I ended up doing awhile ago:

|gentimes start=-365 end=0 increment=1d
|eval endtime=case(strftime(now(),"%A")="Saturday",endtime - (1*86400),strftime(now(),"%A")="Sunday",endtime - (2*86400),strftime(now(),"%A")="Monday",endtime - (3*86400),strftime(now(),"%A")="Tuesday",endtime - (4*86400),strftime(now(),"%A")="Wednesday",endtime - (5*86400),strftime(now(),"%A")="Thursday",endtime - (6*86400),strftime(now(),"%A")="Friday",endtime - (7*86400))
|eval Date=strftime(endtime,"%m-%d-%Y")
|eval day_of_week = strftime(endtime,"%A")
|where NOT (day_of_week="Saturday" OR day_of_week="Sunday")

[nickhills]

I know this is an old question, but its a good example of how you can use splunk to generate values based on timedata from the past or future.

[nickhills]

This was a cool little question!

Try this:

 |gentimes start="01/01/2018" end="12/31/2019" increment=d
    |eval numMonth=strftime(starttime, "%m"), month=strftime(starttime, "%b"), day=strftime(starttime, "%a"), year=strftime(starttime, "%Y")
    |search day!=Sat day!=Sun
    |stats count as weekdays by year month numMonth
    |eval hours=weekdays*24
    |sort + year numMonth
    |fields - numMonth

Ideally i gues you would want to write that to a lookup file with |outputlookup and maybe shedule it to run with a relative time rather than specified values.

[jcioffari]

Sorry, let me provide more detail. I need splunk to generate the number of hours by calendar month excluding weekends and then use that value in a calculation. Once I have the ability to create the values I then need to use the value in a calcuation using EVAL and CASE. So imagine a lookup file that would have the following info :

Month-YYYY | Hours
Jan-2018 | 525
Feb-2018 | 463
Mar-2018 | 535
...

Except I don't want to use a lookup file as solution given the manual work involved.

[rajeshjlnt]

@jcioffari did you find a solution for this?

[niyaz006]

Calculate the day of week and then remove weekends (sat/sun) from your query using search/where

| eval day_of_week =strftime(_time,"%a")
| search day_of_week!="Sat" AND day_of_week!="Sun"

[jcioffari]

Sorry, perhaps I didn't explain well enough . I need splunk to generate the number of hours per calendar month excluding weekends so I can then use the values in an EVAL and CASE statement. So imagine a lookup file as follows:

MM-YYYY | Hours
Jan-2018 535
Feb-2018 425
Mar-2018 545

(Except I don't want to use a lookupfile given the manual work to create and maintain it)