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!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...