How to get union of two in one query and extract even duplicate result?

[dezmadi]

I have two queries

index="gtw-ilb" /v1/platform/change_indicators host="*dev01*"| search sourcetype="nginx:plus:access" |eval env = mvindex(split(host, "-"), 1)
| convert num(status) as response_code
| eval env = mvindex(split(host, "-"), 1) |eval tenant=split(access_request, "tenantId=")| eval tenant=mvindex(tenant, 1) | eval tenant=split(tenant, "&") | eval tenant=mvindex(tenant, 0)
| stats count(eval(like(response_code,"%%%"))) AS total_request count(eval(like(response_code,"4%%"))) AS error_request4 count(eval(like(response_code,"5%%"))) AS error_request5 by tenant
| eval pass_percent = round(100-((error_request4+error_request5)/total_request*100),2) | where total_request >1
| table tenant, pass_percent, total_request | sort -pass_percent limit=3

And

index="gtw-ilb" /v1/platform/change_indicators host="*dev01*"| search sourcetype="nginx:plus:access" |eval env = mvindex(split(host, "-"), 1)
| convert num(status) as response_code
| eval env = mvindex(split(host, "-"), 1) |eval tenant=split(access_request, "tenantId=")| eval tenant=mvindex(tenant, 1) | eval tenant=split(tenant, "&") | eval tenant=mvindex(tenant, 0)
| stats count(eval(like(response_code,"%%%"))) AS total_request count(eval(like(response_code,"4%%"))) AS error_request4 count(eval(like(response_code,"5%%"))) AS error_request5 by tenant
| eval pass_percent = round(100-((error_request4+error_request5)/total_request*100),2) | where total_request >1
| table tenant, pass_percent, total_request | sort -total_request limit=10

These 2 queries have 90% search criteria common except sorting by column

I want to union of two in one query and extract even duplicate result, what will be that one query please?

[dezmadi]

Thanks for the quick reply, however there are 2 issues with first query(If I am not applying splitter), 1: I am getting passorder and totalreqorder also as part of table, also with where passorder<=3 AND totalreqorder <=10, I am getting just 3 records

In second query, its saying "Error in 'eval' command: The expression is malformed. Expected )." However format seems to be correct

[PickleRick]

Bah. My bad. I was writing it on my tablet, without splunk at hand to verify, and mixed different syntaxes 🙂

The "if" command indeed lacks parentheses

| eval splitter=mvappend(if(passorder<=3,"split",null()),if(totalreqorder<=10,"split",null()))

[dezmadi]

Cheers , it works now, Any suggestion how can we remove passorder, splitter and totalreqorder from table, we don't want those

[PickleRick]

| fields - passorder and so on 😉

[PickleRick · Accepted solution]

There could be a more practical way like re-using results from a saved job, but assuming that you really really want to do it in one search, you have to simply prepare your results, sort, count and filter.

<your base earch up to:>
| table tenant pass_percent total_request
| sort - pass_percent
| streamstats count as passorder
| sort -  total_request
| streamstats count as totalreqorder
| where passorder<=3 AND totalreqorder <=10

Now you have only those results that are in either top 3 of pass_percent or top 10 of total_request.

The only downside is that if a results meets both those conditions it's listed only once. If you want it twice, you'd need to duplicate those rows. Something like that:

| eval splitter=mvappend(if passorder<=3,"split",null(),if totalreqorder<=10,"split",null())
| mvexpand splitter

I understand you can remove the extra fields on your own 😉