MackerelでOracle Databaseを監視してみた

こんにちは、SCSKの嶋谷です。

これまでに3度SaaS型監視サービスMackerelに関する記事を投稿してきた結果、先日Mackerelアンバサダーを拝命いたしました。今後もアンバサダーとしてMackerelを広めていきたいです。

今回の記事もMackerelに関する記事になっています。

弊社が提供している監視サービスではOracle Databaseの監視をしたいというお客様が一定数います。
Mackerelでは、DBのクエリ実行回数やテーブルロックの回数を監視する機能がありますが、それだけでは不足している部分があります。その一例としてOracle Databaseでの監視が挙げられます(後述)。
そのため、お客様に対してMackerelを利用した監視サービスを提供する場合、Oracle Databaseの監視は提供できないのでお客様の要望に応えることができません。

どうにかしたいなとMackerelのドキュメントを読み漁っていると、ユーザが独自に取得した監視データ(数値)をエージェントを利用してMackerelに連携する機能を発見しました。そこで、Oracle Databaseからデータさえ取得できればMackerelに連携することができ、Oracle Databaseの監視ができるのではないかと考え、実装してみました。

私の部署では若手を中心にブログを書いているので、ぜひ他の記事もご覧ください！

MackerelでのDB監視

Mackerelでは、メトリックプラグインを利用してMySQLやPostgreSQLの状態を監視することが可能です。SQL Serverの状態を監視するメトリックプラグインは提供されていません。
メトリックプラグイン – mackerel-plugin-mysql – Mackerel ヘルプ
メトリックプラグイン – mackerel-plugin-postgres – Mackerel ヘルプ

導入においてMackerelではOracle Databaseの監視する機能が不足していると記載しましたが、監視する方法はあります。例えば、AWS RDSでDBを構築する際にエンジンとしてOracleを選択することでAWSインテグレーションの機能を利用して監視することができます。
AWSインテグレーション – Mackerel ヘルプ
しかし、オンプレミスサーバやEC2上のサーバに自前でOracle Databaseを構築している場合、監視可能な機能が不足しています。

今回はこの部分を解決したいと考えています。

MackerelでのOracleDB監視の概要

今回は下記の流れをPowershellスクリプトで実装して、Oracle Databaseの監視を実現しました。
①Oracle Databaseに接続
OracleのクライアントツールであるSQLPlusでOracle Databaseに接続しています。
②SQL文を実行してデータ(数値)を取得
③取得データをエージェントを利用してMackerelに投稿

スクリプト概要

Oracle Databaseの監視を実現するために、作成したファイルを以下に示します。
今回はスクリプトに修正を加えることを減らすために、接続情報やSQL文を別のファイルとして用意しました。
修正が必要な場合には、スクリプトではなくsqlファイルやiniファイルを修正することを想定しています。

ファイル名	説明
mac_ora_text.ps1	Oracle Databaseに接続してデータを取得後、Mackerelに連携するスクリプト
query.sql	実行するSQL文を記述したファイル
setting.ini	Oracle Databaseへの接続情報などを記載したファイル

以下が作成したスクリプトです。

# Oracle DatabaseにSQLPlusで接続→クエリを実行してデータを取得→Mackerelに送信

# mackerel-agent.confファイルからAPIキーを読み取る
function Get-MackerelApiKey {
    param (
       [string]$filePath
    )

    $configContent = Get-Content -Path $filePath
    foreach ($line in $configContent) {
       if ($line -match "apikey\s*=\s*`"(.+)`"") {
          return $matches[1]
       }
    }
    return $null
}

# APIキーを取得
$MACKEREL_APIKEY = Get-MackerelApiKey -filePath ".\mackerel-agent.conf"

# hostIDの取得
$HOSTID = Get-Content -Path ".\id"

# 現在時刻をエポック秒で取得
$currentUtcTime = [DateTime]::UtcNow
$unixtime = [int][double]((Get-Date $currentUtcTime -UFormat %s))

# ユーザ自身で接続情報(ユーザ名やパスワード)とSQL文を別のファイルに準備していただき、そのファイルを読み込み
$filePath = "setting.ini"
$sqlfilePath = "query.sql"

$PARAM = @{}
$ses_counts = @()

try {
    Get-Content $filePath -ErrorAction Stop | % { $PARAM += ConvertFrom-StringData $_ -ErrorAction Stop }
}
catch {
    Write-Error "File not exists: $filePath"
    exit 0
}
# グラフ名とメトリック名を,区切りで分割して配列に格納
$metric_num_arrays = $PARAM.metric_num -split ","
$fig_name = $PARAM.figure_num -split ","

# 取得した接続情報を各変数に代入
$username = $PARAM.username
$password = $PARAM.password
$connectString = $PARAM.connectString

# SQLファイルからクエリを読み込み
try {
    $query = Get-Content -Path $sqlfilePath -Raw -ErrorAction Stop
}
catch {
    Write-Error "SQL File not exists: $sqlfilePath"
    exit 0
}

# Oracleへの接続・クエリの実行
# -Sオプションを付与して結果のみを取得する
$result = $query | sqlplus -S $username/$password@$connectString

# SQLの結果ごとに空白行が生成されるので削除
$result = $result | Where-Object { $_ -ne "" }

# エラー内容はAPIを利用して送信
# ヘッダーの設定
$headers = @{
    "X-Api-Key" = $MACKEREL_APIKEY
    "Content-Type" = "application/json; charset=UTF-8"
}
$counter = 0

# 下記4つの文字列がクエリ結果に含まれていればSQL文のミスや接続情報の設定ミスと判断してエラー内容を送信して終了
$errorMessages = @("ORA-", "TNS-", "SP2-","ERROR:")
$maxCheckAttempts = 3
foreach ($line in $result) {
    foreach ($errorMessage in $errorMessages) {
       if ($line -match $errorMessage) {
          $line = $line -replace '"', ''

          # JSONデータの作成
          # messageにエラー内容を格納
          # maxCheckAttemptsにはにアラートを発生させる条件である連続同一エラー回数
          # statusはWARNINGになっていますが、Criticalでも問題ありません。。
          # エラー内容によってアラートレベルを変更する場合は改変が必要です。
          $POSTJSON = @"
{
    "reports": [{
     "source": {
      "type": "host",
      "hostId": "$HOSTID"
     },
     "name": "$monitorname",
     "status": "WARNING",
     "message": "$line",
     "occurredAt": $unixtime,
     "maxCheckAttempts": $maxCheckAttempts
    }]
}
"@

          $utf8Bytes = [System.Text.Encoding]::UTF8.GetBytes($POSTJSON)
          $response = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/monitoring/checks/report" -Method Post -Headers $headers -Body $utf8Byte
          exit 1
       }
    }
}

# この部分まで到達した際は、エラーが起きずデータが取得できている状態。
# 現在のアラートが起きているかを確認して、起きていればアラートをクローズする(データが取得できているため)
# APIを利用してアラートの内容を取得
$alert_response = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/alerts" -Method Get -Headers $headers

# 取得したアラート($alert_response)にはMackerel発生しているアラートすべてが含まれている
# アラートから$errorMessagesに含まれているアラートだけを取得
$response_removes = @()
foreach ($errorMessage in $errorMessages) {
    $response_removes += $alert_response.alerts | Where-Object { $_.message -match $errorMessage}
}

if ($response_removes.Length -ne 0){
    Write-Host "not null"
    $body = @"
{
"reason": "solve" 
}
"@

#アラートをクローズ
$utf8Bytes = [System.Text.Encoding]::UTF8.GetBytes($body)
    for ($i = 0; $i -lt $response_removes.Length; $i++) {
    $return = Invoke-RestMethod -Uri "https://api.mackerelio.com/api/v0/alerts/$($response_removes[$i].id)/close" -Method POST -Headers $headers -Body $utf8Bytes
    Write-Output $return 
    }
}else{
Write-Output "null"
}

# クエリ結果の値は文字列として取得されるので数値としてあつかう処理
for ($i = 0; $i -le $result.Length-1; $i++) {
    Write-Host $result[$i]
    $ses_counts += [float]$result[$i]
}

# マカレルエージェントが認識できる形式で出力・Mackerelへデータ送信
# {metric name}\t{metric value}\t{epoch seconds}
# ↑上記のフォーマットで標準出力することでMackerelにデータを送信することができる。
# metric nameにおいて、名前の最後のドットまでが共通するメトリックがひとつのグラフにまとめられて投稿される
# メトリック名の先頭には自動的に"custom."が付与される#>
# 下記の場合、グラフ名「custom.oracle.sql」にses_countのメトリック名でデータが可視化される
foreach ($metric in $metric_num_arrays) {
    Write-Host "oracle.sql.$($fig_name[$counter]).$metric`t$($ses_counts[$counter])`t$unixtime"
    $counter++
}

以下にスクリプトの簡単な仕様を記載します。
(1)必要情報の取得
スクリプト実行における必要な情報を取得します。
・MackerelAPIKey
・ホストID
・DB接続情報(ユーザ名・パスワード・インスタンス名)
・Mackerelに表示されるグラフ名、メトリック名
・SQL文

(2)Oracle Databaseに接続してデータを取得
DB接続情報、SQL文をもとにOracle Databaseに接続してデータを取得。
データが取得できない場合は、エラー内容を文字列で取得し、アラートとしてMackerelに連携します。
エラーの原因は下記が考えられます。
・DB接続情報の設定ミス
・SQL文の記述ミス
・ネットワークエラー　など
Mackerelの仕様上、一度投稿されたアラートはMackerel上に表示され続けます。
そのためデータを取得できた場合は、アラートが解決されたと判断してOracle Databaseに関する投稿済みのアラートを自動でクローズします。

(3)データの連携
取得したデータをMackerelに連携します。連携したデータはグラフとして可視化されるので、(1)の処理で取得したグラフ名やメトリック名も同時に連携します。

利用手順

(1)以下のファイルをエージェントのconfigファイルが格納されているフォルダに配置
・mac_ora_text.ps1
・query.sql
・setting.ini

(2)setting.iniに以下の情報を記述
・DB接続情報
・グラフ名
・メトリック名
グラフ名、メトリック名は連携するデータの個数分定義する必要があります。

(3)query.sqlにSQL文を記述

(4)エージェントのconfigファイルに以下を追記
[plugin.metrics.プラグイン名]
command = [“powershell”, “mac_ora_text.ps1”]
プラグイン名は任意で設定可能です。

(5)エージェントの再起動

スクリプトの実行結果

データを取得できた場合

データを取得できた場合、Mackerelに連携されて下記のグラフのように可視化されることを確認しました。
このグラフは接続しているOracle Databaseのセッション数を示しています。
今回はセッション数のグラフを可視化してみましたが、SQLを実行して取得できる数値データはすべて連携できます。

データを取得できない場合

データを取得できない場合、以下のようなアラートがMackerel上に表示されることを確認しました。

DB接続情報の設定ミス時のエラー

SQL文の記述ミス時のエラー

上記のエラー内容は一例ですが、エラー内容を連携することでアラートの原因を一目で理解できます。

課題

Oracle Databaseに接続してデータ取得後、Mackerelに連携することはできましたが、課題はあります。

・今回作成スクリプトはWindowsにしか対応しておらず、Linuxに対応していない
・MySQLやPostgreSQLなどの他のデータベースには対応していない(プラグインでは取得できないデータを取得する場合に限る)

今後はこの課題を解決していきたいと考えています。

ただ、MackerelでOracle Databaseの監視を実現する第一歩になったと感じています。

今回はOracle Databaseを対象としましたが、今後はSQL Serverの監視を実現できるようにもしていきたいです。

まとめ

今回はOracle DatabaseからSQL文を用いてデータを取得して、Mackerelに連携するスクリプトを作成しました。

PowerShellスクリプトでの開発は初めてだったので、構文や規則から学ぶ必要があり実装に時間がかかりました。

時間がかかった分、Mackerel上にグラフが可視化されたときは感動しました！

現在のスクリプトには課題が多くあり、機能として提供するには程遠い状態です。

自分一人の力では限界があるので、部署の方と協力して提供レベルにまで高度化していきたいです。

最後までご覧いただきありがとうございました！