topic Re: How to create a table with fields from two indexes based on one common field? in Splunk Search

How to create a table with fields from two indexes based on one common field?

markwymer — Wed, 30 Sep 2015 14:14:52 GMT

Hi all,

I've found many answers to questions that are similar to my question, but not quite the same. Still, my apologies if this has been answered before......

We have live events from a web based application being indexed into, e.g., indexA
We also have a daily CSV file (generated from a SQL query) being ingested into indexB

The events look something like (actually they don't but I hope you will understand my example:) )
indexA - common_field, fieldB, fieldC,fieldD,fieldE
IndexB - fieldM,fieldN,common_field, fieldO,fieldP

My objective is to produce a table that has...
common_field,fieldC,fieldE,fieldN,fieldO

I've tried playing around with subsearches, but can't seem to get all the fields that I need. I did, also, toy with join, but got lost on that too!

Thank you for any hints, tips, advice
Mark.

Re: How to create a table with fields from two indexes based on one common field?

richgalloway — Wed, 30 Sep 2015 14:25:32 GMT

Have you tried something like this?

index=indexA | join common_field [search index=indexB] | table common_field,fieldC,fieldE,fieldN,fieldO

Re: How to create a table with fields from two indexes based on one common field?

Ricapar — Wed, 30 Sep 2015 14:38:14 GMT

How big is that CSV you load up daily? It may make more sense to use that CSV as a lookup table. If you're already indexing, you can use outputlookup to create one from it:

index=index-with-csv | table common_field fieldA fieldB | outputlookup my-csv-lookup

And then use that lookup against your app logs:

index=index-with-applog | lookup my-csv-lookup common_field | table common_field fieldA fieldB ... fieldY fieldZ

This method will be a lot more efficient than a join or a transaction. If you're ingesting the CSV daily (likely via a scheduled job somewhere?) you can have a scheduled search to run that | outputlookup search and regenerate the lookup table within Splunk at a time shortly after the CSV gets dropped off for Splunk to index it.

Alternatively, if you happen to be using DB Connect in your environment, you can use that to run your SQL query directly against your database and generate the lookup table automatically, or even use an on-the-fly lookup. However, the above should work with your existing setup.

Re: How to create a table with fields from two indexes based on one common field?

somesoni2 — Wed, 30 Sep 2015 15:02:31 GMT

Try something like this

index=A OR index=B | table index common_field,fieldC,fieldE,fieldN,fieldO | stats values(*) as * by common_field | where mvcount(index)=2 | fields  - index

This will take all events from both index=A and index=B, group them by common_field, and show only the events which are present in both index (mvcount(index)=2)

Re: How to create a table with fields from two indexes based on one common field?

markwymer — Thu, 01 Oct 2015 14:41:09 GMT

Thanks for the response Ricapar. The CSV is a daily update which accumulated in Splunk so single common_field may have multiple entries tracking its usage.

You are quite correct, the most appropriate would be to use DB Connect to directly query the fields from our Data Warehouse. unfortunately, our DBA's are quite reluctant to give us, effectively, API access to the data. It is being worked on and so are they 😉