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
- :
- How do I extract the JSON type data from this fiel...
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!
See your relevant APM services, dashboards, and alerts in one place with the updated ...
As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...
Index This | What goes away as soon as you talk about it?
May 2025 Edition
Hayyy Splunk Education Enthusiasts and the Eternally Curious!
We’re back with this month’s ...
What's New in Splunk Observability Cloud and Splunk AppDynamics - May 2025
This month, we’re delivering several new innovations in Splunk Observability Cloud and Splunk AppDynamics ...
