KQL Cafe September 2023

Zeek

• Enrich your advanced hunting experience using network layer signals from Zeek

InboundInternetScanInspected

DeviceNetworkEvents
| where ActionType == "InboundInternetScanInspected"
| project TimeGenerated, DeviceName, LocalIP, LocalPort, RemoteIP, RemotePort, RemoteIPType
| extend geoinfo = geo_info_from_ip_address(LocalIP)
| extend country = tostring(geoinfo.country)
| extend city = tostring(geoinfo.city)
| extend state = tostring(geoinfo.state)
| project-away geoinfo

FTP

DeviceNetworkEvents
| where ActionType == "FtpConnectionInspected"
| extend json = todynamic(AdditionalFields)
| extend command = tostring(json.command)
| extend reply_code = tostring(json.reply_code)
| extend reply_msg = tostring(json.reply_msg)
| extend direction = tostring(json.direction)
| extend user = tostring(json.user)
| extend arg = tostring(json.arg)
| extend cwd = tostring(json.cwd)

Azure Resource Graph

• Query Azure Resource Graph from Azure Monitor
• Query data in Azure Data Explorer and Azure Resource Graph from Azure Monitor
• Azure Resource Graph table and resource type reference

Show all Resource Types

arg("").Resources
| distinct type

Log Analytics Workspace Info

arg("").Resources
| where type == "microsoft.operationalinsights/workspaces"
| extend SKUName = tostring(parse_json(tostring(properties.sku)).name)
| extend dailyQuotaGb = tostring(parse_json(tostring(properties.workspaceCapping)).dailyQuotaGb)
| extend quotaNextResetTime = todatetime(tostring(parse_json(tostring(properties.workspaceCapping)).quotaNextResetTime))
| extend retentionInDays = tostring(properties.retentionInDays)
| project name, location, resourceGroup, retentionInDays,SKUName, dailyQuotaGb, quotaNextResetTime

Identify Azure Subscriptions that are not monitored by the Azure Activity Data Connector in Sentinel

// Identify Azure Subscriptions that are not monitored by the Azure Activity Data Connector in Sentinel
let allsubscriptions = 
arg("").resourcecontainers
| where type == "microsoft.resources/subscriptions"
| distinct subscriptionId, name;
allsubscriptions
| join kind=leftouter  (AzureActivity
| extend AzureActivitySyubscriptionId = SubscriptionId
| distinct AzureActivitySyubscriptionId)
on $left. subscriptionId == $right.AzureActivitySyubscriptionId
| extend IsMonitored = iff(isempty(AzureActivitySyubscriptionId),"No","Yes")
| project subscriptionId, name, AzureActivitySyubscriptionId, IsMonitored

KQLQuery.com

• kqlquery.com

Beta KQL Search.com

• Beta KQL Search

Graph Operators

• Graph operators (Preview) - Azure Data Explorer | Microsoft Learn

IdentityInfo

• IdentityInfo
• Identity hunting with an enhanced IdentityInfo table

MDI Disabling Accounts (Automatic Attack Disruption)

let AllDomainControllers =
        DeviceNetworkEvents
        | where TimeGenerated > ago(7d)
        | where LocalPort == 88
        | where LocalIPType == "FourToSixMapping"
        | extend DCDevicename = tostring(split(DeviceName,".")[0])
        | distinct DCDevicename;
IdentityDirectoryEvents
| where TimeGenerated > ago(190d)
| where ActionType == "Account disabled"
| extend ACTOR_DEVICE = tolower(tostring(AdditionalFields.["ACTOR.DEVICE"]))
| where isnotempty( ACTOR_DEVICE)
| where ACTOR_DEVICE in (AllDomainControllers)
| project TimeGenerated, TargetAccountDisplayName, ACTOR_DEVICE

Set query now

set query_now = datetime('2023-08-04T14:46:34.3319494Z');
SigninLogs
| where TimeGenerated between (ago(1d) .. now())