Splunk Search

Unable to extract common values by joining indexes?

amaurya1
Explorer
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?

0 Karma
1 Solution

HiroshiSatoh
Champion

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

View solution in original post

Vijeta
Influencer

@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
Explorer

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

0 Karma

HiroshiSatoh
Champion

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

Get Updates on the Splunk Community!

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...