Search code examples
kqlazure-sentinel

Azure Sentinel Kusto query table with data from another query

I'm trying to find a way to use the Azure Sentinel to pull all DNS results to a domain based upon a Security Alert.

Under the Security Alert table, they provide the domain name for an event as part of a JSON, here is the table for extracting that data.

SecurityAlert
| where parse_json(ExtendedProperties).AnalyticDescription == "Usage of digital currency mining pool"
| extend DomainName_ = tostring(parse_json(ExtendedProperties).DomainName);

What I would like to do is take that query, and then Query the DnsEvents table to find all queries that match the domain name on the table Name. An example of the query is

DnsEvents
| where Name contains "xmr-au1.nanopool.org"

How can I perform the second query but use the data from the first query to filter?

Solution

  • you could try something like this:

    let domain_names = 
   SecurityAlert
   | where ExtendedProperties has 'Usage of digital currency mining pool' // this line is optional, but may improve performance
   | extend props = parse_json(ExtendedProperties).
   | where props.AnalyticDescription == "Usage of digital currency mining pool"
   | project DomainName_ = tostring(props.DomainName)
;
DnsEvents
| where Name has_any (domain_names)