How to Join indexes on common field and preserve order?

[adomenico]

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?

[richgalloway · Accepted solution]

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

[adomenico]

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?  

[richgalloway]

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 ...