Subsearch and Join(?) for VPN+Azure+O365 correlation

[fdevera]

I'm trying to look at all of our users using personal VPN who have accessed O365 (Sharepoint, OneDrive, etc.) from their personal systems.
For starters we're trying to combine VPN+Azure+O365 activity logs. Any advice on how to do that with the following information?
- First attempt at combining returned correct results for the day but changed in 30 minutes for an unknown reason.
- And yes the cip referenced in VPN logs is the external IP and NOT the VPN IP address.

index=network sourcetype=syslog_vpn eventtype=vpnuser device_type="Personal Device"
| rename vpn_uid as user
| lookup uid2userinfo user OUTPUT FULL_NAME
| rex "(?i) cip:(?P<cip>[^ ]+)"
| lookup dnslookup clientip AS cip OUTPUT clienthost AS chost
| table _time type user FULL_NAME device_type cip chost clientos country employee_type tunnel_mode
- cip is the "ExtIP" field I want to match on (but once you start adding sub searches it breaks the rex for some reason)
- I care about the fields in the table and would lke to add Workload and ObjectId fields to it from the o365_activity sourcetype

index=azuread deviceDetail.trustType=null status.errorCode=0
- ipAddress is the "ExtIP" field I want to match on

eventtype=o365_activity
- ClientIP is the "ExtIP" field I want to match on
- I care about the Workload and ObjectId fields and want them to be part of my results

[fdevera]

Any ideas?

[richgalloway]

Try appending each search to the previous results and then using stats to combine them.

index=network sourcetype=syslog_vpn eventtype=vpnuser device_type="Personal Device"
| rename vpn_uid as user
| lookup uid2userinfo user OUTPUT FULL_NAME
| rex "(?i) cip:(?P<cip>[^ ]+)"
| lookup dnslookup clientip AS cip OUTPUT clienthost AS chost
| append [ search index=azuread deviceDetail.trustType=null status.errorCode=0 | rename ipAddress as cip ]
| append [ search eventtype=o365_activity | rename ClientIP as cip ]
| stats values(*) as * by cip
| table _time type user FULL_NAME device_type cip chost clientos country employee_type tunnel_mode

[fdevera]

Thanks Rich but I only want VPN personal users who also have entries in O365 and Azure with those constraints. How would I do that? This returned 13,000 results which should only be less than 20 per day. 

[fdevera]

VPN personal users who have Azure activity. And out of that, only those that also have O365 activity.