Join two rows and compare fields

[Peely]

I feel i'm so close, but can't quite make it work. I've tried map and am now trying a sub search (I think it's a sub search). I'm trying to get the time difference between two events, but now using the "_time" field, instead using a timestamp field of my own. My events look something like this

{

    action: "start",

    correlationId:"_GUID_",

    timestamp: "2021-07-13T03:44:46.100Z"

}

{

    action: "end",

    correlationId:"_GUID_",

    timestamp: "2021-07-13T03:44:46.260Z"

}

And my query so far is

index=* action=start
| eval start_time=timestamp
| join correlationId [ search index=action=end | eval end_time=timestamp ]
| eval timeTaken=end_time-start_time

But timeTaken is never populated. It seams my `timestamp` field has a "none" in it as well as a timestamp, but i'm not sure why as the raw text does not have any spaces or anything.

I also tried a selfjoin, that overwrite the first `timestamp` with the second one, and a map, which came back with no results. 

[richgalloway]

You can't do math on timestamp strings because they're not numbers.  Convert them to integers using strptime() and then do the math.

index=* action=start
| eval start_time=timestamp
| join correlationId [ search index=action=end | eval end_time=timestamp ]
| eval endTime=strptime(end_time,"%Y-%m-%dT%H:%M:%S.%3N%Z"), startTime=strptime(start_time,"%Y-%m-%dT%H:%M:%S.%3N%Z")
| eval timeTaken=endTime-startTime

[Peely]

Ok cool, I didn't know that! So I think that takes me a step closer but I'm still not getting a value for `timeTaken` in the result. I think my input timestamps have multiple values (though they shouldn't), would that effect it? See attached a screenshot of the Splunk GUI showing one of the timestamps on one of the input events.

[richgalloway · Accepted solution]

It looks like the timestamp field is multi-valued.  The strptime function doesn't work with multi-valued fields.  Use mvindex to select one of the values or visit how the timestamp field is created to avoid multi-values.

[Peely]

`mvindex` was the missing piece! Thank you very much. My final query was in the form of

index=*(action=start) 
| eval singleTimestamp=mvindex(timestamp, 1) | eval start_time=singleTimestamp 
| join correlationId
    [ search index=* action=end   | eval singleTimestamp=mvindex(timestamp, 1) | eval end_time=singleTimestamp  ]
| eval endTime=strptime(end_time,"%Y-%m-%dT%H:%M:%S.%3N%Z"), startTime=strptime(start_time,"%Y-%m-%dT%H:%M:%S.%3N%Z")
| eval timeTaken=floor((endTime-startTime)*1000)