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

[rohanmiskin]

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   |

[kamlesh_vaghela]

@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

[rohanmiskin]

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.

[kamlesh_vaghela]

@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

[renjith_nair · Accepted solution]

@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%")

[rohanmiskin]

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.

[renjith_nair]

@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%")

[vishaltaneja070]

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

[vishaltaneja070]

@rohanmiskin

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

[rohanmiskin]

May or may not be.