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

[sandeepmakkena]

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!

[kamlesh_vaghela · Accepted solution]

@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

[sandeepmakkena]

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 ?

[kamlesh_vaghela]

@sandeepmakkena

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

[sandeepmakkena]

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

[kamlesh_vaghela]

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

[sandeepmakkena]

"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

[kamlesh_vaghela]

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.

[sandeepmakkena]

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 ?

[kamlesh_vaghela]

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?

[sandeepmakkena]

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

[kamlesh_vaghela]

I have updated answer. Pls check and confirm it.

[sandeepmakkena]

index=my_index | rex field=_raw max_match=0 "errorCode\\\":\\\"(?\d+)\\\""
| rex field=_raw max_match=0 "responseCode\":\"(?\w+)"
| eval error_code = if(isnotnull(error_code) AND error_code!="", error_code,response_code)
| table error_code

This part works like a champ, but when we add the lookup it is not giving any results(I did cross check the data from table and lookup we have couple of matching fields)
Thank you.,

[kamlesh_vaghela]

Can you please check the output of following search?

 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

[kamlesh_vaghela]

Meanwhile can you pls execute this?

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 | mvexpand error_code | lookup error.csv error_code OUTPUT description | stats count by description

[sandeepmakkena]

This did work but, with small changes. here is the final query.
index=my_index
| rex field=_raw max_match=0 "errorCode\\\":\\\"(?\d+)\\\""
| rex field=_raw max_match=0 "responseCode\":\"(?\w+)"
| eval error_code1 = if(isnotnull(error_code) AND error_code!="", error_code,response_code)
| stats count by error_code1
| lookup error.csv error_code as error_code1 OUTPUTNEW Description | table Description count | where Description!= " "
Thanks for your help Kamlesh, I really appreciate your time.

[kamlesh_vaghela]

Glad to help you

Happy Splunking

[sandeepmakkena]

Kamlesh, The pie chart we worked on is working fine but, when any transaction failed due to technical error, it is logged as just TECHNICAL_ERROR which is very generic. I want to brake down that in the same pie chart. I am thinking of this when there is a technical failure I want to lookup a .csv file with different reason of technical failure and search for it in the logs in the time rage +5sec to -5sec from the _time technical failure is found and include the reason with the match into pie chart. Can you help me in implementing this
2.I am using strftime on date it is working fine on date range picker but not on relevant time range picker like last 24 hrs or last week something like this, is this a bug or am I doing something wrong.
Thank you.,

[kamlesh_vaghela]

@sandeepmakkena

Can you please share some sample event?

[sandeepmakkena]

This is one sample event.

[sandeepmakkena]

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]