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!
Dashboards: Hiding charts while search is being executed and other uses for tokens
There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...
Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...
This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...
Brains, Bytes, and Boston: Learn from the Best at .conf25
When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...
