Join two tables and retrieve latest record from the second table

[djroks89]

Hi Team,

I have a requirement that i'm writing a join query.

Query-1 returns 

id ,time

5560007

2020-09-27 12:30:18.915

Query-2 returns 

ID, time, status

5560007	2020-09-27 18:49:13.757	Completed
5560007	2020-09-27 18:49:11.862	ActivityCompletedNotification
5560007	2020-09-27 18:49:08.781	Activity
5560007	2020-09-27 18:44:02.812	ActivityInProgressNotification

I'm using outer join to join both query-1 and query-2 and i would need the latest value i.e. Completed from the Query-2. Currently, when i write an outer join, its randomly picking the values from the query-2. 

Query :

index="xxxxxx" "5560007"
| table id, _time
| join type=outer id [ search
index="xxxxx" "5560007"
| table id, _time,notificationType
| sort -_time]
|table id,notificationType

please help

Many thanks!

[djroks89]

Hi @bowesmana - Thanks so much for your answer and its working.

I have another doubt. I want to display the min time and latest time. 

| stats latest(_time) as _time min(_time) as mintime latest(notificationType) as notificationType by id
| sort - _time

but mintime is displaying in a different format.  ((1602688800.300))

Could you please help here ?

Thanks in advance!

[bowesmana]

The _time field is special, in that it will be formatted as a readable time value, whereas other fields that represent time are not automatically formatted as readable times.

To format time as fields, use eval+strftime as in

| eval mintime=strftime(mintime, "%F %T.%Q")

Those % format variables are described here, but this formats it in standard ISO8601 format with milliseconds.

https://docs.splunk.com/Documentation/Splunk/8.0.6/SearchReference/Commontimeformatvariables

[bowesmana · Accepted solution]

If you need to use join, then if you just need the latest notificationType from query 2, then use stats inside  the second query.

index="xxxxxx" "5560007"
| table id, _time
| join type=outer id [ 
  search index="xxxxx" "5560007"
  | stats latest(_time) as _time latest(notificationType) as notificationType by id
  | sort - _time
]
|table id,notificationType

 If you want all the results from query 2, then use max=0 on the join to get all the results and use table instead of stats in q2.

However, it's always a good idea to approach the join issue from trying to avoid using join. It's more efficient to use stats, e.g.

(index="xxxxxx" "5560007") OR (index="xxxxx" "5560007")
| stats latest(_time) as _time latest(notificationType) as notificationType by id
| sort - _time

I think this would give the same outcome as the first.