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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...