How to join changes queries data output?

[Dzmitry]

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:

3) search status=pass | chart dc(id) as pass by name | table name pass
| join name [ search status=failed | chart dc(id) as failed by name | table name failed ]
| table name pass failed

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

[gcusello · Accepted solution]

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

[Dzmitry]

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

[Dzmitry]

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

[gcusello]

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

[ITWhisperer]

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?