Splunk Search

how to calculate the difference between two dates in transaction

Mubarish
Path Finder

I have a set of logs over which the transaction command is applied and some fields are extracted .Below is the sample result.

SessionID

Filename

StartDateTime

EndDateTime

Tranmission id

FileSize

status

814xxx

6/6/2014 4:15:12 AM

6/6/2014 4:15:12 AM

0

Completed

Setxxxx.csv

6/6/2014 4:15:12 AM

6/6/2014 4:15:14 Am

213333

814xxx

ABC.ZIP.asc

6/6/2014 4:15:16 AM

6/6/2014 4:15:18 AM

0

Completed

EFG.ZIP.asc

6/6/2014 4:15:18 AM

6/6/2014 4:15:20 AM

1359

xxx.ZIP.asc

6/6/2014 4:15:20 AM

6/6/2014 4:15:22 AM

14940

6/6/2014 4:15

69341

814xxx

6/6/2014 4:15:30 AM

6/6/2014 4:15:30 AM

0

0

Completed

   For each sessionid different files are transferred at different times. For each file there is a separate start and end time .When calculating the difference between the start and end time I am unable to calculate it for a sessionid that has multiple files transferred at different times, whereas there is no problem in calculating the difference between the start and end time for the sessionid that has only one file transmitted . 

The expected output is

SessionID

Filename

StartDateTime

EndDateTime

Tranmission id

FileSize

status

814xxx

6/6/2014 4:15:12 AM

6/6/2014 4:15:12 AM

0

0

Completed

Setxxxx.csv

6/6/2014 4:15:12 AM

6/6/2014 4:15:14 Am

2

213333

814xxx

ABC.ZIP.asc

6/6/2014 4:15:16 AM

6/6/2014 4:15:18 AM

2

0

Completed

EFG.ZIP.asc

6/6/2014 4:15:18 AM

6/6/2014 4:15:20 AM

2

1359

xxx.ZIP.asc

6/6/2014 4:15:20 AM

6/6/2014 4:15:22 AM

2

14940

6/6/2014 4:15

69341

814xxx

6/6/2014 4:15:30 AM

6/6/2014 4:15:30 AM

0

0

Completed

           Can anyone tell me how to calculate the difference between the start and end time here
Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

Try this

your base search with transaction command | eval DiffInSecs=mvindex(EndDateTime,eventcount-1)-mvindex(StartDateTime,0)

Assuming for session with multiple file, your need to take the first StartDateTime and last EndDateTime and calculate the difference.

Updated

The best option here would be to calculate the different before the transaction command itself. After transaction command, both date fields have become multivalued fields, and there is no in-built command to to arithmetic on the same (unless you can develop your custom search command for the same).

Your base search | eval DiffInSecs=strptime(EndDateTime,"%m/%d/%Y %H:%M:%S")-strptime(StartDateTime,"%m/%d/%Y %H:%M:%S") | your transaction command..

(your can drop the strptime thing if they are already in epoch)
This should give a multivalued field DiffInSecs which will hold difference between corresponding StartDateTime and EndDateTime value.

View solution in original post

somesoni2
Revered Legend

Try this

your base search with transaction command | eval DiffInSecs=mvindex(EndDateTime,eventcount-1)-mvindex(StartDateTime,0)

Assuming for session with multiple file, your need to take the first StartDateTime and last EndDateTime and calculate the difference.

Updated

The best option here would be to calculate the different before the transaction command itself. After transaction command, both date fields have become multivalued fields, and there is no in-built command to to arithmetic on the same (unless you can develop your custom search command for the same).

Your base search | eval DiffInSecs=strptime(EndDateTime,"%m/%d/%Y %H:%M:%S")-strptime(StartDateTime,"%m/%d/%Y %H:%M:%S") | your transaction command..

(your can drop the strptime thing if they are already in epoch)
This should give a multivalued field DiffInSecs which will hold difference between corresponding StartDateTime and EndDateTime value.

somesoni2
Revered Legend

See my updated answer.

0 Karma

Mubarish
Path Finder

Thanks but I need to calculate the time difference for individual file. How can i do?

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...