How to associate and create fields between 2 JSON events?

[jeanmatthieu]

Hey Everyone,

I'm trying to extract fields from an event using a somewhat similar foreign key concept/mechanism.
For the two events as below:

Event#1

{
   colour=blue,
   metadata_id=1234-56,
   record_type=car
}

Event#2

 {
       material=plastic,
       country_of_origin=germany
       metadata_id=1234-56,
       record_type=metadata
    }

I would like to be able to add to Event#1 the material and country_of_origin fields so I can easily search for all blue cars made in Germany as such:

index=cars country_of_origin=germany colour=blue record_type=car

Could you kindly suggest how I could build fields using metadata_id as a foreign key in Event#1 that extracts the necessary info from Event #2 ?

Thank you!
Jean-Matthieu

[somesoni2 · Accepted solution]

Since they are in same index/sourcetype but present in different event, you will not be able to do filters, like you need, directly. You would need to do some processing (correlation) before the filter. Something like this

index=cars | table colr, metadata_id, record_type, material, country_of_origin | stats values(*) as * by metadata_id | search country_of_origin=germany colour=blue record_type=car

For long term, you can assign different sourcetype from same data source, based on regular expression. See this
http://docs.splunk.com/Documentation/Splunk/6.2.4/Data/Advancedsourcetypeoverrides

Once you've the color (any attribute which appears less number of time) on different sourcetype, Then you may have different options, like you can create a lookup table and use that as filter, OR use the 2nd sourcetype as subsearch filter etc.

[jeanmatthieu]

@somesoni2, you're awesome Sir!

I did exactly what you said: source type override with transforms/props and filtering with sub search + using join to aggregate fields and my queries are flying.

Thanks again!

[jeanmatthieu]

Thank you for pointing me to the advanced source type override.
I should probably implement that first as follow
In transforms.conf:

[my_car_metadata]
REGEX = *.record_type=metadata.*
FORMAT = sourcetype::car_metadata
DEST_KEY = MetaData:Sourcetype

In props.conf:

[original_sourcetype]
TRANSFORMS-car_metatdata_sourcetype = my_car_metadata

Of your two suggestions (creating and using lookup table as a filter and sub search) I would not know which one is the most efficient. Would you be able to illustrate one technique please ? I have seen the ease of CSV lookups before and was hoping for a similar solution with JSON based events.

On a side note, naming convention in props.conf looks like a strategy of its own !

Thank again for your prompt help!

[somesoni2]

Both events appears on the same index/sourcetype??

[jeanmatthieu]

They do indeed -- it would be difficult to post to a different source type as events are received through a tcp port on a heavy forwarder and safely brought to our backend afterwards. Thanks !