Splunk Search

Searching through join results

pcookhayboo
Explorer

I've been working to recreate a query in Splunk from Microsoft Defender Endpoint that shows what files users have copied to USB Drives. The query works like this:

  • Step 1: Get all file USB Mount Events
  • Step 2: Get all file creation events on drives that are not C. 
  • Step 3: Join the above two datasources by Device ID. 
  • Step 4: Match drive letters and make sure the USB Mount time is less than File Create time. 

Here's Microsoft's query: Microsoft-365-Defender-Hunting-Queries/Exfiltration/Files copied to USB drives.md at master · micros...

In Splunk I get to step three and then I'm not able to filter values based on that. Below is my query so far. Any suggestions would be helpful. 

index=atp category="AdvancedHunting-DeviceFileEvents" properties.InitiatingProcessAccountName!="system" properties.ActionType="FileCreated" properties.FolderPath!="C:\\*" properties.FolderPath!="\\*"
| fields properties.ReportId, properties.DeviceId, properties.InitiatingProcessAccountDomain, properties.InitiatingProcessAccountName, properties.InitiatingProcessAccountUpn, properties.FileName, properties.FolderPath, properties.SHA256, properties.Timestamp, properties.SensitivityLabel, properties.IsAzureInfoProtectionApplied
| rename properties.ReportId as ReportId, properties.DeviceId as DeviceId, properties.InitiatingProcessAccountDomain as InitiatingProcessAccountDomain, properties.InitiatingProcessAccountName as InitiatingProcessAccountName, properties.InitiatingProcessAccountUpn as InitiatingProcessAccountUpn, properties.FileName as FileName, properties.FolderPath as FolderPath, properties.SHA256 as SHA256, properties.Timestamp as Timestamp, properties.SensitivityLabel as SensitivityLabel, properties.IsAzureInfoProtectionApplied as IsAzureInfoProtectionApplied
| eval Timestamp_epoch = strptime (Timestamp, "%Y-%m-%dT%H:%M:%S.%6N%Z")
| sort DeviceId, Timestamp desc
| join type=inner left=L right=R where L.DeviceId = R.DeviceId
[search index=atp category="AdvancedHunting-DeviceEvents" properties.ActionType="UsbDriveMounted" | spath input=properties.AdditionalFields | fields properties.DeviceId, properties.DeviceName, DriveLetter, properties.Timestamp, ProductName, SerialNumber, Manufacturer | sort properties.DeviceId, properties.Timestamp desc
| rename properties.DeviceId as DeviceId, properties.DeviceName as DeviceName, properties.Timestamp as MountTime
| eval MountTime_epoch = strptime (MountTime, "%Y-%m-%dT%H:%M:%S.%6N%Z") ]
| table L.FolderPath,R.DriveLetter, R.MountTime, R.MountTime_epoch, L.Timestamp, L.Timestamp_epoch


 

 

 

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Something like this?

index=atp category="AdvancedHunting-DeviceFileEvents"
  ( (properties.InitiatingProcessAccountName!="system" properties.ActionType="FileCreated" properties.FolderPath!="C:\\*" properties.FolderPath!="\\*")
  OR (category="AdvancedHunting-DeviceEvents" properties.ActionType="UsbDriveMounted") )
| spath input=properties.AdditionalFields
| fields properties.ReportId, properties.DeviceId, properties.InitiatingProcessAccountDomain, properties.InitiatingProcessAccountName, properties.InitiatingProcessAccountUpn, properties.FileName, properties.FolderPath, properties.SHA256, properties.Timestamp, properties.SensitivityLabel, properties.IsAzureInfoProtectionApplied properties.DeviceName, DriveLetter, properties.Timestamp, ProductName, SerialNumber, Manufacturer
| rename properties.ReportId as ReportId, properties.DeviceId as DeviceId, properties.InitiatingProcessAccountDomain as InitiatingProcessAccountDomain, properties.InitiatingProcessAccountName as InitiatingProcessAccountName, properties.InitiatingProcessAccountUpn as InitiatingProcessAccountUpn, properties.FileName as FileName, properties.FolderPath as FolderPath, properties.SHA256 as SHA256, properties.Timestamp as Timestamp, properties.SensitivityLabel as SensitivityLabel, properties.IsAzureInfoProtectionApplied as IsAzureInfoProtectionApplied, properties.DeviceName as DeviceName, properties.Timestamp as MountTime
| eval Timestamp_epoch = strptime (Timestamp, "%Y-%m-%dT%H:%M:%S.%6N%Z")
| eval MountTime_epoch = strptime (MountTime, "%Y-%m-%dT%H:%M:%S.%6N%Z")
| sort Timestamp MountTime
| stats list(FolderPath) as FolderPath, list(DriveLetter) as DriveLetter, list(MountTime) as MountTime, list(MountTime_epoch) as MountTime_epoch, list(Timestamp) as Timestamp, list(Timestamp_epoch) as Timestamp_epoch by DeviceId

Not sure what the real question/difficulty is.  The above really performs the same task as your join, just more efficient with stats.

Tags (1)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...