All Apps and Add-ons

How to join 2 datamodel searches with multiple AND clauses

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

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

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

Explorer

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

0 Karma