Splunk Search

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

rohanmiskin
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

renjith_nair
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%")
Happy Splunking!

View solution in original post

0 Karma

kamlesh_vaghela
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

rohanmiskin
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

kamlesh_vaghela
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

renjith_nair
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%")
Happy Splunking!
0 Karma

rohanmiskin
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

renjith_nair
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%")
Happy Splunking!
0 Karma

vishaltaneja070
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

vishaltaneja070
Motivator

@rohanmiskin

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

0 Karma

rohanmiskin
Explorer

May or may not be.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...