Dashboards & Visualizations

Use the spath command correctly to create a pie chart divided by type of errors received

zeroCalm
New Member

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

zeroCalm
New Member

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

zeroCalm
New Member

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

0 Karma

zeroCalm
New Member

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...