topic separate json payload to separate events in Getting Data In

separate json payload to separate events

Esky73 — Thu, 21 Mar 2019 13:20:50 GMT

my search :

SEARCH...
| rex field=Message "^(?<Short>.*),\sRequestBody:\s(?<ShortMessage>[^\s]+)\".*$" 
| spath input=ShortMessage
| rename sapResponseData{}.contractAccounts{}.nameId AS NameId sapResponseData{}.contractAccounts{}.contractAccount AS ContractAccountNumber sapResponseData{}.contractAccounts{}.sapResponses{}.contractNumber AS ContractNumber sapResponseData{}.contractAccounts{}.sapResponses{}.messageId AS messageId sapResponseData{}.contractAccounts{}.sapResponses{}.messageNumber AS messageNumber sapResponseData{}.contractAccounts{}.sapResponses{}.dataset AS dataSet metadata.correlationId AS CorrelationId metadata.sendDate AS SendDate metadata.sendTime AS SendTime 
| lookup dataset_lookup.csv dataset AS dataSet OUTPUTNEW usage AS Usage
| table _time NameId ContractAccountNumber ContractNumber messageId messageNumber dataSet Usage CorrelationId SendDate SendTime

new field after split field:

{
  "sapResponseData": [
    {
      "responseOriginatedIn": "ERROR",
      "contractAccounts": [
        {
          "nameId": "ABC_999999999999999",
          "contractAccount": "888888888888",
          "sapResponses": [
            {
              "contractNumber": 999999999,
              "responseCode": "002",
              "messageId": "DEF456",
              "messageNumber": "031",
              "dataset": "002"
            },
            {
              "contractNumber": 999999999,
              "responseCode": "002",
              "messageId": "ABC123",
              "messageNumber": "094",
              "dataset": "001"
            }
          ]
        }
      ]
    }
  ],
  "metadata": {
    "correlationId": "Correlation_ID",
    "priority": "LOW",
    "sendDate": "2019-03-21",
    "sendTime": 224813
  }
}

currently sapResponses all appear in the same field in the table

How to create new events per sapResponse ? and keep metadata for all events ?

Re: separate json payload to separate events

coccyx — Thu, 21 Mar 2019 15:03:30 GMT

You can look into mvexpand with your JSON array to explode each event into multiples. As an alternative, Cribl allows you to pre-process events in the ingestion pipeline and we include a function called JSON Unroll which allows you to create an event per entry an a JSON array.

Re: separate json payload to separate events

somesoni2 — Thu, 21 Mar 2019 19:29:09 GMT

Give this a try

 SEARCH...
 | rex field=Message "^(?<Short>.*),\sRequestBody:\s(?<ShortMessage>[^\s]+)\".*$" 
 | spath input=ShortMessage
 | rename sapResponseData{}.contractAccounts{}.* AS *  sapResponseData{}.contractAccounts{}.sapResponses{}.*  metadata.* AS *
 | lookup dataset_lookup.csv dataset OUTPUTNEW usage AS Usage
 | table _time nameId contractAccount contractNumber messageId messageNumber dataset Usage correlationId sendDate sendTime
| eval temp=mvzip(contractNumber,mvzip(messageId,mvzip(messageNumber,dataset,"|"),"|"),"|") 
| mvexpand temp | rex field=temp "(?<contractNumber>]^\|]+)\|(?<messageId>]^\|]+)\|(?<messageNumber>]^\|]+)\|(?<dataset>]^\|]+)" | fields - temp

Re: separate json payload to separate events

Esky73 — Sun, 24 Mar 2019 11:49:33 GMT

Hi - thanks - i tried mvexpand initially but that gave me a duplicate event.

Re: separate json payload to separate events

Esky73 — Sun, 24 Mar 2019 12:05:24 GMT

Hi Thanks for answering - this has no effect :

i still have the following for contractNumber,messageId,messageNumber,dataset

contractNumber  messageId   messageNumber   dataset
123456          12345       0123            001
123456          67891       4567            002

Re: separate json payload to separate events

Esky73 — Sun, 24 Mar 2019 12:24:15 GMT

I think i figured it out with help from here :

https://answers.splunk.com/answers/25653/mvexpand-multiple-multi-value-fields.html

| rex field=Message "^(?<Short>.*),\sRequestBody:\s(?<ShortMessage>[^\s]+)\".*$" 
| spath input=ShortMessage 
| rename sapResponseData{}.contractAccounts{}.* AS * metadata.* AS * sapResponseData{}.contractAccounts{}.sapResponses{}.* AS * 
| eval reading=mvzip(contractNumber, messageId) 
| eval reading=mvzip(reading, dataset) 
| eval reading=mvzip(reading, messageNumber) 
| mvexpand reading 
| makemv reading delim="," 
| eval contractNumber=mvindex(reading, 0) 
| eval messageId=mvindex(reading, 1) 
| eval messageNumber=mvindex(reading, -1) 
| eval dataset=mvindex(reading, 2) 
| lookup dataset_lookup.csv dataset OUTPUTNEW usage AS Usage 
| table _time nameId contractAccount contractNumber messageId messageNumber dataset Usage correlationId sendDate sendTime

Re: separate json payload to separate events

woodcock — Sun, 24 Mar 2019 13:08:32 GMT

Click Accept on your answer to close the question.