topic Re: Join two rows and compare fields in Getting Data In

Join two rows and compare fields

Peely — Tue, 13 Jul 2021 13:32:02 GMT

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.

Re: Join two rows and compare fields

richgalloway — Tue, 13 Jul 2021 13:46:26 GMT

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

Re: Join two rows and compare fields

Peely — Tue, 13 Jul 2021 14:33:30 GMT

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.

Re: Join two rows and compare fields

richgalloway — Tue, 13 Jul 2021 15:42:58 GMT

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.

Re: Join two rows and compare fields

Peely — Wed, 14 Jul 2021 13:34:12 GMT

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