Splunk Search
Highlighted

Get the Time difference from the logs to display in "%d/%m/%Y %H:%M:%S" format

Hi All,
I have a csv file, as following:

`

FINISHDATE STARTDATE
30/09/09 19:51:16 30/09/09 19:49:03

01/10/09 16:58:44 01/10/09 16:55:48

02/10/09 16:54:08 02/10/09 16:53:17

`

I want to find the difference between the two timestamps, and display it in third column in "%d/%m/%Y %H:%M:%S" format.
for e.g. the difference for the first row should be like 30/09/09 00:2:13
I have tried the following query, but it didnt helped.

convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as  mktime(FINISH_DATE) as finish_date_epoch | eval diff_date_epoch=start_date_epoch -finish_date_epoch|eval DIFF_DATE=strftime(diff_date_epoch,"%H:%M:%S")

What I am doing wrong?
Please Help...!!!

0 Karma
Highlighted

Re: Get the Time difference from the logs to display in "%d/%m/%Y %H:%M:%S" format

Splunk Employee
Splunk Employee

Is this actually missing ? You should have startdateepoch where I've put MISSING:

convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as  **MISSING**
mktime(FINISH_DATE) as finish_date_epoch 
| eval diff_date_epoch=start_date_epoch -finish_date_epoch
| eval DIFF_DATE=strftime(diff_date_epoch,"%H:%M:%S")

I'm assuming thats just a typo in your post...

Maybe you can break apart your search and see which part fails, e.g.

convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as  start_date_epoch
mktime(FINISH_DATE) as finish_date_epoch 
| table start_date_epoch, finish_date,epoch

Then progressively add the evals. Then you might have a better idea of where its breaking...

0 Karma
Highlighted

Re: Get the Time difference from the logs to display in "%d/%m/%Y %H:%M:%S" format

Legend

Try this:

convert timeformat="%d/%m/%y %H:%M:%S" mktime(START_DATE) as start_date_epoch  
mktime(FINISH_DATE) as finish_date_epoch 
| eval DIFF_DATE=tostring(start_date_epoch -finish_date_epoch,"duration")

Also note that the duration will not contain a month/day/year, because that doesn't make any sense for a difference. The difference is not a date.

0 Karma