Forum Discussion
External data in MDE advanced hunting
is it possible to reference external data, such as in Azure storage or data service, inside an MDE advanced hunting query?
- Yes that is an option. you will want to use the External Data operator.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuredataexplorer
Here is an example of how it will work.
let AsrDescriptionTable = externaldata (RuleDescription:string, RuleGuid:string)
[
@"http://dougsbaker.com/wp-content/uploads/2021/02/ASR-KQL.txt"
]
with(format="csv");
DeviceEvents
| where ActionType startswith "Asr" and InitiatingProcessFileName endswith ".exe"
| extend RuleGuid = tolower(tostring(parsejson(AdditionalFields).RuleId))
| extend AuditMode = parse_json(AdditionalFields).IsAudit
| join kind = leftouter (AsrDescriptionTable | project RuleGuid = tolower(RuleGuid), RuleDescription) on RuleGuid
| summarize count() by tostring(AuditMode), RuleDescription, ActionType
Yes that is an option. you will want to use the External Data operator.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuredataexplorer
Here is an example of how it will work.
let AsrDescriptionTable = externaldata (RuleDescription:string, RuleGuid:string)
[
@"http://dougsbaker.com/wp-content/uploads/2021/02/ASR-KQL.txt"
]
with(format="csv");
DeviceEvents
| where ActionType startswith "Asr" and InitiatingProcessFileName endswith ".exe"
| extend RuleGuid = tolower(tostring(parsejson(AdditionalFields).RuleId))
| extend AuditMode = parse_json(AdditionalFields).IsAudit
| join kind = leftouter (AsrDescriptionTable | project RuleGuid = tolower(RuleGuid), RuleDescription) on RuleGuid
| summarize count() by tostring(AuditMode), RuleDescription, ActionType
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuredataexplorer
Here is an example of how it will work.
let AsrDescriptionTable = externaldata (RuleDescription:string, RuleGuid:string)
[
@"http://dougsbaker.com/wp-content/uploads/2021/02/ASR-KQL.txt"
]
with(format="csv");
DeviceEvents
| where ActionType startswith "Asr" and InitiatingProcessFileName endswith ".exe"
| extend RuleGuid = tolower(tostring(parsejson(AdditionalFields).RuleId))
| extend AuditMode = parse_json(AdditionalFields).IsAudit
| join kind = leftouter (AsrDescriptionTable | project RuleGuid = tolower(RuleGuid), RuleDescription) on RuleGuid
| summarize count() by tostring(AuditMode), RuleDescription, ActionType
Thanks! I thought I had tried this before to no avail, but it works so far. Now I need to figure out how to use this to reference data in our Azure storage hub. Baby steps.
