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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...