Used Microsoft 365 Defender Advanced Hunting for Querying Custom Log Data

Microsoft 365 Defender Advanced Hunting uses the Kusto Query Language (KQL) and information within Microsoft 365 to identify potential risks. Once licensed, most tenants will see the following tables of data:

AlertInfo
AlertEvidence
Identity
AADSpnSignInEventsBeta
AADSignInventsBeta
EmailEvents
EmailAttachmentInfo
EmailUrlInfo
EmailPostDeliveryEvents
UrlClickEvents
DeviceInfo
DeviceNetworkInfo
DeviceProcessEvents
DeviceNetworkEvents

DeviceFileEvents
DeviceRegistryEvents
DeviceLogonEvents
DeviceImageLoadEvents
DeviceEvents
DeviceFileCertificateInfo
DeviceTvmSoftwareVulnerabilities
DeviceTvmSoftwareVulnerabilitiesKB
DeviceTvmSecureConfigurationAssessment
DeviceTvmSecureConfigurationAssessmentKB
DeviceTvmSoftwareInventory
DeviceTvmInfoGathering
DeviceTvmInfoGatheringKB
DeviceTvmSoftwareEvidenceBeta

These tables contain varying types of data which you can query or join together. Logically, you could take sign-in events, map them to devices, and then threats. An example of this could be:

let winamp = 
DeviceTvmSoftwareVulnerabilities
| where SoftwareName contains "winamp"
| summarize makelist(DeviceName);
DeviceFileEvents
| where DeviceName in (winamp)
| where FileName contains "mp3"

The above Kusto Query Language (KLQ) query queries the first table looking for devices that have “Winamp,” then joins that with looking for “mp3” files, matching the two together. If you have no idea what “Winamp” is, then I suggest some light reading here: “https://en.wikipedia.org/wiki/Winamp“

Side note, I didn’t realize there is a new version of “Winamp“: https://www.winamp.com.

As you can see from the query, you can quickly join tables together to query each other. Though that is a great thing to use and do, I want to focus on when you have some of your data you want to combine with the tables or use the power of Kusto Query Language (KQL) query with some log data you have.

The good news is you can by adding your data to the query editor as data tables. A simple example could be you have log entries from an older application that you would like to combine with the standard Azure Active Directory or Microsoft 365 information, but you don’t have connectors for it, and you are not using Microsoft Sentinel. You could even have newer log entries from a system that stores them in a custom format that is hard to understand. Let’s pretend that these log entries are in a CSV format and contain information relating to security alerts mapped to MITRE ATT&CK categories. As you can see, this entry focuses on a Microsoft 365 password spray attack.

2023-03-19T10:00:00Z,
'UserLoggedIn',
'attacker',
'192.168.1.100','
Mozilla/5.0 (Windows NT 10.0, Win64, x64) AppleWebKit/537.36 
(KHTML, like Gecko)
Chrome/95.0.4638.54 Safari/537.36','
Microsoft Office 365',
'Microsoft 365 Tenant',
'Success','
NULL',
'{ 
	\"AttackMethod\": \"Password Spray Attack\", 
	\"AttackStage\": \"Initial Access\" }',
'T1078',
'Initial Access'

To use these within the Advanced Hunting Query Editor, we can create a data table and populate the needed rows. For this example, we need to format the dates correctly, which we can do using the “datetime” function.

let events = datatable(Timestamp:datetime, 
Operation:string, User:string, 
ClientIP:string, UserAgent:string, Source:string, 
Target:string, ResultStatus:string, 
ResultError:string, ExtendedProperties:string, 
ID:string, Details:string)
[
// Add rows of data here
];
events

The query creates a variable called “events” and then creates the header row, followed by the rows of data in the required format.

let events = datatable(Timestamp:datetime, 
Operation:string, User:string, 
ClientIP:string, UserAgent:string, Source:string, 
Target:string, ResultStatus:string, 
ResultError:string, ExtendedProperties:string, 
ID:string, Details:string)
[
datetime(2023-03-19T10:00:00Z),'UserLoggedIn','attacker',
'192.168.1.100',
'Mozilla/5.0 (Windows NT 10.0, Win64, x64) AppleWebKit/537.36
 (KHTML, like Gecko) 
Chrome/95.0.4638.54 Safari/537.36','Microsoft Office 365',
'Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"Password Spray Attack\",
\"AttackStage\": \"Initial Access\" }',
'T1078','Initial Access',
datetime(2023-03-19T10:02:00Z),'UserLoggedIn','attacker',
'192.168.1.100',
'PowerShell/7','Microsoft 365 Tenant','Microsoft 365 Tenant',
'Success','NULL',
'{ \"AttackMethod\": \"PowerShell Execution\", 
\"AttackStage\": \"Execution\" }',
'T1086','Execution'
];
events

You can execute this command as it will populate the results as if you had performed a standard SQL SELECT statement.

The great thing is that you can add extra query options just like any other Kusto Query within Advanced Hunting. For example, we can sort and extend fields containing JSON data and then project (SELECT) the fields to show.

let events = datatable(Timestamp:datetime, 
Operation:string, User:string, 
ClientIP:string, UserAgent:string, Source:string, 
Target:string, ResultStatus:string, 
ResultError:string, ExtendedProperties:string, 
ID:string, Details:string)
[
datetime(2023-03-19T10:00:00Z),'UserLoggedIn','attacker',
'192.168.1.100',
'Mozilla/5.0 (Windows NT 10.0, Win64, x64) AppleWebKit/537.36
 (KHTML, like Gecko) 
Chrome/95.0.4638.54 Safari/537.36','Microsoft Office 365',
'Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"Password Spray Attack\",
\"AttackStage\": \"Initial Access\" }',
'T1078','Initial Access',
datetime(2023-03-19T10:02:00Z),'UserLoggedIn','attacker',
'192.168.1.100',
'PowerShell/7','Microsoft 365 Tenant','Microsoft 365 Tenant',
'Success','NULL',
'{ \"AttackMethod\": \"PowerShell Execution\", 
\"AttackStage\": \"Execution\" }',
'T1086','Execution'
];
events
| sort by Timestamp asc
| extend ExtendedProperties = parse_json(ExtendedProperties)
| project Timestamp, Operation, User, ClientIP, Source, Target, 
ResultStatus, ID, Details, 
AttackMethod = ExtendedProperties.AttackMethod, 
AttackStage = ExtendedProperties.AttackStage

The above query sorts the data by the timestamp, then parses the “ExtendedProperties” column into values from JSON.

# Existing format
'{ 
	\"AttackMethod\": \"PowerShell Execution\", 
	\"AttackStage\": \"Execution\"
}'

After executing, you can now see the “AttackMethod” and “AttackStage” as discrete columns instead of the existing single column “ExtendedProperties” in the original data.

If we wanted to, we could join this data with existing data. For example, suppose we modify the original data to replace the user “Attacker” and match an existing account from the Microsoft 365 Tenant. In that case, we can then use it within a join statement. Here is the modified data:

[
datetime(2023-03-19T10:00:00Z),'UserLoggedIn',
'admin@msdx.onmicrosoft.com','192.168.1.100',
'Mozilla/5.0 (Windows NT 10.0, Win64, x64) AppleWebKit/537.36 
(KHTML, like Gecko) 
Chrome/95.0.4638.54 Safari/537.36','Microsoft Office 365','
Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"Password Spray Attack\", 
\"AttackStage\": \"Initial Access\" }',
'T1078','Initial Access',
datetime(2023-03-19T10:02:00Z),'UserLoggedIn',
'admin@msdx.onmicrosoft.com','192.168.1.100',
'PowerShell/7','Microsoft 365 Tenant',
'Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"PowerShell Execution\", 
\"AttackStage\": \"Execution\" }',
'T1086','Execution'
];

If we choose the “AADSignInEventsBeta” you can combine the custom data with stored events. For example, we could change the query to add extra columns to the results. For this query, we want to select all the results from our custom data, then join to the “AADSignInEventsBeta” table by using the “AccountUpn” field that matches the “User” in the custom data, and then add “AccountDisplayName,” “Latitude,” “Longitude,” and “LogonType” columns.

let events = datatable(Timestamp:datetime, 
Operation:string, User:string, 
ClientIP:string, UserAgent:string, Source:string, 
Target:string, ResultStatus:string, 
ResultError:string, ExtendedProperties:string, 
ID:string, Details:string)
[
datetime(2023-03-19T10:00:00Z),'UserLoggedIn',
'admin@msdx.onmicrosoft.com','192.168.1.100',
'Mozilla/5.0 (Windows NT 10.0, Win64, x64) AppleWebKit/537.36 
(KHTML, like Gecko) 
Chrome/95.0.4638.54 Safari/537.36','Microsoft Office 365','
Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"Password Spray Attack\", 
\"AttackStage\": \"Initial Access\" }',
'T1078','Initial Access',
datetime(2023-03-19T10:02:00Z),'UserLoggedIn',
'admin@msdx.onmicrosoft.com','192.168.1.100',
'PowerShell/7','Microsoft 365 Tenant',
'Microsoft 365 Tenant','Success','NULL',
'{ \"AttackMethod\": \"PowerShell Execution\", 
\"AttackStage\": \"Execution\" }',
'T1086','Execution'
];;
events
| join kind=inner AADSignInEventsBeta on $left.User == $right.AccountUpn
| sort by Timestamp asc
| extend ExtendedProperties = parse_json(ExtendedProperties)
| project Timestamp, Operation, User, ClientIP, Source, Target, 
ResultStatus, ID, Details, 
AttackMethod = ExtendedProperties.AttackMethod, 
AttackStage = ExtendedProperties.AttackStage, 
AccountDisplayName, Latitude, Longitude, LogonType

As you can see, the Advanced Hunting Kusto Query Language (KQL) query capabilities allow you to query internal logs and data you manually add as data tables or even link to the query editor. For example, if the custom data was in a flat file stored on a web server, we could still use it by using this syntax instead of loading the data table variable.

let events = datatable(Timestamp:datetime, 
Operation:string, User:string, 
ClientIP:string, UserAgent:string, Source:string, 
Target:string, ResultStatus:string, 
ResultError:string, ExtendedProperties:string, 
ID:string, Details:string)
[
@"https://gist.githubusercontent.com/helloitsliam/ad9a59600a10dab578a5fd87c93790e0/raw/17c17392c94395f33c5956491c9df31821a83707/Events"
]
with(format="csv");
events
| join kind=inner AADSignInEventsBeta on $left.User == $right.AccountUpn
| sort by Timestamp asc
| extend ExtendedProperties = parse_json(ExtendedProperties)
| project Timestamp, Operation, User, ClientIP, Source, Target, 
ResultStatus, ID, Details, 
AttackMethod = ExtendedProperties.AttackMethod, 
AttackStage = ExtendedProperties.AttackStage, 
AccountDisplayName, Latitude, Longitude, LogonType

This is just an example of what you could do; there is much more you can do using these powerful capabilities. More to come.