topic Re: How do I append event(s) fields to a separate event based on two timestamps? in Splunk Search

How do I append event(s) fields to a separate event based on two timestamps?

kligms — Tue, 29 Sep 2020 22:05:18 GMT

Hello,

I have two sets of data: Trip Metadata(A) and Individual Trip Coordinates(B).

Set A fields:
- StartTime
- EndTime

Set B fields:
- Coordinates (Latitude and Longitude)
- Timestamp

How do I append the Set B Coordinate fields to events of Set A only if the Set B timestamp is between the Set A Start and End time?

Thanks for reading.

Both sets of data live in the same index but with different source types.

Sample Data:
Set A Trip Metadata (Multiple events(trips))
{ [-]
device_sn: GXXXXXG

id: qwg098wq7wq8

trip_distance_km: 4.927902

trip_end_time: 2018-11-19T23:46:56.0000000-05:00

trip_start_time: 2018-11-19T23:36:35.0000000-05:00

vehicle_name: Eric Jim
vehicle_vin: WG15
},
{ [-]
device_sn: GXXXXXG

id: 23jg9802j32
trip_distance_km: 4.927902

trip_end_time: 2018-11-19T23:06:56.0000000-05:00

trip_start_time: 2018-11-19T23:16:35.0000000-05:00

vehicle_name: Eric Jim

vehicle_vin: WG15
}

Set B Trip Individual Coordinates
{ [-]
device_sn: GXXXXXG

id: 9854gu45g
latitude: 33.7303314
longitude: -116.96965
speed_kph: 0

timestamp: 2018-11-19T23:47:02.0000000-05:00

vehicle_name: Eric Jim

vehicle_vin: WG15
}

Re: How do I append event(s) fields to a separate event based on two timestamps?

laurie_gellatly — Tue, 29 Sep 2020 22:04:54 GMT

Could you post some sample data from the index?
index=theindex sourcetype=trip_meta OR sourcetype=trip_coords
So you're saying that _time for the coords is > starttime and < endtime for the trip_meta event (or events)?

Re: How do I append event(s) fields to a separate event based on two timestamps?

kligms — Tue, 20 Nov 2018 13:24:35 GMT

I added some sample data. Yes, the coords time has to be between the trip meta start and end time. There are multiple trips and there's alot of trip coordinate events.

Re: How do I append event(s) fields to a separate event based on two timestamps?

laurie_gellatly — Tue, 29 Sep 2020 22:10:04 GMT

It looks like there are additional values that must be used to correlate the events?
Is the 'id' field shown sufficient to associate Set B with Set A data or field 'vehicle_vin' or a combination of both?
The sample data you provided would not correlate any values as the time for Set B data is not within any of Set A event time ranges. The second event, the end_time is before the start_time??? Guess you hand generated this?
Are you trying to achieve an association of the lat/long with the start and end times? Is that the goal?

Re: How do I append event(s) fields to a separate event based on two timestamps?

laurie_gellatly — Wed, 21 Nov 2018 23:52:34 GMT

Try this (assuming that 'timestamp' field is actually extracted):

  index="your_index" 
    | strcat timestamp ";" latitude ";" longitude latv
    | eventstats values(latv) as latv by vehicle_vin 
    | where trip_start_time=trip_start_time 
    | mvexpand latv
    | rex field=latv "(?<timestmp>[^;]*);(?<latitude>[^;]*);(?<longitude>.*)"
    | eval ts = strptime(timestmp,"%Y-%m-%dT%H:%M:%S") 
    | eval trip_start_time = strptime(trip_start_time ,"%Y-%m-%dT%H:%M:%S") 
    | eval trip_end_time = strptime(trip_end_time ,"%Y-%m-%dT%H:%M:%S") 
    | where ts>=trip_start_time AND ts <=trip_end_time
    | table trip_distance_km trip_start_time ts trip_end_time vehicle_vin latitude

The evals would not be needed if the timestamps are already able to be numerically compared.
If you need to keep additional fields from the coordinate events then add them to the strcat and the rex lines.

...Laurie:{)