Forum Discussion
Advanced Hunting Data Schema
Hello everyone,
I have a question regarding the use of schema for Advanced Hunting queries.
We are an organization with several companies under our holding.
I need to recover the USB connections on the machines but only for one company and not the others.
I need to sort on Company Name for the user. But in the Advanced Hunting schema there are no fields to filter on this.
I looked specifically in UserInfo and DeviceInfo.
Here's the query I use to detect USBs. I need to filter by CompanyName to retrieve the list of devices or users for this company only.
DeviceEvents
| where ActionType == “PnpDeviceConnected”
| extend parsed=parse_json(AdditionalFields)
| project Timestamp, DeviceName, DeviceId=tostring(parsed.DeviceId), ClassName=tostring(parsed.ClassName)
| where ClassName == “DiskDrive”
| summarize UsbFirstSeen=min(Timestamp), UsbLastSeen=max(Timestamp) by DeviceId, DeviceName;
Is there another solution ?
Thanks in advance for your answers,
HKN
Hello everyone,
Thank you for your precious answers. Since I can't search on the CompanyName which doesn't exist in the Advanced Hunting schema and I didn't have a different email address for my users I looked for a different solution and found this:
I created an Admin Unit from Microsoft Defender Admin Center in the Identity Groups - AdminUnit section. I made a query and a dynamic group so that my users fall into it according to their Company name. Then, instead of using Advanced Hunting, I went to the Defender Audit portal and performed a search on the copied file to removable file activity on the admin unit I had created.Best regards,
HKN
8 Replies
Hello everyone,
Thank you for your precious answers. Since I can't search on the CompanyName which doesn't exist in the Advanced Hunting schema and I didn't have a different email address for my users I looked for a different solution and found this:
I created an Admin Unit from Microsoft Defender Admin Center in the Identity Groups - AdminUnit section. I made a query and a dynamic group so that my users fall into it according to their Company name. Then, instead of using Advanced Hunting, I went to the Defender Audit portal and performed a search on the copied file to removable file activity on the admin unit I had created.Best regards,
HKN
Hi,
You may try the below solution because it worked for me:
To filter USB connections by company name when the Advanced Hunting schema lacks a specific "CompanyName" field, you can use a workaround by leveraging information in other fields that indirectly tie devices or users to a company. For example, check if there are unique device naming conventions, user account naming formats, or domain names (from DeviceInfo or UserInfo) that can help identify the company. You can then add a filter to your query based on those attributes. For instance, if devices in the target company have a naming convention like "https://nulsbrawl.com/", you could use:
DeviceEvents | where ActionType == "PnpDeviceConnected" | extend parsed=parse_json(AdditionalFields) | project Timestamp, DeviceName, DeviceId=tostring(parsed.DeviceId), ClassName=tostring(parsed.ClassName) | where ClassName == "DiskDrive" | where DeviceName startswith "COMPANYNAME-" | summarize UsbFirstSeen=min(Timestamp), UsbLastSeen=max(Timestamp) by DeviceId, DeviceName;Alternatively, if domain information is available, filter on DeviceDomain or related fields in DeviceInfo. If no such indirect filters are viable, consider integrating company metadata into a custom table or enriching your data with external sources to bridge the gap.
Do you use different machine groups for each company, do the users have different email names?
If so, add this code to the end (moving the summarize to the final line)| join
(
DeviceNetworkEvents
| extend domainName = tostring(split(InitiatingProcessAccountUpn,'@').[1])
) on DeviceName
| where isnotempty( InitiatingProcessAccountUpn)
| summarize UsbFirstSeen=min(Timestamp), UsbLastSeen=max(Timestamp) by DeviceId, DeviceName, InitiatingProcessAccountUpn, domainName, MachineGroup
Really great and correct answer from Clive_Watson tested in my environment and it works well with this, I've slightly modified to filter as you search.
DeviceEvents | where ActionType == "PnpDeviceConnected" | extend parsed = parse_json(AdditionalFields) | project Timestamp, DeviceName, DeviceId = tostring(parsed.DeviceId), ClassName = tostring(parsed.ClassName) | where ClassName == "DiskDrive" | summarize UsbFirstSeen = min(Timestamp), UsbLastSeen = max(Timestamp) by DeviceId, DeviceName | join ( DeviceNetworkEvents | extend domainName = tostring(split(InitiatingProcessAccountUpn,'@').[1]) ) on DeviceName | where isnotempty(InitiatingProcessAccountUpn) and InitiatingProcessAccountUpn contains "@corpa.com" | summarize UsbFirstSeen=min(Timestamp), UsbLastSeen=max(Timestamp) by DeviceId, DeviceName, InitiatingProcessAccountUpn, domainNameThis will then bring only users that plugged USB and have email addresses containing 'corpa.com'
I don't suppose you are lucky enough that the different companies under the one holding company have named their devices with a naming standard for each?
i.e Corporation a has CORPA-Laptopname, and Corporation B has CORPB-Laptopnane
then you could use DeviceName startswith as below which will just find those devices named CORPA-
DeviceEvents | where ActionType == "PnpDeviceConnected" | extend parsed = parse_json(AdditionalFields) | project Timestamp, DeviceName, DeviceId = tostring(parsed.DeviceId), ClassName = tostring(parsed.ClassName) | where ClassName == "DiskDrive" and DeviceName startswith "CORPA-" | summarize UsbFirstSeen = min(Timestamp), UsbLastSeen = max(Timestamp) by DeviceId, DeviceNameUnfortunately, we use the same nomenclature for the devices.
did my solution help, or so you need something else?
