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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...