Dashboards & Visualizations

How to 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

Labels (1)
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

mallika_reddy_
New Member

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 🙂

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...