Getting Data In

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

splunker1981
Path Finder

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.

Tags (2)
0 Karma

javiergn
Super Champion

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
0 Karma

lukejadamec
Super Champion

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
0 Karma

sundareshr
Legend

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>[^\"]+)" | ...
0 Karma

dmaislin_splunk
Splunk Employee
Splunk Employee

Are you leveraging indexed_extractions = JSON or using another method?

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

0 Karma

splunker1981
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...