It seems to be my standard response to a lot of these types of queries so apologies - but perhaps a list would be the way to go? Or a second filter after the initial correlation/search?

Not sure I fully grasp the nature of the log messages, perhaps you could gives us some examples?

Hello @Nils Krumey, 

thank you for your quick reply!

Here is such a set of logs (11 logs in this case) which I want to correlate:

<22>Feb 14 16:29:01 mail_log_syslog: Info: Start MID 105970882 ICID 0
<22>Feb 14 16:29:01 mail_log_syslog: Info: Delivery start DCID 59057473 MID 105970882 to RID  0] to Centralized Policy Quarantine
<22>Feb 14 16:29:01 mail_log_syslog: Info: Message finished MID 105970882 done
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 ICID 0 RID 0 To: <censored@censored.com>
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 queued for delivery
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 ICID 0 From: <censored@censored.com>
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 was generated based on MID 105970871 by duplicate-quarantine filter 'DKIMVerificationFilter'
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 ready 23417 bytes from <censored@censored.com>
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 enqueued for transfer to centralized quarantine "EmailAuthQuarantine" (duplicated by content filter DKIMVerificationFilter)
<22>Feb 14 16:29:01 mail_log_syslog: Info: Message done DCID 59057473 MID 105970882 to RID  0] (centralized policy quarantine) 
<22>Feb 14 16:29:01 mail_log_syslog: Info: MID 105970882 RID  0] Response 'ok:  Message 1452832 accepted'

The idea is to have a table (e.g. with “fields”) to see log_ts, sender and receiver.

Hm. Not quite the same thing, but perhaps something like [message="*DKIMVeri*"] | process toList (failed_verification_ids, message_id) and then a separate second search just for message_id IN FAILED_VERIFICATION_IDS ? Running out of ideas on how to limit it to just the rows for a single message ID though...

Hello @Nils Krumey,

thanks for the advice.

But i believe that the result for a search template or alert rule must be available in a single search query.

Isn’t it a bug, that the right join is not returning all the elements of the right side (s2)?

According to the documentation, this could be understood as such:

Right Join

You can use a right join to return all the values from the table or stream on the right and only the common values from the table or stream on the left.

https://docs.logpoint.com/docs/search-query-language/en/latest/Search%20Query%20Language/Pattern%20Finding.html#right-join

So this query should return more than just 2 rows:

odevice_ip=10.0.0.25 message="*DKIMVeri*"] as s1 right join odevice_ip=10.0.0.25] as s2 on s1.message_id = s2.message_id

But it doesn’t...

That’s what I would think, yes. Does it behave differently if you swap the streams and do a LEFT join?

Hello @Nils Krumey,

I just tried it out.

It has the exact same result.

Well, that makes it slightly less likely to be a bug as we’re not normally that consistent with them but your actual use case aside the JOIN queries look fairly straightforward to me and I don’t know why they wouldn’t return more than two results. I might be missing something, but it’s probably a case for Support? I’ll try to find some example logs to test some JOINs with as well if I get the time. Is this on 7.0?

We are still using 6.12.2.

There are some more missing “features”  with having same and followed by.

E.g. a preceeded by would be nice too, especially for detecting attacks like Kerberoasting etc.

But these things should have their own idea or feature request.

Yes, I agree, and feel free to log them in the ideas portal. I raised a request for a “not followed by” a while back, but it really helps to have real life examples on why these things are useful.

Try with some aggregation, I had the same issue and I simply parsed the values I needed and then did some aggregation at the end as it will show all results, the join only shows the first result. 

Example:
>entityName=* risk > 70 | chart count() by entityName ] as s1 join
>user=* | chart count() by user, category, server, threat, alert, alertId, server_risk ] as s2
on s1.entityName = s2.user
| chart count() by s1.entityName, s2.category, s2.server, s2.threat, s2.alert, s2.alertId, s2.server_risk

Hello Markus,

I’ve not tested it on your log sample but the following query (with some adjustments) should do the trick:

message_id=*
| chart distinct_list(msg) as msg, distinct_list(sender) as sender, distinct_list(receiver) as receiver, max(log_ts) as log_ts by message_id
| search "*DKIMVeri*"
| rename msg as full_msg
| fields log_ts, sender, receiver, message_id, full_msg

The idea is to:

1. Merge all events sharing the same Message ID into a “super event” while keeping some normalized fields
2. Filter the super events we are interested in
3. Display all relevant fields

The full_msg field is just here for debugging purposes and can be dropped.

Hope it helps !

Regards,

Adrien