Getting Data In

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

ykoolhout
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

whrg
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

ykoolhout
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

whrg
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) 

ykoolhout
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

whrg
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

ykoolhout
Explorer

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

0 Karma

whrg
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")

ykoolhout
Explorer

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

0 Karma

ykoolhout
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

whrg
Motivator

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

0 Karma

ykoolhout
Explorer

Not too sure how to check this

0 Karma

whrg
Motivator

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

0 Karma

ykoolhout
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

whrg
Motivator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...