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 |
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
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
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 |
| stats
count(eval(status="pass")) AS passed
count(eval(status="failed" )) AS failed
BY name
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
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?