Splunk Search

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

fdevera
Path Finder

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

 

Labels (1)
0 Karma

fdevera
Path Finder

Any ideas?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

fdevera
Path Finder

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. 

0 Karma

fdevera
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...