Splunk Search

How do you find two string values in every group of events grouped by a particular field?

Explorer

I have log events for a spring boot application in the format

10.30 2019 | 1111 | POST /data1
10.31 2019 | 1111 | data verified
10.32 2019 | 1111 | 404 not found

10.30 2019 | 2222 | Get /data2
10.31 2019 | 2222 | data verified
10.32 2019 | 2222 | 400 error

10.30 2019 | 3333 | DELETE /data3
10.31 2019 | 3333 | data verified
10.32 2019 | 3333 | 304 not modified

10.30 2019 | 4444 | PUT /data4
10.31 2019 | 4444 | data verified
10.32 2019 | 4444 | 201 Created

Every HTTP request has a unique correlation ID and is maintained till the request either gets failed or succeeded.

In our case 1111,2222,3333,4444 are the correlation IDs for 4 different requests, POST,GET,DELETE,PUT respectively.

I want to get the result with error codes 304,400,404,500 (excluding success scenarios i.e 201) in this format

request         | corrid | error code         |
POST /data1     | 1111   | 404 not found      | 
Get /data2      | 2222   | 400 error          | 
DELETE /data3   | 3333   | 304 not modified   |
0 Karma
1 Solution

SplunkTrust
SplunkTrust

@rohanmiskin,

Updated:

"your base search"
|stats values(eval(if(match(request,"PUT|DELETE|GET|POST"),request,null()))) as Request,
       values(eval(if(match(response,"^\d"),response,null())))  as Response by code
|where !LIKE(Response ,"20%")

Try,

"your base search" 
|stats earliest(request) as Request,latest(request) as Response by corrid |where !LIKE(Response ,"20%")

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

@rohanmiskin,

Can you please try this?

YOUR_SEARCH | rex field=error_code "^(?<status_code>\d+).*" | stats first(error_code) as request last(error_code) as error values(status_code) as status_code by corrid | where status_code in (304,400,404,500) | table request corrid error status_code

MY Sample Search:

| makeresults 
| eval _raw="A  B   C
10.30 2019  1111    POST /data1
10.31 2019  1111    data verified
10.32 2019  1111    404 not found
10.30 2019  2222    Get /data2
10.31 2019  2222    data verified
10.32 2019  2222    400 error
10.30 2019  3333    DELETE /data3
10.31 2019  3333    data verified
10.32 2019  3333    304 not modified
10.30 2019  4444    PUT /data4
10.31 2019  4444    data verified
10.32 2019  4444    201 Created
" 
| multikv 
|rename B as corrid,C as error_code
| rex field=error_code "^(?<status_code>\d+).*" | stats first(error_code) as request last(error_code) as error values(status_code) as status_code by corrid | where status_code in (304,400,404,500) | table request corrid error status_code

Thanks

0 Karma

Explorer

actually for a particular corrid, POST /data1,Get /data2 etc are no the first and the status codes are not the last logs. The are other logs too before POST /data1,Get /data2 and after status codes.

0 Karma

SplunkTrust
SplunkTrust

@rohanmiskin,

Can you please try this?

YOUR_SEARCH | rex field=error_code "^(?<status_code>\d+).*" | eval Request= if(match(error_code,"PUT|DELETE|GET|POST"),error_code,null()), Response= if(match(error_code,"^\d"),error_code,null()) 
| stats values(Request) as request values(Response) as error values(status_code) as status_code by corrid | where status_code in (304,400,404,500) | table request corrid error status_code
0 Karma

SplunkTrust
SplunkTrust

@rohanmiskin,

Updated:

"your base search"
|stats values(eval(if(match(request,"PUT|DELETE|GET|POST"),request,null()))) as Request,
       values(eval(if(match(response,"^\d"),response,null())))  as Response by code
|where !LIKE(Response ,"20%")

Try,

"your base search" 
|stats earliest(request) as Request,latest(request) as Response by corrid |where !LIKE(Response ,"20%")

View solution in original post

0 Karma

Explorer

actually for a particular corrid, POST /data1,Get /data2 etc are no the earliest and the status codes are not the latest. The are other logs too before POST /data1,Get /data2 and after status codes.

0 Karma

SplunkTrust
SplunkTrust

@rohanmiskin ,

Give this a try

 "your base search"
 |stats values(eval(if(match(request,"PUT|DELETE|GET|POST"),request,null()))) as Request,
        values(eval(if(match(response,"^\d"),response,null())))  as Response by code
 |where !LIKE(Response ,"20%")
0 Karma

Motivator

I have uploaded the above data and tried the below query:

source="abcde.txt" host="test" sourcetype="csv4" ip="10.30 2019"  | join corr_id [search source="abcde.txt" host="test" sourcetype="csv4" ip="10.32 2019" | table corr_id activity | where NOT (activity like "201%") | rename activity as error_code] | table corr_id activity error_code
0 Karma

Motivator

@rohanmiskin

Ip is constant for one kind of event like Post/DELETE/PUT?

0 Karma

Explorer

May or may not be.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!