Extracting json from event data

[Cuyose]

Ive poured over about all the json extracting solutions I could find and can't seem to find anything that actually works.

If I have an even that is nothing but json, setting the source type kv_mode = json in props.conf works just fine, the issue is I have events like below. I need to preserve the timestamp obviously, and have splunk be able to recognize the json elements. This particular example has an epoch timestamp for the created_at key in the json, but for sake of argument, lets just assume the initial timestamp is what we want to use. I would optimally like all the elements in the json to be picked up from field discovery and the entire event stamped with the time stamp.

2013-06-11T15:24:38+00:00 DEBUG (7): 
 The path of the api call: /se/get/maven/116490
Json sent: []
Json recieved: {"status":"success","data":{"maven":{"email":"value","first_name":"value","last_name":"value","subscription":{"length":1,"status":"ACTIVE","first_name":"value","last_name":"value","start_month": value,"start_year": value,"tier_id": value,"skip_earned":false,"skip_allowed":true,"style_profile":"value","prepaid_shipments":0,"repeat_billing":true,"recurring_price": value,"shipping_address":{"address1":"value","address2":null,"city":"value","country":"value","phone":"value","state":"value","zip":"value"},"payment_method":{"payment_method_id": value,"processor_token":"value","processor_code":"value","expiration_month": value,"expiration_year": value},"skip_count":0,"successive_skips":0,"successive_fails":0,"created_at":1397068985000,"modified_at":1397069252000},"orderJSON":{"customer_id":"value","maven_id":value,"maven_status":"ACTIVE","tier_id":1,"shipping_address":{"firstname":"value","lastname":"value","street1":"value","street2":null,"city":"value","region":"value","postcode":"value","telephone":"value","country_id":"US"},"earned_skip":false,"always_can_skip":true,"repeat_billing":true,"prepaidShipments":0,"recurring_price": value,"subscription_term":1,"style_profile_code":"value","start_month": value,"start_year": value,"processor_code":"value"},"maven_id": value,"billing_address":{"address1":"value","address2":null,"city":"value","country":"value","phone":"value","state":"value","zip":"value"},"cim_customer_id":null,"customer_id":"value"}}}

[emiller42 · Accepted solution]

So I don't think you can get this to be WHOLLY automatic, but you can get darn close.

First, you want to add an EXTRACT to your props.conf for this sourcetype, with the intention of capturing the entire JSON string as a separate field. For example:

EXTRACT-json_recieved=Json\srecieved:\s(?<json_recieved>\{.*)$

Then when searching the sourcetype, pipe it to | spath input=json_recieved and it'll do the rest.

You can do more with spath as well. Check the documentation for details.

[Cuyose]

Is there a way to rename the fields across an app? Im sure i can research it, however whenever splunk extracts a discovered field like "data.order_info.order.default_box_sku" from the json, have it rename in the results as "default_box_sku" or whatever I configure. The goal being to do the configuration once for the source type and not have to mess with it again unless needed.

[Cuyose]

Thanks, this is working for me. I am seeing it is now discovering the fields, maybe too many, however I can do a rename on a lot of these and assign event types to obfuscate the crazy spath queries.