AlertHub と Google スプレッドシートを連携する

Alerthub の Webhook アクションとランブックを用いて、Google スプレッドシートの情報を Google Apps Script (以下 GAS )経由で取得する方法を紹介します。

AlertHub とスプレッドシートを連携すると、スプレッドシート上のみにある情報をアクションの変数として含めたりルールの条件として使用することが可能になります。
具体的には、スプレッドシート上に機器の一覧を記載し、各機器に対して異常を検知した場合の対応事項を記入しておくことで、アラートに応じて通知内容に機器ごとに必要な対応事項を記載することができます。

さらに、GAS の内容を編集することにより、参照先のシートを変更したりシートに記載されている特定の情報のみを抜き出すなど、様々な方法で Google スプレッドシート上の情報を取得できます。
例えば、スプレッドシートに製品ごとのシートを作成し、各シートに担当者の当番表等を記入することで、日付によって各製品の該当する担当者に通知先を変更する、といった処理も可能です。

想定する状況

下図のように記載された Google スプレッドシートにおいて、A列（Key）の入力からそれに対応したB列（対応内容）に関する文字列を取得します。
今回は、対象のホスト名に対して障害通知メールに含める対応内容の文章をスプレッドシート上から取得することを想定しています。

例えば、hostA に関するアラートの場合、対応の必要はありません。の文字列を取得してメールに含めます。

上記のGoogleスプレッドシートは、共有範囲が制限されている(全体公開ではない)ことを前提としています。
全体公開の場合も連携自体は可能ですが、セキュリティの観点から共有範囲を制限することを推奨します。

Google Cloud Platform の設定

GAS に対して AlertHub からアクセスするため、OAuth 認証を使用して認証を行います。

OAuth 認証を導入するために、Google Cloud Platform のプロジェクトを作成します。
Google API Console にて 「プロジェクトを作成」をクリックします。

任意のプロジェクト名を入力し、作成ボタンをクリックします。

サイドメニューの「Cloud の概要」から「ダッシュボード」をクリックし、「プロジェクト情報」の中に記載のプロジェクト番号を控えます。

OAuth 同意画面の作成

OAuth 同意画面を作成します。
サイドメニューの 「API とサービス」から「OAuth 同意画面」をクリックし、「外部」を選択して作成ボタンをクリックします。

「アプリ名」に任意の名称、「ユーザーサポートメール」に任意のメールアドレスを入力します。

画面をスクロールし、「デベロッパーの連絡先情報」にも任意のメールアドレスを入力して「保存して次へ」をクリックします。

「②スコープ」及び「③テストユーザー」では、なにも設定せず「保存して次へ」をクリックします。

最後に設定内容を確認し、「ダッシュボードに戻る」をクリックします。

「公開ステータス」の「アプリを公開」をクリックします。
ポップアップが表示されるため、「確認」をクリックします。

※ テスト環境のまま後述の動作を行う場合は、テストユーザーに Google Cloud Platform を作成した Google アカウントを登録する必要があります。
また、テスト環境の場合、リフレッシュトークンを定期的に取得しなおす必要があります。

OAuth クライアント ID の作成

認証情報に関する設定を行います。

サイドメニューの「認証情報」をクリックし「＋認証情報を作成」から「PAuth クライアント ID」を選択します。

「アプリケーションの種類」は「Webアプリケーション」を選択し、「名前」には任意の名前を入力します。

画面をスクロールし、「承認済みのリダイレクト URL」の「＋URL を追加」をクリックます。
http://localhost と入力し、作成ボタンをクリックします。

下記の画面が表示されるため、クライアント ID とクライアントシークレットを控えます。

認証コードの取得

上記で作成した OAuth クライアントを用いて、認証コードの取得を行います。
ブラウザ上で以下の URL を入力します。
{{クライアント ID }}には作成した OAuth2.0 クライアント ID のクライアント ID を入力します。

https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/drive&include_granted_scope=true&response_type=code&access_type=offline&redirect_uri=http://localhost&client_id={{クライアントID}}

ログインユーザーとしてプロジェクトを作成した Google アカウントを選択します。

以下のような画面が表示された場合は「詳細」をクリックし「スプレッドシート連携(安全ではないページ)に移動」をクリックします。

Google ドライブの操作権限を付与する旨の表示がされるため、「続行」をクリックします。

以下のような画面が表示されるため、画面上部の URL の code= 後から &scope 前までの文字列を控えます。

※ code は「英数字1文字＋/」から始まる文字列になります。
もし「英数字1文字＋%2F」から始まっていた場合、URL エンコードされているため、%2Fを/に置き換えた文字列を控えてください。

リフレッシュトークンの取得

上記で取得した認証コードを用いてリフレッシュトークンを取得するためのリクエストを送ります。
認証コードは短時間で有効期限が切れてしまうため、注意してください。
認証コードの有効期限が切れた場合、「認証コードの取得」から再度手続きを行う必要があります。

今回は、ターミナル上で curl を使用してリクエストを送信します。

ターミナル上で以下のコマンドを入力します。
{{コード}}には先ほどの「英数字1文字＋/」から始まる文字列を入力します。
{{クライアント ID}}、{{クライアントシークレット}}には OAuth クライアント ID 作成時に確認した文字列を入力します。

curl -H "Content-Type: application/json" -d '{"code":"{{コード}}","client_id":"{{クライアントID}}","client_secret":"{{クライアントシークレット}}","redirect_uri":"http://localhost","grant_type":"authorization_code"}' "https://www.googleapis.com/oauth2/v4/token"

※Windows で作業を行う場合、 下記の通りエスケープを行った上でコマンドプロンプトを使用してください。

  • シングルクォート(`)→ダブルクォート(")に変換
  • ダブルクォート(")→円マークとダブルクォート(\")に変換
curl -H "Content-Type: application/json" -d "{\"code\":\"{{コード}}\",\"client_id\":\"{{クライアントID}}\",\"client_secret\":\"{{クライアントシークレット}}\",\"redirect_uri\":\"http://localhost\",\"grant_type\":\"authorization_code\"}" "https://www.googleapis.com/oauth2/v4/token"

正しく認証されると以下のように返ってきます。
refresh_token 部分に記載のリフレッシュトークンを控えます。

{ "access_token": "{{アクセストークン(ここの内容は使いません)}}",
 "expires_in": 3599,
 "refresh_token": "{{リフレッシュトークン}}",
 "scope": "https://www.googleapis.com/auth/drive",
 "token_type": "Bearer" }

Google Apps Script の設定

Google スプレッドシートから、GAS の設定を行います。

Google スプレッドシートの画面上部の「拡張機能」から「Apps Scripts」を選択します。

Google Cloud Platform プロジェクトの登録

画面左の歯車マークをクリックして、設定画面を開きます。
「GCP のプロジェクト番号」に「Google Cloud Platform の設定」で作成したプロジェクトのプロジェクト番号を入力して「プロジェクトを設定」をクリックします。

GAS の実装・デプロイ

GAS を外部から HTTP リクエストの GET メソッドで実行した場合に実行する関数 doGet(e) を登録します。
画面左の<>のマークをクリックし以下のコードを入力します。
※ GAS のウェブアプリの仕様ですので、関数名は doGet(e) から変更しないでください。

function doGet(e) {
  // 受け取り
  let key = e.parameter.key;
  let spreadSheet = SpreadsheetApp.openById("{{スプレッドシートのID}}").getSheetByName("{{シート名}}");
  let keys = spreadSheet.getRange(1, 1, spreadSheet.getLastRow()).getValues();
  // 該当のセルが無かった場合は空の文字列を返す
  targetCellValue = "";
  for(i = 1; i <= spreadSheet.getLastRow(); i++) {
    if (keys[i] == key){
      targetCellValue = spreadSheet.getRange(i+1, 2).getDisplayValue();
    }
  }
  // 返信作成用定数
  const output = ContentService.createTextOutput();
  // Mime TypeをJSONに設定
  output.setMimeType(ContentService.MimeType.JSON);
  // JSONテキストをセットする
  output.setContent(JSON.stringify({"target_cell_value": targetCellValue}));
  return output;
}

{{スプレッドシートの ID}}には URL の spreadsheets/d/ 後から /edit 前までの部分を入力します。

{{シート名}}には取得するシートの名称を入力します。
Google スプレッドシート自体の名称ではなく、画面下部に記載されているシートの名称です。

画面右上の「デプロイ」より「新しいデプロイ」をクリックします。

画面左の歯車マークをクリックして、「ウェブアプリ」にチェックを入れます。
「次のユーザーとして実行」に「自分」、「アクセスできるユーザー」に「自分のみ」を選択しデプロイボタンをクリックします。

以下のような画面が表示されるため、「アクセスを承認」をクリックします。

以下のような画面が表示されます。
英語表記ですが、ここからは「リフレッシュトークンの取得」の手順と同様です。
プロジェクトを作成したGoogleアカウントでログインします。

Google Cloud Platform のプロジェクトが利用者のアカウントにアクセスすることを承諾する旨の表示がされるため、「Allow」をクリックします。

完了すると以下の画面が表示されるため、ウェブアプリ URL を控え完了ボタンをクリックします。

AlertHub の設定

AlertHub で Google スプレッドシートにアクセスするための設定を行います。

今回は、Webhook でアラートが送信される想定で、受信スロットから直接ランブックを動かすよう設定します。
ランブックの詳細な設定方法は、Kompira AlertHub ランブック操作マニュアル を参照してください。

アクションの作成

AlertHub から Google スプレッドシートにアクセスするための Webhook アクションを作成します。
今回は3つのアクションを作成します。

アクセストークン取得のアクション

Google スプレッドシート及び GAS にアクセスするためのアクセストークンを生成するアクションを作成します。

AlertHub のサイドメニューより「アクション」をクリックし、右上の「WEBHOOK」をクリックします。

ダイアログが表示されるため、以下の通り入力します。

  1. 任意の表示名を入力します
  2. https://www.googleapis.com/oauth2/v4/token と入力します
  3. 「POST」を選択します
  4. 下記のリクエストボディを入力します
  5. 保存ボタンをクリックします
{
"refresh_token":"{{リフレッシュトークン}}",
"client_id":"{{クライアントID}}",
"client_secret":"{{クライアントシークレット}}",
"grant_type":"refresh_token"
}

{{リフレッシュトークン}}には「リフレッシュトークンの取得」で取得したリフレッシュトークンを入力します。
{{クライアント ID}}、{{クライアントシークレット}}には「OAuth クライアント ID の作成」で取得したクライアント ID とクライアントシークレットをそれぞれ入力します。

スプレッドシートからセル値を取得するアクション

上記で取得したアクセストークンを用いて GAS を実行するアクションを作成します。

同様に「アクション」をクリックし、右上の「WEBHOOK」をクリックします。

ダイアログが表示されるため、以下の通り入力します。

  1. 任意の表示名を入力します
  2. 下記の通り Webhook URL を入力します
  3. 「GET」を選択します
  4. 保存ボタンをクリックします
{{ウェブアプリ URL}}?access_token={{tokeninfo.access_token}}&key={{message.content.data.host_name}}

{{ウェブアプリ URL}}には「GAS の実装・デプロイ」で取得したウェブアプリ URL を入力します。
{{message.content.data.host_name}}部分は、後述の「スプレッドシート検索用 key の取得」を参照し条件に合った変数を入力します。

スプレッドシート検索用 key の取得

AlertHub では、受信した Webhook や AlertHub 上で扱う情報に対し取得したい値の項目を指定することができます。
項目の指定方法についての詳細は、Kompira AlertHub 基本マニュアル > 設定の流れ > フィールドの指定方法 を参照してください。

ここでは、想定される取得項目について例を記載します。

AlertHub で受信した Webhook メッセージの内容を key に指定することができます。
下記のようなリクエストボディをアラートとして受信しているとします。

{
"host_name":"hostB",
"status":"error",
"ping":"XXX.XXX.XXX.XXX",
"date":"0000-00-00T00:00:00"
}

ホスト名（今回は hostB 部分）を key として取得したい場合、{{message.content.data.host_name}} と指定します。

アラートメッセージにスプレッドシート検索として使用可能な key が含まれている場合や、含めるようにアラートメッセージを編集できる場合に適した方法です。

メール送信のアクション

AlertHub から通知を行うためのアクションを作成します

「アクション」をクリックし、右上の「メール」をクリックします。

ダイアログが表示されるため、以下の通り入力します。

  1. 任意の表示名を入力します
  2. メールの送信先を指定します
  3. メールの件名を入力します
  4. メールの本文を入力します※
  5. 保存ボタンをクリックします

※メール本文にスプレッドシートの内容を記載するため、{{cell.target_cell_value}} を含めるように入力します。

ランブックの作成

ランブックを作成します。

サイドメニューより「ランブック」をクリックし、右上の「＋」マークをクリックします。

ダイアログが表示されるため、任意の表示名を入力し保存ボタンをクリックします。

ランブックの編集画面が表示されるため、左上の「＋」をクリックしアクションステップの中から「アクセストークン取得のアクション」で作成したアクションを選択します。

ダイアログが表示されるため、任意の表示名を入力し保存ボタンをクリックします。
「アクションの実行完了を待つ」はチェックを入れたままにします。

作成したステップが出現するため、クリックして右に出現するドロワーのステップ ID をコピーし控えます。

再度左上の「＋」をクリックしオペレーターステップの中から「フィールドを JSON としてパースする」 を選択します。

ダイアログが表示されるため、以下の通り入力します。

  1. 任意の表示名を入力します
  2. steps.{{「アクセストークン取得」のステップID}}.output.body と入力します
  3. 任意のフィールド名を入力します
  4. 保存ボタンをクリックします

左上の「＋」をクリックしアクションステップの中から「 スプレッドシートからセル値を取得するアクション 」で作成したアクションを選択します。

ダイアログが表示されるため、任意の表示名を入力し保存ボタンをクリックします。
「アクションの実行完了を待つ」はチェックを入れたままにします。

作成したステップが出現するため、クリックして右に出現するドロワーのステップ ID をコピーし控えます。

左上の「＋」をクリックしオペレーターステップの中から「フィールドを JSON としてパースする」 を選択します。

ダイアログが表示されるため、以下の通り入力します。

  1. 任意の表示名を入力します
  2. steps.{{「セル値取得」のステップID}}.output.body と入力します
  3. cell と入力します
  4. 保存ボタンをクリックします

左上の「＋」をクリックしアクションステップの中から「メール送信のアクション」で作成したアクションを選択します。

全てのステップを以下の通り繋げ、右上の保存ボタンをクリックします。

受信スロットの作成

受信スロットを作成します。

サイドメニューより「受信スロット」をクリックし、右上の「＋」マークをクリックします。

ダイアログが表示されるため、以下の通り入力します。

  1. 「Webhook」を選択します
  2. 任意の表示名を入力します
  3. 「ランブックの作成」で作成したランブックを選択します
  4. 保存ボタンをクリックします

受信スロット一覧が表示されるため、作成した受信スロットの表示名をクリックします。
URL が表示されるため、 連携元システムからのアラートの送信先に設定します。

以上で設定は終了です。

今回はランブックを受信スロットから直接動かすよう設定を行いましたが、ランブックはトリガーにも設定が可能です。
トリガーにランブックを設定する場合、スコープやトリガーでアラートの集約を行った上での使用が可能となります。
用途に応じてランブックの設定箇所を変更しご活用ください。

動作確認

動作確認を行います。

実際に以下のリクエストボディを含んだアラートを送信します。

{
"host_name":"hostB",
"status":"error",
"ping":"XXX.XXX.XXX.XXX",
"date":"0000-00-00T00:00:00"
}

アラートを受信すると、AlertHub から以下の通りスプレッドシートに対応した通知が届きました。

ランブックの実行履歴でも、実行が完了したことが確認できました。

TOP