How to join 2 datamodel searches with multiple AND clauses

[msashish]

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 ?

[koshyk · Accepted solution]

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]
| ...

[msashish]

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