Splunk Search

How to extract Json field having key value pairs delimited by pipe character?

BK_MSP
New Member

I had data like this in Splunk.

DT=2023-09-13T23:59:56.029-0500|LogId=WFTxLog|AppId=SWBS|AppInst=server1:/apps/comp/swbs/instances/99912|TId=executorWithCallerRunsPolicy-30|ProcId=dc47cf25-2318-4f61-bd33-10f8928916ce|RefMsgNm=creditDecisionInformation2011_02|MsgId=fc1935b6-06c0-42bb-89d1-caf1076fff68|SrcApp=XX|ViewPrefs=CUSTOMER_202108-customerDetailIndicator,customerRelationshipDailyUpdateIndicator,customerRelationshipMonthlyUpdateIndicator,customerRelationshipSummaryIndicator,customerRiskSummaryIndicator~ACCOUNT_201505-accountOverLimitIndicator,creditChargeoffIndicator,creditDelinquencyIndicator,creditLineDecreaseIndicator,creditLineRestrictionIndicator,creditProductNSFIndicator,depositClosedForCauseIndicator,depositHardHoldIndicator,forcedClosedIndicator,nonExpansionQueueIndicator,outOfBoundIndicator,overdraftOnDepositIndicator,returnedDepositItemsIndicator,disasterReliefIndicator~APPLICANT_201111-applicationDetailIndicator,incomeDetailIndicator~MULTIPLE_ACCOUNT_CUSTOMER-riskRecommendationIndicator~CUSTOMER_CLI_201705-customerCLIDataIndicator|TxAct=Response Received|DestId=EIW|MsgNm=customerRltnshipMonthlySummaryTaxId|Elapsed=199|AppBody=SOR_RESP|

I had a query like this.

index=wf_pvsi_other wf_id=swbs wf_env=prod sourcetype="wf:swbs:profiling:txt" AppBody=SOR_RESP DestId=EIW | table SrcApp, SubApp, RefMsgNm, DestId, MsgNm | fillnull value=NA SubApp | top SrcApp, SubApp, RefMsgNm, DestId, MsgNm limit=100 | rename SrcApp as Channel, SubApp as "Sub Application", RefMsgNm as Message, DestId as SOR, MsgNm as "SOR Message" | fields Channel, "Sub Application", Message,SOR,"SOR Message",count | sort Channel,"Sub Application", Message,SOR, "SOR Message", count

Now, my app moved to cloud and the log data is embedded within JSON and event show up in Splunk like this. Application specific log data is present as a value of JSON field "msg".

{"cf_app_id":"75390614-95dc-474e-ad63-2358769b0641","cf_app_name":"CA00000-app-uat","cf_org_id":"7cc80b1c-0453-4487-ba19-4e3ffc868cf3","cf_org_name":"CA00000-test","cf_space_id":"dd35773f-63cb-4ed3-8199-2ae2ff1331f8","cf_space_name":"uat","deployment":"cf-ab9ba4f5a0f082dfc130","event_type":"LogMessage","ip":"00.00.000.00","job":"diego_cell","job_index":"77731dca-f4e8-4079-a97d-b65f20911c53","message_type":"OUT","msg":"DT=2023-09-14T21:41:52.638-0500|LogId=WFTxLog|AppId=SWBS|AppInst=ff664658-a378-42f4-4a4d-2330:/home/vcap/app|TId=executorWithCallerRunsPolicy-7|ProcId=92d42ef2-7940-48e8-b4df-1f92790b657e|RefMsgNm=creditDecisionInformation2011_02|MsgId=8f5a46f8-7288-442f-9f56-ecaa05b345af|SrcApp=XX|ViewPrefs=CUSTOMER_201605-customerDetailIndicator,customerRiskSummaryIndicator~ACCOUNT_201505-accountOverLimitIndicator,creditChargeoffIndicator,creditDelinquencyIndicator,creditLineDecreaseIndicator,creditLineRestrictionIndicator,creditProductNSFIndicator,depositClosedForCauseIndicator,depositHardHoldIndicator,forcedClosedIndicator,foreclosureIndicator,loanModifcationPrgrmIndicator,nonExpansionQueueIndicator,outOfBoundIndicator,overdraftOnDepositIndicator,returnedDepositItemsIndicator,SSDILPrgrmIndicator|TxAct=Response Received|DestId=EIW|MsgNm=customerRiskSummaryTaxId|Elapsed=259|AppBody=SOR_RESP|","origin":"rep","source_instance":"0","source_type":"APP/PROC/WEB","tags":{"instance_id":"0","process_instance_id":"ff664658-a378-42f4-4a4d-2330","space_id":"dd35773f-63cb-4ed3-8199-2ae2ff1331f8","space_name":"uat"},"timestamp":1694745712641480200}

I want Splunk query output to present like earlier. How to do this? Any suggestions?

 

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

I assume that Splunk already gives you msg as a field.  You can then use extract on it.

index=wf_pvsi_other wf_id=swbs wf_env=prod sourcetype="wf:swbs:profiling:txt"
| rename msg as _raw
| extract
| search AppBody=SOR_RESP DestId=EIW
| table SrcApp, SubApp, RefMsgNm, DestId, MsgNm
| fillnull value=NA SubApp
| top SrcApp, SubApp, RefMsgNm, DestId, MsgNm limit=100
| rename SrcApp as Channel, SubApp as "Sub Application", RefMsgNm as Message, DestId as SOR, MsgNm as "SOR Message"
| fields Channel, "Sub Application", Message,SOR,"SOR Message",count
| sort Channel,"Sub Application", Message,SOR, "SOR Message", count

(As your new source is JSON, overriding _raw should be fine.)  Hope this helps.

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...