Unable to extract common values by joining indexes?

[amaurya1]

index=abc sourcetype=xyz 
 | eval is_passed=if(label=="PASS", 1, 0)
 | eval is_failed=if(label=="FAIL", 1, 0)
| stats min(_time) as _time, values(operator_id) as o_id, sum(is_passed) as pass, sum(is_failed) as fail by acc_no, session_no
| table o_id | where o_id!=""
| join o_id
[ search index=def (ACTION_TYPE=101 OR ACTION_TYPE=102) | stats values(worker_id) as o_id | table o_id]

I have two different indexes and I'm trying to find all the common values from the column
"operator_id" of 1st index and column "worker_id" from the 2nd index.
Even though there are common values in both the columns but still I'm getting no result.
Can someone please help me debug the issue?

[Vijeta]

@amaurya1 Try below

index=abc sourcetype=xyz 
| eval is_passed=if(label=="PASS", 1, 0)
| eval is_failed=if(label=="FAIL", 1, 0)
|where ISNOTNULL(operator_id)
| join operator_id
[ search index=def (ACTION_TYPE=101 OR ACTION_TYPE=102) | rename worker_id as operator_id|fields operator_id]| stats min(_time) as _time, values(operator_id) as o_id, sum(is_passed) as pass, sum(is_failed) as fail by acc_no, session_no

[amaurya1]

Hi @Vijeta even this query is working fine.. I think I got the mistake with my join. Thanks a lot.

[HiroshiSatoh · Accepted solution]

Try this!

index=abc sourcetype=xyz [ search index=def (ACTION_TYPE=101 OR ACTION_TYPE=102) | stats count by worker_id|rename worker_id as operator_id|table operator_id]
| eval is_passed=if(label=="PASS", 1, 0)
| eval is_failed=if(label=="FAIL", 1, 0)
| stats min(_time) as _time, values(operator_id) as o_id, sum(is_passed) as pass, sum(is_failed) as fail by acc_no, session_no