跳至主要內容
翻譯頁面

本頁面由英文在 AI 協助下翻譯而成。若兩者有出入,請以英文版本為準。

使用 StackQL 實現 Azure 活動可觀測性

在本指南中,您將了解如何使用 StackQL 查詢 Azure 活動記錄並產生報表。StackQL 是一款強大的開發工具,可使用 SQL 語法查詢及部署雲端基礎架構與資源。

測試環境:內嵌式 SQL 後端 macos linux powershell

基本查詢​

下方的基本查詢使用 azure.monitor.activity_logs 資源,此資源位於 azure Provider 中;請將 subscriptionId 取代為您自己的值。在此查詢中,我們將使用帶有外部變數的 jsonnet 對 StackQL 查詢進行前置處理,如需詳細資訊,請參閱使用變數。此例中我們以內嵌的 jsonnet 程式碼區塊完成這項處理,但也可以使用 stackql exec 或 stackql shell 的 --iqldata 或 -q 引數,以外部檔案提供。

JSON_EXTRACT 與 SPLIT_PART 函式用於處理回應資料。

<<<jsonnet
{
stackqlQuery: {
subscriptionId: '631d1c6d-2a65-43e7-93c2-688bfe4e1468',
filter: "eventTimestamp ge ''" + std.extVar('startdate') + "''",
}
}
>>>
--
-- StackQL query with jsonnet preprocessing to retrieve all recent Azure activity
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(JSON_EXTRACT(authorization, '$.scope'), '/', 5) as resource_group,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = '{{ .stackqlQuery.filter }}'
AND subscription_id = '{{ .stackqlQuery.subscriptionId }}';

以互動方式執行查詢​

在已向 Azure 進行驗證的電腦上(包括 Azure Portal Cloud Shell):

# to run on mac or linux
./stackql shell --var startdate=$(date -u -d '1 day ago' '+%Y-%m-%dT%H:%M:%SZ')

或使用 PowerShell:

# to run using windows powershell
$startDate = (Get-Date).AddDays(-1).ToString("yyyy-MM-ddTHH:mm:ssZ")
stackql.exe shell --var startdate=$startDate

您應該會看到表格形式的輸出,其中包含下列資料行:event_timestamp、caller、ip_address、subscriptionId、tenantId、resource_group、resource_name、level、action、category、operation 和 status。

產生 CSV 報表​

將 StackQL 查詢儲存至檔案(activity_report.iql)後,您可以依下列方式從活動查詢產生 csv 報表(如需 stackql 輸出的詳細資訊,請參閱 --output csv):

# to run on mac or linux
./stackql exec \
--output csv \
-i activity_report.iql \
-f activity_report.csv \
--var startdate=$(date -u -d '1 day ago' '+%Y-%m-%dT%H:%M:%SZ')

或使用 PowerShell:

# to run using windows powershell
$startDate = (Get-Date).AddDays(-1).ToString("yyyy-MM-ddTHH:mm:ssZ")
stackql.exe exec `
--output csv `
-i activity_report.iql `
-f activity_report.csv `
--var startdate=$startDate

使用 KQL 進行伺服器端篩選​

Azure 中的伺服器端篩選是透過 KQL(Kusto 查詢語言)達成。只要擴充查詢中的 $filter 子句,您就可以直接在伺服器上篩選資料。這種方法非常有效率,因為它會減少透過網路傳輸的資料量。以下是一些伺服器端篩選的範例(為求簡潔,這些查詢直接使用參數,而不使用 jsonnet 前置處理器):

指定活動的日期範圍​

--
-- List events in a time range
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(JSON_EXTRACT(authorization, '$.scope'), '/', 5) as resource_group,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-24T00:00:00Z'' and eventTimestamp le ''2024-01-25T00:00:00Z'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

取得資源群組的活動​

--
-- List events for a resource group
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-24T00:00:00Z'' and eventTimestamp le ''2024-01-25T00:00:00Z'' and resourceGroupName eq ''MC_kube-03_aks862a_australiaeast'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

取得資源類型的活動​

--
-- List events for a resource provider
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(JSON_EXTRACT(authorization, '$.scope'), '/', 5) as resource_group,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-24T00:00:00Z'' and eventTimestamp le ''2024-01-25T00:00:00Z'' and resourceProvider eq ''Microsoft.Compute'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

取得特定資源的活動​

請注意,我們新增了一個額外欄位(correlationId),將在下一個伺服器端篩選中使用。

--
-- List events for resource using the resourceUri
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(resourceId, '/', -1) as resource_name,
correlationId,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-24T00:00:00Z'' and eventTimestamp le ''2024-01-25T00:00:00Z'' and resourceUri eq ''/subscriptions/631d1c6d-2a65-43e7-93c2-688bfe4e1468/resourceGroups/MC_kube-03_aks862a_australiaeast/providers/Microsoft.Compute/virtualMachines/aks-agentpool-35064155-1'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

取得相互關聯識別碼的活動​

相互關聯識別碼是一個唯一識別碼,用於追蹤 Azure 中與特定作業或交易相關的事件並將其分組。此查詢有助於在指定時間範圍內找出與特定作業相關的特定事件,讓疑難排解或了解活動順序更加容易。以下範例使用在上一個查詢中擷取的 correlationId:

--
-- List events for a correlation Id
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(JSON_EXTRACT(authorization, '$.scope'), '/', 5) as resource_group,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-24T00:00:00Z'' and eventTimestamp le ''2024-01-25T00:00:00Z'' and correlationId eq ''30b92c6d-4a82-401f-ac73-e28377669539'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

使用 WHERE 子句進行用戶端篩選​

用戶端篩選可以使用傳統的 SQL WHERE 子句完成。這種方法會在從伺服器擷取資料之後才篩選資料,在 StackQL 環境中提供資料處理與分析的彈性。

取得特定使用者的活動​

在範例查詢中,WHERE 子句根據 caller(使用者)篩選資料列,進一步精煉資料。

--
-- Filter events for a particular user (caller)
--
SELECT
eventTimestamp as event_timestamp,
caller,
JSON_EXTRACT(claims, '$.ipaddr') as ip_address,
subscriptionId,
tenantId,
SPLIT_PART(JSON_EXTRACT(authorization, '$.scope'), '/', 5) as resource_group,
SPLIT_PART(resourceId, '/', -1) as resource_name,
level,
JSON_EXTRACT(authorization, '$.action') as action,
JSON_EXTRACT(category, '$.localizedValue') as category,
JSON_EXTRACT(operationName, '$.localizedValue') as operation,
JSON_EXTRACT(status, '$.localizedValue') as status
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-01T00:00:00Z'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468'
AND caller = 'javen@stackql.io';

執行摘要/彙總查詢​

由於 StackQL 是完整的 SQL 引擎,所有 Provider、服務與資源都原生支援 GROUP BY/彙總查詢(包括 AVG、COUNT、SUM、MIN、MAX 等)。

依嚴重性層級計算事件數​

在此範例中,我們會將自開始日期時間起的事件依嚴重性分組,並計算每個層級的數量。

--
-- Summarize events by severity level
--
SELECT
level,
COUNT(*) as num_events
FROM azure.monitor.activity_logs
WHERE $filter = 'eventTimestamp ge ''2024-01-29T00:00:00Z'''
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468'
GROUP BY level;
/* returns..
|---------------|------------|
| level | num_events |
|---------------|------------|
| Error | 58 |
|---------------|------------|
| Informational | 57 |
|---------------|------------|
| Warning | 57 |
|---------------|------------|
*/

使用 JOIN 擴充活動資料​

SELECT
l.eventTimestamp as event_timestamp,
l.level,
JSON_EXTRACT(l.operationName, '$.localizedValue') as operation,
vm.name,
vm.location,
JSON_EXTRACT(vm.properties, '$.timeCreated') as time_created
FROM azure.monitor.activity_logs l
INNER JOIN azure.compute.virtual_machines vm
ON vm.name = SPLIT_PART(l.resourceId, '/', -1)
WHERE vm.resource_group_name = 'MC_kube-03_aks862a_australiaeast'
AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468'
AND l.$filter = 'eventTimestamp ge ''2024-01-28T00:00:00Z'' and resourceGroupName eq ''MC_kube-03_aks862a_australiaeast''';

或使用來自 jsonnet 的變數,這些變數可以透過 --iqldata 或 -q 引數以檔案提供,也可以如下所示以 jsonnet 區塊提供:

<<<jsonnet
{
stackqlQuery: {
subscriptionId: '631d1c6d-2a65-43e7-93c2-688bfe4e1468',
resourceGroupName: 'MC_kube-03_aks862a_australiaeast',
}
}
>>>
SELECT
l.eventTimestamp as event_timestamp,
l.level,
JSON_EXTRACT(l.operationName, '$.localizedValue') as operation,
vm.name,
vm.location,
JSON_EXTRACT(vm.properties, '$.timeCreated') as time_created
FROM azure.monitor.activity_logs l
INNER JOIN azure.compute.virtual_machines vm
ON vm.name = SPLIT_PART(l.resourceId, '/', -1)
WHERE vm.resource_group_name = '{{ .stackqlQuery.resourceGroupName}}'
AND subscription_id = '{{ .stackqlQuery.subscriptionId}}'
AND l.$filter = 'eventTimestamp ge ''2024-01-28T00:00:00Z'' and resourceGroupName eq ''{{ .stackqlQuery.resourceGroupName}}''';
資訊

JOIN 查詢中未明確區分的 WHERE 參數(例如上例中的 subscription_id)會由兩個資源共用(此例中為 azure.monitor.activity_logs 與 azure.compute.virtual_machines)。