topic Re: How to show two json response in single field? in Splunk Search

How to show two json response in single field?

karthi2809 — Mon, 01 Apr 2024 16:50:30 GMT

Hi Guys,

I want to show two field values into single column in a table .query and sample logs given below.

index="mulesoft" applicationName="api" |spath content.payload{} |mvexpand content.payload{}| transaction correlationId | rename "content.payload{}.AP Import flow processing results{}.requestID" as RequestID "content.payload{}.GL Import flow processing results{}.impConReqId" as ImpConReqId content.payload{} as response | eval OracleRequestId="RequestID: ".RequestID." ImpConReqId: ".ImpConReqId|table response OracleRequestId

Actual Result

response	OracleRequestId
GL Import flow related results : Extract has no GL records to Import into Oracle { "AP Import flow processing results" : [ { "concurBatchId" : "395", "requestID" : "101660728", "returnMessage" : null, "returnStatus" : "SUCCESS", "insertCount" : "72", "fileName" : "CONCUR_APAC_APINV_1711965640.csv" } ] }
{ "AP Import flow processing results" : [ { "concurBatchId" : "393", "requestID" : "101572722", "returnMessage" : null, "returnStatus" : "SUCCESS", "insertCount" : "66", "fileName" : "CONCUR_APAC_APINV_1711620043.csv" } ] } { "GL Import flow processing results" : [ { "concurBatchId" : "393", "batchId" : "6409", "count" : "5", "impConReqId" : "101572713", "errorMessage" : null, "filename" : "CONCUR_APAC_GLJE_51711620043.csv" } ] }	RequestID: 101572722 ImpConReqId: 101572713

Expected Result:

response	OracleRequestId
GL Import flow related results : Extract has no GL records to Import into Oracle { "AP Import flow processing results" : [ { "concurBatchId" : "395", "requestID" : "101660728", "returnMessage" : null, "returnStatus" : "SUCCESS", "insertCount" : "72", "fileName" : "CONCUR_APAC_APINV_1711965640.csv" } ] }	requestID:101660728
{ "AP Import flow processing results" : [ { "concurBatchId" : "393", "requestID" : "101572722", "returnMessage" : null, "returnStatus" : "SUCCESS", "insertCount" : "66", "fileName" : "CONCUR_APAC_APINV_1711620043.csv" } ] } { "GL Import flow processing results" : [ { "concurBatchId" : "393", "batchId" : "6409", "count" : "5", "impConReqId" : "101572713", "errorMessage" : null, "filename" : "CONCUR_APAC_GLJE_51711620043.csv" } ] }	RequestID: 101572722 ImpConReqId: 101572713

Re: How to show two json response in single field?

ITWhisperer — Mon, 01 Apr 2024 17:27:14 GMT

OracleRequestId is empty because your eval doesn't take into account that ImpConReqId is not present in some of your events - either use fillnull on this field or modify the eval to take care when this field is null

Re: How to show two json response in single field?

karthi2809 — Tue, 02 Apr 2024 10:27:59 GMT

Hi @ITWhisperer

Yes its working i used isnull before the field values its working .But in below senario its not showing any values. Out of three there are two null values in impConReqId.so its not showing any values in table

AP Import flow related results : Extract has no AP records to Import into Oracle { "GL Import flow processing results" : [ { "concurBatchId" : "463", "batchId" : "6393", "count" : "1000", "impConReqId" : null, "errorMessage" : null, "filename" : "81711505038.csv" }, { "concurBatchId" : "463", "batchId" : "6393", "count" : "1000", "impConReqId" : null, "errorMessage" : null, "filename" : "11505038.csv" }, { "concurBatchId" : "463", "batchId" : "6393", "count" : "1000", "impConReqId" : null, "errorMessage" : null, "filename" : "CONCUR_GLJE_37681711505038.csv" }, { "concurBatchId" : "463", "batchId" : "6393", "count" : "768", "impConReqId" : "101539554", "errorMessage" : null, "filename" : "711505038.csv" } ] }

Re: How to show two json response in single field?

ITWhisperer — Tue, 02 Apr 2024 10:31:09 GMT

What is your current search?

Re: How to show two json response in single field?

karthi2809 — Tue, 02 Apr 2024 10:42:24 GMT

index="mulesoft" applicationName="api" |spath content.payload{} |mvexpand content.payload{}| transaction correlationId | rename "content.payload{}.AP Import flow processing results{}.requestID" as RequestID "content.payload{}.GL Import flow processing results{}.impConReqId" as ImpConReqId content.payload{} as response |eval OracleRequestId="RequestID: ".if(isnull(RequestID),0,RequestID)." ImpConReqId: ".if(isnull(ImpConReqId),0,ImpConReqId)|table OracleRequestId response

Re: How to show two json response in single field?

ITWhisperer — Tue, 02 Apr 2024 10:35:12 GMT

What is the full search?

Re: How to show two json response in single field?

karthi2809 — Tue, 02 Apr 2024 10:43:15 GMT

index="mulesoft" applicationName="api" |spath content.payload{} |mvexpand content.payload{}| transaction correlationId | rename "content.payload{}.AP Import flow processing results{}.requestID" as RequestID "content.payload{}.GL Import flow processing results{}.impConReqId" as ImpConReqId content.payload{} as response |eval OracleRequestId="RequestID: ".if(isnull(RequestID),0,RequestID)." ImpConReqId: ".if(isnull(ImpConReqId),0,ImpConReqId)|table OracleRequestId response

Re: How to show two json response in single field?

ITWhisperer — Tue, 02 Apr 2024 12:41:54 GMT

The fields are probably multivalue fields for some of your transactions which is why the eval is not working. You should probably start again with your events and work out how to break them up into separate parts so that you can create the composite id, in other words, to get where you want to be, don't start from where you are, you need to go back some steps.