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_%
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
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
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.
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.
Right!... easy to avoid.