はじめに
インスタンスの停止し忘れや想定外のストレージ利用等にすぐに気づけるように、前日分のコストレポートが毎日メールで送られてきたら便利だと思い、Always FreeのAutonomous Databaseと通知サービス（OCI Notifications）を使用してやってみました。
1. 通知サービスの設定
まずはじめに、メール送信のために称する通知サービスの設定を行います。
コンソールメニューから「アプリケーション統合」セクションの「通知」をクリックします。
「トピックの作成」をクリックします。
トピック名を入力して「作成」をクリックします。
ここではトピック名を「Cost-Report-Topic」としました。
トピック「Cost-Report-Topic」が作成されました。
のちほど使用するので、トピックのOCIDをコピーしてメモしておきます。
左側のメニューの「サブスクリプション」をクリックします。
「サブスクリプションの作成」をクリックします。
以下の内容を入力し、「作成」をクリックします。
| 項目 | 値 |
|---|---|
| サブスクリプション・トピック | 「Cost-Report-Topic」を選択 |
| プロトコル | 「電子メール」を選択 |
| 電子メール | メールの送信先アドレス |
サブスクリプションが作成されました。
作成直後は状態が「Pending」になっています。
サブスクリプションを作成すると、「電子メール」に入力したメールアドレスに以下のような確認メールが送られますので、メール内の「Confirm subscription」をクリックします。
メール内の「Confirm subscription」をクリックすると、以下のようなページにアクセスして、トピックへのサブスクライブが完了します。
コンソール画面を確認すると、状態が「Active」に変わっています。
以上で、通知サービスの準備が整いました。
2. PL/SQLプロシージャの作成
前日分のOCIコストレポートを送信するプロシージャsend_daily_costを作成します。
Autonomous Databaseでは、こちらに記載されている事前準備を行うことにより、テナントのコストデータ（OCI_COST_DATAビュー）や利用状況データ（OCI_USAGE_DATAビュー）にアクセスできるので、そちらを使用して前日分のコストレポートを作成します。
プロシージャを作成する際は、4行目の「ocid1.onstopic.xxxxx」の部分を、先ほどコピーしておいたトピックのOCIDで置き換えてください。
send_daily_costプロシージャ
CREATE OR REPLACE PROCEDURE send_daily_cost
IS
-- メールの送信に使用するトピックのOCIDをセット
topic_ocid VARCHAR2(100) := 'ocid1.onstopic.oc1.ap-tokyo-1.aaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxp7q';
yesterday VARCHAR2(100);
region VARCHAR2(100);
request_uri VARCHAR2(200);
resp DBMS_CLOUD_TYPES.resp;
api_endpoint VARCHAR2(100);
-- 前日のコストレポートのサマリーを取得するカーソルの定義
CURSOR daily_cost_cur IS
SELECT compartment_name, product_sku, product_description, TO_CHAR(SUM(my_cost),'999999999990.00') as cost
FROM oci_cost_data
WHERE TO_CHAR(interval_usage_start + NUMTODSINTERVAL(9,'HOUR'),'YYYYMMDD') = TO_CHAR(SYSDATE-1, 'YYYYMMDD')
GROUP BY compartment_name, product_sku, product_description
ORDER BY compartment_name, product_sku, product_description;
daily_cost_row daily_cost_cur%ROWTYPE;
title VARCHAR2(100) := 'Daily Usage Report of ';
body CLOB;
payload JSON_OBJECT_T := JSON_OBJECT_T('{}');
BEGIN
-- 前日の日付を取得
SELECT TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') INTO yesterday FROM dual;
title := title || yesterday;
body := title || CHR(10)||CHR(10);
-- ADBが存在するリージョンを取得
SELECT json_value(cloud_identity, '$.REGION') INTO region FROM v$pdbs;
-- デバッグ用出力
DBMS_OUTPUT.put_line ('ADB Region : '||region||CHR(10));
-- トピックAPIエンドポイントを取得
request_uri := 'https://notification.'||region||'.oraclecloud.com/20181201/topics/'||topic_ocid;
resp := DBMS_CLOUD.send_request(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
uri => request_uri,
method => DBMS_CLOUD.METHOD_GET
);
api_endpoint := JSON_VALUE(DBMS_CLOUD.get_response_text(resp), '$.apiEndpoint');
-- デバッグ用出力
DBMS_OUTPUT.PUT_LINE('apiEndpoint: '||api_endpoint||CHR(10));
-- カーソルをオープン
OPEN daily_cost_cur;
LOOP
-- 1行取得
FETCH daily_cost_cur INTO daily_cost_row;
EXIT WHEN daily_cost_cur%NOTFOUND;
-- 各列の値をメール本文用の変数に追加
body := body||RPAD(daily_cost_row.compartment_name,10)||' | '||daily_cost_row.product_sku||' | '||RPAD(daily_cost_row.product_description,60)||' | '||daily_cost_row.cost||CHR(10);
END LOOP;
-- カーソルをクローズ
CLOSE daily_cost_cur;
-- デバッグ用出力
DBMS_OUTPUT.PUT_LINE(body);
-- メールタイトルを設定
payload.put('title', 'OCI Daily Cost Report of '||yesterday);
-- メール本文を設定
payload.put('body' , body);
-- トピックAPIエンドポイントをコールしてメールを送信
request_uri := api_endpoint||'/20181201/topics/'||topic_ocid||'/messages';
resp := DBMS_CLOUD.send_request(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
uri => request_uri,
method => DBMS_CLOUD.METHOD_POST,
body => UTL_RAW.CAST_TO_RAW(payload.to_clob)
);
-- デバッグ用出力
DBMS_OUTPUT.PUT_LINE('resp body: '||DBMS_CLOUD.get_response_text(resp));
DBMS_OUTPUT.PUT_LINE('resp headers: '||DBMS_CLOUD.get_response_headers(resp).to_clob);
END;
/
SQL*PlusやDatabase Actionsから実行して、プロシージャを作成します。
SQL> CREATE OR REPLACE PROCEDURE send_daily_cost
2 IS
3 -- メールの送信に使用するトピックのOCIDをセット
4 topic_ocid VARCHAR2(100) := 'ocid1.onstopic.oc1.ap-tokyo-1.aaaxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxp7q';
5 yesterday VARCHAR2(100);
6 region VARCHAR2(100);
7 request_uri VARCHAR2(200);
8 resp DBMS_CLOUD_TYPES.resp;
9
10 api_endpoint VARCHAR2(100);
11 -- 前日のコストレポートのサマリーを取得するカーソルの定義
12 CURSOR daily_cost_cur IS
13 SELECT compartment_name, product_sku, product_description, TO_CHAR(SUM(my_cost),'999999999990.00') as cost
14 FROM oci_cost_data
15 WHERE TO_CHAR(interval_usage_start + NUMTODSINTERVAL(9,'HOUR'),'YYYYMMDD') = TO_CHAR(SYSDATE-1, 'YYYYMMDD')
16 GROUP BY compartment_name, product_sku, product_description
17 ORDER BY compartment_name, product_sku, product_description;
18 daily_cost_row daily_cost_cur%ROWTYPE;
19
20 title VARCHAR2(100) := 'Daily Usage Report of ';
21 body CLOB;
22 payload JSON_OBJECT_T := JSON_OBJECT_T('{}');
23
24 BEGIN
25 -- 前日の日付を取得
26 SELECT TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') INTO yesterday FROM dual;
27 title := title || yesterday;
28 body := title || CHR(10)||CHR(10);
29 -- ADBが存在するリージョンを取得
30 SELECT json_value(cloud_identity, '$.REGION') INTO region FROM v$pdbs;
31 -- デバッグ用出力
32 DBMS_OUTPUT.put_line ('ADB Region : '||region||CHR(10));
33 -- トピックAPIエンドポイントを取得
34 request_uri := 'https://notification.'||region||'.oraclecloud.com/20181201/topics/'||topic_ocid;
35 resp := DBMS_CLOUD.send_request(
36 credential_name => 'OCI$RESOURCE_PRINCIPAL',
37 uri => request_uri,
38 method => DBMS_CLOUD.METHOD_GET
39 );
40 api_endpoint := JSON_VALUE(DBMS_CLOUD.get_response_text(resp), '$.apiEndpoint');
41 -- デバッグ用出力
42 DBMS_OUTPUT.PUT_LINE('apiEndpoint: '||api_endpoint||CHR(10));
43 -- カーソルをオープン
44 OPEN daily_cost_cur;
45 LOOP
46 -- 1行取得
47 FETCH daily_cost_cur INTO daily_cost_row;
48 EXIT WHEN daily_cost_cur%NOTFOUND;
49 -- 各列の値をメール本文用の変数に追加
50 body := body||RPAD(daily_cost_row.compartment_name,10)||' | '||daily_cost_row.product_sku||' | '||RPAD(daily_cost_row.product_description,60)||' | '||daily_cost_row.cost||CHR(10);
51 END LOOP;
52 -- カーソルをクローズ
53 CLOSE daily_cost_cur;
54 -- デバッグ用出力
55 DBMS_OUTPUT.PUT_LINE(body);
56 -- メールタイトルを設定
57 payload.put('title', 'OCI Daily Cost Report of '||yesterday);
58 -- メール本文を設定
59 payload.put('body' , body);
60 -- トピックAPIエンドポイントをコールしてメールを送信
61 request_uri := api_endpoint||'/20181201/topics/'||topic_ocid||'/messages';
62 resp := DBMS_CLOUD.send_request(
63 credential_name => 'OCI$RESOURCE_PRINCIPAL',
64 uri => request_uri,
65 method => DBMS_CLOUD.METHOD_POST,
66 body => UTL_RAW.CAST_TO_RAW(payload.to_clob)
67 );
68 -- デバッグ用出力
69 DBMS_OUTPUT.PUT_LINE('resp body: '||DBMS_CLOUD.get_response_text(resp));
70 DBMS_OUTPUT.PUT_LINE('resp headers: '||DBMS_CLOUD.get_response_headers(resp).to_clob);
71 END;
72 /
プロシージャが作成されました。
SQL>
プロシージャが作成できました。
3. PL/SQLプロシージャの動作確認
プロシージャが作成できたので、早速実行してみます。
SQL> exec send_daily_cost
ADB Region : ap-tokyo-1
apiEndpoint: https://xxxxx.notification.ap-tokyo-1.oraclecloud.com
AlwaysFree | B88525 | Native DNS | 0.00
AlwaysFree | B90926 | Monitoring Service - Retrieval | 0.00
AlwaysFree | B91391 | Oracle Autonomous Data Warehouse - Free | 0.00
AlwaysFree | B91392 | Oracle Autonomous Data Warehouse - Exadata Storage - Free | 0.00
AlwaysFree | B91393 | Oracle Autonomous Transaction Processing - Free | 0.00
AlwaysFree | B91444 | Virtual Machine Standard - E2 Micro - Free | 0.00
AlwaysFree | B91445 | Block Volume - Free | 0.00
AlwaysFree | B91627 | Object Storage - Requests | 0.00
AlwaysFree | B91628 | Object Storage - Storage | 0.00
AlwaysFree | B92092 | Key Management Service - Key Version Counts | 0.00
AlwaysFree | B93455 | Object Storage - Outbound Data Transfer Zone 2 | 0.00
AlwaysFree | B93455 | Outbound Data Transfer Zone 2 | 0.00
Test | B91627 | Object Storage - Requests | 0.00
Test | B91628 | Object Storage - Database Backup /Image Storage | 0.00
Test | B91628 | Object Storage - Storage | 0.00
Test | B93455 | Object Storage - Outbound Data Transfer Zone 2 | 0.00
xxxx | B88525 | Native DNS | 0.01
xxxx | B90926 | Monitoring Service - Retrieval | 0.00
xxxx | B91627 | Object Storage - Requests | 0.00
xxxx | B93455 | Object Storage - Outbound Data Transfer Zone 2 | 0.00
resp body: {
"messageId" : "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
}
resp headers: {"Connection":"close","Date":"Tue, 13 Dec 2022 05:45:00
GMT","opc-request-id":"/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX","Content-Type":"application/json","Content-Length":"58"}
PL/SQLプロシージャが正常に完了しました。
SQL>
プロシージャが正常に完了しました。
メールボックスを確認してみます。
コンパートメント毎、SKU毎で集計した前日のOCIコストレポートを受信できていることが確認できました。
コメントとデバッグ用出力を除いた最終版はこのようになりました。
send_daily_costプロシージャ（最終版）
CREATE OR REPLACE PROCEDURE send_daily_cost
IS
topic_ocid VARCHAR2(100) := 'replace_your_topic_ocid';
yesterday VARCHAR2(100);
title VARCHAR2(100) := 'Daily Usage Report of ';
body CLOB;
region VARCHAR2(100);
request_uri VARCHAR2(200);
resp DBMS_CLOUD_TYPES.resp;
api_endpoint VARCHAR2(100);
payload JSON_OBJECT_T := JSON_OBJECT_T('{}');
CURSOR daily_cost_cur IS
SELECT compartment_name, product_sku, product_description, TO_CHAR(SUM(my_cost),'999999999990.00') as cost
FROM oci_cost_data
WHERE TO_CHAR(interval_usage_start + NUMTODSINTERVAL(9,'HOUR'),'YYYYMMDD') = TO_CHAR(SYSDATE-1, 'YYYYMMDD')
GROUP BY compartment_name, product_sku, product_description
ORDER BY compartment_name, product_sku, product_description;
daily_cost_row daily_cost_cur%ROWTYPE;
BEGIN
SELECT TO_CHAR(SYSDATE-1, 'YYYY/MM/DD') INTO yesterday FROM dual;
title := title || yesterday;
body := title || CHR(10)||CHR(10);
SELECT json_value(cloud_identity, '$.REGION') INTO region FROM v$pdbs;
request_uri := 'https://notification.'||region||'.oraclecloud.com/20181201/topics/'||topic_ocid;
resp := DBMS_CLOUD.send_request(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
uri => request_uri,
method => DBMS_CLOUD.METHOD_GET);
api_endpoint := JSON_VALUE(DBMS_CLOUD.get_response_text(resp), '$.apiEndpoint');
OPEN daily_cost_cur;
LOOP
FETCH daily_cost_cur INTO daily_cost_row;
EXIT WHEN daily_cost_cur%NOTFOUND;
body := body||RPAD(daily_cost_row.compartment_name,10)||' | '||daily_cost_row.product_sku||' | '||RPAD(daily_cost_row.product_description,60)||' | '||daily_cost_row.cost||CHR(10);
END LOOP;
CLOSE daily_cost_cur;
payload.put('title', 'OCI Daily Cost Report of '||yesterday);
payload.put('body' , body);
request_uri := api_endpoint||'/20181201/topics/'||topic_ocid||'/messages';
resp := DBMS_CLOUD.send_request(
credential_name => 'OCI$RESOURCE_PRINCIPAL',
uri => request_uri,
method => DBMS_CLOUD.METHOD_POST,
body => UTL_RAW.CAST_TO_RAW(payload.to_clob));
END;
/
4. 自動実行の設定（ジョブの作成）
DBMS_SCHEDULER.CREATE_JOBでプロシージャの定期的に実行するジョブを作成します。
ここでは、初回が2022/12/14の朝8時、以降毎日朝8時にsend_daily_costプロシージャを実行するジョブsend_daily_cost_jobを作成します。
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SEND_DAILY_COST_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'ADMIN.SEND_DAILY_COST',
start_date => TO_TIMESTAMP_TZ('2022-12-14 08:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
auto_drop => FALSE,
enabled => TRUE
);
END;
/
SQL*PlusやDatabase Actionsから実行して、ジョブを作成します。
SQL> BEGIN
2 DBMS_SCHEDULER.CREATE_JOB (
3 job_name => 'SEND_DAILY_COST_JOB',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'ADMIN.SEND_DAILY_COST',
6 start_date => TO_TIMESTAMP_TZ('2022-12-14 08:00:00 +9:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
7 repeat_interval => 'FREQ=DAILY;INTERVAL=1',
8 auto_drop => FALSE,
9 enabled => TRUE
10 );
11 END;
12 /
PL/SQLプロシージャが正常に完了しました。
SQL>
ジョブsend_daily_cost_jobがログを出力するように設定します。
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE ('SEND_DAILY_COST_JOB', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
END;
/
SQL> BEGIN
2 DBMS_SCHEDULER.SET_ATTRIBUTE ('SEND_DAILY_COST_JOB', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
3 END;
4 /
PL/SQLプロシージャが正常に完了しました。
SQL>
ジョブの設定内容を確認します。
col job_name for a25
col next_run_date for a35
col logging_level for a15
SELECT job_name, next_run_date, logging_level FROM all_scheduler_jobs
WHERE job_name = 'SEND_DAILY_COST_JOB';
SQL> col job_name for a25
SQL> col next_run_date for a35
SQL> col logging_level for a15
SQL> SELECT job_name, next_run_date, logging_level FROM all_scheduler_jobs
2 WHERE job_name = 'SEND_DAILY_COST_JOB';
JOB_NAME NEXT_RUN_DATE LOGGING_LEVEL
------------------------- ----------------------------------- ---------------
SEND_DAILY_COST_JOB 22-12-14 08:00:00.000000 +09:00 FULL
1行が選択されました。
SQL>
ジョブが正しく設定されていることが確認できました。
5. ジョブの動作確認
ジョブの初回実行時間以降にジョブの実行状況を確認します。
ジョブの実行状況はuser_scheduler_job_run_detailsビューで確認できます。
col job_name for a25
col log_timestamp for a35
col status for a15
SELECT
job_name,
TO_CHAR(FROM_TZ(sys_extract_utc(log_date), 'UTC') at time zone 'Asia/Tokyo','YYYY/MM/DD HH24:Mi:SS') log_timestamp,
status
FROM user_scheduler_job_run_details
WHERE job_name = 'SEND_DAILY_COST_JOB'
ORDER BY log_date;
ジョブの初回実行時間以降に、上記のSQLを実行してジョブの実行状況をします。
SQL> col job_name for a25
SQL> col log_timestamp for a35
SQL> col status for a15
SQL> SELECT
2 job_name,
3 TO_CHAR(FROM_TZ(sys_extract_utc(log_date), 'UTC') at time zone 'Asia/Tokyo','YYYY/MM/DD HH24:Mi:SS') log_timestamp,
4 status
5 FROM user_scheduler_job_run_details
6 WHERE job_name = 'SEND_DAILY_COST_JOB'
7 ORDER BY log_date;
JOB_NAME LOG_TIMESTAMP STATUS
------------------------- ----------------------------------- ---------------
SEND_DAILY_COST_JOB 2022/12/14 08:06:28 SUCCEEDED
SQL>
ジョブに実行が成功したこと、ログのタイムスタンプが8時6分なので処理に6分ほどかかったことがわかりました。
メールボックスを確認してみます。
ジョブの自動実行により、前日分のコストレポートのメールが配信されていました。
これでコンソールにアクセスしなくても前日の利用料がわかるので、あとから慌てることはなくなりそうです。めでたし、めでたし。
参考資料
・Track Oracle Cloud Infrastructure Resources, Cost and Usage Reports with Autonomous Database Views
・トピックの作成
・サブスクリプションの作成
・OCI Notification API：PublishMessage