How do you count Unique IDs, in both indexes, based on a specific event?

[diegofavoretto]

I am trying to get Unique IDs (appears in both indexes) but I only want to count if there is event_name="AccountFinalized OR event_name="AccountActivated" in index 1 AND event_name="Deposit" in index 2.

(index=capi_event event_name="AccountFinalized" OR event_name="AccountActivated" account_id ) OR (index=piq_event ) | join type=inner  "account_id" [search index=piq_event event_name="DEPOSIT" account_id state!="WAITING_INPUT"] | stats dc(account_id) AS uniqueID dc(status) AS uniqueStatus count(state) AS State by event_name | 

[valiquet]

(index=capi_event event_name="AccountFinalized" OR event_name="AccountActivated" account_id ) AND ( index=piq_event event_name="DEPOSIT" account_id state!="WAITING_INPUT")
| stats values(account_id)

[whrg]

Hello @diegofavoretto,

You can use a subsearch to filter for IDs in the second index:

index=capi_event event_name="AccountFinalized" OR event_name="AccountActivated" [search index=piq_event event_name="DEPOSIT" | table account_id]
| stats ...