Splunk Search

How to associate and create fields between 2 JSON events?

jeanmatthieu
Explorer

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

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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.

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

@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!

0 Karma

jeanmatthieu
Explorer

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Both events appears on the same index/sourcetype??

0 Karma

jeanmatthieu
Explorer

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 !

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...