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!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...