Getting Data In

Splunk Query to extract data from Json with escape characters

Hema003
Engager

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 StatusPlease ResendPackage codeFor amountReference Value
Value card from package was successfully issued but no Guest email was providedN/APC0J0$476.01843667077

 

 

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

Labels (2)
0 Karma

Hema003
Engager

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.

Hema003_0-1638525233340.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Hema003
Engager

i have tried still no luck..

Hema003_0-1638526136480.png

 

0 Karma

Hema003
Engager
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Hema003
Engager

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

 

Hema003_0-1638528921319.png

 

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 Guests 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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| rex field=_raw "commentText\":\"(?<PackageStatus>[^,]+), please resend - (?<PackageResend>[^,]+), Package code - (?<PackageCode>[^,]+), For amount - (?<Amount>[^\"]+)" 
| rex field=_raw "referenceValue\":(?<referenceValue>\d+)"
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...