Splunk Search

How to get Date Range per month?

katrinamara
Path Finder

I need to get the no. of days per month in my data. But in my data there is 2 fields for Date which is Start Date and End Date.
Ex. Start Date: October 15, 2017
End Date: February 10, 2018

I need to get the no. of days per months in above example.
I tried to used the mvrange to get the range between the 2 fields and mvcount to get the no. of days but the result I'm getting total range of the data starting october to february.

Is there a way I can get it?

0 Karma
1 Solution

493669
Super Champion

Try this run anywhere search:

|makeresults|eval Start Date="October 15, 2017", End Date="February 10, 2018"|eval Start Date=strptime('Start Date',"%B %d, %Y"),End Date=strptime('End Date',"%B %d, %Y")| eval days=round(('End Date'-'Start Date')/86400)

strptime('Start Date',"%B %d, %Y") should give you seconds since Jan 1st 1970.
Hope this helps!

View solution in original post

0 Karma

sjbriggs
Path Finder

I have a report that runs every day and is doing calculations based on the number of days that have occurred so far.  After pulling my hair out and using a variety of techniques posted in these forums, I ended up with:

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

It looks like if I use the same logic to span across months, I would simply do

| eventstats dc(date_mday) AS daysInMonth BY date_month

Which when my table spans a couple of months, gives me the count of days for the specific month associated with the _time field and other data for that month.

0 Karma

493669
Super Champion

Try this run anywhere search:

|makeresults|eval Start Date="October 15, 2017", End Date="February 10, 2018"|eval Start Date=strptime('Start Date',"%B %d, %Y"),End Date=strptime('End Date',"%B %d, %Y")| eval days=round(('End Date'-'Start Date')/86400)

strptime('Start Date',"%B %d, %Y") should give you seconds since Jan 1st 1970.
Hope this helps!

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...