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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...