Filter events

[kirthika26]

Device_ID : 1 A.txt

2021-07-06 23:30:34.2379| Started!
2021-07-06 23:30:34.6808|3333|-0.051|0.051|0.008|0.016

Device_ID : 1 E.txt

2021-07-13 18:28:26.7769|**
2021-07-13 18:28:27.1363|aa

Device_ID : 2 E.txt

2016-03-02 13:56:06.9283|**
2016-03-02 13:56:07.3333|ff

Device_ID : 2 A.txt

2020-03-02 13:42:30.0111| Started!
2020-03-02 13:42:30.0111|444|-0.051|0.051|0.008|0.016

Query:

index="xx" source="*A.txt"
| eval Device_ID=mvindex(split(source,"/"),5)
| reverse
| table Device_ID _raw
| rex field=_raw "(?<timestamp>[^|]+)\|(?<Probe_ID>[^|]+)"
| table Device_ID timestamp Probe_ID
| rex mode=sed field=timestamp "s/\\\\x00/ /g"
| table Device_ID timestamp Probe_ID
| eval time=strptime(timestamp,"%F %T.%4N")
| streamstats global=f max(time) as latest_time by Device_ID
| where time >= latest_time
| eval _time=strptime(timestamp,"%Y-%m-%d %H:%M:%S.%4N")
| table Device_ID _time Probe_ID
|join type=left Device_ID [ search index="xx" source="*E.txt"
| eval Device_ID=mvindex(split(source,"/"),5)
| reverse
| rex field=_raw "(?<timestamp>[^|]+)"
| stats first(timestamp) as earliesttime last(timestamp) as latesttime by Device_ID
|table Device_ID earliesttime latesttime
]
|where _time >= strptime(earliesttime, "%Y-%m-%d %H:%M:%S.%4N") AND _time <= strptime(latesttime, "%Y-%m-%d %H:%M:%S.%4N")

|search Device_ID="1"

Filtering events based on E.txt earliest timestamp on A.txt.

It is working for Device_ID 1 and not for Device_ID 2.

Both logs are same format.

It is not generating earliest and latest timestamp for device_ID 2. If i run subsearch alone, it is generating.

[bowesmana]

To dIagnose these problems run the outer search on its own and the inner search on its own.

You are using join, which is not necessary and may be the issue depending on your data size.

You don't need the table commands all the time and you seem to be duplicating your time parsing (time and _time).

Not sure you need reverse either - in the join, you are reversing to get the first timestamp, which in practice without the reverse would be the oldest _time, so you could just to earliest(timestamp) instead without reverse.