Splunk Search

Difference between two string date fields

perryd
Engager

Hi, i searched but i don't found any solution. I wont the difference between two fields that are date in string format. My field are:

  • StartDate: String->dd/mm/yyyy;
  • EndDate: String-> dd/mm/yyyy; Its possible obtain the difference in days? For example: in first row, i've 01/01/2017 and 01/07/2017. My result must be 183.

I don't understand how to convert my string date in values day.

Tags (2)
0 Karma
1 Solution

adonio
Ultra Champion

hello there,

run this search anywhere and apply the logic to address your challenge.
here i used eval strptime and leverage the epoch numeric value to calculate gap

| makeresults count=1
| eval start_date = "01/01/2017"
| eval end_date="01/07/2017"
| rename COMMENT as "above creates fake data, below is your solution" 
| eval start_epoch = strptime(start_date, "%d/%m/%Y")
| eval end_epoch = strptime(end_date, "%d/%m/%Y")
| eval gap_in_seconds = end_epoch - start_epoch
| eval gap_in_days = round(gap_in_seconds / 86400)

further reading:
https://docs.splunk.com/Documentation/Splunk/7.2.5/SearchReference/Commontimeformatvariables

hope it helps

View solution in original post

perryd
Engager

Hi, don't work. Specifically, when i use the function strptime(StardDate) or strptime(EndDate) i lost all data in these fields. Splunk don't convert my string in strptime, so, When i try to do difference between startdate and enddate i don't have any output.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Can you please share some more information about your events and fields (StardDate, EndDate & others)?

0 Karma

perryd
Engager

I've one file CSV. In this file i have some fields, two of this are date. Splunk read this date like a strings. Now, i have need to calcolate the difference between this two dates, row-by-row. My final output must be a new column with all difference of this dates in days. i wrote 183 days, but was an example. I want all difference, for any row and any dates, in day, only this.

I try to write this:
...
| eval start_epoch = strptime(StardDate, "%d/%m/%Y")
| eval end_epoch = strptime(EndDate, "%d/%m/%Y")
| eval gap_in_seconds = end_epoch - start_epoch
| eval gap_in_days = round(gap_in_seconds / 86400)
and my output is null. Splunk don't convert my string date in strptime, if i try to write only " eval start_epoch = strptime(StardDate, "%d/%m/%Y")" i don't see anythings, i don't have output.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@perryd

I have tried with below CSV content. It's working. Is that any space OR double quotes in your CSV content?

StardDate,EndDate
01/01/2017,01/07/2017
01/01/2017,01/08/2017
01/01/2017,01/09/2017
01/01/2017,01/10/2017
0 Karma

perryd
Engager

Can i see how work on your splunk? It make the difference between these dates? (in day). Because my CVS is not controllable from me, i can't modify it. I can try to upload a my file with some dates and do test for it.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

You can share your CSV data here OR data from search, using | inputlookup. So we can look at that.

0 Karma

perryd
Engager

I've data protect from non disclosure agreement. Now i try with a my CSV test and i see if run. If i continue to have problem i ask here, thank you.

0 Karma

beingkaran
New Member

HI Perry are your dates in 01/01/2017 format or 01-01-2017 format coz that will change the time format we are giving in order to convert the epoch strings.

SO the strptime
eval start_epoch = strptime(StardDate, "%d/%m/%Y")
will become
eval start_epoch = strptime(StardDate, "%d-%m-%Y")

I was making the same mistake

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Glad to help you.

Happy Splunking

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@perryd

The difference between these two dates is 181. And if you want to add last day also in your count then add 1 in your search. Is that any specific calculation for getting 183?

| makeresults 
| eval start_date="01/01/2017",end_date="01/07/2017" 
| eval dates=mvcount(mvrange(strptime(start_date,"%d/%m/%Y"),strptime(end_date,"%d/%m/%Y"),86400))

| eval dates=dates+1

0 Karma

adonio
Ultra Champion

hello there,

run this search anywhere and apply the logic to address your challenge.
here i used eval strptime and leverage the epoch numeric value to calculate gap

| makeresults count=1
| eval start_date = "01/01/2017"
| eval end_date="01/07/2017"
| rename COMMENT as "above creates fake data, below is your solution" 
| eval start_epoch = strptime(start_date, "%d/%m/%Y")
| eval end_epoch = strptime(end_date, "%d/%m/%Y")
| eval gap_in_seconds = end_epoch - start_epoch
| eval gap_in_days = round(gap_in_seconds / 86400)

further reading:
https://docs.splunk.com/Documentation/Splunk/7.2.5/SearchReference/Commontimeformatvariables

hope it helps

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...