Skip to main content

Building Event Search with Full Outer Join

  • February 18, 2026
  • 7 replies
  • 52 views

Jsandy
Forum|alt.badge.img+1

Is it possible to build an event search (not rule), that aggregates events from two different sources, correlating on a custom user field built from regex.

What I am trying to do is get events from two different event sources where they have the same username field (custom created to normalise the username from different fields within each event source) and simply aggregate these.

 

The idea being that for a user I want to see all related events that meet some criteria in each event source, Id imagine when actually showing the relevant fields for some rows there will be empty values for where that particular field exists in one event type it does not exist in the other.

 

Sample:

events:

// --- AWS LOGIC ---

// Add hashi.target.user.user_display_name = “someuser@mail.com”

$aws.metadata.vendor_name = "amazon"

$aws.metadata.product_event_type = /AssumeRole(WithWebIdentity|WithSAML)?/

 

// --- HASHICORP LOGIC ---

// Add hashi.target.user.user_display_name = “okta-someuser”

$hashi.metadata.log_type = "HASHICORP"

$hashi.target.user.user_display_name = /^(okta|ldap)-.*/

$hashi.target.url = /.*(secret|kv)\/.*/

 

// --- SHARED BINDING ---

// This connects both event types to the same match variable

$username = re.capture($aws.principal.user.user_display_name, "([^@]+)@mail.com")

$username = re.capture($hashi.target.user.user_display_name, "^(?:okta-|ldap-)(.*)")

 

match:

$username over 1h

 

condition:

$aws or $hashi

7 replies

Asura
Forum|alt.badge.img+3
  • February 18, 2026

Hello ​@Jsandy,

 

Yes it is possible, at least I was able to do it on my side. The event section of the query looks good. 

 

Could you try to change in the condition section by replacing “or” by “and”?

 

Do not hesitate to let me know if there is an error message after doing the change
 


Jsandy
Forum|alt.badge.img+1
  • Author
  • New Member
  • February 19, 2026

This did not work, unless events exist in both event types.

If i have event 1 with columns A and B

and event 2 with columns A and C

I want my search do be able to returnt the following:

Header           A                        B                      C

Event 1          value                 value                empty
Event 2          value                 empty              value


Asura
Forum|alt.badge.img+3
  • February 19, 2026

I am not sure to get what you mean by Header, which udm field are we talking about?

Since you are joining two events from different source together, you need both events to exist and have the same pivot point. In what you describe pivot will be based on Header A.

 

If you want to join two events on one common field, if one of these event does not exist (or matching value), it is normal to have 0 results.

 

If your goal is to join two events from different source, and make sure than one of them is always included no matter what the value is. You can probably use left or or right outer join,

left outer join re.capture($aws.principal.user.user_display_name, "([^@]+)@mail.com") = re.capture($hashi.target.user.user_display_name, "^(?:okta-|ldap-)(.*)")

But you might still need to make sure that your pivot point always exist.

 

Outer join was recently released, so I still need a little bit of practice on it to be more accurate. Let me know if I understood correctly your problem, and if left outer join could help.


Jsandy
Forum|alt.badge.img+1
  • Author
  • New Member
  • February 19, 2026

Apologies, header was just a reference to a field name i.e. (the column names that shows in the table when you run a search, for example in outcome you might have one called “count”) . 

Maybe a join is not the right term, I am looking for something that will allow me to aggregate events in one search from multiple event sources and manipulate the fields without them having to be present in both data sources.

The problem with left and right outer join is that they only include one of the event sources where there is not match, going back to my example.

Perhaps something easier to start with would be a way of simply aggregating and returning events from two event sources. 


Asura
Forum|alt.badge.img+3
  • February 19, 2026

Oh okay got it, thanks for clarification.


Sadly, full outer join is not supported at this time, I have an ongoing FR for it. 

 

If your goal is to display the same type of information based on two types of events. Maybe you can keep your or in the condition section. You can then, remove the $username equality from the condition part. Instead, you define the username in your outcome section and use if to change the re.capture depending of the event log type.


Jsandy
Forum|alt.badge.img+1
  • Author
  • New Member
  • February 19, 2026

Thought this might be the case. Thank you for your assistance  :)


Asura
Forum|alt.badge.img+3
  • February 19, 2026

No problem, don’t hesitate to let me know if leveraging the outcome section can help :)