topic Re: Table containing url, total requests, error responses in Splunk Search

Table containing url, total requests, error responses

oompaloompa — Sat, 18 Jul 2020 14:55:58 GMT

Seems pretty simple, but it's kicking my butt so here I am. I've tried more variations than I'd like, but I have a ton of log writes. some of them are response values. What I'd like to do is create a simple table displaying the URL, its total number of incoming requests, and its total number of error responses. I can't get the error responses to work properly.

In the latest iteration, this is what I have:

... line.status != "" | stats count as total_requests by line.url | eventstats count(eval(line.status!="200")) as errors by line.url

line.status doesn't exist on ever log write, of course, so I want to search only on log writes that document a response - for those, line.status will exist. total_requests works as expected, but errors does not. Ideally, errors could be a count where line.status > 399, but the value is a string and nothing I've tried has worked properly. I either get a query error, a 0 value, or a 1 value for all line.url values.

Where am I going wrong here?

Re: Table containing url, total requests, error responses

arjunpkishore5 — Sat, 18 Jul 2020 15:23:28 GMT

Try this

| stats count as total_requests, count(eval(tonumber(line.status)>399)) as errors by line.url

Re: Table containing url, total requests, error responses

oompaloompa — Sat, 18 Jul 2020 15:49:47 GMT

The error count is still zero on all urls using this:

line.status != "" | stats count as total_requests, count(eval(tonumber(line.status)>399)) as errors by line.url | sort -"errors"

Re: Table containing url, total requests, error responses

arjunpkishore5 — Sat, 18 Jul 2020 15:53:31 GMT

not sure why it isn't working since I don't have the raw data, you could try this alternative

| eval error=if(tonumber(line.status)>399, 1, 0) | stats count as total_requests, sum(error) as errors by line.url | sort -"errors"

If this isn't working, check if error is being set properly in the eval.

Re: Table containing url, total requests, error responses

oompaloompa — Sat, 18 Jul 2020 16:06:34 GMT

Same 0 error count for all URLs. If I break it down by url _and_ status, I also see an error count of 0, even when the line.status value in a given row is > 399:

| stats count as total_requests, count(eval(tonumber(line.status)>399)) as errors by line.url, line.status

In that query, for any row whose line.status value is < 399, I'd expect the value of the errors field to match that of the total_requests field, right?

Re: Table containing url, total requests, error responses

oompaloompa — Sat, 18 Jul 2020 18:02:20 GMT

Even this returns all zeros:

| eval foo=if(tonumber(line.status) > 399, 1, 0) | table foo

I'm not sure how much simpler of a case I can try. I've also tried these variants just to see if i could isolate a _specific_ error status code.

| eval foo=if(tonumber(line.status="401"), 1, 0) | table foo // all zeros | eval foo=if(line.status=401, 1, 0) | table foo // Error in 'eval' command: Type checking failed. The '==' operator received different types.

Re: Table containing url, total requests, error responses

oompaloompa — Sat, 18 Jul 2020 21:25:26 GMT

I think I've found something that gets me the raw data I'm after, but could use a sanity check.

... | stats count as status_code_count by line.url, line.status | eventstats sum(status_code_count) as total_hits by line.url | eval status_code_percent=round((status_code_count/total_hits)*100, 2) | fields line.url, line.status, total_hits, status_code_percent | sort + line.url, + line.status, - status_code_percent

The last thing I'd like to do is filter for line.status values > 399 (errors), but I still can't find the magic touch for that. I've tried

| where tonumber(line.status)>399 // No results | where line.status="500" // No results | where line.status=500 // Type mismatch error

Something definitely seems to be wrong with either my status data or the way I'm trying to query it, but I can't find the key for the life of me.

Re: Table containing url, total requests, error responses

to4kawa — Sun, 19 Jul 2020 01:40:06 GMT

please provide sample logs. your fields extraction is maybe wrong.

Re: Table containing url, total requests, error responses

oompaloompa — Sun, 19 Jul 2020 15:02:25 GMT

Here is an example log line from the base query which is:

index="<redacted>" "attrs.app"="api" attrs.stack="prod" line.component="gateway" line.message="request completed"

{ "line": { "level":"info", "component":"gateway", "elapsed-time":5.548328, "request-id":"c52e5e50-fb15-4a37-96ba-17e208e947ee", "status":200, "method":"GET", "path":"/beneinfo/keep-alive", "url":"/beneinfo/keep-alive", "trimmed path":"public", "query":"", "error message":"", "error details":[], "pid":1, "te":"", "serve local docs":false, "time":"2020-07-19T14:49:55.633253281Z", "message":"request completed" }, "source":"stderr", "attrs":{ "app":"api", "stack":"prod" } }

Would definitely appreciate any thoughts you might have.

Re: Table containing url, total requests, error responses

to4kawa — Sun, 19 Jul 2020 20:11:09 GMT

index=_internal | head 1 | fields _time _raw | eval _raw="{\"line\":{\"level\":\"info\",\"component\":\"gateway\",\"elapsed-time\":5.548328,\"request-id\":\"c52e5e50-fb15-4a37-96ba-17e208e947ee\",\"status\":200,\"method\":\"GET\",\"path\":\"/beneinfo/keep-alive\",\"url\":\"/beneinfo/keep-alive\",\"trimmed path\":\"public\",\"query\":\"\",\"error message\":\"\",\"error details\":[],\"pid\":1,\"te\":\"\",\"serve local docs\":false,\"time\":\"2020-07-19T14:49:55.633253281Z\",\"message\":\"request completed\"},\"source\":\"stderr\",\"attrs\":{\"app\":\"api\",\"stack\":\"prod\"}}" | spath | stats count by "line.status" "line.url"

line.status	line.url	count
200	/beneinfo/keep-alive	1

This query works fine.

index="<redacted>" "attrs.app"="api" attrs.stack="prod" line.component="gateway" line.message="request completed" | stats count by "line.status" "line.url"

If this result doesn't have status over 399, there is not the error.

If there are errors, add

| search "line.status" > 399

Re: Table containing url, total requests, error responses

oompaloompa — Sun, 19 Jul 2020 21:14:36 GMT

What you have here has always worked fine. I get a table of all status codes for each URL. What I can't seem to do is limit the output to > 399 only . Every attempt I make fails for one of the reasons mentioned above. If I through "| search line.status > 399" at it, then I get a search canceled message.

I can't seem to right the right fit to filter the working query above, nor can i find another way to do the same thing that limits the results to only error status codes.

Thanks for your help.

Re: Table containing url, total requests, error responses

to4kawa — Sun, 19 Jul 2020 21:30:42 GMT

>search canceled message.

what's this?

we can't see your display, so you should provide exactly messages.