Join two indexes with the same field data

[JandrevdM]

Good day, I have a query that I would like to add more information onto. The query pulls all users that accessed a AI site and gives my data for weekdays as a 1 or 0 if the site was accessed. The query 1 gets a user from index db_it_network and I would like to add the department of each user by querying theindex=collect_identities sourcetype=ldap:query The users are displayed in the collect identities index as 'email' and their department in the bunit field 

index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" OR app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base | where date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" OR date_wday="thursday" OR date_wday="friday" | eval app=if(url_domain="www.perplexity.ai", url_domain, app) | table user, app, date_wday | stats count by user app date_wday | chart count by user app | sort app 0

 Note: the |stats | chart is necessary to distinct so that one user return results for one app per day

[JandrevdM]

user	bunit	gemini	perplexity	openai
[email protected]	HR	1	1	0
[email protected]	IT	0	1	1

This is the results that I am getting with the query without the bunit column which is what I want to add. So basically a join to see where the email=user (email is in index=collect_identities)

[ITWhisperer · Accepted solution]

Try something like this

index=db_it_network sourcetype=pan* url_domain="www.perplexity.ai" OR app=claude-base OR app=google-gemini* OR app=openai* OR app=bing-ai-base | where date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" OR date_wday="thursday" OR date_wday="friday" | eval app=if(url_domain="www.perplexity.ai", url_domain, app) | table user, app, date_wday | stats count by user app date_wday | chart count by user app
| join type=left user [search index=collect_identities | rename email as user | table user bunit]

[ITWhisperer]

It is not clear what you are trying to do here - after the chart command, the app field no longer exists so the sort is meaningless.

What are your expected results going to look like?

How do events in he collect_identities index relate to the events from the db_it_network index?