How to Join Event Table Logs in Google SecOps Preview Dashboards?

[prashant_nakum]

Hi everyone,

I’m working on creating preview dashboards in Google SecOps and need to join logs from the event table based on a common field.

For example, I have events where:

metadata.product_event_type = "evt1"
metadata.product_event_type = "evt2"

Both events share a common field, fingerprint, and I need to join them based on this field. The goal is to use some fields from evt1 and some from evt2 in the same query for my dashboard.

Is there a way to perform this type of join in Google SecOps preview dashboard queries?

Any guidance or documentation would be greatly appreciated!

Thanks,
Prashant Nakum

[jstoner]

At the moment, the dashboards charts do not handle joins like a rule does. That's not to say that is the end state but that is where we are at the moment...

[Alebb]

Hello @Jsjsdjsjs12,
Do you  know if this functionality is in development and if a possible release date is known? Join funciontality It's only allowed in the rule editor, but it would be an excellent function both for being able to do it in the UDM search and in the native dashboards

[chrisd2]

Hello @prashant_nakum , do your different logs have the "fingerprint" data mapped to the same UDM field ?
If so, you can try something like :

// Filter for relevant log_types only
metadata.log_type = /^LOGTYPE_1$|^LOGTYPE_2$/

// Replace by the path of your UDM field "fingerprint"
$agg_field = <your_fingerprint_udm_field> 

// Other filter criterias to fit your needs...
// ...

match:
    $agg_field

outcome:
    $distinct_prod_evt_types = array_distinct(metadata.product_event_type)
    // If the data you want to output for a given fingerprint is tied to a particular log_type you can filter with if(). 
    // Let's say you want to ouptut the user(s) associated with the fingerprint, and take it from LOGTYPE_1 logs :
    $involved_user = array_distinct(if(metadata.log_type = "LOGTYPE_1", principal.user.userid, ""))

[prashant_nakum]

Hi @chrisd2  ,

Apologies for the delayed response.

I tried implementing your approach, but my use case is a bit different. I need to join two log types using the fingerprint field and then use multiple fields from both log types to create the visualization. Unfortunately, the suggested approach doesn’t fully meet my requirements.

Is there an alternative approach to achieve this?

Your response would be greatly appreciated.

Thanks,
Prashant Nakum