topic Re: How to extract additional JSON objects from within my extracted JSON? in Getting Data In

How to extract additional JSON objects from within my extracted JSON?

splunker1981 — Wed, 19 Oct 2016 14:50:46 GMT

Hello All,

I was wondering how to go about extracting additional objects within my extracted JSON. For example here is a sample of one of my events I'm working with. Each of these key/value pairs are being extracted into their own fields. I would like to pull out additional fields by grabbing specific elements from the extracted fields.

{
    "oderNumber": 23994,
    "orderDelay": 120,
    "orderedDate": "2016/03/01 18:47:22",
    "processedDate": "",
    "orderDetails": "Account:11111, AccountName:1111-xxx, OrderIpAddress:1.1.1.1",
    "orderProcessor": "user",
    "orderErrors": "",
    "acknowledgedErrors": "",
    "orderId": {
        "value": 97655
    }
}

For example; from the orderDetails field I would like to extract Account, AccountName and OrderIpAddress into its own fields. How can I accomplish this?

Thanks for the help.

Re: How to extract additional JSON objects from within my extracted JSON?

dmaislin_splunk — Wed, 19 Oct 2016 15:08:52 GMT

Are you leveraging indexed_extractions = JSON or using another method?

http://docs.splunk.com/Documentation/Splunk/6.5.0/Data/Extractfieldsfromfileswithstructureddata

Re: How to extract additional JSON objects from within my extracted JSON?

splunker1981 — Wed, 19 Oct 2016 15:56:27 GMT

Correct - based on the example data that I pasted each of the key/val pairs are extracted into their own field. The issue is that I want to further extract key/values from an extracted field.

example:
"orderDetails": "Account:11111, AccountName:1111-xxx, OrderIpAddress:1.1.1.1",

This is extracted as
orderDetails = Account:11111, AccountName:1111-xxx, OrderIpAddress:1.1.1.1

I would like to extract account, accountName and OrderIpAddress into its own fields
Account = 111111
AccountName = 1111-xxxx
OrderIpAddress = 1.1.1.1

Re: How to extract additional JSON objects from within my extracted JSON?

sundareshr — Wed, 19 Oct 2016 15:59:43 GMT

You can use a regex to extract these fields values if they are not already extracted. Try like this

.... | rex field=orderDetails "\w+:(?<acct>[^,]+),\s?\w+(?<name>[^,]+),\s?\w+(?<ip>[^\"]+)" | ...

Re: How to extract additional JSON objects from within my extracted JSON?

lukejadamec — Wed, 19 Oct 2016 16:16:57 GMT

I did not have luck using rex with orderDetails, but _raw worked:

... | rex field=_raw "\"Account\:(?<Account>[^,]+).*Name\:(?<AccountName>[^,]+).*OrderIpAddress\:(?<OrderIpAddress>[^\"]+)"  | Table Account,AccountName,OrderIpAddress,orderDetails

Re: How to extract additional JSON objects from within my extracted JSON?

javiergn — Wed, 19 Oct 2016 16:18:29 GMT

See if the following generic option helps.
You can ignore everything up to foreach, as this is what I used to replicate your issue in my lab.

| stats count | fields - count
| eval _raw = "
{
    \"oderNumber\": 23994,
    \"orderDelay\": 120,
    \"orderedDate\": \"2016/03/01 18:47:22\",
    \"processedDate\": \"\",
    \"orderDetails\": \"Account:11111, AccountName:1111-xxx, OrderIpAddress:1.1.1.1\",
    \"orderProcessor\": \"user\",
    \"orderErrors\": \"\",
    \"acknowledgedErrors\": \"\",
    \"orderId\": {
        \"value\": 97655
    }
}
"
| spath
| foreach * [ 
    | eval temp = split('<<FIELD>>', ",")
    | eval size = mvcount(temp)
    | mvexpand temp
    | rex field=temp "^(?<key>[^:]+)\s?:\s?(?<value>.+)$"
    | eval {key} = if (size > 1, value, null())
    | fields - key, value, size, temp
]
| stats first(*) as * by _raw