This article is contributed. See the original author and article here.
We are strong supporters of automation, and fully acknowledge the value of automating repetitive actions and being able to adjust technology to the specific security practices and processes used by our customers and partners. This is what motivates us in developing and enriching our API layer. But as we all know, with great scale comes great responsibility, and here efficiency is the name of the game.
This blog series will provide you best practices and recommendations on how to best use the different Microsoft 365 Defender features and APIs, in the most efficient way to power your automation to achieve the outcome you are desire.
In this first blog we will focus on two aspects:
- Don’t automatically default to the Advanced hunting API
- If you do need to use the Advanced hunting API for your scenario, how to use it in the most optimal way.
When to use the Advanced hunting API and when to use other APIs / features?
The Advanced hunting API is a very robust capability that enables retrieving raw data from all Microsoft 365 Defender products (covering endpoints, identities, applications docs and email), and can also be leveraged to generate statistics on entities, translating identifiers, e.g. to which machine IP X.X.X.X belongs to. While this is a great feature with broad reach across your data, it can also be challenging to maintain, because;
- More team members need to know the internals of KQL to leverage it, and;
- Consuming the hunting resource pool where there is no real need for that
Below are a few examples of how we have developed a dedicated API to provide you with the intended answer in a single API call:
1. You have a 3rd party alert on an IP address. You would like to see which device this IP was assigned to at that time and to get more information on this device. Easy ! You can do it by :
a. First using Find devices by internal IP API – Find devices seen with the requested internal IP in the time range of 15 minutes prior and after a given timestamp.
b. Once you have device ID you can use Get machine by ID API to get more details on the device including its OS Platform, MDE groups, tags, and exposure level.
2. You have a malicious domain IOC and you would like to see its prevalence in your organization.
Easy! You can use Get domain statistics API for that, it retrieves the organization statistics on the given domain for the lookback time you configured, by default the last 30 days, based on Microsoft Defender for Endpoint (MDE) including:
a. Prevalence
b. First seen
c. Last seen
For example: https://wdatpapi-eus-stg.cloudapp.net/api/domains/microsoft.com/stats?lookBackHours=24
3. You have a list of IOCs, and you would like to make sure you are alerted if there is any activity associated with this URL in your organization.
To implement this scenario you can use Indicators:
a. Add IOCs to MDE indicators via Indicators API and set the required action (“Alert” or “Alert and Block”).
b. To check if any of the IOCs was observed in the organization in the last 30 days, you can run a single Advanced hunting query:
// See if any process created a file matching a hash on the list
let covidIndicators = (externaldata(TimeGenerated:datetime, FileHashValue:string, FileHashType: string )
[@”https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/Microsoft.Covid19.Indicators.csv”]
with (format=”csv”))
| where FileHashType == ‘sha256’; //and TimeGenerated > ago(1d);
covidIndicators
| join (DeviceFileEvents
| where ActionType == ‘FileCreated’
| take 100) on $left.FileHashValue == $right.SHA256
How to optimize your Advanced hunting queries
Once you determine that the only way to resolve your scenario is using Advanced hunting queries, you should write efficient optimized queries so your queries will execute faster and will consume less resources. Queries may be throttled or limited based on how they’re written, to limit impact to other sessions. You can read all our best practices recommendations, and also watch this webcast to learn more. In this section we will highlight a few recommendations to improve query performance.
- Always use time filters as your first query condition. Most of the time you will use Advanced hunting to get more information on an entity following an incident, so make sure to insert the time of the incident, and narrow your lookback time. The shorter the lookback time is, the faster the query will be executed.
There are multiple ways to insert time filters to your query.
Scenario example – get all logon activities of the Finance departments users in Office 365.
// Filter timestamp in the query using “ago”
IdentityInfo
| where Department == “Finance”
| distinct AccountObjectId
| join (IdentityLogonEvents | where Timestamp > ago(10d)) on AccountObjectId
| where Application == “Office 365”
// Filter timestamp in the query using “between”
let selectedTimestamp = datetime(2020-11-12T19:35:03.9859771Z);
IdentityInfo
| where Department == “Finance”
| distinct AccountObjectId
| join (IdentityLogonEvents | where Timestamp between ((selectedTimestamp – 2h) .. (selectedTimestamp + 2h))) on AccountObjectId
| where Application == “Office 365”
In general, always filter your query by adding Where conditions, so it will be accurate and will query for the exact data you are looking for.
2. Only use “join” when it is necessary for your scenario.
a. If you are using a join, try to reduce the dataset before joining to limit the join size. Filter the table on the left side, to reduce its size as much as you can.
b. Use an accurate key for the join.
c. Choose the join flavor(kind) according to your scenario.
In the following example we want to see all details of emails and their attachments.
The following example is an inefficient query, because:
a. EmailEvents table is the largest table, it should never be on the left side of the join, without substantial filtering on it.
b. Join kind=leftouter returns all emails, including ones without attachments, which make the result set very large. We don’t need to see emails without attachments therefore this kind of join is not the right kind for this scenario.
c. The Key of the join is not accurate , NetworkMessageId. This is an email identifier, but the same email can be set to multiple recipients.
EmailEvents
| project NetworkMessageId, Subject, Timestamp, SenderFromAddress , SenderIPv4 , RecipientEmailAddress , AttachmentCount
| join kind=leftouter(EmailAttachmentInfo
| project NetworkMessageId,FileName, FileType, MalwareFilterVerdict, SHA256, RecipientEmailAddress )
on NetworkMessageId
This query should be changed and improved to the following query by:
a. Putting the smaller table, EmailAttachmentInfo, on the left.
b. Increasing join accuracy using join kind=inner
c. Using an accurate key for the join (NetworkMessageId, RecipientEmailAddress)
d. Filtering the EmailEvents table to only include emails with attachments before the join.
// Smaller table on the left side, with kind = inner, as default join (innerunique)
// will remove left side duplications, so if a single email has more than one attachments we will miss it
EmailAttachmentInfo
| project NetworkMessageId, FileName, FileType, MalwareFilterVerdict, SHA256, RecipientEmailAddress
| join kind=inner
(EmailEvents
| where AttachmentCount > 0
|project NetworkMessageId, Subject, Timestamp, SenderFromAddress , SenderIPv4 , RecipientEmailAddress , AttachmentCount)
on NetworkMessageId, RecipientEmailAddress
3. When you want to search for an attribute/entity in multiple tables, use the search in operator instead of using union. For example, if you want to search for list of Urls, use the following query:
let ListOfIoc = dynamic([“t20saudiarabia@outlook.sa”, “t20saudiarabia@hotmail.com”, “t20saudiarabia@gmail.com”, “munichconference@outlook.com”,
“munichconference@outlook.de”, “munichconference1962@gmail.com”, “ctldl.windowsupdate.com”]);
search in (DeviceNetworkEvents, DeviceFileEvents, DeviceEvents, EmailUrlInfo )
Timestamp > ago(1d) and
RemoteUrl in (ListOfIoc) or FileOriginUrl in (ListOfIoc) or FileOriginReferrerUrl in (ListOfIoc)
4. Using “Has” is better than “contains”: When looking for full tokens, “has” is more efficient,
since it doesn’t look for substrings.
Instead of using “contains”:
DeviceNetworkEvents
| where RemoteUrl contains “microsoft.com”
| take 50
Use “has”:
DeviceNetworkEvents
| where RemoteUrl has “microsoft.com”
| take 50
If possible, Use case-sensitive operators
DeviceNetworkEvents
| where RemoteUrl has_cs “microsoft.com”
| take 50
For more information about Advanced hunting and the features discussed in this article, read:
- Advanced hunting overview
- Advanced hunting best practices
- Advanced hunting APIs – Microsoft 365 security | Microsoft Docs
- Advanced hunting quota and resources
- Microsoft 365 Defender APIs
- Microsoft Defender for Endpoint APIs
As always, we’d love to know what you think. Leave us feedback directly on Microsoft 365 security center or start a discussion in Microsoft 365 Defender community.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments