Splunk Query to extract data from Json with escape characters

[Hema003]

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

[Hema003]

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.

[ITWhisperer]

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+)"

[Hema003]

i have tried still no luck..

[Hema003]

@ITWhisperer 

[ITWhisperer]

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+)"

[Hema003]

@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:

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\":1844634700}],\"auditDetails\":null}]}

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

[ITWhisperer]

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