Splunk Dev

How to Join indexes on common field and preserve order?

adomenico
Explorer

I have 2 indexes, one with server events and one with server temperature readings.  The server events come in when generated and the temperature readings come in every 15 mins.  How do I create a summary index so that I can see all the events for each server in order? 

The goal would be to use that summary index for MLTK and predict failures based on event sequence and temperature reading.

In SQL, I could do:

CREATE TABLE mydb.mytable as SELECT (fields) from table1.a LEFT JOIN table2.b ON (primary_key) order by (timestamp);

How to achieve this in Splunk?

Labels (1)
Tags (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Splunk has a manual to help SQL users adapt to SPL.  See https://docs.splunk.com/Documentation/SplunkCloud/8.2.2203/SearchReference/SQLtoSplunk

You don't need anything fancy for this, though.  Since all events have a timestamp, we just need to read them in and sort them by time.

index=index1 OR index=index2
| sort 0 _time
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Splunk has a manual to help SQL users adapt to SPL.  See https://docs.splunk.com/Documentation/SplunkCloud/8.2.2203/SearchReference/SQLtoSplunk

You don't need anything fancy for this, though.  Since all events have a timestamp, we just need to read them in and sort them by time.

index=index1 OR index=index2
| sort 0 _time
---
If this reply helps you, Karma would be appreciated.

adomenico
Explorer

It doesn't quite work.  I have to do some field parsing because the primary key has a different name in each index (also, it's not just index but also sourcetype), so if I need to do a | rename, where doe it go?  Before or after the OR?  

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Neither.  You can't rename before the first pipe.

I like to pick one name from either side and use that for both sides via coalesce.

index=index1 OR index=index2
| eval field1 = coalesce(index1field1, index2field1)
| eval field2 = coalesce(index1field2, index2field2)
...
| sort 0 _time
| table _time field1 field2 ...
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...