Dashboards & Visualizations

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

dezmadi
Path Finder

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?

Labels (2)
Tags (2)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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 😉

View solution in original post

dezmadi
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust
| fields - passorder and so on 😉
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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 😉

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...