All Apps and Add-ons

How to join 2 datamodel searches with multiple AND clauses

msashish
Explorer

Hi,

I am reading through https://docs.splunk.com/Documentation/SplunkInvestigate/Current/SearchReference/JoinCommandOverview to construct my search query.

query1: | from datamodel.MODELS.EVENT | where ..............| eval....| eval....|stats....| table
Output will have multiple rows with columns: col1, col2, col3, col4, acol1, acol2, acol3

query2: | from datamodel.MODELS.METADATA | where ..............| eval....| eval....|stats....| table
Output will have multiple rows with columns: col1, col2, col3, col4, mcol1, mcol2, mcol3

I need to join query1 and query2 on col1, col2, col3, col4

When I tried, it is giving unrecognised AND error.
| from datamodel.MODELS.EVENT | where ..............| eval....| eval....|stats....| table
| join left=L right=R type=inner
where L.col1=R.col1 AND L.col2=R.col2 AND L.col3=R.col3 AND L.col4=R.col4
| from datamodel.MODELS.METADATA | where ..............| eval....| eval....|stats....| table

I tried using [AND L.col2=R.col2 AND L.col3=R.col3 AND L.col4=R.col4] as well but it gave an unrecognised | error.

Please kindly suggest if there are any other ways ?

0 Karma
1 Solution

koshyk
Super Champion

Are you using this in Splunk Enterprise? The join command you put is from another product SpunkInvestigate

The default Splunk join is in different format and can be seen https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

If you really want to use join (try the alternatives in that page first as they are efficient), then something in the lines of

| from datamodel.MODELS.EVENT | where ..............| eval....| eval....|stats....| fields col1, col2,col3,col4
| join type=left col1 col2 col3 col4  [ | from datamodel.MODELS.METADATA | where ..............| eval....| eval....|stats....| fields | fields col1, col2,col3,col4, someothercolumns, anotherColumn]
| ...

View solution in original post

0 Karma

koshyk
Super Champion

Are you using this in Splunk Enterprise? The join command you put is from another product SpunkInvestigate

The default Splunk join is in different format and can be seen https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

If you really want to use join (try the alternatives in that page first as they are efficient), then something in the lines of

| from datamodel.MODELS.EVENT | where ..............| eval....| eval....|stats....| fields col1, col2,col3,col4
| join type=left col1 col2 col3 col4  [ | from datamodel.MODELS.METADATA | where ..............| eval....| eval....|stats....| fields | fields col1, col2,col3,col4, someothercolumns, anotherColumn]
| ...
0 Karma

msashish
Explorer

Thank you @koshyk Example reference worked. I will also try the page shared.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...