I'm looking to calculate the number of hours per month (minus Saturday and Sunday). Is there straightfoward way to do this?
Thanks
| 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.
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")
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.
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.
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.
@jcioffari did you find a solution for this?
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"
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)