Unable to extract mixed json from the splunk log

[karthi25]

I want to extract the mixed json object from the splunk log, my logs are in the following format:

2019-03-06 07:31:48 - {
  "Reference" : {
    "Success" : {
      "name" : "TEST",
      "type" : "type1",
      "payload" : {
        "endTime" : "2019-03-06 07:31:48",
        "level" : "INFO",
        "srcCount" : 0,
        "sucCount" : 0,
        "startTime" : "2019-03-06 07:31:38",
        "pubCount" : 9
      }
    }
  }
}

Now I want to get the result like:

 ....| stats sum(pubCount) as totalPubCount

I tried with the below query;

| rex field=_raw "(?msi)^\[[^=]+=(?.+)\]$"
 | spath input=my_json 
 | rename pubCount AS publishedCount 
 | stats sum(publishedCount) as totalPublishedCount

But, It is not working. Since am not that much familiar with the regex, can anyone please help me with the right solution.

[DMohn]

Have you tried using the spath command here?

<your base query> | spath output=extractedPubCount path=Reference.Success.payload.pubCount | stats sum(extractedPubCount) as totalPubCount

Reference here: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath

[karthi25]

@DMohn Thanks for your reply. Sorry I tried it before, it is not returning anything. My log contains datetime like "2019-03-06 07:31:48 - " before the json. So first we need to extract the json from the mixed log then we need to proceed with spath .Please correct me if am wrong.