How to format SPL Search Query as code ??

[venkat0896]

HI All

i am creating a dashboard in SPLUNK .. i am trying capture the API counts and response time .
here is a sample logger
|METRICS|[SERVICE]=XYZ|POST|[URI]=/a/b/c|[HTTP-STATUS-CODE]=200|[RESPONSE_TIME]=209ms"

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 !!

[richgalloway · Accepted solution]

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

[venkat0896]

Thanks @richgalloway .. it worked 🙂

[venkat0896]

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 !!

[richgalloway]

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"

[nickhills]

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.

[nickhills]

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

[richgalloway]

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

[venkat0896]

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

[richgalloway]

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

[venkat0896]

Yes.. can you help on this.. ?