Splunk Search

How to search in index with a condition from another index

Communicator

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?

0 Karma

Champion

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.

Communicator

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.

0 Karma

Champion

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!