Запросы для таблицы AzureDiagnostics
Сведения об использовании этих запросов в портал Azure см. в руководстве по Log Analytics. Сведения о REST API см. в разделе "Запрос".
Поиск журналов отчетов об ошибках в заданиях автоматизации с последнего дня.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION"
| where StreamType_s == "Error"
| project TimeGenerated, Category, JobId_g, OperationName, RunbookName_s, ResultDescription
Список всех ошибок в заданиях автоматизации.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION"
| where StreamType_s == "Error"
| project TimeGenerated, Category, JobId_g, OperationName, RunbookName_s, ResultDescription, _ResourceId
Список всех заданий автоматизации, которые завершились сбоем, приостановлены или остановлены.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobLogs" and (ResultType == "Failed" or ResultType == "Stopped" or ResultType == "Suspended")
| project TimeGenerated , RunbookName_s , ResultType , _ResourceId , JobId_g
Список всех заданий, завершенных с ошибками.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobStreams" and StreamType_s == "Error"
| project TimeGenerated , RunbookName_s , StreamType_s , _ResourceId , ResultDescription , JobId_g
Вывод списка всех заданий автоматизации.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobLogs" and ResultType != "started"
| summarize AggregatedValue = count() by ResultType, bin(TimeGenerated, 1h) , RunbookName_s , JobId_g, _ResourceId
Список всех заданий автоматизации, которые были завершены.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.AUTOMATION" and Category == "JobLogs" and ResultType == "Completed"
| project TimeGenerated , RunbookName_s , ResultType , _ResourceId , JobId_g
Предоставляет количество успешных задач на задание.
AzureDiagnostics
| where OperationName=="TaskCompleteEvent"
| where executionInfo_exitCode_d==0 // Your application may use an exit code other than 0 to denote a successful operation
| summarize successfulTasks=count(id_s) by jobId=jobId_s
Список неудачных задач по родительскому заданию.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where OperationName=="TaskFailEvent"
| summarize failedTaskList=make_list(id_s) by jobId=jobId_s, ResourceId
Предоставляет истекшее время выполнения задач в секундах от начала задачи до завершения задачи.
AzureDiagnostics
| where OperationName=="TaskCompleteEvent"
| extend taskId=id_s, ElapsedTime=datetime_diff('second', executionInfo_endTime_t, executionInfo_startTime_t) // For longer running tasks, consider changing 'second' to 'minute' or 'hour'
| summarize taskList=make_list(taskId) by ElapsedTime
Список времени изменения размера по пулу и коду результата (успешно или сбою).
AzureDiagnostics
| where OperationName=="PoolResizeCompleteEvent"
| summarize operationTimes=make_list(startTime_s) by poolName=id_s, resultCode=resultCode_s
Вывод списка ошибок изменения размера пула по коду ошибки и времени.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where OperationName=="PoolResizeCompleteEvent"
| where resultCode_s=="Failure" // Filter only on failed pool resizes
| summarize by poolName=id_s, resultCode=resultCode_s, resultMessage=resultMessage_s, operationTime=startTime_s, ResourceId
График отрисовки, показывающий общий запрос в час.
// Summarize number of requests per hour
// Change bins resolution from 1hr to 5m to get real time results)
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == "true"
| summarize RequestCount = count() by bin(TimeGenerated, 1h), Resource, _ResourceId
| render timechart
Отображение исходящих данных из пограничных адресов CDN по URL-адресу.
// Change bins resolution from 1 hour to 5 minutes to get real time results)
// CDN edge response traffic by URL
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == true
| summarize ResponseBytes = sum(toint(responseBytes_s)) by requestUri_s
Отображение частоты ошибок 4XX по URL-адресу.
// Request errors rate by URL
// Count number of requests with error responses by URL.
// Summarize number of requests by URL, and status codes are 4XX
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog" and isReceivedFromClient_b == true
| extend Is4XX = (toint(httpStatusCode_s ) >= 400 and toint(httpStatusCode_s ) < 500)
| summarize 4xxrate = (1.0 * countif(Is4XX) / count()) * 100 by requestUri_s, bin(TimeGenerated, 1h), _ResourceId
Количество запросов с ответами на ошибки по агенту пользователя.
// Summarize number of requests per user agent and status codes >= 400
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == true
| where toint(httpStatusCode_s) >= 400
| summarize RequestCount = count() by UserAgent = userAgent_s, StatusCode = httpStatusCode_s , Resource, _ResourceId
| order by RequestCount desc
Отображение лучших 10 URL-адресов по количеству запросов.
// top URLs by request count
// Render line chart showing total requests per hour .
// Summarize number of requests per hour
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == true
| summarize UserRequestCount = count() by requestUri_s
| order by UserRequestCount
| limit 10
Отображение количества уникальных IP-запросов.
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write"and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == true
| summarize dcount(clientIp_s) by bin(TimeGenerated, 1h)
| render timechart
Показывать первые 10 IP-адресов клиента и http-версий.
// Top 10 client IPs and http versions
// Show top 10 client IPs and http versions.
// Summarize top 10 client ips and http versions
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where isReceivedFromClient_b == true
| summarize RequestCount = count() by ClientIP = clientIp_s, HttpVersion = httpVersion_s, Resource
| top 10 by RequestCount
| order by RequestCount desc
Показывать первые 20 заблокированных клиентов по IP-адресу и имени правила.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorWebApplicationFirewallLog"
| where action_s == "Block"
| summarize RequestCount = count() by ClientIP = clientIP_s, UserAgent = userAgent_s, RuleName = ruleName_s,Resource
| top 20 by RequestCount
| order by RequestCount desc
Количество запросов для каждого маршрута и источника в минуту. Суммирование количества запросов в минуту для каждого маршрута и источника.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| summarize RequestCount = count() by bin(TimeGenerated, 1m), Resource, RouteName = routingRuleName_s, originName = originName_s, ResourceId
Количество запросов с ответами на ошибки по агенту пользователя. Суммирование количества запросов для каждого агента пользователя и кодов >состояния = 400.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| where toint(httpStatusCode_s) >= 400
| summarize RequestCount = count() by UserAgent = userAgent_s, StatusCode = httpStatusCode_s , Resource, ResourceId
| order by RequestCount desc
Отображение первых 10 IP-адресов клиента и http-версий по количеству запросов.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| summarize RequestCount = count() by ClientIP = clientIp_s, HttpVersion = httpVersion_s, Resource
|top 10 by RequestCount
| order by RequestCount desc
Количество запросов с ответами на ошибки по узлу и пути. Суммирование количества запросов по узлам, путям и кодам >состояния = 400.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| where toint(httpStatusCode_s) >= 400
| extend ParsedUrl = parseurl(requestUri_s)
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), StatusCode = httpStatusCode_s, ResourceId
| order by RequestCount desc
Количество заблокированных запросов брандмауэра в час. Суммирование количества заблокированных запросов брандмауэра в час по политике.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorWebApplicationFirewallLog"
| where action_s == "Block"
| summarize RequestCount = count() by bin(TimeGenerated, 1h), Policy = policy_s, PolicyMode = policyMode_s, Resource, ResourceId
| order by RequestCount desc
[Azure Front Door Standard/Premium] Количество запросов брандмауэра по узлу, пути, правилу и действию
Подсчет запросов брандмауэра, обработанных по узлу, пути, правилу и действиям. Суммирование количества запросов по узлу, пути, правилу и действию.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorWebApplicationFirewallLog"
| extend ParsedUrl = parseurl(requestUri_s)
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), RuleName = ruleName_s, Action = action_s, ResourceId
| order by RequestCount desc
График отрисовки, показывающий общее количество запросов в час для каждого ресурса FrontDoor.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorWebApplicationFirewallLog"
| summarize RequestCount = count() by bin(TimeGenerated, 1h), Resource, ResourceId
| render timechart
Отображение лучших 10 URL-адресов по количеству запросов.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| summarize UserRequestCount = count() by requestUri_s
| order by UserRequestCount
| limit 10
Отображение исходящих данных из края AFD по URL-адресу. Измените разрешение ячеек с 1 часа на 5 млн, чтобы получить результаты в режиме реального времени.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| summarize ResponseBytes = sum(toint(responseBytes_s)) by requestUri_s
Показать количество уникальных IP-запросов.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.CDN" and Category == "FrontDoorAccessLog"
| summarize dcount(clientIp_s) by bin(TimeGenerated, 1h)
| render timechart
Найдите в AzureDiagnostics поиск определенного значения в таблице AzureDiagnostics./nNote, для выполнения запроса необходимо обновить <параметр SeachValue> для получения результатов.
// This query requires a parameter to run. Enter value in SearchValue to find in table.
let SearchValue = "<SearchValue>";//Please update term you would like to find in the table.
AzureDiagnostics
| where ResourceProvider == "Microsoft.ContainerService"
| where * contains tostring(SearchValue)
| take 1000
Определите запросы, которые их время выполнения превышает 10 секунд.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMARIADB"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s, ResourceId
| where query_time_d > 10 // You may change the time threshold
Определите первые 5 самых медленных запросов.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMARIADB"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| top 5 by query_time_d desc
Создайте сводную таблицу статистики по запросу.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMARIADB"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| summarize count(), min(query_time_d), max(query_time_d), avg(query_time_d), stdev(query_time_d), percentile(query_time_d, 95) by LogicalServerName_s ,sql_text_s
| top 50 by percentile_query_time_d_95 desc
Определите события общего класса для сервера.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMARIADB"
| where Category == 'MySqlAuditLogs' and event_class_s == "general_log"
| project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
| order by TimeGenerated asc
Определите связанные с подключением события для сервера.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMARIADB"
| where Category == 'MySqlAuditLogs' and event_class_s == "connection_log"
| project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
| order by TimeGenerated asc
Определите запросы, которые их время выполнения превышает 10 секунд.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s, ResourceId
| where query_time_d > 10 //You may change the time threshold
Определите первые 5 самых медленных запросов.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| top 5 by query_time_d desc
Создайте сводную таблицу статистики по запросу.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, LogicalServerName_s, event_class_s, start_time_t , query_time_d, sql_text_s
| summarize count(), min(query_time_d), max(query_time_d), avg(query_time_d), stdev(query_time_d), percentile(query_time_d, 95) by LogicalServerName_s ,sql_text_s
| top 50 by percentile_query_time_d_95 desc
Определите события общего класса для сервера.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlAuditLogs' and event_class_s == "general_log"
| project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
| order by TimeGenerated asc
Определите связанные с подключением события для сервера.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORMYSQL"
| where Category == 'MySqlAuditLogs' and event_class_s == "connection_log"
| project TimeGenerated, LogicalServerName_s, event_class_s, event_subclass_s, event_time_t, user_s , ip_s , sql_text_s
| order by TimeGenerated asc
Найдите события autovacuum за последние 24 часа. Для него требуется параметр "log_autovacuum_min_duration".
AzureDiagnostics
| where TimeGenerated > ago(1d)
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "automatic vacuum"
Выполните поиск событий завершения работы сервера и готовности сервера.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where TimeGenerated > ago(7d)
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "database system was shut down at" or Message contains "database system is ready to accept"
Выполните поиск ошибок за последние 6 часов.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where TimeGenerated > ago(6h)
| where Category == "PostgreSQLLogs"
| where errorLevel_s contains "error"
Поиск несанкционированных (отклоненных) попыток подключения.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "password authentication failed" or Message contains "no pg_hba.conf entry for host"
Поиск событий взаимоблокировки.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "deadlock detected"
Найдите конфликт блокировки. Для этого требуется log_lock_waits=ON и зависит от параметра deadlock_timeout.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Message contains "still waiting for ShareLock on transaction"
Получение всех журналов аудита. Для этого требуется включить журналы аудита [https://docs.microsoft.com/azure/postgresql/concepts-audit].
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:"
Найдите журналы аудита для определенной таблицы и типа событий DDL. Другие типы событий: READ, WRITE, FUNCTION, MISC. Для этого требуется включенные журналы аудита. [https://docs.microsoft.com/azure/postgresql/concepts-audit].
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.DBFORPOSTGRESQL"
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:"
| where Message contains "table name" and Message contains "DDL"
Определите запросы, которые занимают более 10 секунд. Хранилище запросов нормализует фактические запросы для агрегирования аналогичных запросов. По умолчанию записи агрегируются каждые 15 минут. Запрос использует среднее время выполнения каждые 15 минут и другую статистику запросов, например max, min можно использовать соответствующим образом.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreRuntimeStatistics"
| where user_id_s != "10" //exclude azure system user
| project TimeGenerated, LogicalServerName_s, event_type_s , mean_time_s , db_id_s , start_time_s , query_id_s, _ResourceId
| where todouble(mean_time_s) > 0 // You may change the time threshold
Определите первые 5 самых медленных запросов.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreRuntimeStatistics"
| where user_id_s != "10" //exclude azure system user
| summarize avg(todouble(mean_time_s)) by event_class_s , db_id_s ,query_id_s
| top 5 by avg_mean_time_s desc
Создайте сводную таблицу статистики по запросу.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreRuntimeStatistics"
| where user_id_s != "10" //exclude azure system user
| summarize sum(toint(calls_s)), min(todouble(min_time_s)),max(todouble(max_time_s)),avg(todouble(mean_time_s)),percentile(todouble(mean_time_s),95) by db_id_s ,query_id_s
| order by percentile_mean_time_s_95 desc nulls last
Тенденция выполнения по запросу, агрегированная на 15 минут.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreRuntimeStatistics"
| where user_id_s != "10" //exclude azure system user
| summarize sum(toint(calls_s)) by tostring(query_id_s), bin(TimeGenerated, 15m), ResourceId
| render timechart
Определите первые 5 событий ожидания по запросам.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreWaitStatistics"
| where user_id_s != "10" //exclude azure system user
| where query_id_s != 0
| summarize sum(toint(calls_s)) by event_s, query_id_s, bin(TimeGenerated, 15m)
| top 5 by sum_calls_s desc nulls last
Отображение тенденций событий ожидания с течением времени.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Category == "QueryStoreWaitStatistics"
| where user_id_s != "10" //exclude azure system user
| extend query_id_s = tostring(query_id_s)
| summarize sum(toint(calls_s)) by event_s, query_id_s, bin(TimeGenerated, 15m), ResourceId // You may change the time threshold
| render timechart
Определение ошибок подключения устройства.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where Category == "Connections" and Level == "Error"
Определите устройства, которые сделали большинство запросов, что приводит к ошибкам регулирования.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where ResultType == "429001"
| extend DeviceId = tostring(parse_json(properties_s).deviceId)
| summarize count() by DeviceId, Category , _ResourceId
| order by count_ desc
Определите мертвые или неработоспособные конечные точки по количеству сообщений о проблеме, а также причину.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where Category == "Routes" and OperationName in ("endpointDead", "endpointUnhealthy")
| extend parsed_json = parse_json(properties_s)
| extend Endpoint = tostring(parsed_json.endpointName), Reason =tostring(parsed_json.details)
| summarize count() by Endpoint, OperationName, Reason, _ResourceId
| order by count_ desc
Количество ошибок во всех операциях по типу.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where Level == "Error"
| summarize count() by ResultType, ResultDescription, Category, _ResourceId
Список устройств, которые Центр Интернета вещей видели подключение за указанный период времени.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where Category == "Connections" and OperationName == "deviceConnect"
| extend DeviceId = tostring(parse_json(properties_s).deviceId)
| summarize max(TimeGenerated) by DeviceId, _ResourceId
Список устройств и версий пакета SDK.
// this query works on device connection or when your device uses device to cloud twin operations
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DEVICES" and ResourceType == "IOTHUBS"
| where Category == "Connections" or Category == "D2CTwinOperations"
| extend parsed_json = parse_json(properties_s)
| extend SDKVersion = tostring(parsed_json.sdkVersion) , DeviceId = tostring(parsed_json.deviceId)
| distinct DeviceId, SDKVersion, TimeGenerated, _ResourceId
Определите потребляемые ЕЗ/с в базах данных и коллекциях Cosmos.
// To create an alert for this query, click '+ New alert rule'
//You can compare the RU/s consumption with your provisioned RU/s to determine if you should scale up or down RU/s based on your workload.
AzureDiagnostics
| where TimeGenerated >= ago(24hr)
| where Category == "DataPlaneRequests"
//| where collectionName_s == "CollectionToAnalyze" //Replace to target the query to a collection
| summarize ConsumedRUsPerMinute = sum(todouble(requestCharge_s)) by collectionName_s, _ResourceId, bin(TimeGenerated, 1m)
| project TimeGenerated , ConsumedRUsPerMinute , collectionName_s, _ResourceId
| render timechart
Определите коллекции и операции, получившие 429 (регулирование), которые происходят при превышении подготовленной пропускной способности (ЕЗ/с).
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where TimeGenerated >= ago(24hr)
| where Category == "DataPlaneRequests"
| where statusCode_s == 429
| summarize numberOfThrottles = count() by databaseName_s, collectionName_s, requestResourceType_s, _ResourceId, bin(TimeGenerated, 1hr)
| order by numberOfThrottles
Определите основные операции с ресурсами Cosmos по количеству и потреблению единиц запросов на операцию.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where TimeGenerated >= ago(24h)
| where Category == "DataPlaneRequests"
| summarize numberOfOperations = count(), totalConsumedRU = sum(todouble(requestCharge_s)) by databaseName_s, collectionName_s, OperationName, requestResourceType_s, requestResourceId_s, _ResourceId
| extend averageRUPerOperation = totalConsumedRU / numberOfOperations
| order by numberOfOperations
Определите самые большие значения ключа логического раздела. PartitionKeyStatistics выдает данные для основных ключей логических секций по хранилищу.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where Category == "PartitionKeyStatistics"
//| where collectionName_s == "CollectionToAnalyze" //Replace to target the query to a collection
| summarize arg_max(TimeGenerated, *) by databaseName_s, collectionName_s, partitionKey_s, _ResourceId //Get the latest storage size
| extend utilizationOf20GBLogicalPartition = sizeKb_d / 20000000 //20GB
| project TimeGenerated, databaseName_s , collectionName_s , partitionKey_s, sizeKb_d, utilizationOf20GBLogicalPartition, _ResourceId
Суммирует дуарацию сбоя при записи.
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.EVENTHUB\"
| where Category == \"ArchiveLogs\"
| summarize count() by \"failures\", \"durationInSeconds\", _ResourceId
Сводные сведения о состоянии запроса на присоединение клиента.
AzureDiagnostics // Need to turn on the Capture for this
| where ResourceProvider == \"MICROSOFT.EVENTHUB\"
| project \"OperationName\"
Суммирует доступ к keyvault, если ключ не найден.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.EVENTHUB\"
| where Category == \"Error\" and OperationName == \"wrapkey\"
| project Message, _ResourceId
Суммирует операцию, выполненную с помощью keyvault, чтобы отключить или восстановить ключ.
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.EVENTHUB\"
| where Category == \"info\" and OperationName == \"disable\" or OperationName == \"restore\"
| project Message
В этом списке перечислены все ошибки за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d)
| where ResourceProvider =="MICROSOFT.EVENTHUB"
| where Category == "OperationalLogs"
| summarize count() by "EventName", _ResourceId
Суммирует дуарацию сбоя при записи.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.EVENTHUB"
| where Category == "ArchiveLogs"
| summarize count() by "failures", "durationInSeconds", _ResourceId
Сводные сведения о состоянии запроса на присоединение клиента.
AzureDiagnostics // Need to turn on the Capture for this
| where ResourceProvider == "MICROSOFT.EVENTHUB"
| project "OperationName"
Суммирует доступ к keyvault, если ключ не найден.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.EVENTHUB"
| where Category == "Error" and OperationName == "wrapkey"
| project Message, ResourceId
Суммирует операцию, выполненную с помощью keyvault, чтобы отключить или восстановить ключ.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.EVENTHUB"
| where Category == "info" and OperationName == "disable" or OperationName == "restore"
| project Message
[Классический] Линейчатая диаграмма, показывающая тенденцию тома запросов KeyVault на каждую операцию с течением времени.
// KeyVault diagnostic currently stores logs in AzureDiagnostics table which stores logs for multiple services.
// Filter on ResourceProvider for logs specific to a service.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| summarize count() by bin(TimeGenerated, 1h), OperationName // Aggregate by hour
| render timechart
[Классический] Список вызывающих абонентов, определяемых ip-адресом с их числом запросов.
// KeyVault diagnostic currently stores logs in AzureDiagnostics table which stores logs for multiple services.
// Filter on ResourceProvider for logs specific to a service.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| summarize count() by CallerIPAddress
[Классический] Список запросов KeyVault, которые заняли больше 1sec.
// To create an alert for this query, click '+ New alert rule'
let threshold=1000; // let operator defines a constant that can be further used in the query
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| where DurationMs > threshold
| summarize count() by OperationName, _ResourceId
[Классический] Линейчатая диаграмма, показывающая тенденцию длительности запроса с течением времени с использованием различных агрегатов.
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| summarize avg(DurationMs) by requestUri_s, bin(TimeGenerated, 1h) // requestUri_s contains the URI of the request
| render timechart
[Классический] Количество неудачных запросов KeyVault по коду состояния.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| where httpStatusCode_d >= 300 and not(OperationName == "Authentication" and httpStatusCode_d == 401)
| summarize count() by requestUri_s, ResultSignature, _ResourceId
// ResultSignature contains HTTP status, e.g. "OK" or "Forbidden"
// httpStatusCode_d contains HTTP status code returned by the request (e.g. 200, 300 or 401)
// requestUri_s contains the URI of the request
[Классический] Перечисляет все запросы на обновление и исправления за последние 30 дней.
// KeyVault diagnostic currently stores logs in AzureDiagnostics table which stores logs for multiple services.
// Filter on ResourceProvider for logs specific to a service.
AzureDiagnostics
| where TimeGenerated > ago(30d) // Time range specified in the query. Overrides time picker in portal.
| where ResourceProvider =="MICROSOFT.KEYVAULT"
| where OperationName == "VaultPut" or OperationName == "VaultPatch"
| sort by TimeGenerated desc
[Классический] Отображает ошибки, вызванные неправильными событиями, которые не могут быть десериализированы заданием.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.KEYVAULT" and parse_json(properties_s).DataErrorType in ("InputDeserializerError.InvalidData", "InputDeserializerError.TypeConversionError", "InputDeserializerError.MissingColumns", "InputDeserializerError.InvalidHeader", "InputDeserializerError.InvalidCompressionType")
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
[Классический] Найдите в AzureDiagnostics поиск определенного значения в таблице AzureDiagnostics./nNote, для выполнения запроса необходимо обновить <параметр SeachValue> для получения результатов.
// This query requires a parameter to run. Enter value in SearchValue to find in table.
let SearchValue = "<SearchValue>";//Please update term you would like to find in the table.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.KEYVAULT"
| where * contains tostring(SearchValue)
| take 1000
Общее количество оплачиваемых выполнений по имени операции.
// Total billable executions
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where Category == "WorkflowRuntime"
| where OperationName has "workflowTriggerStarted" or OperationName has "workflowActionStarted"
| summarize dcount(resource_runId_s) by OperationName, resource_workflowName_s
Почасовая диаграмма времени выполнения приложения логики, распределение по рабочим процессам.
// Hourly Time chart for Logic App execution distribution by workflows
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where Category == "WorkflowRuntime"
| where OperationName has "workflowRunStarted"
| summarize dcount(resource_runId_s) by bin(TimeGenerated, 1h), resource_workflowName_s
| render timechart
Завершено выполнение по рабочему процессу, состоянию и ошибке.
//logic app execution status summary
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where OperationName has "workflowRunCompleted"
| summarize dcount(resource_runId_s) by resource_workflowName_s, status_s, error_code_s
| project LogicAppName = resource_workflowName_s , NumberOfExecutions = dcount_resource_runId_s , RunStatus = status_s , Error = error_code_s
Отображение сбоев действия и триггера для всех выполнений приложения логики по имени ресурса.
// To create an alert for this query, click '+ New alert rule'
//Action/Trigger failures for all Logic App executions
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where Category == "WorkflowRuntime"
| where status_s == "Failed"
| where OperationName has "workflowActionCompleted" or OperationName has "workflowTriggerCompleted"
| extend ResourceName = coalesce(resource_actionName_s, resource_triggerName_s)
| extend ResourceCategory = substring(OperationName, 34, strlen(OperationName) - 43) | summarize dcount(resource_runId_s) by code_s, ResourceName, resource_workflowName_s, ResourceCategory, _ResourceId
| project ResourceCategory, ResourceName , FailureCount = dcount_resource_runId_s , ErrorCode = code_s, LogicAppName = resource_workflowName_s, _ResourceId
| order by FailureCount desc
Количество входящих запросов на Шлюз приложений.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess"
| summarize AggregatedValue = count() by bin(TimeGenerated, 1h), _ResourceId
| render timechart
Количество запросов, отличных от SSL, в Шлюз приложений.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess" and sslEnabled_s == "off"
| summarize AggregatedValue = count() by bin(TimeGenerated, 1h), _ResourceId
| render timechart
Количество запросов, к которым Шлюз приложений ответила ошибка.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess" and httpStatus_d > 399
| summarize AggregatedValue = count() by bin(TimeGenerated, 1h), _ResourceId
| render timechart
Количество ошибок по агенту пользователя.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess" and httpStatus_d > 399
| summarize AggregatedValue = count() by userAgent_s, _ResourceId
| sort by AggregatedValue desc
Количество ошибок по URI.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess" and httpStatus_d > 399
| summarize AggregatedValue = count() by requestUri_s, _ResourceId
| sort by AggregatedValue desc
Количество запросов на IP-адрес клиента.
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess"
| summarize AggregatedValue = count() by clientIP_s
| top 10 by AggregatedValue
Количество запросов на версию HTTP.
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayAccess"
| summarize AggregatedValue = count() by httpVersion_s
| top 10 by AggregatedValue
Поиск сообщений о событиях безопасности сети заблокирован входящий трафик.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK"
| where Category == "NetworkSecurityGroupEvent"
| where direction_s == "In" and type_s == "block"
График отрисовки, показывающий общее количество запросов в час для каждого ресурса FrontDoor.
// Summarize number of requests per hour for each FrontDoor resource
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| summarize RequestCount = count() by bin(TimeGenerated, 1h), Resource, ResourceId
| render timechart
Количество запросов для каждого правила маршрутизации и внутреннего узла в минуту.
// Summarize number of requests per minute for each routing rule and backend host
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| summarize RequestCount = count() by bin(TimeGenerated, 1m), Resource, RoutingRuleName = routingRuleName_s, BackendHostname = backendHostname_s, ResourceId
Количество запросов с ответами на ошибки по узлу и пути.
// Summarize number of requests by host, path, and status codes >= 400
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| where toint(httpStatusCode_s) >= 400
| extend ParsedUrl = parseurl(requestUri_s)
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), StatusCode = httpStatusCode_s, ResourceId
| order by RequestCount desc
Количество запросов с ответами на ошибки по агенту пользователя.
// Summarize number of requests per user agent and status codes >= 400
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| where toint(httpStatusCode_s) >= 400
| summarize RequestCount = count() by UserAgent = userAgent_s, StatusCode = httpStatusCode_s , Resource, ResourceId
| order by RequestCount desc
Показывать первые 10 IP-адресов клиента и http-версий.
// Summarize top 10 client ips and http versions
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorAccessLog"
| summarize RequestCount = count() by ClientIP = clientIp_s, HttpVersion = httpVersion_s, Resource
| top 10 by RequestCount
| order by RequestCount desc
Количество заблокированных запросов брандмауэра в час.
// Summarize number of firewall blocked requests per hour by policy
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorWebApplicationFirewallLog"
| where action_s == "Block"
| summarize RequestCount = count() by bin(TimeGenerated, 1h), Policy = policy_s, PolicyMode = policyMode_s, Resource, ResourceId
| order by RequestCount desc
Показывать первые 20 заблокированных клиентов по IP-адресу и имени правила.
// Summarize top 20 blocked clients by IP and rule
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorWebApplicationFirewallLog"
| where action_s == "Block"
| summarize RequestCount = count() by ClientIP = clientIP_s, UserAgent = userAgent_s, RuleName = ruleName_s ,Resource
| top 20 by RequestCount
| order by RequestCount desc
Подсчет запросов брандмауэра, обработанных по узлу, пути, правилу и действиям.
// Summarize request count by host, path, rule, and action
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "FrontdoorWebApplicationFirewallLog"
| extend ParsedUrl = parseurl(requestUri_s)
| summarize RequestCount = count() by Host = tostring(ParsedUrl.Host), Path = tostring(ParsedUrl.Path), RuleName = ruleName_s, Action = action_s, ResourceId
| order by RequestCount desc
Анализирует данные журнала правил приложения.
AzureDiagnostics
| where Category == "AzureFirewallApplicationRule"
//this first parse statement is valid for all entries as they all start with this format
| parse msg_s with Protocol " request from " SourceIP ":" SourcePort:int *
//Parse action as this is the same for all log lines
| parse kind=regex flags=U msg_s with * ". Action\\: " Action "\\."
// case1: Action: A. Reason: R.
| parse kind=regex flags=U msg_s with "\\. Reason\\: " Reason "\\."
//case 2a: to FQDN:PORT Url: U. Action: A. Policy: P. Rule Collection Group: RCG. Rule Collection: RC. Rule: R.
| parse msg_s with * "to " FQDN ":" TargetPort:int * "." *
//Parse policy if present
| parse msg_s with * ". Policy: " Policy ". Rule Collection Group: " RuleCollectionGroup "." *
| parse msg_s with * " Rule Collection: " RuleCollection ". Rule: " Rule
//case 2.b: Web Category: WC.
| parse Rule with * ". Web Category: " WebCategory
//case 3: No rule matched. Proceeding with default action"
| extend DefaultRule = iff(msg_s contains "No rule matched. Proceeding with default action", true, false)
| extend
SourcePort = tostring(SourcePort),
TargetPort = tostring(TargetPort)
| extend
Action = case(Action == "","N/A", case(DefaultRule, "Deny" ,Action)),
FQDN = case(FQDN == "", "N/A", FQDN),
TargetPort = case(TargetPort == "", "N/A", tostring(TargetPort)),
Policy = case(RuleCollection contains ":", split(RuleCollection, ":")[0] ,case(Policy == "", "N/A", Policy)),
RuleCollectionGroup = case(RuleCollection contains ":", split(RuleCollection, ":")[1], case(RuleCollectionGroup == "", "N/A", RuleCollectionGroup)),
RuleCollection = case(RuleCollection contains ":", split(RuleCollection, ":")[2], case(RuleCollection == "", "N/A", RuleCollection)),
WebCategory = case(WebCategory == "", "N/A", WebCategory),
Rule = case(Rule == "" , "N/A", case(WebCategory == "N/A", Rule, split(Rule, '.')[0])),
Reason = case(Reason == "", case(DefaultRule, "No rule matched - default action", "N/A"), Reason )
| project TimeGenerated, msg_s, Protocol, SourceIP, SourcePort, FQDN, TargetPort, Action, Policy, RuleCollectionGroup, RuleCollection, Rule, Reason ,WebCategory
Анализирует данные журнала правил сети.
AzureDiagnostics
| where Category == "AzureFirewallNetworkRule"
| where OperationName == "AzureFirewallNatRuleLog" or OperationName == "AzureFirewallNetworkRuleLog"
//case 1: for records that look like this:
//PROTO request from IP:PORT to IP:PORT.
| parse msg_s with Protocol " request from " SourceIP ":" SourcePortInt:int " to " TargetIP ":" TargetPortInt:int *
//case 1a: for regular network rules
| parse kind=regex flags=U msg_s with * ". Action\\: " Action1a "\\."
//case 1b: for NAT rules
//TCP request from IP:PORT to IP:PORT was DNAT'ed to IP:PORT
| parse msg_s with * " was " Action1b:string " to " TranslatedDestination:string ":" TranslatedPort:int *
//Parse rule data if present
| parse msg_s with * ". Policy: " Policy ". Rule Collection Group: " RuleCollectionGroup "." *
| parse msg_s with * " Rule Collection: " RuleCollection ". Rule: " Rule
//case 2: for ICMP records
//ICMP request from 10.0.2.4 to 10.0.3.4. Action: Allow
| 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), split(Action1a,".")[0]),
Protocol = case(Protocol == "", Protocol2, Protocol),
SourceIP = case(SourceIP == "", SourceIP2, SourceIP),
TargetIP = case(TargetIP == "", TargetIP2, TargetIP),
//ICMP records don't have port information
SourcePort = case(SourcePort == "", "N/A", SourcePort),
TargetPort = case(TargetPort == "", "N/A", TargetPort),
//Regular network rules don't have a DNAT destination
TranslatedDestination = case(TranslatedDestination == "", "N/A", TranslatedDestination),
TranslatedPort = case(isnull(TranslatedPort), "N/A", tostring(TranslatedPort)),
//Rule information
Policy = case(Policy == "", "N/A", Policy),
RuleCollectionGroup = case(RuleCollectionGroup == "", "N/A", RuleCollectionGroup ),
RuleCollection = case(RuleCollection == "", "N/A", RuleCollection ),
Rule = case(Rule == "", "N/A", Rule)
| project TimeGenerated, msg_s, Protocol, SourceIP,SourcePort,TargetIP,TargetPort,Action, TranslatedDestination, TranslatedPort, Policy, RuleCollectionGroup, RuleCollection, Rule
Анализирует данные журнала правил аналитики угроз.
AzureDiagnostics
| where OperationName == "AzureFirewallThreatIntelLog"
| parse msg_s with Protocol " request from " SourceIP ":" SourcePortInt:int " to " TargetIP ":" TargetPortInt:int *
| parse msg_s with * ". Action: " Action "." Message
| parse msg_s with Protocol2 " request from " SourceIP2 " to " TargetIP2 ". Action: " Action2
| extend SourcePort = tostring(SourcePortInt),TargetPort = tostring(TargetPortInt)
| extend 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)
| sort by TimeGenerated desc
| project TimeGenerated, msg_s, Protocol, SourceIP,SourcePort,TargetIP,TargetPort,Action,Message
Начните с этого запроса, если вы хотите проанализировать журналы из сетевых правил, правил приложений, правил NAT, IDS, аналитики угроз и многое другое, чтобы понять, почему определенный трафик разрешен или запрещен. Этот запрос будет отображать последние 100 записей журнала, но путем добавления простых инструкций фильтра в конце запроса результаты можно настроить.
// Parses the azure firewall rule log data.
// Includes network rules, application rules, threat intelligence, ips/ids, ...
AzureDiagnostics
| where Category == "AzureFirewallNetworkRule" or Category == "AzureFirewallApplicationRule"
//optionally apply filters to only look at a certain type of log data
//| where OperationName == "AzureFirewallNetworkRuleLog"
//| where OperationName == "AzureFirewallNatRuleLog"
//| where OperationName == "AzureFirewallApplicationRuleLog"
//| where OperationName == "AzureFirewallIDSLog"
//| where OperationName == "AzureFirewallThreatIntelLog"
| extend msg_original = msg_s
// normalize data so it's eassier to parse later
| extend msg_s = replace(@'. Action: Deny. Reason: SNI TLS extension was missing.', @' to no_data:no_data. Action: Deny. Rule Collection: default behavior. Rule: SNI TLS extension missing', msg_s)
| extend msg_s = replace(@'No rule matched. Proceeding with default action', @'Rule Collection: default behavior. Rule: no rule matched', msg_s)
// extract web category, then remove it from further parsing
| parse msg_s with * " Web Category: " WebCategory
| extend msg_s = replace(@'(. Web Category:).*','', msg_s)
// extract RuleCollection and Rule information, then remove it from further parsing
| parse msg_s with * ". Rule Collection: " RuleCollection ". Rule: " Rule
| extend msg_s = replace(@'(. Rule Collection:).*','', msg_s)
// extract Rule Collection Group information, then remove it from further parsing
| parse msg_s with * ". Rule Collection Group: " RuleCollectionGroup
| extend msg_s = replace(@'(. Rule Collection Group:).*','', msg_s)
// extract Policy information, then remove it from further parsing
| parse msg_s with * ". Policy: " Policy
| extend msg_s = replace(@'(. Policy:).*','', msg_s)
// extract IDS fields, for now it's always add the end, then remove it from further parsing
| parse msg_s with * ". Signature: " IDSSignatureIDInt ". IDS: " IDSSignatureDescription ". Priority: " IDSPriorityInt ". Classification: " IDSClassification
| extend msg_s = replace(@'(. Signature:).*','', msg_s)
// extra NAT info, then remove it from further parsing
| parse msg_s with * " was DNAT'ed to " NatDestination
| extend msg_s = replace(@"( was DNAT'ed to ).*",". Action: DNAT", msg_s)
// extract Threat Intellingence info, then remove it from further parsing
| parse msg_s with * ". ThreatIntel: " ThreatIntel
| extend msg_s = replace(@'(. ThreatIntel:).*','', msg_s)
// extract URL, then remove it from further parsing
| extend URL = extract(@"(Url: )(.*)(\. Action)",2,msg_s)
| extend msg_s=replace(@"(Url: .*)(Action)",@"\2",msg_s)
// parse remaining "simple" fields
| parse msg_s with Protocol " request from " SourceIP " to " Target ". Action: " Action
| extend
SourceIP = iif(SourceIP contains ":",strcat_array(split(SourceIP,":",0),""),SourceIP),
SourcePort = iif(SourceIP contains ":",strcat_array(split(SourceIP,":",1),""),""),
Target = iif(Target contains ":",strcat_array(split(Target,":",0),""),Target),
TargetPort = iif(SourceIP contains ":",strcat_array(split(Target,":",1),""),""),
Action = iif(Action contains ".",strcat_array(split(Action,".",0),""),Action),
Policy = case(RuleCollection contains ":", split(RuleCollection, ":")[0] ,Policy),
RuleCollectionGroup = case(RuleCollection contains ":", split(RuleCollection, ":")[1], RuleCollectionGroup),
RuleCollection = case(RuleCollection contains ":", split(RuleCollection, ":")[2], RuleCollection),
IDSSignatureID = tostring(IDSSignatureIDInt),
IDSPriority = tostring(IDSPriorityInt)
| project msg_original,TimeGenerated,Protocol,SourceIP,SourcePort,Target,TargetPort,URL,Action, NatDestination, OperationName,ThreatIntel,IDSSignatureID,IDSSignatureDescription,IDSPriority,IDSClassification,Policy,RuleCollectionGroup,RuleCollection,Rule,WebCategory
| order by TimeGenerated
| limit 100
Начните с этого запроса, если вы хотите понять данные журнала прокси-сервера DNS брандмауэра. Этот запрос будет отображать последние 100 записей журнала, но путем добавления простых инструкций фильтра в конце запроса результаты можно настроить.
// DNS proxy log data
// Parses the DNS proxy log data.
AzureDiagnostics
| where Category == "AzureFirewallDnsProxy"
| parse msg_s with "DNS Request: " SourceIP ":" SourcePortInt:int " - " QueryID:int " " RequestType " " RequestClass " " hostname ". " protocol " " details
| extend
ResponseDuration = extract("[0-9]*.?[0-9]+s$", 0, msg_s),
SourcePort = tostring(SourcePortInt),
QueryID = tostring(QueryID)
| project TimeGenerated,SourceIP,hostname,RequestType,ResponseDuration,details,msg_s
| order by TimeGenerated
| limit 100
Таблица маршрутов BPG узнала за последние 12 часов.
AzureDiagnostics
| where TimeGenerated > ago(12h)
| where ResourceType == "EXPRESSROUTECIRCUITS"
| project TimeGenerated , ResourceType , network_s , path_s , OperationName
Информационные сообщения BGP по уровню, типу ресурсов и сети.
AzureDiagnostics
| where Level == "Informational"
| project TimeGenerated , ResourceId, Level, ResourceType , network_s , path_s
Найдите причину, из-за которой состояние мониторинга Диспетчер трафика Azure конечных точек не работает.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceType == "TRAFFICMANAGERPROFILES" and Category == "ProbeHealthStatusEvents"
| where Status_s == "Down"
| project TimeGenerated, EndpointName_s, Status_s, ResultDescription, SubscriptionId , _ResourceId
Успешные подключения P2S за последние 12 часов.
AzureDiagnostics
| where TimeGenerated > ago(12h)
| where Category == "P2SDiagnosticLog" and Message has "Connection successful"
| project TimeGenerated, Resource ,Message
Неудачные подключения P2S за последние 12 часов.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where TimeGenerated > ago(12h)
| where Category == "P2SDiagnosticLog" and Message has "Connection failed"
| project TimeGenerated, Resource ,Message
Успешные изменения конфигурации шлюза, внесенные администратором за последние 24 часа.
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "GatewayDiagnosticLog" and operationStatus_s == "Success" and configuration_ConnectionTrafficType_s == "Internet"
| project TimeGenerated, Resource, OperationName, Message, operationStatus_s
События консети и отключения туннеля S2S за последние 24 часа.
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "TunnelDiagnosticLog" and (status_s == "Connected" or status_s == "Disconnected")
| project TimeGenerated, Resource , status_s, remoteIP_s, stateChangeReason_s
Обновления маршрута BGP за последние 24 часа.
AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "RouteDiagnosticLog" and OperationName == "BgpRouteUpdate"
Выводит последние журналы в таблице AzureDiagnostics, отсортированные по времени (последняя версия).
AzureDiagnostics
| top 10 by TimeGenerated
Поиск журналов, сообщаемых о неудачных заданиях резервного копирования с последнего дня.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.RECOVERYSERVICES" and Category == "AzureBackupReport"
| where OperationName == "Job" and JobOperation_s == "Backup" and JobStatus_s == "Failed"
| project TimeGenerated, JobUniqueId_g, JobStartDateTime_s, JobOperation_s, JobOperationSubType_s, JobStatus_s , JobFailureCode_s, JobDurationInSecs_s , AdHocOrScheduledJob_s
В этом списке перечислены все вызовы управления.
AzureDiagnostics
| where ResourceProvider ==\"MICROSOFT.SERVICEBUS\"
| where Category == \"OperationalLogs\"
| summarize count() by EventName_s, _ResourceId
Суммирует все возникшие ошибки.
AzureDiagnostics
| where ResourceProvider ==\"MICROSOFT.SERVICEBUS\"
| where Category == \"Error\"
| summarize count() by EventName_s, _ResourceId
Суммирует доступ к keyvault, если ключ не найден.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.SERVICEBUS\"
| where Category == \"Error\" and OperationName == \"wrapkey\"
| project Message, _ResourceId
Сводка всех сущностей, которые были удалены автоматически.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.SERVICEBUS\"
| where Category == \"OperationalLogs\"
| where EventName_s startswith \"AutoDelete\"
| summarize count() by EventName_s, _ResourceId
Суммирует операцию, выполненную с помощью keyvault, чтобы отключить или восстановить ключ.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == \"MICROSOFT.SERVICEBUS\"
| where (Category == \"info\" and (OperationName == \"disable\" or OperationName == \"restore\"))
| project Message, _ResourceId
В этом списке перечислены все вызовы управления за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d)
| where ResourceProvider =="MICROSOFT.SERVICEBUS"
| where Category == "OperationalLogs"
| summarize count() by EventName_s, _ResourceId
Суммирует все ошибки, которые были замечены за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d)
| where ResourceProvider =="MICROSOFT.SERVICEBUS"
| where Category == "Error"
| summarize count() by EventName_s, _ResourceId
Суммирует доступ к keyvault, если ключ не найден.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SERVICEBUS"
| where Category == "Error" and OperationName == "wrapkey"
| project Message, _ResourceId
Сводка всех сущностей, которые были удалены автоматически.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SERVICEBUS"
| where Category == "OperationalLogs"
| where EventName_s startswith "AutoDelete"
| summarize count() by EventName_s, _ResourceId
Суммирует операцию, выполненную с помощью keyvault, чтобы отключить или восстановить ключ.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SERVICEBUS"
| where (Category == "info" and (OperationName == "disable" or OperationName == "restore"))
| project Message, _ResourceId
Отображение всех управляемых экземпляров с использованием хранилища выше 90 %.
// To create an alert for this query, click '+ New alert rule'
let storage_percentage_threshold = 90;
AzureDiagnostics
| where Category =="ResourceUsageStats"
| summarize (TimeGenerated, calculated_storage_percentage) = arg_max(TimeGenerated, todouble(storage_space_used_mb_s) *100 / todouble (reserved_storage_mb_s))
by _ResourceId
| where calculated_storage_percentage > storage_percentage_threshold
Отображение всех управляемых экземпляров с превышением частоты передачи ЦП более 95 % в трисхолде.
// To create an alert for this query, click '+ New alert rule'
let cpu_percentage_threshold = 95;
let time_threshold = ago(1h);
AzureDiagnostics
| where Category == "ResourceUsageStats" and TimeGenerated > time_threshold
| summarize avg_cpu = max(todouble(avg_cpu_percent_s)) by _ResourceId
| where avg_cpu > cpu_percentage_threshold
Отображение всех активных проблем с производительностью, обнаруженных интеллектуальными аналитическими сведениями. Обратите внимание, что для каждой отслеживаемой базы данных необходимо включить журнал SQLInsights.
AzureDiagnostics
| where Category == "SQLInsights" and status_s == "Active"
| distinct rootCauseAnalysis_s
Подождите статистику за последний час по логическому серверу и базе данных.
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where TimeGenerated >= ago(60min)
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize Total_count_60mins = sum(delta_waiting_tasks_count_d) by LogicalServerName, DatabaseName, wait_type_s
Отображает все ошибки, возникшие при обработке данных из входных данных.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).Type == "DataError"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, вызванные неправильными событиями, которые не могут быть десериализированы заданием.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType in ("InputDeserializerError.InvalidData", "InputDeserializerError.TypeConversionError", "InputDeserializerError.MissingColumns", "InputDeserializerError.InvalidHeader", "InputDeserializerError.InvalidCompressionType")
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, вызванные событиями, в которых невозможно преобразовать значение выражения TIMESTAMP BY в datetime.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "InvalidInputTimeStamp"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, вызванные событиями, в которых значение TIMESTAMP BY OVER timestampColumn равно NULL.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "InvalidInputTimeStampKey"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки из-за событий, в которых разница между временем приложения и временем прибытия превышает политику позднего прибытия.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "LateInputEvent"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки из-за событий, в которых разница между временем приложения и временем прибытия превышает 5 минут.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "EarlyInputEvent"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки из-за событий, поступающих из порядка в соответствии с политикой вне порядка.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutOfOrderEvent"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает все ошибки, которые произошли при записи результатов запроса в выходные данные задания.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType in ("OutputDataConversionError.RequiredColumnMissing", "OutputDataConversionError.ColumnNameInvalid", "OutputDataConversionError.TypeConversionError", "OutputDataConversionError.RecordExceededSizeLimit", "OutputDataConversionError.DuplicateKey")
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает все ошибки, в которых выходные записи, созданные заданием, имеют отсутствующий столбец.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutputDataConversionError.RequiredColumnMissing"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, в которых выходные записи, созданные заданием, имеют имя столбца, которое не сопоставляется с столбцом в выходных данных.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutputDataConversionError.ColumnNameInvalid"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, в которых выходные записи, созданные заданием, не могут быть преобразованы в допустимый тип в выходных данных.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutputDataConversionError.TypeConversionError"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, в которых размер выходных записей, созданных заданием, превышает поддерживаемый размер выходных данных.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutputDataConversionError.RecordExceededSizeLimit"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает ошибки, в которых выходная запись, созданная заданием, содержит столбец с тем же именем, что и системный столбец.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).DataErrorType == "OutputDataConversionError.DuplicateKey"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает все журналы, которые, скорее всего, негативно повлияли на работу.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and Level == "Error"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает все операции с заданием, которые привели к сбою.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and status_s == "Failed"
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Показывает все экземпляры, в которых запись в один из выходных данных была регулироваться целевой службой.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).Type in ("DocumentDbOutputAdapterWriteThrottlingError", "EventHubOutputAdapterEventHubThrottlingError", "PowerBIServiceThrottlingError", "PowerBIServiceThrottlingError")
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Отображает все ошибки, связанные с входными и выходными данными, которые прерывисты в природе.
// To create an alert for this query, click '+ New alert rule'
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).Type in ("AzureFunctionOutputAdapterTransientError", "BlobInputAdapterTransientError", "DataLakeOutputAdapterTransientError", "DocumentDbOutputAdapterTransientError", "EdgeHubOutputAdapterEdgeHubTransientError", "EventHubBasedInputInvalidOperationTransientError", "EventHubBasedInputOperationCanceledTransientError", "EventHubBasedInputTimeoutTransientError", "EventHubBasedInputTransientError", "EventHubOutputAdapterEventHubTransientError", "InputProcessorTransientFailure", "OutputProcessorTransientError", "ReferenceDataInputAdapterTransientError", "ServiceBusOutputAdapterTransientError", "TableOutputAdapterTransientError")
| project TimeGenerated, Resource, Region_s, OperationName, properties_s, Level, _ResourceId
Сводка всех ошибок данных за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d) //last 7 days
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and parse_json(properties_s).Type == "DataError"
| extend DataErrorType = tostring(parse_json(properties_s).DataErrorType)
| summarize Count=count(), sampleEvent=any(properties_s) by DataErrorType, JobName=Resource
Сводка всех ошибок за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d) //last 7 days
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS"
| extend ErrorType = tostring(parse_json(properties_s).Type)
| summarize Count=count(), sampleEvent=any(properties_s) by ErrorType, JobName=Resource
Сводка операций с ошибкой за последние 7 дней.
AzureDiagnostics
| where TimeGenerated > ago(7d) //last 7 days
| where ResourceProvider == "MICROSOFT.STREAMANALYTICS" and status_s == "Failed"
| summarize Count=count(), sampleEvent=any(properties_s) by JobName=Resource