Splunk Search

How to search in index with a condition from another index

spisiakmi
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

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

spisiakmi
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

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