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
Legend

@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%")
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
Legend

@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%")
---
What goes around comes around. If it helps, hit it with Karma 🙂
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
Legend

@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%")
---
What goes around comes around. If it helps, hit it with Karma 🙂
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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...