Splunk Search

Correlate across data models using a common field

krishnakesiraju
Explorer

hi all,

I have 2 accelerated data models defined, both having a common field (AccountId in one and account_id in another). The events run into millions of events for the first data model and hundreds of thousands for the second data model. I am now trying to get data across these 2 sets, can you please help me get the best approach.

First Data Model is a JSON file, from which i've extracted fields:

Field1, Field2, Field3, AccountId......

Second Data Model is a search ---> index=index1 sourcetype=st1
Fieldx, Fieldy, account_id, Fieldz.......

I have tried:

  • Left Join : This one takes way too long to return the search result, but have noticed that the results from the inner search are duplicated - not really correlating the data correctly.

| datamodel DM1 DS1 search
| rename AccountId as account_id
| join type=left account_id [datamodel DM2 DS2 search]
| table DS1.Field1 DS2.Field2......

  • Multisearch : Unable to send value of a field from one search as a parameter into another. Also, the multisearch returns the results of 2nd query only
  • Subsearch : I believe this is the best approach based on my limited knowledge of Splunk, but am not able to get this working across the data models.

Appreciate any pointers helping me solve the issue.

Thanks,
Krishna

0 Karma

woodcock
Esteemed Legend

I will not pretend to understand the nuances but the fully correct way to do this is to do something like this:

| tstats ... prestats=t          FROM dtaamodel=FirstDM ...
| tstats ... prestats=t append=t FROM datamodel=SecondDM ...
| eval AccountIdJoiner = coalesce(FirstDM.AccountId, SecondDM.account_id)
| stats ... BY AccountIdJoiner

See here for the best docs there are:
https://answers.splunk.com/answers/595509/what-exactly-are-the-rulesrequirements-for-using-t.html
DEFINITELY do NOT use join, no matter what.

0 Karma