Knowledge Management
×
Are you a member of the Splunk Community?
Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
- Find Answers
- :
- Splunk Administration
- :
- Admin Other
- :
- Knowledge Management
- :
- Re: How do I extract the JSON type data from this ...
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How do I extract the JSON type data from this field?
tkwaller1
Path Finder
08-02-2023
09:32 AM
Hello
I have some array data located within a field in my data. It comes from DBConnect and isnt exactly JSON. Im trying to figure out how to extract this data so I can make it useful but Ive gone through several iterations and have been unsuccessful so far.
Hers a sample record(I picked a fairly long one for an example):
2023-08-02 08:23:28.000, CASE_ID="50031iIQAQ", STAGE="Initial", EVENT_TYPE="SS", SUBMISSION_DATE="2023-08-02 06:23:23.0", SUBMISSION_TYPE="Application INITIAL SUBMISSION", DESTINATION=""Application ", SUBMISSION_DATA="{
"submissionType": "Application INITIAL SUBMISSION",
"submissionDate": "2023-08-02 06:23:23",
"stage": "Initial",
"repository": "central",
"xxxxMessage": {
"header": {
"timeStamp": "2023-08-02 06:23:24",
"source": "XXXXX",
"messageType": "XXX CCC SSS",
"domain": "APPLICATION/SUBMISSION"
},
"body": {
"submissionUnit": {
"field": [
{
"value": "500871iIQAQ",
"name": "sourceId"
},
{
"value": "CCC VVVV",
"name": "submission_unit_type"
},
{
"value": "1",
"name": "submission_unit_number"
},
{
"value": "2023-08-02 06:23:23",
"name": "submit_date"
},
{
"value": "2023-08-02",
"name": "xxxx_received_date"
}
]
},
"submission": {
"field": [
{
"value": "5000871iIQAQ",
"name": "sourceId"
},
{
"value": "00132546",
"name": "submission_event_id"
},
{
"value": "XXXX",
"name": "submission_type"
},
{
"value": "1",
"name": "submission_number"
},
{
"value": "Pending",
"name": "submission_status"
},
{
"value": "2023-08-02",
"name": "submission_status_effective_date"
},
{
"value": "TTTT 1",
"name": "xxxxx_xxxxxx"
},
{
"value": "String of data goes here",
"name": "proposed_change"
}
]
},
"referencedMonographs": {
"referencedMonograph": [
{
"field": [
{
"value": "a001YmysUAC",
"name": "sourceId"
},
{
"value": "XXX2",
"name": "ref_monograph_number"
},
{
"value": " String of data goes here",
"name": "ref_monograph_description"
}
]
}
]
},
"organizations": {
"organization": [
{
"field": [
{
"value": "a07000nMzDQAU",
"name": "sourceId"
},
{
"value": "String of data goes here",
"name": "organization_name"
},
{
"value": "117185493",
"name": "xxxx_number"
},
{
"value": "12071",
"name": "company_global_id"
},
{
"value": "Requestor",
"name": "contact_type"
},
{
"value": "String of data goes here",
"name": "address_line1"
},
{
"value": "CityName",
"name": "city"
},
{
"value": "US",
"name": "country"
},
{
"value": "XX",
"name": "state"
},
{
"value": "XXXXX-AAAA",
"name": "postal_code"
}
],
"contact": {
"field": [
{
"value": "a07nMzDQAU",
"name": "sourceId"
},
{
"value": "Requestor",
"name": "contact_type"
},
{
"value": "XXXX",
"name": "first_name"
},
{
"value": "CCCC",
"name": "last_name"
},
{
"value": "2221112222",
"name": "phone_number"
},
{
"value": "test@test.com",
"name": "email_address"
},
{
"value": "1111 NW 1st St",
"name": "address_line1"
},
{
"value": "CCCCC",
"name": "city"
},
{
"value": "United States",
"name": "country"
},
{
"value": "CD",
"name": "state"
},
{
"value": "11111",
"name": "postal_code"
}
]
}
}
]
},
"ingredients": {
"ingredient": [
{
"field": [
{
"value": "a041R8IbQAK",
"name": "sourceId"
},
{
"value": "XXXXXXXXX",
"name": "sssss_aaaaaa"
},
{
"value": "U8LYN0Y118",
"name": "CCCC"
},
{
"value": "311218848",
"name": "wwwwww_global_id"
},
{
"value": "String of data goes here",
"name": "XXXXX_strength"
},
{
"value": "XX",
"name": "numerator_unit"
},
{
"value": "12.00",
"name": "numerator_strength"
},
{
"value": "1",
"name": "denominator_unit"
},
{
"value": "1.00000",
"name": "denominator_strength"
},
{
"value": "40",
"name": "CCCCCC"
},
{
"value": "Month",
"name": "xxxxx_frequency"
},
{
"value": "String of data goes here",
"name": "age_group"
},
{
"value": "String of data goes here",
"name": "xxxxx_form"
},
{
"value": "xxxxx",
"name": "xxx_xxxx_xxx"
},
{
"value": "xxxxxx/ccccc",
"name": "xxxxx_class"
},
{
"value": "0117984AB",
"name": "xxxxx_xxxxx"
},
{
"value": "U8LY118",
"name": "xxxxx_value"
},
{
"value": "xx",
"name": "xxxxx_type"
}
]
}
]
},
"xxxx_event_id": "00132542",
"contacts": {
"contact": [
{
"field": [
{
"value": "a070nMzEQAU",
"name": "sourceId"
},
{
"value": "String of data goes here",
"name": "contact_type"
},
{
"value": "XXXXXXXX",
"name": "first_name"
},
{
"value": "CCCCCCCC",
"name": "last_name"
},
{
"value": "+12223334444",
"name": "phone_number"
},
{
"value": "test@test.com",
"name": "email_address"
},
{
"value": "321 Drive",
"name": "address_line1"
},
{
"value": "XXXXXXXXX",
"name": "city"
},
{
"value": "United States",
"name": "country"
},
{
"value": "VVVVVVV",
"name": "state"
},
{
"value": "11111",
"name": "postal_code"
}
]
}
]
},
"attachment_metadata": {
"total_attachment_count": 1,
"application_attachment": {
"type": "application",
"submission_attachment": {
"sub_submission": [
{
"name": "String of data goes here",
"attachment_count": 1
}
],
"name": "String of data goes here"
},
"name": "CCCCC"
}
},
"application": {
"referencedMonographs": {
"referencedMonograph": [
{
"field": [
{
"value": "a0Z3S000001YmynUAC",
"name": "sourceId"
},
{
"value": "M009",
"name": "ref_monograph_number"
},
{
"value": " String of data goes here",
"name": "ref_monograph_description"
}
]
}
]
},
"field": [
{
"value": "5003872puQAA",
"name": "sourceId"
},
{
"value": "XXXXX",
"name": "application_type"
},
{
"value": "12345678",
"name": "application_number"
},
{
"value": "Pending",
"name": "application_status"
},
{
"value": "2023-08-02",
"name": "application_status_effective_date"
},
{
"value": "Requestor",
"name": "requestor_role"
},
{
"value": "test1",
"name": "application_justification"
}
]
}
}
},
"eventType": "WE",
"documentDestination": "Applications",
"caseID": "5003871iIQAQ",
"attachments": [
{
"type": "docx",
"storageLocation": "/XXXX/Data Stored HEre",
"processedInd": "N",
"name": "Test 3.docx",
"fileSize": "11885",
"fileId": "0683S000001o43hQAA",
"file_metadata": [
{
"value": "String of data goes here",
"key": "docCategory"
},
{
"value": "1.1",
"key": "sectionNumber"
},
{
"value": "Table of Contents",
"key": "sectionName"
},
{
"value": "5003iIQAQ",
"key": "sourceId"
}
],
"contentVersionId": "xxx/cccc/dddddd/0693hQAM/0683001QAA/Test 3.docx"
}
]
}", XXXX_MESSAGE_ID="7eewrty6e9-00ea-4e58-981f-3cn56igb82f", CREATED_BY="XXXX_CCC_APP", CREATED_DATETIME="2023-08-02 09:23:25", MODIFIED_BY="XXXX_CCC_APP", MODIFIED_DATETIME="2023-08-02 09:23:28", PROCESSED_STATUS="success"
Anyone have any ideas how I might accomplish getting the SUBMISSION_DATA field extracted properly?
Thanks for the help
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
gcusello
SplunkTrust
08-02-2023
09:48 AM
Hi @tkwaller1 ,
did you already tried to use "INDEXED_EXTRACTIONS = json" in the props.conf and eventually spath command?
usually spath extract all the fields, if both these solutions doesn't work, the only solution is some regexes, but it's a long job!
Ciao.
Giuseppe
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
tkwaller1
Path Finder
08-02-2023
10:14 AM
I did think about that however, theres only a single field that has the JSON data in it. So when I set that value it wont work properly as the rest of the fields in the log record arent JSON
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
richgalloway
SplunkTrust
08-02-2023
11:48 AM
Here's a way that uses rex, but it's a bit of a hack since it assumes what will follow the JSON field. Perhaps someone will have a more JSON-native way to do it.
| rex "SUBMISSION_DATA\s*=\s*\\\"(?<SUBMISSION_DATA>[\s\S]*)\\\", XXXX"
| eval foo=json_extract(SUBMISSION_DATA)
---
If this reply helps you, Karma would be appreciated.
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!
Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...
This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...
Elevate Your Organization with Splunk’s Next Platform Evolution
Thursday, July 10, 2025 | 11AM PDT / 2PM EDT
Whether you're managing complex deployments or looking to ...
Splunk Answers Content Calendar, June Edition
Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...
