topic Re: Splunk Query to extract data from Json with escape characters in Getting Data In

Splunk Query to extract data from Json with escape characters

Hema003 — Fri, 03 Dec 2021 06:44:27 GMT

Below is the part of log from which i need to extract data into tabular format in splunk dashboard.

Payload:{\"comments\":[{\"isActive\":true,\"sendToGSR\":false,\"confidential\":false,\"profileId\":197229,\"profileCode\":null,\"commentId\":null,\"commentText\"😕"Value card from package was successfully issued but no Guest email was provided, please resend - N/A, Package code - PC0J0 , For amount - $476.0\",\"commentType\":null,\"commentLevelEnum\"😕"TC\",\"externalReferences\":[{\"referenceType\"😕"TC\",\"referenceValue\":1843667077}],\"auditDetails\":null}]}

Expected output:

Package Status

Package Status	Please Resend	Package code	For amount	Reference Value
Value card from package was successfully issued but no Guest email was provided	N/A	PC0J0	$476.0	1843667077

My splunk query:

i tried for 2 columns, its displaying rows but not able to load data into table..

index=*wdpr_syw* source="*stage*" "reservation-fulfillment" "comments*" "package" "POST" Logger="com.disney.service.ioc.rest.OutboundRestRequestInterceptor" "Payload*" "externalReferences*" "referenceValue*" | rex field=_raw "commentText*: (?<PackageStatus>.*?\d+)," | rex field=_raw "referenceValue*:(?<referenceValue>.*?\d+),"| table PackageStatus,referenceValue

Re: Splunk Query to extract data from Json with escape characters

ITWhisperer — Fri, 03 Dec 2021 09:30:42 GMT

| rex field=_raw "commentText\":\"(?<PackageStatus>[^,]+), please resend - (?<PackageResend>[^,]+), Package code - (?<PackageCode>[^,]+), For amount - (?<Amount>[^\"]+)" | rex field=_raw "referenceValue\":(?<referenceValue>\d+)"

Re: Splunk Query to extract data from Json with escape characters

Hema003 — Fri, 03 Dec 2021 09:54:42 GMT

Thanks @ITWhisperer for the solution.. but still showing empty rows but there is no error . The query is working but somewhere i have missed something its not displaying data. its showing empty rows as given in the screenshot. Do we need to change any settings in splunk table? ..iam working on splunk for first time.

Re: Splunk Query to extract data from Json with escape characters

ITWhisperer — Fri, 03 Dec 2021 10:04:15 GMT

If the event data actually has the backslashes in, then you may need to cater for them

| rex field=_raw "commentText\\\":\\\"(?<PackageStatus>[^,]+), please resend - (?<PackageResend>[^,]+), Package code - (?<PackageCode>[^,]+), For amount - (?<Amount>[^\\\"]+)" | rex field=_raw "referenceValue\\\":(?<referenceValue>\d+)"

Re: Splunk Query to extract data from Json with escape characters

Hema003 — Fri, 03 Dec 2021 10:09:05 GMT

i have tried still no luck..

Re: Splunk Query to extract data from Json with escape characters

ITWhisperer — Fri, 03 Dec 2021 10:20:47 GMT

Try with another couple of backslashes (splunk is tricky when it comes to backslashes!)

| rex field=_raw "commentText\\\\\":\\\\\"(?<PackageStatus>[^,]+), please resend - (?<PackageResend>[^,]+), Package code - (?<PackageCode>[^,]+), For amount - (?<Amount>[^\\\\\"]+)" | rex field=_raw "referenceValue\\\\\":(?<referenceValue>\d+)"

Re: Splunk Query to extract data from Json with escape characters

Hema003 — Fri, 03 Dec 2021 10:35:02 GMT

@ITWhisperer

Re: Splunk Query to extract data from Json with escape characters

Hema003 — Fri, 03 Dec 2021 11:05:50 GMT

@ITWhisperer

Thank you so much .. its working..

but PackageStatus is empty even if has value if other columns Packagecode,PleaseResend,amount are empty.. Do we have any condition to display Packagestatus values if not empty even if other fields present or not present.

Please find the below screenshot and logs with all fields present and some with only packagestatus in commentText Section.

Event logs scenario 1 with all fields in commentText:

Scenario 2 without packagecode,amount,please resend fields in commentText:

Payload:{\"comments\":[{\"isActive\":true,\"sendToGSR\":false,\"confidential\":false,\"profileId\":197230,\"profileCode\":null,\"commentId\":null,\"commentText\"😕"Value card from package was unsuccessful in issuing - Generic Email Error\",\"commentType\":null,\"commentLevelEnum\"😕"TC\",\"externalReferences\":[{\"referenceType\"😕"TC\",\"referenceValue\":1844634026}],\"auditDetails\":null}]}

Scenario 3: All fields are present in commentText except 'Please Resend' field

Payload:{\"comments\":[{\"isActive\":true,\"sendToGSR\":false,\"confidential\":false,\"profileId\":197228,\"profileCode\":null,\"commentId\":null,\"commentText\"😕"Value card from package was successfully sent to Guest’s email - WDP************************, Package code - PC0J0 , For amount - $476.0\",\"commentType\":null,\"commentLevelEnum\"😕"TC\",\"externalReferences\":[{\"referenceType\"😕"TC\",\"referenceValue\":1843819496}],\"auditDetails\":null}]}

Finally, i want to display all the column values which are present and other columns as empty if they are not present in event logs