応用編

let q1 = view() {
AzureDiagnostics
| where Category == "AzureFirewallApplicationRule" 
| where msg_s contains " to "
|parse kind = regex msg_s with Protocol @" request from " SourceIP ":" SourcePort @" to " TargetURL ":" targetPort @". Action: " Action @".\sN" Reason
};
let q2 = view() {
AzureDiagnostics
| where Category == "AzureFirewallApplicationRule" 
| where msg_s !contains " to "
|parse kind = regex msg_s with Protocol @" request from " SourceIP ":" SourcePort @". Action: " Action @". Reason:" Reason
};
union withsource="temptable" q1,q2 
| where SourceIP == "xx.xx.xx.xx"
| summarize count() by TargetURL, targetPort
//#| summarize count() by FQDN | order by count_ desc 
| order by count_ desc 

AzureDiagnostics
| where Category == "AzureFirewallNetworkRule"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePortInt:int " to " TargetIP ":" TargetPortInt:int *
| parse msg_s with * ". Action: " Action1a
| parse msg_s with * "was " Action1b " to " NatDestination
| parse msg_s with Protocol2 " request from " SourceIP2 " to " TargetIP2 ". Action:" Action2
| extend SourcePort = tostring(SourcePortInt),TargetPort = tostring(TargetPortInt)
| extend Action = case(Action1a == "", case(Action1b == "",Action2,Action1b), Action1a),Protocol = case(Protocol == "", Protocol2, Protocol),SourceIP = case(SourceIP == "", SourceIP2, SourceIP),TargetIP = case(TargetIP == "", TargetIP2, TargetIP),SourcePort = case(SourcePort == "", "N/A", SourcePort),TargetPort = case(TargetPort == "", "N/A", TargetPort),NatDestination = case(NatDestination == "", "N/A", NatDestination)
| project TimeGenerated,Protocol, SourceIP,SourcePort,TargetIP,TargetPort,Action, NatDestination
| where SourceIP == "10.1.4.25" and TargetPort == 22 and Action == "Deny"
| order by TimeGenerated desc 

  AzureNetworkAnalytics_CL
  | where SubType_s == "FlowLog" 
  | where SrcIP_s == "ipaddress"
  | summarize by DestIP_s, DestPort_d

AzureNetworkAnalytics_CL
  | where SubType_s == "FlowLog" 
  | where DestIP_s == "ipaddress"
  | summarize by SrcIP_s, DestPort_d

VMConnection
| where Computer == "computername"
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Received = sum(BytesReceived/1024) by ProcessName
| top 3 by Received desc

VMConnection
| where Computer == "computername"
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Sent = sum(BytesSent/1024) by ProcessName
| top 3 by Sent desc

VMConnection
| where Computer == "computername"
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Received = sum(BytesReceived/1024), Sent = sum(BytesSent/1024) by ProcessName
| extend TotalMb = Received + Sent
| extend SentMb = Sent
| extend ReceivedMb = Received
| project ProcessName, SentMb, ReceivedMb, TotalMb
| top 3 by TotalMb desc

Perf
| where Computer == "computername"
| where TimeGenerated between (ago(1d) .. ago(5m))
| where ObjectName == "Network Adapter"
| where InstanceName == "Microsoft Hyper-V Network Adapter _2"
| summarize avg(CounterValue) by CounterName, bin(TimeGenerated, 30min)
| order by TimeGenerated desc
| render timechart 

VMConnection
| where Computer == "computername"
| where TimeGenerated between (ago(1d) .. ago(5m))
| extend DNS = tostring(parse_json(RemoteDnsQuestions)[0])
| summarize Received = sum(BytesReceived/1024), Sent = sum(BytesSent/1024) by DestinationIp, DNS
| extend TotalMb = Received + Sent
| extend SentMb = Sent
| extend ReceivedMb = Received
| project DestinationIp, SentMb, ReceivedMb, TotalMb, DNS
| top 3 by TotalMb desc

VMConnection
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Events = count() by Computer
| top 10 by Events desc

VMConnection
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Events = count() by DestinationPort
| top 10 by Events desc

VMConnection
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Received = sum(BytesReceived/1024), Sent = sum(BytesSent/1024) by DestinationPort
| extend TotalMb = Received + Sent
| extend SentMb = Sent
| extend ReceivedMb = Received
| project DestinationPort, SentMb, ReceivedMb, TotalMb
| top 3 by TotalMb desc

VMConnection
| where TimeGenerated between (ago(1d) .. ago(5m))
| summarize Received = sum(BytesReceived/1024), Sent = sum(BytesSent/1024) by ProcessName
| extend TotalMb = Received + Sent
| extend SentMb = Sent
| extend ReceivedMb = Received
| project ProcessName, SentMb, ReceivedMb, TotalMb
| top 3 by TotalMb desc

Heartbeat 
| summarize LastHeartBeat = arg_max(TimeGenerated, *) by Computer

Heartbeat 
| summarize LastHeartBeat = arg_max(TimeGenerated, *) by Computer
| where LastHeartBeat < ago(5m)

InsightsMetrics
| where TimeGenerated > ago(4h)
| where Namespace == "LogicalDisk"
| where Name == "FreeSpacePercentage"
| extend DiskInstance = tostring(parse_json(Tags).["vm.azm.ms/mountId"])
| summarize DiskFreeSpace = avg(Val) by bin(TimeGenerated, 4h), Computer, DiskInstance

UpdateSummary 
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, LastUpdateApplied, OldestMissingSecurityUpdateInDays, WindowsUpdateSetting, SecurityUpdatesMissing, OtherUpdatesMissing, RestartPending

UpdateRunProgress
| where TimeGenerated > ago(7d)
| summarize Failed = countif(InstallationStatus == "Failed"), FailedtoStart = countif(InstallationStatus == "FailedtoStart"), InProgress = countif(InstallationStatus == "InProgress"), Succeeded = countif(InstallationStatus == "Succeeded"), NotIncluded = countif(InstallationStatus == "NotIncluded"), NotStarted = countif(InstallationStatus == "NotStarted") by UpdateRunName

AzureDiagnostics
| summarize by Category

AzureDiagnostics
| where Category == 'DatabaseWaitStatistics'
| summarize sum(delta_wait_time_ms_d) by wait_type_s
| sort by sum_delta_wait_time_ms_d desc
| render piechart 

AzureDiagnostics
| where Category == 'DatabaseWaitStatistics'
| summarize sum(delta_waiting_tasks_count_d) by wait_type_s
| sort by sum_delta_waiting_tasks_count_d desc
| render piechart 

AzureDiagnostics
| where Category == 'QueryStoreWaitStatistics'
| summarize sum(total_query_wait_time_ms_d) by wait_category_s
| sort by sum_total_query_wait_time_ms_d desc
| render piechart 

AzureDiagnostics
| where Category == 'Errors'
| project TimeGenerated, error_number_d, Severity, state_d, Message

AzureDiagnostics
| where Category == 'Errors'
| summarize count() by TimeGenerated
| render timechart  

AzureDiagnostics
| where Category == "ResourceUsageStats"

AzureDiagnostics
| where Category == "ResourceUsageStats"
| project avg_cpu_percent_s, io_bytes_read_s, io_bytes_written_s

AzureDiagnostics
| where Category == "ResourceUsageStats" 
| sort by  asc 
| summarize avg(todouble(avg_cpu_percent_s)), avg(todouble(io_bytes_read_s)), avg(todouble(io_bytes_written_s)) by bin(TimeGenerated, 5m)

AzureDiagnostics
| where Category == "ResourceUsageStats" 
| sort by TimeGenerated asc 
| summarize avg(todouble(avg_cpu_percent_s)), avg(todouble(io_bytes_read_s)), avg(todouble(io_bytes_written_s)) by bin(TimeGenerated, 5m)
| render timechart

AzureDiagnostics
| where Category == 'ExecRequests'
| where StatementType_s !in ('Batch','Execute')
| summarize Session_ID=any(SessionId_s),Request_ID=any(RequestId_s),Submit_Time=max(SubmitTime_t),Start_Time=max(StartTime_t),End_Time=max(EndTime_t),Command=any(Command_s),Status=min(Status_s), Statement_Type=any(StatementType_s),Resource_class=any(ResourceClass_s) by RequestId_s
| summarize ElapsedTime_min=anyif((End_Time - Start_Time)/1m,Start_Time > ago(30d)),Session_ID=any(Session_ID), Submit_Time=any(Submit_Time) ,Start_Time=any(Start_Time), End_Time=any(End_Time),Command=any(Command),Status=any(Status),Statement_Type=any(Statement_Type),Resource_class=any(Resource_class) by Request_ID
| order by ElapsedTime_min desc
| limit 20

AzureDiagnostics
| where Category  == 'RequestSteps'
| where RequestId_s == 'QIDnnnnnn' //Put your QueryID here
| where Status_s != 'Running'
| summarize max(StartTime_t),max(EndTime_t),max(RequestId_s),max(OperationType_s),max(RowCount_d),max(Command_s),max(Status_s) by StepIndex_d
| order by StepIndex_d asc

AzureDiagnostics
| where Category  == 'RequestSteps'
| extend elapsedTime = EndTime_t - StartTime_t
| extend elapsedTime_min = elapsedTime/1m
| where EndTime_t >= ago(30d)
| where StartTime_t >= ago(30d)
| order by RowCount_d desc
| project RequestId_s,OperationType_s, RowCount_d,elapsedTime_min , StartTime_t, EndTime_t, Status_s
| limit 20

AzureDiagnostics
| where Category  == 'RequestSteps'
| where RequestId_s == 'QIDnnnnn' //Insert your QID here
| where Status_s != 'Running'
| summarize max(StartTime_t),max(EndTime_t),max(RequestId_s),max(OperationType_s),max(RowCount_d),max(Command_s),max(Status_s) by StepIndex_d
| order by StepIndex_d asc

search * 
| where TimeGenerated > ago(24h)
| distinct $table
| where $table startswith "WVD"

WVDAgentHealthStatus
| where TimeGenerated > ago(24h)
| summarize TotalActiveSessions=sum(toint(ActiveSessions))/10 by bin(TimeGenerated, 5m)
| render timechart 

WVDAgentHealthStatus
| where TimeGenerated > ago(24h)
| summarize TotalInactiveSessions=sum(toint(InactiveSessions))/10 by bin(TimeGenerated, 5m)
| render timechart 

WVDAgentHealthStatus
| where TimeGenerated > ago(10m)
| summarize arg_max(TimeGenerated,*) by SessionHostName
| project SessionHostName, ActiveSessions, InactiveSessions, TimeGenerated

WVDErrors 
| where TimeGenerated > ago(24h) 
| where ServiceError == "true" 

WVDErrors 
| where TimeGenerated > ago(24h) 
| where ServiceError == "false" 
| summarize NumOfErrors=count() by ActivityType, Source, CodeSymbolic
| sort by Source

WVDErrors 
| where TimeGenerated > ago(24h) 
| where ServiceError == "false" 
| summarize NumOfErrors=count() by UserName

WVDErrors 
| where TimeGenerated > ago(24h) 
| where ServiceError == "false" 
| where UserName == "<User UPN>"

WVDConnections
| where TimeGenerated > ago(24h)
| extend Multi=split(_ResourceId, "/")
| extend HostPool = tostring(Multi[8])
| summarize count() by ClientSideIPAddress, HostPool

WVDConnections  
| where TimeGenerated > ago(24h)  
| extend Multi=split(_ResourceId, "/")
| extend HostPool = tostring(Multi[8])
| where HostPool == "<Host Pool>"
| where State == "Connected"  
| project CorrelationId, UserName, StartTime=TimeGenerated
| join kind=inner(WVDConnections  
| where State == "Completed"  
| project EndTime = TimeGenerated, CorrelationId) on CorrelationId  
| project Duration = EndTime - StartTime, UserName
| summarize ConnectionTimeTotal = sum(Duration) by UserName

WVDConnections
| where TimeGenerated > ago(7d)
| extend Multi=split(_ResourceId, "/")
| extend HostPool = tostring(Multi[8])
| where HostPool == "<Host Pool>"
| where State == "Connected"
| project CorrelationId, UserName, StartTime = TimeGenerated  
| join kind=inner(WVDConnections  
| where State == "Completed"  
| project EndTime = TimeGenerated, CorrelationId) on CorrelationId  
| project DurationInMinutes = (EndTime - StartTime)/1m, UserName, EndTime
| summarize ConnectionTimeInMinutes=sum(DurationInMinutes) by bin(EndTime, 24h), UserName
| render timechart

WVDConnectionNetworkData
| where TimeGenerated > ago(24h)
| extend Multi = split(_ResourceId, "/")
| extend HostPool = tostring(Multi[8])
| summarize avg(EstRoundTripTimeInMs) by bin(TimeGenerated, 10m), HostPool
| render timechart 

WVDConnectionNetworkData
| where TimeGenerated > ago(24h)
| join kind=inner (WVDConnections
| distinct CorrelationId, UserName) on CorrelationId
| where UserName == "<User UPN>"
| summarize avg(EstRoundTripTimeInMs) by bin(TimeGenerated, 10m)
| render timechart 

requests
| where timestamp > ago(12h) 

requests
| where timestamp > ago(12h) 
| where success == false

requests
| summarize RequestsCount=sum(itemCount), AverageDuration=avg(duration), percentiles(duration, 50, 95, 99) by operation_Name

requests
| summarize CountByCountry=count() by client_CountryOrRegion
| top 10 by CountByCountry
| render piechart

pageViews

pageViews
| where client_type == 'Browser'

pageViews
| where notempty(duration) and client_Type == 'Browser'
| extend total_duration=duration*itemCount
| summarize avg_duration=(sum(total_duration)/sum(itemCount)) by operation_Name
| top 10 by avg_duration desc

let threshold = 10;
let unit = 10m;
SigninLogs
| where ResultType !in ("0", "50125", "50140")
| summarize loginfailure = count() by UserPrincipalName, bin(TimeGenerated,unit)
| where loginfailure > threshold 

let threshold = 20;
let unit = 10m;
SigninLogs
| where ResultType !in ("0", "50125", "50140")
| summarize loginfailure = dcount(UserPrincipalName) by  bin(TimeGenerated,unit)
| where loginfailure > threshold 

let threshold = 10;
let lookback = 10m;
AzureDiagnostics
| where TimeGenerated > ago(lookback)
| where Category == "ApplicationGatewayAccessLog"
| where httpStatus_d >= 300
| summarize ErrCount= count() by clientIP_s,userAgent_s
| where ErrCount > threshold

let threshold = 5;
let lookback = 10m;
AzureDiagnostics
| where TimeGenerated > ago(lookback)
| where Category == "ApplicationGatewayAccessLog"
| where httpStatus_d >= 300
| summarize UriCount = dcount(requestUri_s) by clientIP_s
| where UriCount > threshold

let lookback = 10m;
let threshold = 10;
let SuccessIPs = AzureDiagnostics
| where TimeGenerated > ago(lookback)
| where Category == "ApplicationGatewayAccessLog"
| where httpStatus_d between (200 .. 299) 
| summarize AccessTime = max(TimeGenerated) by clientIP_s;
AzureDiagnostics
| where TimeGenerated > ago(lookback)
| where httpStatus_d >= 300
| join kind=inner SuccessIPs on clientIP_s 
| where TimeGenerated < AccessTime
| summarize ErrBeforeSuccess = count() by clientIP_s
| where ErrBeforeSuccess > threshold

AzureDiagnostics
| where Category== "ApplicationGatewayFirewallLog"
| project TimeGenerated,clientIp_s,hostname_s,requestUri_s,ruleSetVersion_s,ruleId_s,Message,details_message_s,details_data_s

let past1month = AzureActivity
| where TimeGenerated between (ago(30d) .. startofday(now()))
| where CategoryValue == "Administrative"
| distinct  OperationNameValue;
AzureActivity
| where TimeGenerated >  startofday(now())
| where CategoryValue == "Administrative"
| where OperationNameValue !in (past1month)
| project TimeGenerated, OperationNameValue, OperationName, ResourceGroup, ResourceProviderValue, Caller

VMProcess 
| summarize UniqueProcesses = dcount(Computer) by ExecutableName
| where UniqueProcesses == 1
| join (
VMProcess
| project Computer, ExecutableName, ExecutablePath
) on ExecutableName
| distinct ExecutableName, Computer, ExecutablePath
| order by Computer, ExecutableName

SecureScores 
| where TimeGenerated > ago(7d)
| extend SecureScorePercentage = PercentageScore*100
| summarize AverageScore = avg(SecureScorePercentage) by bin(TimeGenerated, 1d)

let backend = AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
| where serverRouted_s <> ""
| distinct serverRouted_s;
AzureDiagnostics
| where Category == "AzureFirewallNetworkRule" or Category == "AzureFirewallApplicationRule"
| parse msg_s with *  " request from " SourceIP ":" SourcePort " to " Dest ":" DestPort "." *
| project TimeGenerated,SourceIP,SourcePort,Dest,DestPort
| where SourceIP in (backend)

SecurityEvent 
| where EventID == 4624 and (LogonType == 2 or LogonType == 10) and AccountType == "User"
| summarize count() by Computer, Account

let maliciousIPs = 
ThreatIntelligenceIndicator
| where NetworkIP <> "" and Active == true and ExpirationDateTime > now()
| distinct NetworkIP;
AzureDiagnostics
| where Category == "AzureFirewallNetworkRule"
| parse msg_s with *  " request from " SourceIP ":" SourcePort " to " DestIP ":" DestPort "." *
| where DestIP  in (maliciousIPs)

ResourceContainers 
| where type=='microsoft.resources/subscriptions'
| parse id with "/subscriptions/" SubscriptionID
| project subscriptionId, SubscriptionName = name
| join kind=leftouter (
Resources
| where type == 'microsoft.keyvault/vaults'
| project id, name, subscriptionId
) on subscriptionId
| join kind= leftouter (
Resources
| where type == 'microsoft.keyvault/vaults'
| summarize ResourceCount = count() by subscriptionId
) on subscriptionId
| extend RCount = iff(isnull(ResourceCount), 0, ResourceCount)
| project-away ResourceCount, subscriptionId1, subscriptionId2
| extend HealthStatus = iff(RCount == 1, "Not Compliant", "Compliant")

resources
| where type == "microsoft.compute/virtualmachines"
| project ComputerName = name, subscriptionId
| join kind=leftouter  (
resources
| where type == "microsoft.compute/virtualmachines/extensions"
| where name in ("AzureMonitorLinuxAgent", "AzureMonitorWindowsAgent")
| parse id with * "/virtualMachines/" ComputerName "/extensions/" *
| extend AMAStatus = properties.provisioningState,
    WorkspaceID = tostring(properties.settings.workspaceId)
| project AMA = name, ComputerName, AMAStatus
) on ComputerName
| join kind=leftouter  (
resources
| where type == "microsoft.compute/virtualmachines/extensions"
| where name in ("MicrosoftMonitoringAgent", "OmsAgentForLinux")
| parse id with * "/virtualMachines/" ComputerName "/extensions/" *
| extend MMAStatus = properties.provisioningState,
    WorkspaceID = tostring(properties.settings.workspaceId)
| project MMA = name, ComputerName, MMAStatus, WorkspaceID
) on ComputerName
| join kind=leftouter  (
resources
| where type == "microsoft.operationalinsights/workspaces"
| extend WorkspaceID = tostring(properties.customerId)
| project WorkspaceName = name, WorkspaceID
) on WorkspaceID
| project-away WorkspaceID1, ComputerName1, ComputerName2
| extend ["Both Agents"] = iff(isnotempty(AMA) and isnotempty(MMA), "True", "False")

Resources
| where type =~ 'microsoft.compute/Disks'
| where managedBy == ""
| project name, id

resources
| where type == "microsoft.network/networkinterfaces"
| where isnull (properties.virtualMachine) and isnull (properties.privateEndpoint)

resources
| where type =~ 'microsoft.network/networksecuritygroups' and isnull(properties.networkInterfaces) and isnull(properties.subnets)
| project name, resourceGroup

resources
| where type == "microsoft.network/routetables" and isnull(properties.subnets)

resources
| where type == "microsoft.storage/storageaccounts" and properties.allowBlobPublicAccess == 'true'

  resources
  | where type == "microsoft.storage/storageaccounts" and properties.allowBlobPublicAccess == 'true'        

resources
| where ['type'] == 'microsoft.network/virtualnetworks'
| mv-expand peerings=properties.virtualNetworkPeerings
| project VNetName=name, peeringName=peerings.name, peeringTarget=split(peerings.properties.remoteVirtualNetwork.id,"/",8)[0], peeringState=peerings.properties.peeringState,useRemoteGateways=peerings.properties.useRemoteGateways,
allowGatewayTransit=peerings.properties.allowGatewayTransit

resources
| where ['type'] == 'microsoft.network/virtualnetworks'
//| project dynProperties=todynamic(properties)
| mv-expand subnetNames=properties.subnets
| project VNetName=name, Subnet=subnetNames.name,AddressPrefix=subnetNames.properties.addressPrefix,
RouteTable=split(subnetNames.properties.routeTable.id,"/",8)[0],
NSG=split(subnetNames.properties.networkSecurityGroup.id,"/",8)[0],type="microsoft.network/subnets"

Resources | where type == "microsoft.compute/virtualmachines"
| summarize Total=count() by Region=location

resources
| where ['type'] == 'microsoft.compute/virtualmachines'
| project osType=properties.storageProfile.osDisk.osType
| summarize count() by tostring(osType)

Resources 
| where type == "microsoft.network/networkinterfaces"
| where isnotnull (properties.ipConfigurations[0].properties.applicationSecurityGroups)
| project subscriptionId, resourceGroup, name, ASGs=properties.ipConfigurations[0].properties.applicationSecurityGroups
| mv-expand ASGs
| project subscriptionId, resourceGroup, name, ASG=ASGs.id
| where ASG endswith "<ASG>"