How do you combine stats results for a base data grid?

[mikeydee77]

I would like to combine the results of two searches to use as a dashboard base search and then filter in different ways in sub search. I can calculate all the numbers I need independently, but don't know the best approach to combine the results.

My end-goal is to display a metric like response time only for those service_category with a low % of success (i.e. web in below example)

Search 1

service_category | OK | ServerError | Total
web | 85 | 15 | 100
retail | 50 | 1 | 51

Search 2

service_category | response_mS 
web | 650 
retail |  500

Desired Outcome

service_category | response_mS | success_%
web | 650 | 85   (filter this row)
retail |  500 | 95 (show this row) 

The query that I have been developing is below. The problem seems to be how the xyseries command removes unrelated info about duration.

index=mydata  type=RESPONSE 
            | eval duration=response_in_timestamp-(request_in_timestamp+request_out_timestamp)
            | stats p95(duration) as 95pc_duration count by domain, response_out_http_code, index
            | eval tps = round(count/60)
            | eval statusCategory=if(response_out_http_code>=200 AND response_out_http_code < 300, "OK", if(response_out_http_code>=400 AND response_out_http_code < 500, "ClientError", if(response_out_http_code>=500 AND response_out_http_code < 600, "ServerError", "Other"))) 

|  stats p95(duration) as 95pc_duration sum(count) as count by domain, statusCategory, index
              |  xyseries domain,statusCategory, count
              | fillnull value=0 ClientError, ServerError, OK, Other
              |  eval Total = ClientError + OK + ServerError + Other
              |  eval ServerError_%= round((ServerError/Total) * 100)
              |  eval ClientError_%= round((ClientError/Total) * 100)
              |  eval Success_%= round((OK/Total) * 100)
              |  eval Other_%= round((Other/Total) * 100)
              |  search Success_% <= 92
              | fields domain Success_%

[somesoni2 · Accepted solution]

Give this a try

index=mydata type=RESPONSE 
| eval duration=response_in_timestamp-(request_in_timestamp+request_out_timestamp)
| eval statusCategory=if(response_out_http_code>=200 AND response_out_http_code < 300, "OK", if(response_out_http_code>=400 AND response_out_http_code < 500, "ClientError", if(response_out_http_code>=500 AND response_out_http_code < 600, "ServerError", "Other")))
| stats p95(duration) as 95pc_duration count by domain, statusCategory
| eval domain=domain."#".95pc_duration
| xyseries domain,statusCategory, count
| fillnull value=0 ClientError, ServerError, OK, Other
| eval Total = ClientError + OK + ServerError + Other
| eval ServerError_%= round((ServerError/Total) 100)
| eval ClientError_%= round((ClientError/Total) 100)
| eval Success_%= round((OK/Total) 100)
| eval Other_%= round((Other/Total) 100)
| search Success_% <= 92
| rex field=domain  "(?<domain>.+)#(?<95pc_duration>.+)"
| fields domain Success_% 95pc_duration

[mikeydee77]

Yes this works...Thanks

I see the idea...

1. create a combined column with category.#.duration value (or other values want to combine)
2. do some calculations
3. separate out the category.#.duration later on.

In my case I also had to add a stats command to re-combine repeated rows
1. recombine any duplicated rows
2. finalise the stats

index=mydata  type=RESPONSE request_type=* 
| eval duration=response_in_timestamp-(request_in_timestamp+request_out_timestamp)
| stats p95(duration) as duration_95pc count by domain, response_out_http_code, index
| eval tps = round(count/60)
| eval statusCategory=if(response_out_http_code>=200 AND response_out_http_code < 300, "OK",      
 if(response_out_http_code>=400 AND response_out_http_code < 500, "ClientError", if(response_out_http_code>=500 AND   response_out_http_code < 600, "ServerError", "Other"))) 
 |  stats avg(duration_95pc) as duration_95pc sum(count) as count by domain, statusCategory, index
 | eval domain=domain."#".duration_95pc
 |  xyseries domain,statusCategory, count
 | fillnull value=0 ClientError, ServerError, OK, Other
 | rex field=domain  "(?<domain>.+)#(?<duration_95pc>.+)"
 | stats sum(ClientError) as ClientError, sum(ServerError) as ServerError, sum(OK) AS OK, sum(Other) as Other, sum(Total) as Total      avg(ClientError_%) as ClientError_%, avg(ServerError_%) as ServerError_%, avg(Success_%) as Success_%, avg(Other_%) as Other_%  avg(duration_95pc)   by domain
  |  eval Total = ClientError + OK + ServerError + Other
  |  eval ServerError_%= round((ServerError/Total) * 100)
  |  eval ClientError_%= round((ClientError/Total) * 100)
  |  eval Success_%= round((OK/Total) * 100)
  |  eval Other_%= round((Other/Total) * 100)

But couple of things worth pointing out to any future readers.
My choice of field name 95pc_duration seems to be a bad idea. The eval command would not work with this field until I renamed it... ? Must be because it has a number at the beginning. I guess eval gets confused ... is it a number or is it a string I suspect it is saying to itself.

I also needed to add a final stats command as I had some duplicateed rows.

[somesoni2]

It's recommended to have your field names start with alphabets. If it does start with number, you'd have to use it within single quotes when referencing in expression section of eval/where command.

[mikeydee77]

Right!... easy to avoid.