All Apps and Add-ons

Unable to extract mixed json from the splunk log

Path Finder

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.

0 Karma

Motivator

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

0 Karma

Path Finder

@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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!