topic Re: json field extraction in Splunk Search

json field extraction

prasant — Tue, 20 Jul 2021 10:12:37 GMT

Hi Splunk Experts,

Below is a sample event, I have below spath msg.message.details, I am trying to extract certain fields from the details datapath. How can I extract 'msg.message.details' into fields?, I am still a newbie and learning on the go in splunk world, I am guessing to use rex, but is there a way using spath? Our index has structured other json paths eg:y has other spath eg:msg.message.header.correlationId, etc,

{ [-]
cf_app_id: test123
cf_app_name: test
event_type: LogMessage

job_index: ebcf8d13
message_type: OUT
msg: { [-]
level: INFO
logger: UpdateContact
message: { [-]
details: Data{SystemId='null', language='English', parentSourceSystemAction='null', contactId='cf4cae75-28b3', status='Active', birthDate='1991-01-15', eventAction='Create', Accounts=[CustomerAccounts{ Case='000899', accountid='4DA4F29E', contactRelationship=ContactRelationship{expiryDate='', contactType='owner', endDate=''}}],workContact=WorkContact{faxNumber='null', mobileNumber='null', emailAddress='null', phoneNumber='null'},homeContact=HomeContact{faxNumber='null', mobileNumber='null', emailAddress='', phoneNumber='null'},businessAddress=null,personalAddress=[PersonalAddress{addressId='9205', locality='PARK', internationalPostCode='null', internationalState='null', additionalInfo='null', isPrimary='Y', streetNumberStart='null', addressType='null', status='CO', streetNumberStartSuffix='null', postalCode='765', streetNumberEnd='null', streetName='null', country='null', streetNumberEndSuffix='null', streetType='null', state='null', subAddress=SubAddress{buildingName='null', numberStart='null', addressLines=[MIL PDE,], details=[Details{value='null', detailType='null'}, Details{value='null', detailType='null'}]}}],idv=Identification{doc=License{state='null', number='null'}}}
header: { [-]
correlationId: 707000J-52f6-10df-00f3-f859-1c5ed
entityId: cf75-2b3-cb38-cef-a72ad88
entityName: test
errorCode: null
errorMessage: null
eventName: testevent
processName: process1
processStatus: SUCCESS
serviceName: testservice
serviceType: Dispatch
}
}

timestamp: 2021-07-20
}
origin: rep
timestamp: 1626764261880766200
}

Any help is much appreciated.

Thanks

Re: json field extraction

kamlesh_vaghela — Tue, 20 Jul 2021 10:22:36 GMT

@prasant

Can you please share your sample event in code format? Just use </> tool from toolbar.

Re: json field extraction

prasant — Tue, 20 Jul 2021 10:43:39 GMT

Hi Kamlesh, Appriciate your prompt response. Hope below format helps, this is sample format, the structure is pretty much same as our actual event. Thanks { [-] cf_app_id: test123 cf_app_name: test event_type: LogMessage job_index: ebcf8d13 message_type: OUT msg: { [-] level: INFO logger: UpdateContact message: { [-] details: Data{SystemId='null', language='English', parentSourceSystemAction='null', contactId='cf4cae75-28b3', status='Active', birthDate='1991-01-15', eventAction='Create', Accounts=[CustomerAccounts{ Case='000899', accountid='4DA4F29E', contactRelationship=ContactRelationship{expiryDate='', contactType='owner', endDate=''}}],workContact=WorkContact{faxNumber='null', mobileNumber='null', emailAddress='null', phoneNumber='null'},homeContact=HomeContact{faxNumber='null', mobileNumber='null', emailAddress='', phoneNumber='null'},businessAddress=null,personalAddress=[PersonalAddress{addressId='9205', locality='PARK', internationalPostCode='null', internationalState='null', additionalInfo='null', isPrimary='Y', streetNumberStart='null', addressType='null', status='CO', streetNumberStartSuffix='null', postalCode='765', streetNumberEnd='null', streetName='null', country='null', streetNumberEndSuffix='null', streetType='null', state='null', subAddress=SubAddress{buildingName='null', numberStart='null', addressLines=[MIL PDE,], details=[Details{value='null', detailType='null'}, Details{value='null', detailType='null'}]}}],identification=Identification{driverLicense=DriverLicense{state='null', number='null'}}} header: { [-] correlationId: 707000J-52f6-10df-00f3-f859-1c5ed entityId: cf75-2b3-cb38-cef-a72ad88 entityName: test errorCode: null errorMessage: null eventName: testevent processName: process1 processStatus: SUCCESS serviceName: testservice serviceType: Dispatch } } timestamp: 2021-07-20 } origin: rep timestamp: 1626764261880766200 }

Re: json field extraction

kamlesh_vaghela — Tue, 20 Jul 2021 11:40:48 GMT

@prasant
Your shared JSON is invalid, may be due to you copied from the event bar. You suppose to provide _raw data.

Something like..

{"msg": {"message": {"details": "Data{SystemId='null', language='English', parentSourceSystemAction='null', contactId='cf4cae75-28b3', status='Active', birthDate='1991-01-15', eventAction='Create', Accounts=[CustomerAccounts{ Case='000899', accountid='4DA4F29E', contactRelationship=ContactRelationship{expiryDate='', contactType='owner', endDate=''}}],workContact=WorkContact{faxNumber='null', mobileNumber='null', emailAddress='null', phoneNumber='null'},homeContact=HomeContact{faxNumber='null', mobileNumber='null', emailAddress='', phoneNumber='null'},businessAddress=null,personalAddress=[PersonalAddress{addressId='9205', locality='PARK', internationalPostCode='null', internationalState='null', additionalInfo='null', isPrimary='Y', streetNumberStart='null', addressType='null', status='CO', streetNumberStartSuffix='null', postalCode='765', streetNumberEnd='null', streetName='null', country='null', streetNumberEndSuffix='null', streetType='null', state='null', subAddress=SubAddress{buildingName='null', numberStart='null', addressLines=[MIL PDE,], details=[Details{value='null', detailType='null'}, Details{value='null', detailType='null'}]}}],identification=Identification{driverLicense=DriverLicense{state='null', number='null'}}}header: { [-]correlationId: 707000J-52f6-10df-00f3-f859-1c5edentityId: cf75-2b3-cb38-cef-a72ad88entityName: testerrorCode: nullerrorMessage: nulleventName: testeventprocessName: process1processStatus: SUCCESSserviceName: testserviceserviceType: Dispatch}}"}}}

Well, Your data looks like representation of any Programming Objects. So may be we need to extract thise fields using regex. Can you please share fields names which you expecting?

Meanwhile you can try this?

YOUR_SEARCH | spath path=msg.message.details output=details | eval _raw=details | extract pairdelim="\"{,}" kvdelim=":" | table *

Re: json field extraction

prasant — Wed, 21 Jul 2021 12:25:42 GMT

Thanks a lot @kamlesh_vaghela now I am able to get the important fields with kvdelim and pairdelim to parse the required key value pairs.