Need help in extracting results from two indexes?

[amaurya1]

In the below query, I'm using indexes "abc" and "def" and extracting the results only for the accounts which are present in index "abc" and not in "def" for each hour.
The query works fine but I've an additional requirement where I want to have a third column where I want the count of events coming only from index "abc" in each hour. I'm not able to add that condition, can someone please help?

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts | where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails

[Anantha123]

if you want to see the count in same timechart then use appendcols and add the total events of 1st query.

(index=abc sourcetype=xyz event_type= "Only_Fail")
OR
( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total ]

[amaurya1]

Hi @Anantha123.. thanks for your reply but this is not giving the correct answer. This query will give the total events only for the accounts which have been received from the existing query(ie before appendcols). I want all the events from the index abc with these conditions (index=abc sourcetype=xyz event_type= "Only_Fail")

[Anantha123]

appendcols doesnot take any values from before searches . Appendcols queries as new query . Else can you try doing the calc the total and then use appendcols to extract accounts .

(index=abc sourcetype=xyz event_type= "Only_Fail")
| timechart count as Total
| appendcols
[ search (index=abc sourcetype=xyz event_type= "Only_Fail") OR ( index=def (TYPE1=10 OR TYPE2=20) )
| eval dex1 = if(index=="abc", 1, 0)
| eval dex2 = if(index=="def", 1, 0)
| eval myaccounts = coalesce(CUST_ID, account_number)
| stats min(_time) as _time, sum(dex1) as dex1, sum(dex2) as dex2 by myaccounts
| where dex2 = 0 and dex1 > 0
| timechart span=1h count(myaccounts) as total_accounts,sum(dex1) as all_fails ]