Splunk Search

Difference between two string date fields

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

SplunkTrust
SplunkTrust

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

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

SplunkTrust
SplunkTrust

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

0 Karma

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

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

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

SplunkTrust
SplunkTrust

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

0 Karma

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

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

SplunkTrust
SplunkTrust

Glad to help you.

Happy Splunking

0 Karma

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

SplunkTrust
SplunkTrust

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!