Archive
Highlighted

Query to get results from multiple indexes?

Explorer

I've 2 indexes "abc" and "def". There is a field "accountnumber" in index "abc" and a field "Empnummber" 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 eventtype= "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 accountnumber, sessionid | where dex1 > 0
| stats sum(dex1) as Final_number

0 Karma
Highlighted

Re: Query to get results from multiple indexes?

SplunkTrust
SplunkTrust

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
0 Karma