topic Re: Can you help me create a pie chart that would display info from a CSV Lookup file? in Splunk Search

Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Tue, 29 Sep 2020 21:20:15 GMT

I created a .CSV file with error_code and Description. I am trying to compare error_code with the logs and create a pie chat that shows all the error descriptions. I tried ...

Index=my_index | [|inputlookup error.csv | fields error_code | rename error_code as query]

... it seems to find the right logs but, it’s not a giving stats count by error_code.
Thanks!

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

Vijeta — Tue, 29 Sep 2020 21:20:28 GMT

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Thu, 20 Sep 2018 16:37:17 GMT

I tried that it’s not getting me any results. If this helps There is no error_code field extracted from the _raw events.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

Vijeta — Thu, 20 Sep 2018 17:20:22 GMT

You dont have the error code parsed in your raw events? Can you extract the error code field using regex from raw events?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Thu, 20 Sep 2018 17:30:16 GMT

Thanks for the reply Vijeta, my ErrorCode looks like this \”errorCode\”:\”40025\”, I have hard time in getting regex working and also some of logs does not log errors codes but, just description. So the idea is list all those ErrorCode and error description into a lookup file, match with the log data and display on a dashboard with the count.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Thu, 20 Sep 2018 18:07:24 GMT

I am using this Rex “errorCode.*(?)” but it’ is not giving anything.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Thu, 20 Sep 2018 18:16:19 GMT

@sandeepmakkena

Can you please share some sample event?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Tue, 29 Sep 2020 21:20:39 GMT

000xxxx XXXXX log Call failed for endpoint XXXX after 703 milliseconds with message {"requestingSystemId":"System1","externalReferenceId":"xxx123xxx","responseCode":"GROUP_FAILURE","responseDescription":"Accounting instruction rejected as processing of one or more accounting entries was unsuccessful","entries":[{"externalReferenceId":"System1/TransactionId/1/2","responseCode":"ACCOUNT_LOCKED","responseDescription":"40070,400,AccountOptedOutException: ","processingStatusCode":"REJECTED","accountBalance":{"updateAmountDateTime":"Date.000+0000","accountBalance":0,"availableBalance":0,"source":"CACHE"},"adapterDescription":"adapter1","destinationAccountId":"xxxx....xxxx","destinationAccountIdType":"yyyx","supplementaryData":"{\"developerMessage\":\"AccountOptedOutException: \",\"errorCode\":\"40070\",\"gatewayErrors\":[{\"code\":\"40070\",\"gatewayName\":\"optout\",\"operation\":\"abc.gateway.optout\"}],\"httpStatus\":400,\"userMessage\":\"This account is opted out\",\"validationErrors\":[]}","errors":[]}]} [accounting:50] [PaymentInterchange=123456789, PaymentInstruction=678912345, PaymentTransaction=14000xxx34, OPIC=null, RunId=null, uuid=null, origin=InterchangeLoaderMDBBean]

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Thu, 20 Sep 2018 19:02:37 GMT

This is one sample event.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Thu, 20 Sep 2018 19:32:04 GMT

@sandeepmakkena

Can you please try following search?

Index=my_index | rex field=_raw "errorCode\\\\\":\\\\\"(?<error_code>\d+)\\\\\"" | lookup error.csv error_code OUTPUT description | stats count by description

My Sample Search:

| makeresults 
| eval _raw="000xxxx XXXXX log Call failed for endpoint XXXX after 703 milliseconds with message {\"requestingSystemId\":\"System1\",\"externalReferenceId\":\"xxx123xxx\",\"responseCode\":\"GROUP_FAILURE\",\"responseDescription\":\"Accounting instruction rejected as processing of one or more accounting entries was unsuccessful\",\"entries\":[{\"externalReferenceId\":\"System1/TransactionId/1/2\",\"responseCode\":\"ACCOUNT_LOCKED\",\"responseDescription\":\"40070,400,AccountOptedOutException: \",\"processingStatusCode\":\"REJECTED\",\"accountBalance\":{\"updateAmountDateTime\":\"Date.000+0000\",\"accountBalance\":0,\"availableBalance\":0,\"source\":\"CACHE\"},\"adapterDescription\":\"adapter1\",\"destinationAccountId\":\"xxxx....xxxx\",\"destinationAccountIdType\":\"yyyx\",\"supplementaryData\":\"{\\\"developerMessage\\\":\\\"AccountOptedOutException: \\\",\\\"errorCode\\\":\\\"40070\\\",\\\"gatewayErrors\\\":[{\\\"code\\\":\\\"40070\\\",\\\"gatewayName\\\":\\\"optout\\\",\\\"operation\\\":\\\"abc.gateway.optout\\\"}],\\\"httpStatus\\\":400,\\\"userMessage\\\":\\\"This account is opted out\\\",\\\"validationErrors\\\"::[]}\",\"errors\":[]}]} [accounting:50] [PaymentInterchange=123456789, PaymentInstruction=678912345, PaymentTransaction=14000xxx34, OPIC=null, RunId=null, uuid=null, origin=InterchangeLoaderMDBBean]" | rex field=_raw "errorCode\\\\\":\\\\\"(?<error_code>\d+)\\\\\"" | lookup error.csv error_code OUTPUT description | stats count by description

Updated Answer:

index=my_index | rex field=_raw max_match=0 "errorCode\\\\\":\\\\\"(?<error_code>\d+)\\\\\"" 
| rex field=_raw max_match=0 "responseCode\":\"(?<response_code>\w+)" 
| eval error_code = if(isnotnull(error_code) AND error_code!="", error_code,response_code) 
| table error_code | lookup error.csv error_code OUTPUT description | stats count by description

Thanks

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Tue, 29 Sep 2020 21:20:42 GMT

Thanks for your help Kamlesh.
It is extracting the error_code filed alright, but it is not giving stats count by Description.

One more problem is some logs does not have error_code, but just error_description, How can we get those into the pie chart ?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Fri, 21 Sep 2018 04:15:45 GMT

@sandeepmakkena

PLs share header and sample records from error.csv AND share sample event with no error_code but has error description.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Tue, 29 Sep 2020 21:18:11 GMT

Kamlesh, Thanks for the reply. Here are the details you asked for.

Error_Code Description
40421 This is Error 1
40022 This is Error 2
40025 This is Error 3 error.csv

Fxxxxxxxxxxx E com.xxxx.xx.xxx.xxx.LogLevel$ErrorLogLevel log Call failed for endpoint xxxxxxxx.com and for resource PostingServicesApi.processAccountingInstruction after 49 milliseconds with message {"requestingSystemId":"ABCD0.1","externalReferenceId":"1122334455","responseCode":"GROUP_FAILURE","responseDescription":"At least one entry within the instruction can't be posted due to business error","entries":[{"externalReferenceId":"ABCD0.1/TransactionID_XXX/1/2","responseCode":"ACCOUNT_NOT_FOUND","responseDescription":"Unknown account in Accounting Entry","processingStatusCode":"REJECTED","errors":[]}]} [accounting:50] [PaymentInterchange=1234567, PaymentInstruction=87654321, PaymentTransaction=13579246, OPIC=null, RunId=null, uuid=null, origin=InterchangeLoaderMDBBean]

Sample log without error_code

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Fri, 21 Sep 2018 16:36:27 GMT

@sandeepmakkena
Thanks for sample.

Is that any clue to identify error description from above given sample. How we can categories this event in error code and description.?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Tue, 29 Sep 2020 21:20:59 GMT

"responseCode": is the field that is holding the error description. 2nd part of your question, I am thinking of keeping the error code and description the same in .csv file.

For example: From the sample log, .csv file field will be

Error_Code Description
ACCOUNT_NOT_FOUND, ACCOUNT_NOT_FOUND

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Tue, 29 Sep 2020 21:21:02 GMT

but responseCode is available in very first event also. means that event has error_code and responseCode as well. Both event has multiple responseCode also.

So I'm suggesting to create another pie chart for response data IF possible. So you will be have 2 pie charts,
1) error_code/error_description wise pie chart
2) responseCode/responseDescritpion wise pie chart.

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Fri, 21 Sep 2018 17:15:56 GMT

That is a good idea but, it is not suitable for my requirement.
Can't we compare error_code values from .csv file with two files from logs(errorCode and ResponseCode) and add the count if we have a match ?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Tue, 29 Sep 2020 21:21:19 GMT

Yes, we can do it, We need to extract responseCode from event.
So now we find error_code from event and if error_code not found then will search for responseCode. Then after will fetch description from csv file. right?

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

sandeepmakkena — Fri, 21 Sep 2018 18:06:47 GMT

Yup! that's what my idea is, unless you have something better in this case and can you include rex for extracting responseCode filed, if come up with a solution.
Thank you Kamlesh.,

Re: Can you help me create a pie chart that would display info from a CSV Lookup file?

kamlesh_vaghela — Fri, 21 Sep 2018 20:00:47 GMT

I have updated answer. Pls check and confirm it.