Log analytics workspace and Azure Resource Graph queries

az-snippets.md

Following can be pasted into confluence as table

echo '|name|x|';echo '|----|----|'
az keyvault "$mode" list --vault-name $KvName --query "[].{name: name}" --output tsv | awk '{print "|"$1"| |"}'

# alternative
az keyvault "$mode" list --vault-name $KvName --output json | jq -r '.[] | "| \(.name) | |"'

KQL-snippets.md

Collection of KQL queries

list-kv-secret-gets.kql

AzureDiagnostics
| where OperationName =~ "SecretGet"
| where TimeGenerated > ago(180d)
| extend splitted = split(id_s, '/')
| extend SecretName = tostring(splitted[4])
| summarize count() by SecretName, identity_claim_unique_name_s, identity_claim_appid_g, ResultType

list-nics.kql

resources
| where type =~ 'microsoft.network/networkinterfaces'
//| extend 
| project
nicName = name,
privateIP = properties.ipConfigurations[0].properties.privateIPAddress,
publicIPId = tolower(properties.ipConfigurations[0].properties.publicIPAddress.id),
nicSubnetId = tolower(properties.ipConfigurations[0].properties.subnet.id),
nicNsg = tolower(properties.networkSecurityGroup.id),
vmId = tolower(properties.virtualMachine.id),
subnet = properties.ipConfigurations[0].properties.subnet.id,
privatelinkFqdns = strcat_array(properties.ipConfigurations[0].properties.privateLinkConnectionProperties.fqdns, ", "),
ipConfigurationsCount = array_length(properties.ipConfigurations),
privateLinkConnectionProperties = properties.ipConfigurations[0].properties.privateLinkConnectionProperties,
nicProperties = properties
| join kind=leftouter (
resources
| where ['type'] contains "publicIPAddresses"
| project pipProperties = properties, pipId = tolower(id), publicIPAdress = properties.ipAddress
) on $left.publicIPId == $right.pipId
| join kind=leftouter (
resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project subnet=properties.subnets
| mv-expand subnet
| project subnet
| extend subnetId=tolower(subnet.id), subnetName=tostring(subnet.name), subnetProperties = subnet.properties, subnetNsg=tolower(subnet.properties.networkSecurityGroup.id)
) on $left.nicSubnetId == $right.subnetId
| extend NsgIssue = iff(isnotempty(subnetNsg) and isnotempty(nicNsg) and subnetNsg != nicNsg, "TRUE", "")
// TODO: project required information
| project nicName, privatelinkFqdns, ipConfigurationsCount, publicIPAdress, privateIP, NsgIssue, nicNsg, subnetNsg

list-policu-assignments-and-params.kql

policyresources
| where ['type'] == "microsoft.authorization/policyassignments"
| extend paramCount = array_length(bag_keys(properties.parameters)),
params = properties.parameters,
displayName = properties.displayName,
scope = tostring(split(id, "/providers/Microsoft.Authorization/policyAssignments")[0])
| join kind=leftouter (
resourcecontainers
| where ['type'] in ("microsoft.management/managementgroups", "microsoft.resources/subscriptions")
| project id, name, type
) on $left.scope == $right.id
| project scopeName = name1, scopeType=type1, name, displayName, paramCount, params, identity, properties

list-storage-accounts.md

resources | where ['type'] =~ "microsoft.storage/storageaccounts" | extend publicNetworkAccess = properties.publicNetworkAccess, defaultAction = properties.networkAcls.defaultAction, virtualNetworkRules = properties.networkAcls.virtualNetworkRules, ipRules = properties.networkAcls.ipRules, ipv6Rules = properties.networkAcls.ipv6Rules, bypass = properties.networkAcls.bypass, resourceAccessRules = properties.networkAcls.resourceAccessRules, privateEndpointConnections = properties.privateEndpointConnections, supportsHttpsTrafficOnly = properties.supportsHttpsTrafficOnly, allowBlobPublicAccess = properties.allowBlobPublicAccess, minimumTlsVersion = properties.minimumTlsVersion, creationTime = properties.creationTime, keyCreationTime1 = properties.keyCreationTime.key1, keyCreationTime2 = properties.keyCreationTime.key2, networkAcls = properties.networkAcls, allowCrossTenantReplication = properties.allowCrossTenantReplication, accessTier = properties.accessTier, defaultToOAuthAuthentication = properties.defaultToOAuthAuthentication, allowSharedKeyAccess = properties.allowSharedKeyAccess, isHnsEnabled = properties.isHnsEnabled, dnsEndpointType = properties.dnsEndpointType, isNfsV3Enabled = properties.isNfsV3Enabled, isSftpEnabled = properties.isSftpEnabled, isLocalUserEnabled = properties.isLocalUserEnabled | project-away ['type'], tenantId, managedBy, plan

list-subnets.kql

resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project subnet = properties.subnets, vnetName = name
| mv-expand subnet
| project subnet, vnetName
| extend id=tostring(subnet.id), name=tostring(subnet.name), prefixes=tostring(subnet.properties.addressPrefix)
, privateLinkServiceNetworkPolicies = subnet.properties.privateLinkServiceNetworkPolicies, privateEndpointNetworkPolicies = subnet.properties.privateEndpointNetworkPolicies
, networkSecurityGroup = subnet.properties.networkSecurityGroup, delegations = subnet.properties.delegations, serviceEndpoints = subnet.properties.serviceEndpoints
, properties = subnet.properties
| project id, name, prefixes, 
// subnet
privateLinkServiceNetworkPolicies, privateEndpointNetworkPolicies, networkSecurityGroup, delegations, serviceEndpoints, 
vnetName,
properties
| order by ['id'] asc

// List subnets only. Extract information from properties

list-subscriptions.kql

resourcecontainers
| where type =~ "microsoft.resources/subscriptions"
| project id, name, subscriptionId, locationPlacementId = properties.subscriptionPolicies.locationPlacementId,
spendingLimit = properties.subscriptionPolicies.spendingLimit,
quotaId = properties.subscriptionPolicies.quotaId

# Actual offerId is available on https://service.bmx.azure.com/api/Subscription/SubscriptionEssentials?api-version=2020-03-09
# POST with JSON payload {"subscriptionId":"xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx","subscriptionType":7,"quotaId":"CSP_2015-05-01"}

# or in https://s2.billing.ext.azure.com/api/Billing/Subscription/Subscription or https://management.azure.com/{scope}/providers/Microsoft.Consumption/usageDetails?api-version=2019-10-01
# https://stackoverflow.com/questions/52593867/it-is-possible-to-get-the-azure-subscription-offer-or-offerid-with-powershell
# Management API lists resource usage. For CSP offerId does not exists.
# s2.billing.ext.azure.com is really hard to auth

# offer details: https://azure.microsoft.com/en-in/support/legal/offer-details/
# quota/offer mapping: https://learn.microsoft.com/en-us/azure/cost-management-billing/costs/understand-cost-mgt-data

# Getting responses with browser developer tools from https://service.bmx.azure.com/api/Subscription/SubscriptionEssentials is the fastest way to obtain offerId if portal logic does not reveal it

list-vm-ips.kql

resources
| where ['type'] == "microsoft.compute/virtualmachines"
| project vmId = tolower(['id']), vmName = name, vmProperties = properties
| join kind=leftouter  (
resources
| where type =~ 'microsoft.network/networkinterfaces'
| project
privateIP = properties.ipConfigurations[0].properties.privateIPAddress,
publicIPId = tolower(properties.ipConfigurations[0].properties.publicIPAddress.id),
nicSubnetId = tolower(properties.ipConfigurations[0].properties.subnet.id),
nicNsg = tolower(properties.networkSecurityGroup.id),
vmId = tolower(properties.virtualMachine.id),
subnet = properties.ipConfigurations[0].properties.subnet.id, 
ipConfigurationsCount = array_length(properties.ipConfigurations),
nicProperties = properties
) on $left.vmId == $right.vmId
| join kind=leftouter (
resources
| where ['type'] contains "publicIPAddresses"
| project pipProperties = properties, pipId = tolower(id)
) on $left.publicIPId == $right.pipId
| join kind=leftouter (
resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project subnet=properties.subnets
| mv-expand subnet
| project subnet
| extend subnetId=tolower(subnet.id), subnetName=tostring(subnet.name), subnetProperties = subnet.properties, subnetNsg=tolower(subnet.properties.networkSecurityGroup.id)
) on $left.nicSubnetId == $right.subnetId
//
| project vmName, privateIP, publicIP = pipProperties.ipAddress, nicNsg, subnetNsg, 
NsgIssue = iff(isnotempty(subnetNsg) and isnotempty(nicNsg) and subnetNsg != nicNsg, "TRUE", ""), 
subnetName, ipConfigurationsCount, vmProperties, nicProperties, pipProperties

// This will miss some data if ipConfigurationsCount > 1
// TODO: handle that use case as soon as there is data

list-vnet-and-subnet-verbose.kql

resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project id, name, prefixes = strcat_array(properties.addressSpace.addressPrefixes, " ,"), properties
| union (
resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project subnet=properties.subnets
| mv-expand subnet
| project subnet
| extend id=tostring(subnet.id), name=tostring(subnet.name), prefixes=tostring(subnet.properties.addressPrefix)
, privateLinkServiceNetworkPolicies = subnet.properties.privateLinkServiceNetworkPolicies, privateEndpointNetworkPolicies = subnet.properties.privateEndpointNetworkPolicies
, networkSecurityGroup = subnet.properties.networkSecurityGroup, delegations = subnet.properties.delegations, serviceEndpoints = subnet.properties.serviceEndpoints
, properties = subnet.properties
) // end union
| project id, name, prefixes, 
// subnet
privateLinkServiceNetworkPolicies, privateEndpointNetworkPolicies, networkSecurityGroup, delegations, serviceEndpoints, 
// Remaining VNet properties: TODO. Mostly Peering. Should be covered by other query
// properties with shared name
properties
| order by ['id'] asc

// List Vnet and subnets. Use common column for name, prefix(es) and properties
// Extracts subnet details from the properties

list-vnet-and-subnet.kql

resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project id, name, prefixes = strcat_array(properties.addressSpace.addressPrefixes, " ,"), properties
| union (
resources
| where ['type'] =~ 'microsoft.network/virtualnetworks'
| project subnet=properties.subnets
| mv-expand subnet
| project subnet
| extend id=tostring(subnet.id), name=tostring(subnet.name), prefixes=tostring(subnet.properties.addressPrefix)
, properties = subnet.properties
) // end union
| project id, name, prefixes, 
properties
| order by ['id'] asc

// List Vnet and subnets. Use common column for name, prefix(es) and properties

SAS-token-identification-2.kql

StorageBlobLogs
// parse authentication details
| extend IP = tostring(split(CallerIpAddress, ":")[0])
| extend key = tostring(extract("(.*?)\\(", 1, AuthenticationHash))
| extend key_hash = tostring(extract(".*?\\((.*?)\\)", 1, AuthenticationHash))
| extend sas_hash = tostring(extract("SasSignature\\((.*?)\\)", 1, AuthenticationHash))
// group user-agent header by user-agent field and by well-known client id and by well-known path
| extend UA = case(
UserAgentHeader startswith "azsdk-java-azure-storage-blob", "azsdk-java-azure-storage-blob",
UserAgentHeader startswith "Microsoft Azure Storage Explorer", "Microsoft Azure Storage Explorer",
UserAgentHeader startswith "Mozilla/5.0", "Browser_or_browserlike",
UserAgentHeader startswith "AzureDataFactoryCopy", "AzureDataFactoryCopy",
UserAgentHeader endswith "Azure Synapse Analytics/Spark/", "Azure Synapse Analytics/Spark",
UserAgentHeader startswith "Azure-Storage/", "Azure-Storage | .NET library",
key == "system-1", "MSFT internal processes",
UserAgentHeader)
// parse SAS token st and se & calculate diff
| extend url = parse_url(Uri)
| extend SAS_st = todatetime(url_decode(coalesce(url["Query Parameters"]["st"], ""))), SAS_se = todatetime(url_decode(coalesce(url["Query Parameters"]["se"], "")))
| extend diff = SAS_se - TimeGenerated
| extend diff_days = diff / timespan(24h)
| extend diff_bins = case(
diff_days < 1, 1,
diff_days < 7, 7,
diff_days < 31, 30,
isnull( diff_days), dynamic(null),
9999
)
| extend splittedPath = split(url["Path"], "/")
//| extend filenameslashcount = array_length(splittedPath)
| extend container = tostring(splittedPath[1])
// filter data
| where AuthenticationType == "SAS"
//| finally summarize data
//
//| summarize count() by bin(diff_days, 1)
//| summarize count() by diff_bins
| summarize count() by 
AuthenticationType
, key, key_hash, sas_hash
, IP
, UA
, diff_bins
, container
, AccountName
//, bin(TimeGenerated, 1d)
// sort by IP
| extend ipv4number = parse_ipv4(IP)
| order by AccountName asc, ipv4number asc
| where diff_bins > 999
//| order by TimeGenerated asc, IP asc
//| summarize count() by bin(TimeGenerated, 1d), UserAgentHeader, IP
//| order by TimeGenerated desc
//| summarize count() by key

// list SAS token usage with large SAS token lifetime

SAS-token-identification.kql

// https://github.com/MicrosoftDocs/azure-docs/blob/main/includes/azure-storage-logs-properties-authentication.md
// and https://learn.microsoft.com/en-us/azure/storage/blobs/blob-storage-monitoring-scenarios#identifying-the-sas-token-used-to-authorize-a-request
// Not sure where is the actual documentation

// Documentation is misleading: AuthenticationHash contains SHA256 hash of the SAS token sig --parameter value, NOT has of the full SAS token.

// Steps to use
// 1. run e.g. `echo -n "<FULL SAS TOKEN without leading?>" | python3 -c "import sys; from urllib.parse import urlparse, parse_qs; print(parse_qs(sys.stdin.read())['sig'][0], end='');" | sha256sum` to obtain hash of the signature
// 2. Hash of the previous step is present in *sas_hash* with following query if given SAS token has beend used

StorageBlobLogs 
| where AuthenticationType == "SAS"
| extend IP = tostring(split(CallerIpAddress, ":")[0])
| extend key = tostring(extract("(.*?)\\(", 1, AuthenticationHash))
| extend key_hash = tostring(extract(".*?\\((.*?)\\)", 1, AuthenticationHash))
| extend sas_hash = tostring(extract("SasSignature\\((.*?)\\)", 1, AuthenticationHash))
| summarize count() by IP, key, key_hash, sas_hash, bin(TimeGenerated, 1d)
| order by TimeGenerated asc, IP asc