How to search in index with a condition from another index

[spisiakmi]

Hi, I have 2 different indexes.
Index1:
_time Fehlermeldungtext
2019-07-01 22:01:30 Streckenüberwachung Auslauf!
2019-09-09 04:28:56 Streckenüberwachung Auslauf!
2019-08-26 05:40:59 Streckenüberwachung Auslauf!
2019-08-25 11:18:30 Streckenüberwachung Auslauf!
2019-08-25 11:16:52 Streckenüberwachung Auslauf!
2019-08-25 11:12:30 Streckenüberwachung Auslauf!
2019-08-24 18:37:55 Streckenüberwachung Auslauf!
2019-08-24 18:37:15 Streckenüberwachung Auslauf!
2019-08-24 18:36:36 Streckenüberwachung Auslauf!
2019-08-24 18:35:57 Streckenüberwachung Auslauf!
2019-08-24 17:03:51 Streckenüberwachung Auslauf!

Index2:
Datum_Einlauf Datum_Auslauf
2019-07-01 21:59:37 2019-07-01 22:03:09
2019-07-01 21:58:25 2019-07-01 22:02:02
2019-07-01 21:56:22 2019-07-01 21:59:55
2019-07-01 21:54:37 2019-07-01 21:58:14
2019-07-01 21:54:04 2019-07-01 21:57:42
2019-07-01 21:52:36 2019-07-01 21:56:12
2019-07-01 21:52:15 2019-07-01 21:55:50
2019-07-01 21:50:14 2019-07-01 21:53:45
2019-07-01 21:49:53 2019-07-01 21:53:27
2019-07-01 21:45:19 2019-07-01 21:48:52
2019-07-01 21:44:35 2019-07-01 21:48:12
2019-07-01 21:44:01 2019-07-01 21:47:31
2019-07-01 21:41:45 2019-07-01 21:45:22
2019-07-01 21:41:11 2019-07-01 21:44:49

I want to find such events in Index2, where Datum_Einlauf<_time (from the Index1) AND Datum_Auslauf>_time (from the Index1).
For example: for the 1. row from Index1 2019-07-01 22:01:30 Streckenüberwachung Auslauf!
should appear 2 events from Index2:
2019-07-01 21:59:37 2019-07-01 22:03:09
2019-07-01 21:58:25 2019-07-01 22:02:02
Can anybody help me, please?

[Sukisen1981]

Hi,
You have to use strptime to convert the 3 times from indexes 1 &2 and compare them as per your where condition. That however, is not the big issue. You need a way so that the time from index 1 sort of loops through all rows of the second index.
Try this

index="index1" OR index="index2" |eval Datum=Datum_Einlauf+"*"+Datum_Auslauf|stats values(time),values(Datum)|rename values(time) as time,values(Datum) as Datum|mvexpand time|mvexpand Datum|rex field=Datum "(?<Datum_Einlauf>.*?)\*"| rex field=Datum "\*+(?<Datum_Auslauf>.*)"|table time,Datum_Einlauf,Datum_Auslauf

NOTE - When i took the sample from your index 1 _time became time, so please check the field name for _time in your actual splunk instance.
What this gives you is a row wise table of time(_time) and the 2 Datum fields. Now, all you have to do is use strptime and put in your condition. Check the table first and ensure that the row mappings are correct.

[spisiakmi]

Hi Sukisen1981,

many thanks for your answer. Can you explain me, please, this note? It is not clear for me.
NOTE - When i took the sample from your index 1 _time became time, so please check the field name for _time in your actual splunk instance.

[Sukisen1981]

hi @spisiakmi i copied your data into a csv file, your index1 has a field called _time. That conflicts with the default _time meta data field in Splunk, so splunk automatically renamed this field to time.
I suggest checking this query first by copying your sample data to a static csv file.
I copied your sample data into 2 different csv files and ingested them as index1 and index2 respectively.