How to use the spath command correctly to create a pie chart divided by type of errors received?

[zeroCalm]

Hello all,

I have been trying to use the spath command correctly to create a pie chart divided by type of errors received. When I create the search and click on the object, the field where the error message is, it's in JSON format. Looks like this:

/XXX/projects/Sales_PostSales_processPostSaleOrder_VIP_CCT:{  
   "Service":"Enterprise Sales",
   "Date":"09/11/2017 10:48:35.947",
   "Environment":"XXX",
   "Debug":"Error",
   "Source":"PostSalesIntegration",
   "Description":"Error::processPostSaleOrder_VIP_CCT. Error occurred while trying to process the message. {resultType=FAILED, postsalesId=null, message=Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1, contactId=null, accountId=null}",
   "Message_Unique_Id":null,
   "Message_qualifier":null,
   "JMSMessageID":null,
   "Detail":{  
      "error":{  
         "message":{  
            "resultType":"FAILED",
            "postsalesId":null,
            "message":"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1",
            "contactId":null,
            "accountId":null
         },
         "reason":null,
         "resolution":null
      }
   },
   "stacktrace":{  
      "tracking":null,
      "error":{  
         "resultType":"FAILED",
         "postsalesId":null,
         "message":"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1",
         "contactId":null,
         "accountId":null
      }
   }
}

and the field for this object is titled "msg".

The specific error message I want is in Detail.message.

Right now I have:

index="ips_snaplogic""postsales" lvl="ERROR"| spath| timechart by "msg.Detail.error.message.message"

I'm just not getting anywhere with this. I hope my question is clear. Can anyone help?

Thank you

[woodcock · Accepted solution]

I see 2 problems. First, spath is not working because it doesn't see clear XML or JSON. Once we fix that, it still won't work because you have not given timechart an aggregation function. Try this:

|makeresults | eval _raw="/XXX/projects/Sales_PostSales_processPostSaleOrder_VIP_CCT:{  
    \"Service\":\"Enterprise Sales\",
    \"Date\":\"09/11/2017 10:48:35.947\",
    \"Environment\":\"XXX\",
    \"Debug\":\"Error\",
    \"Source\":\"PostSalesIntegration\",
    \"Description\":\"Error::processPostSaleOrder_VIP_CCT. Error occurred while trying to process the message. {resultType=FAILED, postsalesId=null, message=Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1, contactId=null, accountId=null}\",
    \"Message_Unique_Id\":null,
    \"Message_qualifier\":null,
    \"JMSMessageID\":null,
    \"Detail\":{  
       \"error\":{  
          \"message\":{  
             \"resultType\":\"FAILED\",
             \"postsalesId\":null,
             \"message\":\"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1\",
             \"contactId\":null,
             \"accountId\":null
          },
          \"reason\":null,
          \"resolution\":null
       }
    },
    \"stacktrace\":{  
       \"tracking\":null,
       \"error\":{  
          \"resultType\":\"FAILED\",
          \"postsalesId\":null,
          \"message\":\"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1\",
          \"contactId\":null,
          \"accountId\":null
       }
    }
 }"

 | rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

 | rex mode=sed "s/.*{/{/"
 | spath output=msg path=Detail.error.message.message
 | chart count BY msg

[zeroCalm]

Thanks for responding, this has gotten me much closer and this is helping me to understand the correct usage of spath and rex. THe piechart is showing up, but I don't quite understand what I'm getting. THe pieces of the pie seem to be divided and labeled by time or date (depending on search time period). I'm looking to replace that with the actual Detail.error.message.message. Is this possible?

Thank you,

I have attached screenshots.

https://imgur.com/AGf3Sgb

https://imgur.com/bya90Us

[woodcock]

Oh, you need a pie chart! Then change timechart to just chart and you should be good to go.

[zeroCalm]

@DalJeanis - When using "chart" as opposed to "timechart", I receive a No Results Found. This is on the same search criteria.

index="ips_snaplogic""postsales" lvl="ERROR"| spath| rex mode=sed "s/.*{/{/"
| spath output=msg path=Detail.error.message.message
| chart count BY msg

---

Also, when I use the " | table msg | chart count by msg" . - I get a message instructing me to use verbose. This gives me Events, but no Chart says "No results found."

index="ips_snaplogic""postsales" lvl="ERROR"| spath| rex mode=sed "s/.*{/{/"
| spath output=msg path=Detail.error.message.message
| table msg | chart count by msg

I've looked around, but I see no reason why I should receive these messages.

Thanks again for everything.

[zeroCalm]

Thanks again for the response. Is there any reason I would get results for this search:

index="ips_snaplogic""postsales" lvl="ERROR"| spath| rex mode=sed "s/.*{/{/"
| spath output=msg path=Detail.error.message.message
| timechart count by msg

...but when I change timechart to chart I get "No results found"?

I can't find anything. I've been looking for any thing on chart, but found no reason for this.

Thanks again

[mallika_reddy_]

Hey @zeroCalm 

One of the reason why the results are coming for count and not for timechart is because the timechart must not be pointing to correct field for _time. You can override it by converting your time field to splunk time format and pass to _time.

Ex: eval formatted_time =strptime(you_time_field_value,"%Y-%m-%dT%H:%M:%S") | eval _time = formatted_time

Posting it as it might help others 🙂

[DalJeanis]

@zerocalm - Start off by NOT feeding it to a pie chart. Just look at the data that is left after you extract the JSON.

Cut the data down to ONLY whatever you want the pie chart to show. If you are wanting the count by msg, then all you need is |table msg then | chart count by msg.

Okay, |table msg is redundant if it's immediately followed by that chart command, but I'm teaching a thought process here. Look at the data. Format the data. Get rid of everything that isn't the data you want.

---

In this case, the message is crazy long to put in a pie chart. In the example data, which of these is your error message?

Upsert failed. 

Error Message: Upsert failed. 

Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING

Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\n

---

Use this to look at the top 5 messages and see what part of them you really need.

| stats count by msg
| sort 5 - count

or its equivalent

| top 5 msg

Figure out what part of message you want to use as the title for the pie slice, then we can get that extracted and finish your viz.

[DalJeanis]

Seems like the path in front of the first open curly brace {, and or possibly the carriage returns, might be confusing the spath command. Here's a working run-anywhere example after we pulled out the carriage returns and used a rex, shown here, to extract the portion of the _raw that is actually a valid JSON.

This example should help you to figure out how to make yours work.

| makeresults | eval _raw="/XXX/projects/Sales_PostSales_processPostSaleOrder_VIP_CCT:{    \"Service\":\"Enterprise Sales\",    \"Date\":\"09/11/2017 10:48:35.947\",    \"Environment\":\"XXX\",    \"Debug\":\"Error\",  \"Source\":\"PostSalesIntegration\",    \"Description\":\"Error::processPostSaleOrder_VIP_CCT. Error occurred while trying to process the message. {resultType=FAILED, postsalesId=null, message=Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1, contactId=null, accountId=null}\",    \"Message_Unique_Id\":null,    \"Message_qualifier\":null,    \"JMSMessageID\":null,    \"Detail\":{         \"error\":{            \"message\":{               \"resultType\":\"FAILED\",             \"postsalesId\":null,            \"message\":\"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1\",             \"contactId\":null,            \"accountId\":null          },          \"reason\":null,          \"resolution\":null       }    },    \"stacktrace\":{         \"tracking\":null,       \"error\":{            \"resultType\":\"FAILED\",          \"postsalesId\":null,          \"message\":\"Error Message: Upsert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [LastName]: [LastName]\n\nError Stack Trace: Class.CctDataloadServices.processRestRequest: line 239, column 1\nClass.CctDataloadServices.ProcessRestRequest.processRequest: line 62, column 1\nClass.CommonService.executeRequest: line 118, column 1\nClass.CommonService.doPost: line 37, column 1\",          \"contactId\":null,          \"accountId\":null       }    } }"
| rename COMMENT as "The above just enters test data"

| rex field=_raw "^(?<myJunk>[^{]+)(?<myJSON>.*)"
| spath input=myJSON output=msg path=Detail.error.message.message