I've 2 indexes "abc" and "def". There is a field "account_number" in index "abc" and a field "Emp_nummber" in index "def". I want to find the total number of events, for the accounts present only in "abc" and not in "def",
I wrote the below query but it seems I'm getting all the accounts which are present only in "abc" and also the accounts which are present in both "abc" and "def".
(index=abc sourcetype=xyz event_type= "FAIL")
OR
(index=def (ACTION=10 OR ACTION=20 OR ACTION=30))
| eval dex1= if(index=="abc",1,0)
| stats min(_time), sum(dex1) as dex1 by account_number, session_id | where dex1 > 0
| stats sum(dex1) as Final_number
could you try?
(index=abc sourcetype=xyz event_type= "FAIL") NOT [search index=def (ACTION=10 OR ACTION=20 OR ACTION=30)| dedup Emp_nummber | table Emp_nummber | rename Emp_nummber as account_number ]
| stats count by account_number
OR
(index=abc sourcetype=xyz event_type= "FAIL") OR (index=def (ACTION=10 OR ACTION=20 OR ACTION=30))
| dedup Emp_nummber keepempty=true
| dedup account_number keepempty=true
| eval myNumber=coalesce(Emp_nummber,account_number)
| eval from_index1=if(index="abc",1,null())
| stats dc(index) AS num_occurences sum(from_index1) AS from_index1 by myNumber
| where num_occurences=1 AND from_index1=1
| table myNumber