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
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
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.
| 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
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
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!!
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
OK, that's maybe the best automated solution, I completely forgot about arguments in macro, thanks for finding out this one!
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
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.
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.