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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...