topic Re: Pull in client/server errors into table format in Splunk Search

How to pull in client/server errors into table format?

Khanu89 — Mon, 25 Apr 2022 15:12:41 GMT

Hello - thank you for assisting in advance. I need to write up a query which will pull in client/server errors from event message into table format as shown below.

_time	status_category	Error Code	error_count
2022-01-26:17:30:00	server error	503	2
2022-01-26:18:30:00	client error	404	6

Here are examples of the EvenTypes and available fields for the index.

Error 503

Fields

Re: Pull in client/server errors into table format

VatsalJagani — Mon, 25 Apr 2022 05:31:24 GMT

@Khanu89

Your logs seem not to be formatted properly in order to extract the error code. As if you look at the two examples that you gave here there is no common format as such we can write a regex to extract those values.
- Let me know if any additional information you have on this.
Though, I'm giving you a query that should be able to help you once you find out a way to extract the ErrorCode field.

<your search query> | stats count as error_count, max(_time) as _time by ErrorCode | eval status_category=case(ErrorCode>=400 AND ErrorCode<500, "client error", ErrorCode>=500, "server error", 1==1, "-")

I hope this helps!!!

Re: Pull in client/server errors into table format

Khanu89 — Mon, 25 Apr 2022 23:47:00 GMT

@VatsalJagani I figured out my rex query and was able to use yours to get my table but not all rows are populating. here is what I go so far.

Query:

index=epic_ehr sourcetype="WinEventLog:Epic"
|rex field=_raw "%\s(?P<ErrorCode>.*)\s"
| stats count as error_count, max(_time) as _time by ErrorCode
| eval status_category=case(ErrorCode>=400 AND ErrorCode<500, "client error", ErrorCode>=500, "server error", 1==1, "-")
|table _time,ErrorCode,Status_Category,Error_Count

How can convert this data into a Pie chart which shows percentage of each error code?

Re: Pull in client/server errors into table format

VatsalJagani — Tue, 26 Apr 2022 05:36:24 GMT

@Khanu89 - For pie chart that shows count by ErrorCode.

index=epic_ehr sourcetype="WinEventLog:Epic" |rex field=_raw "%\s(?P<ErrorCode>.*)\s" | stats count by ErrorCode

Re: Pull in client/server errors into table format

Khanu89 — Tue, 26 Apr 2022 14:10:45 GMT

@VatsalJagani I have tired that it does not categorizes the errors. I'd like to see the pie chart breakdown by error code. For example 401 - 2%, 404 - 18%, 443 - 23% and etc.

Re: Pull in client/server errors into table format

VatsalJagani — Tue, 26 Apr 2022 14:19:24 GMT

@Khanu89 - I thought that should work (reference - https://docs.splunk.com/Documentation/Splunk/8.2.6/Viz/PieChart), but please also try:

index=epic_ehr sourcetype="WinEventLog:Epic" |rex field=_raw "%\s(?P<ErrorCode>.*)\s" | chart count by ErrorCode

index=epic_ehr sourcetype="WinEventLog:Epic" |rex field=_raw "%\s(?P<ErrorCode>.*)\s" | chart count over ErrorCode

And change the Visualization to Pie Chart and it should show what you want I think.

For % (percentage), you need to use the following option in the XML part of the pie chart to show percentage values.

I hope this helps!!!

Re: Pull in client/server errors into table format

Khanu89 — Tue, 26 Apr 2022 16:24:52 GMT

@VatsalJagani I have also tried what you've suggested and it gives me the following pie chart which isn't what I want.

I would like the pie chart to be like the one below but group all 400's, 500's, Other instead of listing individually as they are listed in the screenshot.

Re: Pull in client/server errors into table format

VatsalJagani — Tue, 26 Apr 2022 17:06:40 GMT

It's not working as you expect because the ErrorCode field is not being extracted correctly.

Regex seems very generic. It would match anything that starts with % (percentage sign) and comes between two spaces (\s).

A somewhat better regex would be (still not 100% accurate as logs are not as formated):

| rex field=_raw "%\s(?P<ErrorCode>\d+)\s"