How can I correlate two fields which exist in separate events but share a common field?

[pladamsplunk]

I have a group of events which has the sourcetype "users"

The events within sourcetype=users contain the format:

username
field2
field3
group_access: "group1, group2, group3, groupX"
field5...

I also have logs within the same index which group together under the sourcetype=hosts

...

The events within sourcetype=hosts contain the format:

hostname
field2
field3
group_access: "group1, group2"
field5...

...

I want to correlate users with access to certain groups to hosts which have common group assignment. For example using the events below

user1
field2
field3
group_access: "group1, group3,"
field5...

---

host1
field2
field3
group_access: "group2"
field5...

host2
field2
field3
group_access: "group1, group3"
field5...

host3
field2
field3
group_access: "group1, group2"
field5...

I want to show that user 1 has access to host 2 and host 3 by correlation of the group_access. How can i create a query which accomplishes this and expresses it in a table

[renjith_nair · Accepted solution]

@pladamsplunk,

Can you give this a try ?

 index= "your index" (sourcetype="hosts" OR sourcetype="users")
| rex field=_raw "group_access: \"(?<groups>.+)\""| makemv groups delim=","| mvexpand groups
| eventstats values(user) as user by groups
| where isnotnull(hostname)| mvexpand user  | stats values(hostname) as hostnames by user |nomv hostnames

You may remove | rex field=_raw "group_access: \"(?<groups>.+)\"" if you already have a field groups or adjust the rex to match your original events.

Sample data used :

sourcetype=users

user="user1" ,group_access: "group1, group2, group3, groupX"
user="user2",group_access: "group5, group6"
user="user3",group_access: "group1, group6"

sourcetype=hosts

hostname="host1",group_access: "group2"
hostname="host2",group_access: "group1, group3"
hostname="host3",group_access: "group1, group2"

Result

user    hostnames
user1   host1 host2 host3
user3   host2 host3

[pladamsplunk]

This worked!