Getting Data In

How can I subtract two timestamp fields in a transaction to get duration?

Explorer

Helllo, I've been trying to subtract two timestamp fields from each other within a transaction. A timestamp as such:

2018-12-11T09:54:16.869+01:00
2018-12-11T09:54:16.874+01:00

The current search I'm using is as follows:

index=testindex sourcetype="_json" 
| transaction engine.correlationId startswith="tracepoint=Entry" endswith="tracepoint=Exit" mvlist=engine.currentTimestamp
| eval firstValue1=mvindex(engine.currentTimestamp,0) 
| eval secondValue1=mvindex(engine.currentTimestamp,1) 

| eval end_time_epoch = strptime(firstValue1, "%Y-%m-%dT%H:%M:%S.%f")
| eval begin_time_epoch = strptime(secondValue1, "%Y-%m-%dT%H:%M:%S.%f")
| eval duration = end_time_epoch - begin_time_epoch

| table engine.currentTimestamp firstValue1 secondValue1 duration

I was expecting to get "duration" as the two timestamps subtracted from the from each other, which would give the difference in milliseconds. For some reason, only engine.currentTimestamp is returning the multiple timestamp-values of the transaction and the other fields are returning empty in the table.

Perhaps it is the mvlist, which isn't working, but it could also be the calculation since it is trying to subtract within a transaction that has 2 or 3 timestamps from 2 or 3 events.

Any ideas?

Thanks in advance!

0 Karma
1 Solution

Motivator

Hi again! Apparently, the issue lies with this line:

| eval firstValue1=mvindex(engine.currentTimestamp,0) 

I believe the dot is causing the issue. Can you try this:

| eval firstValue1=mvindex("engine.currentTimestamp",0) 

View solution in original post

Explorer

Fixed it, turns out indeed the dot between "engine.currentTimestamp" was causing the problem.
a simple "rename *** AS ****" fixed the problem. Thanks for the input whrg!

0 Karma

Motivator

Hi again! Apparently, the issue lies with this line:

| eval firstValue1=mvindex(engine.currentTimestamp,0) 

I believe the dot is causing the issue. Can you try this:

| eval firstValue1=mvindex("engine.currentTimestamp",0) 

View solution in original post

Explorer

You were right, the dot caused an issue. I believe there is something I'm missing though. I was expecting the firstValue1 and secondValue1 to set the value of the timestamp, not the name of the field. Heres a sample output below:
alt text
Perhaps something with mvlist or mvindex?

0 Karma

Motivator

Instead of double quotes, try single quotes as I just read here: Dealing with field names with a period in it

| eval firstValue1=mvindex('engine.currentTimestamp',0) 
0 Karma

Explorer

Guess it's so simple you look over it. But the single quotes work aswell! Thanks.

0 Karma

Motivator

Having a look at Date and time format variables, %f is not listed. So you might need to change the time format for the strptime function.

Perhaps this will work better:

| makeresults count=1 | eval timestamp="2018-12-11T13:24:16.869+01:00"
| eval epoch_time = strptime(timestamp, "%Y-%m-%dT%H:%M:%S.%3N%:z")

Explorer

This seems to have fixed the time layout, as this command works. Something else seems to be going on though.

0 Karma

Explorer

Hi whrg,

A single value would be as follows:
"currentTimestamp": "2018-12-11T13:24:16.869+01:00"
Though in a transaction it would have multiple timestamps.

0 Karma

Motivator

Does engine.currentTimestamp exist as a multivalue field after the transaction command?

0 Karma

Explorer

Not too sure how to check this

0 Karma

Motivator

Search for index=... | transaction ...
(That is, remove the lines after transaction.)

0 Karma

Explorer

This is what it returns as one event with the transaction command:

 {
    "engine": {
      "currentTimestamp": "2018-12-11T13:54:16.869+01:00",
      "localization": "Central European Time",
      "processId": "10790@DESKTOP-68CLR",
      "applicationName": "cr_quotes",
      "messageId": "de1d3e0-fd4311e8-811c-005056a4ee"
    },
    "tracepoint": "Entry"
  }
{
    "engine": {
      "currentTimestamp": "2018-12-11T13:54:16.967+01:00",
      "localization": "Central European Time",
      "processId": "10790@DESKTOP-68CLR",
      "applicationName": "cr_quotes",
      "messageId": "de46d3e0-fd43-11e8-8f1c-0050563ee"
    },
    "tracepoint": "Exit"
  }

Hope this answers the question!

0 Karma

Motivator

Hi! Can you post the value of engine.currentTimestamp of one sample event?

0 Karma
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!