Splunk Search

Join two tables and retrieve latest record from the second table

djroks89
Explorer

Hi Team,

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

Query-1 returns 

id ,time

55600072020-09-27 12:30:18.915

 

Query-2 returns 

ID, time, status

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


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!

Labels (1)
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

djroks89
Explorer

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!

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...