Splunk Search

Number of day per month

guilhem
Contributor

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

0 Karma
1 Solution

guilhem
Contributor

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

View solution in original post

0 Karma

sjbriggs
Path Finder

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.

0 Karma

cstump_splunk
Splunk Employee
Splunk Employee
| 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

sanderdenheijer
Explorer

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

0 Karma

vganjare
Builder

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!!

jonuwz
Influencer

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

guilhem
Contributor

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

0 Karma

guilhem
Contributor

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

View solution in original post

0 Karma

Ayn
Legend

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.

guilhem
Contributor

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.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!