How to show two json response in single field?

[karthi2809]

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

[ITWhisperer · Accepted solution]

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

[karthi2809]

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"
      } ]
    }

[ITWhisperer]

What is your current search?

[karthi2809]

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

[ITWhisperer]

What is the full search?

[karthi2809]

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

[ITWhisperer]

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.