topic Re: Number of day per month in Splunk Search

Number of day per month

guilhem — Wed, 13 Feb 2013 09:01:19 GMT

Hello!

A very simple question but I can't find the answer anywhere: how to get the number of day (28, 30 or 31) for any month?

I have for now a cumbersome (and false for bissextile years) way to do this (lookup table for each month, setting nb_day for each month).

I couldn't find any topic or question about this.

Thanks!

Guilhem

Re: Number of day per month

Ayn — Wed, 13 Feb 2013 09:21:28 GMT

There's no inbuilt command that provides this. A lookup, like the one you're already using, would probably be the cleanest way to do it.

Re: Number of day per month

guilhem — Wed, 13 Feb 2013 09:26:39 GMT

I am surprised no one ask for this before.

Thanks for the answer. I will set up the lookup for bissextile year and post it when it's done.

Re: Number of day per month

guilhem — Wed, 13 Feb 2013 09:44:17 GMT

As suggested, there is no built-in way to do this, so a lookup table seems the easiest way to do it. Here is the file I use for the lookup:

"date_year","date_month","date_numericmonth","day_number"
2017,january,01,31
2017,february,02,28
2017,march,03,31
2017,april,04,30
2017,may,05,31
2017,june,06,30
2017,july,07,31
2017,august,08,31
2017,september,09,30
2017,october,10,31
2017,november,11,30
2017,december,12,31
2016,january,01,31
2016,february,02,29
2016,march,03,31
2016,april,04,30
2016,may,05,31
2016,june,06,30
2016,july,07,31
2016,august,08,31
2016,september,09,30
2016,october,10,31
2016,november,11,30
2016,december,12,31
2015,january,01,31
2015,february,02,28
2015,march,03,31
2015,april,04,30
2015,may,05,31
2015,june,06,30
2015,july,07,31
2015,august,08,31
2015,september,09,30
2015,october,10,31
2015,november,11,30
2015,december,12,31
2014,january,01,31
2014,february,02,28
2014,march,03,31
2014,april,04,30
2014,may,05,31
2014,june,06,30
2014,july,07,31
2014,august,08,31
2014,september,09,30
2014,october,10,31
2014,november,11,30
2014,december,12,31
2013,january,01,31
2013,february,02,28
2013,march,03,31
2013,april,04,30
2013,may,05,31
2013,june,06,30
2013,july,07,31
2013,august,08,31
2013,september,09,30
2013,october,10,31
2013,november,11,30
2013,december,12,31

Re: Number of day per month

jonuwz — Wed, 13 Feb 2013 17:00:15 GMT

You can do this programattically with a macro.

[days_in_month(1)]
args = month
definition = [ | gentimes [ | gentimes start=-1| eval start="$month$" | rex field=start (?<month>.*?)/(?<year>.*) | eval start=month."/01/".year | return start ] [ | gentimes start=-1| eval start="$month$" | rex field=start (?<month>.*?)/(?<year>.*) | eval end=strftime(relative_time(strptime(month."/01/".year, "%m/%d/%y"),"+1mon@mon"),"%m/%d/%y") | return end ] increment=1d | stats count as search | return $search ]
iseval = 0

Example usage :

 * | head 1 | eval days=`days_in_month(month="02/12")` | table days

Output :

days  
------  
29

Re: Number of day per month

guilhem — Thu, 14 Feb 2013 17:08:51 GMT

OK, that's maybe the best automated solution, I completely forgot about arguments in macro, thanks for finding out this one!

Re: Number of day per month

vganjare — Mon, 28 Sep 2020 17:22:47 GMT

Total number of days in current month can be calculated using following eval statement:

eval lastDay=strftime(relative_time(relative_time(now(),"+1mon@mon"),"-1d@d"),"%d")

Change "now()" to any other date (epoch time) to calculate the number of days in month specific to custom date.

Thanks!!

Re: Number of day per month

sanderdenheijer — Mon, 28 Sep 2020 19:04:24 GMT

You can get a table of all months and the number of days they have as follows:

eval DayNr=strftime(_time,"%d") | timechart span=1mon max(DayNr) as NrOfDays | eval Month=strftime(_time,"%B %Y") | table Month NrOfDays

Re: Number of day per month

cstump_splunk — Wed, 13 Feb 2019 02:44:00 GMT

| eval today=now(), 
  nextMonth=relative_time(today,"+1Mon@Mon"), 
  minusOneDay=relative_time(nextMonth,"-1d@d"), 
  daysInMonth=strftime(minusOneDay,"%e")

daysInMonth will have the value you seek

Re: Number of day per month

sjbriggs — Thu, 04 Nov 2021 18:12:16 GMT

I have tried many of the solutions offered in several posts and ended up with this:

| eventstats dc(date_mday) AS daysInMonth

Unless i'm mistaken, the date_* fields are automatically generated in splunk (at least all the data i've seen has them) and this was the easiest and most reliable method I found.