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.
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
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
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>[^\"]+)" | ...
Are you leveraging indexed_extractions = JSON or using another method?
http://docs.splunk.com/Documentation/Splunk/6.5.0/Data/Extractfieldsfromfileswithstructureddata
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