topic Re: How to join changes queries data output? in Dashboards & Visualizations

How to join changes queries data output?

Dzmitry — Wed, 14 Dec 2022 15:15:31 GMT

Hello

I found a strange behavior of the join command.

Here are two queries:

1) search status=pass | chart dc(id) as pass by name

2) search status=failed | chart dc(id) as failed by name

then I try to join these queries to get one table:

For some reason "failed" column values by the query #2 are different to values from join in the query #3, but "pass" column valuesare the same:

query 1 table		query 2 table
name	pass	name	failed
Tom	12	Tom	4
Jerry	13	Jerry	5

query 3 table
name	pass	failed
Tom	12	7
Jerry	13	9

Re: join changes queries data output

ITWhisperer — Wed, 14 Dec 2022 14:26:44 GMT

There doesn't appear to be anything wrong with the queries so perhaps it is something about your data or the actual queries rather than the example ones.

Please can you provide some real, but anonymised, data or indeed fabricated data, demonstrating the issue?

Re: How to join changes queries data output?

gcusello — Wed, 14 Dec 2022 15:57:10 GMT

Hi @Dzmitry ,

probably you have more than 50,000 results in the subsearch.

Anyway, use join only if you haven't any other solution, so explore something like this:

index=your_index (status="pass" OR status="failed") | stats dc(eval(if(status="pass",id,""))) AS pass dc(eval(if(status="failed",id,""))) AS failed BY name

Ciao.

Giuseppe

Re: How to join changes queries data output?

Dzmitry — Thu, 15 Dec 2022 16:20:04 GMT

Hi Giuseppe
Thank you for the help

May I ask one more question?
In case when I have to use count command
1) search status='passed' | stats count(status) as pass by name
2) search status='failed' | stats count(status) as failed by name
then I try to combine them:
3)
| stats
count(eval(if(status="pass", status, 0))) AS passed
count(eval(if(status="failed" , status, 0))) AS failed
BY name
| table name passed failed

Columns have same values

query 3 table	pass	failed
Tom	16	16
Jerry	18	18

Re: How to join changes queries data output?

Dzmitry — Thu, 15 Dec 2022 17:40:10 GMT

| stats
count(eval(status="pass")) AS passed
count(eval(status="failed" )) AS failed
BY name

Re: How to join changes queries data output?

gcusello — Fri, 16 Dec 2022 07:32:39 GMT

Hi @Dzmitry,

yes, in the stats command you can put more functions, also both dc and count and you can use eval in each of them.

Ciao,

Giuseppe