topic Re: How to format SPL Search Query as code ?? in Splunk Search

How to format SPL Search Query as code ??

venkat0896 — Wed, 30 Sep 2020 03:57:12 GMT

HI All

i need it to displayed like

request total 200 400 500 avg response maximum response
a/b/c 2 1 1 0 209ms 249ms

i tried with query
source="abc"
("[SERVICE]=XYZ" "[URI]=")
| rex field=_raw " \[URI\]=(?<Request>.?)|"
| rex field=_raw " \[RESPONSE_TIME\]=(?<Time>.?)ms"
| stats count as Total,
count(eval(searchmatch("[HTTP-STATUS-CODE]=2*"))) as "2xx Successful Requests", count(eval(searchmatch("[HTTP-STATUS-CODE]=5*"))) as "5xx Technical Errors",count(eval(searchmatch("HTTP-STATUS-CODE]=4*"))) as "4xx Request Errors", avg(Time) as "Average Response (ms)", max(Time) as "Max Response (ms)" by Request

Appreciate your Help on this ..
Thanks in Advance !!

Re: How to format SPL Search Query as code ??

richgalloway — Thu, 30 Jan 2020 13:25:56 GMT

Please provide some sample events, if you can. Anonymize the data as necessary.
What results to you get from your current query?

Re: How to format SPL Search Query as code ??

venkat0896 — Thu, 30 Jan 2020 14:08:22 GMT

Request Count Avg response time 200 response 400 response 500 response
a/b/c 1 1068 ms 1 0 0
x/y/z 1 900 ms 0 1 0
q/w/e 1 1300 ms 0 0 1

i am trying to monitor the service with the number of counts, response returned and the response time

Re: How to format SPL Search Query as code ??

richgalloway — Thu, 30 Jan 2020 14:28:58 GMT

Those events are not in the format specified in your question. The rex commands will not match that data.

Re: How to format SPL Search Query as code ??

venkat0896 — Thu, 30 Jan 2020 14:42:52 GMT

Yes.. can you help on this.. ?

Re: How to format SPL Search Query as code ??

nickhills — Thu, 30 Jan 2020 17:47:10 GMT

you should use the code formatter to post SPL - from the looks of it, some of your formatting has been lost

Re: How to format SPL Search Query as code ??

nickhills — Thu, 30 Jan 2020 18:07:24 GMT

Hi @venkat0896 try this:

|makeresults |eval _raw="|METRICS|[SERVICE]=XYZ|POST|[URI]=/a/b/c|[HTTP-STATUS-CODE]=200|[RESPONSE_TIME]=209ms"

|rex field=_raw "\[SERVICE\]=(?P<service>[^\|]+)\|POST\|\[URI\]=(?P<request>[^\|]+)\|\[HTTP-STATUS-CODE\]=(?P<status>\d+)\|\[RESPONSE_TIME\]=(?P<responseTime>\d+)ms"
|eventstats count(eval(like(status,"2%"))) as "200s", count(eval(like(status,"5%"))) as "500s",count(eval(like(status,"4%"))) as "400s"
|stats count(200s) as 200s count(400s) as 400s count(500s) as 500s count as total avg(responseTime) as avgResponse max(responseTime) as maxResponse by request
|table request total 200s 400s 500s avgResponse maxResponse

The first "makeresults" line above generates the test data - replace line 1 with your realy query.

Re: How to format SPL Search Query as code ??

richgalloway — Thu, 30 Jan 2020 18:13:35 GMT

I couldn't get the query to work with the sample event. This query works, however.

source="abc"
("[SERVICE]=XYZ" "[URI]=")
| rex field=_raw "\[URI]=(?<Request>[^\|]+)" 
| rex field=_raw "\[RESPONSE_TIME]=(?<Time>\d+)ms" 
| rex "\[HTTP-STATUS-CODE]=(?<StatusCode>\d+)"
| stats count as Total,
count(eval(match(StatusCode, "2\d\d"))) as "2xx Successful Requests", count(eval(match(StatusCode, "5\d\d"))) as "5xx Technical Errors",count(eval(match(StatusCode, "4\d\d"))) as "4xx Request Errors", avg(Time) as "Average Response (ms)", max(Time) as "Max Response (ms)" by Request

Re: How to format SPL Search Query as code ??

venkat0896 — Fri, 31 Jan 2020 11:28:19 GMT

Thanks @richgalloway .. it worked 🙂

Re: How to format SPL Search Query as code ??

venkat0896 — Mon, 03 Feb 2020 15:26:22 GMT

Hi @richgalloway this is another Logging i see like
POST|/private/v1/a/b/c/d|testing234||US|xyz|xyz|||xyz|xyz|||METRICS|--|ResponseCode=200|ResponseTime=7481","origin":"rep"

in this Logging how to use the rex to get the table view

for example
Request Count Avg response time 200 response 400 response 500 response
/private/v1/a/b/c/d 1 7481 1 0 0

Thanks in Advance !!

Re: How to format SPL Search Query as code ??

richgalloway — Mon, 03 Feb 2020 16:38:11 GMT

This probably should be a new question.

| rex "\|(?<Request>[^\|]+)"
| rex "ResponseCode=(?<StatusCode>\d+)\|ResponseTime=(?<ResponseTime>\d+)"
| stats count as Total,
 count(eval(match(StatusCode, "2\d\d"))) as "200 Response", count(eval(match(StatusCode, "5\d\d"))) as "500 Response",count(eval(match(StatusCode, "4\d\d"))) as "400 Response", avg(Time) as "Average response time"