如何優化您的 Grafana Loki 查詢效能

grafana loki promtail mssql audit

Ivan Cheng 2023-08-11 17:05:18 ‧ 1237 瀏覽

分享至

記得之前有教大家如何使用 Grafana Loki 分析 SQL Server Audit 日誌，運行一段時間後您就會發現查詢一天以內的數據還蠻正常的，如果想要撈出一周的趨勢就會出現逾時的問題。

今天就讓我們來聊聊如何優化您的 Grafana Loki 查詢效能吧

我們使用到的 promtail-local-config.yaml 範例如下，主要是將 Application Logs 透過 Pipeline Stages 轉成 JSON 格式並將自動解析的 source、event_id 與 leveltext 欄位貼上標籤。

server:
  http_listen_port: 9080
  grpc_listen_port: 0

positions:
  filename: "./positions.yaml"
clients:
  - url: http://your_loki_ip:3100/loki/api/v1/push
    
scrape_configs:
- job_name: windows
  windows_events:
    eventlog_name: "Application"
    use_incoming_timestamp: true
    xpath_query: '*'
    bookmark_path: "./bookmark-application.xml"
    exclude_event_data: true
    exclude_user_data: true
  pipeline_stages:
  - json:
      expressions:
        source: source
        eventID: event_id
        level: levelText
  - labels:
      source:
      eventID:
      level:

也就是 Promtail 不過濾與處理任何的 Logs 全部推到 Loki 進行儲存

再使用 LogQL 將資料從 Loki 撈出，並於 Grafana 呈現分析的儀表板。

這樣等於我們需要把時間序列中的所有 Logs 透過正規表示式解析出的欄位之後才能進行運用與過濾，沒有充分利用到 Loki 過濾器的技巧。

透過正規表示式解析出的欄位，我們稱為標籤過濾器，標籤過濾器是不被納入索引的。

過濾器 Filters
當涉及到海量過濾日誌的需求時，我們可以在 LogQL 使用過濾的查詢技巧在幾秒鐘內過濾 TB 級的數據。

您可以使用的三種過濾器類型：

標籤匹配器 Label matchers
線路濾波器 Line filters
標籤過濾器 Label filters

標籤匹配器 Label matchers
標籤匹配器可以大幅減少您正在搜索的日誌量，例如從 100 TB 到 1 TB 的最佳方法，也是您應該優先考量的第一件事情。

這也代表我們需要與 Promtail 代理定義何謂良好的標籤，以便可以在多個不同維度上對數據進行切片，例如使用 Cluster, Namespace 與 Container 作為標籤。

以下是一些好的和壞的例子：

好的

{cluster="us-central1"}
{container="istio"}
{cluster="us-central1", container=~"agent|promtail"}

壞的

{job=~".*/queue"}
{namespace!~"dev-.*"}

線路濾波器 Line filters
標籤匹配器過濾完第一輪資料之後，我們應該使用線路濾波器再進行篩選。

這不是強制性的，但您應該將它們放在標籤匹配器之後。

允許您過濾包含 |= 或不包含 != 字符串的日誌，並且您還可以使用 RE2 正則表達式來匹配 |~ 或不匹配 !~ 模式。

請注意順序在這裡很重要，您要先過濾哪些是最多的內容。

|= "err" != "timeout" != "cancelled" |~ "failed.*" != "memcached"

例如您意識到大部分錯誤來自 memcached，則將其移至第一個位置。

!= "memcached" |= "err" != "timeout" != "cancelled" |~ "failed.*"

這樣後續過濾器的執行次數就會減少

標籤過濾器 Label filters
標籤過濾器提供更複雜的比較，但它們通常需要先提取標籤，然後將標籤值轉換為另一種類型。

這意味著它們通常是最慢的，因此您應該最後使用它們。
雖然 | json 和 | logfmt 解析器速度很快，但使用 | regex1 卻相當慢。

小技巧，您可以透過 | json 或 | logfmt 之類的解析器，來使用標籤過濾器而不用提取標籤。

如果 | json 或 | logfmt 就能解析出您要用的標籤，就盡量避免使用 | regex1。

{namespace="loki-ops",container="query-frontend"} |= "caller=metrics.go:83" | logfmt | throughput > 1GB and duration > 10s and org_id=29

理解完上面三種過濾器類型後，我們應該優先使用標籤匹配器，在多個不同維度上對數據進行切片，以便大幅減少搜索的日誌量。

Loki 僅對 Metadata (也就是標籤匹配器) 進行索引，而不是日誌行的全文。

Promtail
我們使用到的 promtail-local-config.yaml 範例如下

server:
  http_listen_port: 9080
  grpc_listen_port: 0

positions:
  filename: "./positions.yaml"
clients:
  - url: http://your_loki_ip:3100/loki/api/v1/push
    
scrape_configs:
- job_name: mssql
  windows_events:
    eventlog_name: "Application"
    use_incoming_timestamp: true
    xpath_query: '*'
    bookmark_path: "./bookmark-application.xml"
    exclude_event_data: true
    exclude_user_data: true
  pipeline_stages:
  - json:
      expressions:
        source: source
        eventID: event_id
        level: levelText
  - labels:
      source:
      eventID:
      level:
  - match:
      selector: '{eventID="33205"} |= "action_id:LGIF"'
      stages:
      - regex:
          expression: 'action_id:(?P<ActionID>\w+).*class_type:(?P<ClassType>\w+).*server_principal_name:(?P<ServerPrincipalName>\w+).*\\u003caddress\\u003e(?P<IpAddress>.*)\\u003c\/address'
      - labels:
          ActionID:
          ClassType:
          ServerPrincipalName:
          IpAddress:
  - match:
      selector: '{eventID="33205"} |~ ".*action_id:(CR|AL|DR|UP|DL).*"'
      stages:
      - regex:
          expression: 'action_id:(?P<ActionID>\w+).*class_type:(?P<ClassType>\w+).*server_principal_name:(?P<ServerPrincipalName>.*)\\nserver_principal_sid.*server_instance_name:(?P<ServerInstanceName>.*)\\n.*database_name:(?P<DatabaseName>.*)\\nschema_name:(?P<SchemaName>.*)\\nobject_name:(?P<ObjectName>.*)\\nstatement'
      - labels:
          ActionID:
          ClassType:
          ServerPrincipalName:
          ServerInstanceName:
          DatabaseName:
          SchemaName:
          ObjectName:
          IpAddress:
  - match:
      selector: '{eventID="33205"} !~ ".*action_id:(LGIF|CR|AL|DR|UP|DL).*"'
      action: drop

我們會使用 SQL Server Audit 功能，沒用過的朋友請參考此篇文章。

我們選擇以下稽核動作群組名稱

FAILED_LOGIN_GROUP
DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_ACCESS_GROUP

Pipeline Stages 說明如下

Step1：透過 json 解析器提取出 source、event_id 與 leveltext 欄位，並貼上source、eventID、level 標籤。

Step2：處理 Database 登入失敗的稽核動作

符合 eventID 為 33205 且 action_id 為 LGIF 的數據

使用 regex 解析以下欄位成為標籤匹配器

ActionID
ClassType
ServerPrincipalName
IpAddress

標籤匹配器會被納入索引，需要注意該欄位是否具有索引特質。

登入失敗的描述幾乎都不一樣，參考意義不大。Statement 就不適合做成標籤匹配器。

statement:使用者 ‘sa’ 的登入失敗。原因: 無法開啟明確指定的資料庫 ‘CHIComp18’。 [用戶端: 192.168.0.61]

以下提供 FAILED_LOGIN_GROUP 的日誌給大家參考

2023-08-09T09:36:31+08:00 {"source":"MSSQLSERVER","channel":"Application","computer":"KHS-E0-A0-E10","event_id":33205,"task":4,"levelText":"資訊","taskText":"登入","keywords":"稽核失敗,傳統","timeCreated":"2023-08-09T01:36:31.350802200Z","eventRecordID":76254647,"message":"稽核事件: audit_schema_version:1\nevent_time:2023-08-09 01:36:30.7787661\nsequence_number:1\naction_id:LGIF\nsucceeded:false\nis_column_permission:false\nsession_id:0\nserver_principal_id:0\ndatabase_principal_id:0\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:0\nuser_defined_event_id:0\ntransaction_id:0\nclass_type:LX\npermission_bitmask:00000000000000000000000000000000\nsequence_group_id:7491E919-5C8F-4A95-981C-AAC88359EEC9\nsession_server_principal_name:\nserver_principal_name:sa\nserver_principal_sid:\ndatabase_principal_name:\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:KHS-E0-A0-E10\ndatabase_name:\nschema_name:\nobject_name:\nstatement:使用者 'sa' 的登入失敗。 原因: 無法開啟明確指定的資料庫 'CHIComp18'。 [用戶端: 192.168.0.61]\nadditional_information:\u003caction_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"\u003e\u003cpooled_connection\u003e0\u003c/pooled_connection\u003e\u003cerror\u003e0x00004818\u003c/error\u003e\u003cstate\u003e38\u003c/state\u003e\u003caddress\u003e192.168.0.61\u003c/address\u003e\u003c/action_info\u003e\nuser_defined_information:\n。"}

Step3：處理 Database 與 Schema 物件的稽核動作

符合 eventID 為 33205 且 action_id 為 CR、AL、DR、UP、DL 的數據

使用 regex 解析以下欄位成為標籤匹配器

ActionID
ClassType
ServerPrincipalName
ServerInstanceName
DatabaseName
SchemaName
ObjectName
IpAddress

每次 Database 與 Schema 物件的資料定義語言 DDL 與資料操作語言 DML 也是不盡相同，所以 Statement 也不適合做成標籤匹配器。

statement:Select Case when Exists(SELECT * FROM comBillAttach WHERE ProgID=’ChiOrder.OrdGet’ AND PKValues=’20230809004') then 1 else 0 end

以下提供稽核的日誌給大家參考

2023-08-09T11:18:30+08:00 {"source":"MSSQLSERVER","channel":"Application","computer":"KHS-E0-A0-E10","event_id":33205,"task":5,"levelText":"資訊","taskText":"無","keywords":"稽核成功,傳統","timeCreated":"2023-08-09T03:18:30.244734000Z","eventRecordID":76374405,"message":"稽核事件: audit_schema_version:1\nevent_time:2023-08-09 03:18:29.3436857\nsequence_number:1\naction_id:DR  \nsucceeded:true\nis_column_permission:false\nsession_id:54\nserver_principal_id:1\ndatabase_principal_id:1\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:1691074452\nuser_defined_event_id:0\ntransaction_id:45818682\nclass_type:U \npermission_bitmask:00000000000000000000000000000000\nsequence_group_id:21022BA0-1BDB-44CD-9042-D1BE7BE05C82\nsession_server_principal_name:sa\nserver_principal_name:sa\nserver_principal_sid:01\ndatabase_principal_name:dbo\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:KHS-E0-A0-E10\ndatabase_name:CHIComp01\nschema_name:dbo\nobject_name:LockInvoiceNo_RC03559974\nstatement:Drop Table LockInvoiceNo_RC03559974\nadditional_information:\nuser_defined_information:\n。"}
2023-08-09T11:41:40+08:00 {"source":"MSSQLSERVER","channel":"Application","computer":"KHS-E0-A0-E10","event_id":33205,"task":5,"levelText":"資訊","taskText":"無","keywords":"稽核成功,傳統","timeCreated":"2023-08-09T03:41:40.327501000Z","eventRecordID":76395222,"message":"稽核事件: audit_schema_version:1\nevent_time:2023-08-09 03:41:40.1904970\nsequence_number:1\naction_id:SL  \nsucceeded:true\nis_column_permission:true\nsession_id:64\nserver_principal_id:1\ndatabase_principal_id:1\ntarget_server_principal_id:0\ntarget_database_principal_id:0\nobject_id:274100017\nuser_defined_event_id:0\ntransaction_id:45872265\nclass_type:U \npermission_bitmask:00000000000000000000000000000001\nsequence_group_id:82C811FD-92BA-41C5-9663-5B0BF6D15B65\nsession_server_principal_name:sa\nserver_principal_name:sa\nserver_principal_sid:01\ndatabase_principal_name:dbo\ntarget_server_principal_name:\ntarget_server_principal_sid:\ntarget_database_principal_name:\nserver_instance_name:KHS-E0-A0-E10\ndatabase_name:CHIComp01\nschema_name:dbo\nobject_name:comBillAttach\nstatement:Select Case when Exists(SELECT * FROM comBillAttach WHERE ProgID='ChiOrder.OrdGet' AND PKValues='20230809004') then 1 else 0 end\nadditional_information:\nuser_defined_information:\n。"}

Step4：過濾資料

只儲存 eventID 為 33205 且 action_id 為 CR、AL、DR、UP、DL 的數據

其餘的一律 Drop 掉，避免 Loki 儲存太多垃圾數據影響效能。

回到 Grafana 的 Explore 我們就可以使用標籤匹配器，在幾秒鐘內快速地過濾數據了。

若想要單獨稽核執行 DELETE 陳述式時，目前只有在資料庫層級的稽核才能辦到，而且必須在 SQL Server 企業版中才有機會實現。

在標準版的情況下，只能開啟 SCHEMA_OBJECT_ACCESS_GROUP 來捕捉所有的 DML 陳述式，也就是 SELECT、INSERT、UPDATE、DELETE 的稽核紀錄全部捕捉。然而稽核可能只在意到底誰刪除了資料或者竄改資料，大量的 SELECT 陳述式根本派不上用場又拖累查詢效能。

在過去未導入 Loki 日誌聚合平台的時候，我們只能透過記錄檔檢視來調閱稽核日誌。