Office365 Reports API を Excel や Power Shellから取得してみる：CData REST ODBC Driverを使用

こんにちは。CDataリードエンジニアの杉本です。

今回は最近要望がありました、Microsoft Graph Office365 Report API を CData Driver経由で取得する方法を紹介したいと思います。

Office365 Reports API って何？

Office365 管理センターにログインするとメニューに表示される「レポート」のデータを取得するためのAPIです。

f:id:sugimomoto:20181217142026p:plain

実は私も問い合わせをもらうまで知らなかったのですが、Exchangeなどはもちろんのこと、YammerやSfBのデータも取得できたりしますので、Office365管理者ユーザーの方はログとして保持するもよし、Office365上では分析しづらい角度で処理するもよし、面白いAPIなんじゃないかなと思います。

https://docs.microsoft.com/ja-jp/graph/api/resources/report?view=graph-rest-beta

できること

今回はCData ODBC Driver経由で取得したいと思います。

ODBCですと例えばPower Shellから取得したり

f:id:sugimomoto:20181217142413p:plain

ExcelのPower Queryからアクセスできたりします。

f:id:sugimomoto:20181217142334p:plain

必要なもの

・Office365 アカウント

・Azure AD

・CData REST ODBC Driver

Office365 Reports API アクセス用のAzure AD アプリケーション登録

まずはじめに、Office365 Graph API にアクセスするために、Azure ADのアプリケーション登録を行い、ClientIDやClientSecretなどの必要な情報を取得します。

Office365 管理センターからAzure ADに移動し

f:id:sugimomoto:20181217142511p:plain

アプリ登録を行います。

f:id:sugimomoto:20181217142517p:plain

今回はローカルホストから接続しますが、今後の使い勝手も考えて、WEB アプリを選択しました。

サインオンURLはローカルホストの空いているポート、例えば「http://localhost:33333」を指定しておきます。

f:id:sugimomoto:20181217142525p:plain

作成後に表示されるアプリケーションIDがOAuthのClientIDになります。後ほど使用するので記録しておいてください。

f:id:sugimomoto:20181217142534p:plain

次に「設定」からアプリケーションのアクセス許可設定を変更します。

f:id:sugimomoto:20181217142542p:plain

今回必要なアクセス許可設定は、以下のリファレンスにもある通り「Microsoft Graph」の「Reports.Read.All」です。

developer.microsoft.com

まず、APIの一覧から「Microsoft Graph」を選択し

f:id:sugimomoto:20181217142550p:plain

「Read all usage reports」にチェックを入れます。この権限は委任された権限にもあるので、2つチェックを入れるのを忘れないでください。

f:id:sugimomoto:20181217142559p:plain

f:id:sugimomoto:20181217142607p:plain

これでアプリのアクセス設定は完了です。

最後にClientSecretを取得するために、「設定」のキーから新しく任意のKeyを作成してください。ここで生成されるClientSecretもClientIDと同様に控えておきます。

f:id:sugimomoto:20181217142615p:plain

なお、後々必要となる認証用URL（Authorize URL）とトークンURLはアプリ一覧のエンドポイントから確認できます。

f:id:sugimomoto:20181217142622p:plain

以下のように対象テナントのGUIDが含まれているので、注意しましょう。

authorize url

https://login.microsoftonline.com/b2588042-9bd0-44d8-827b-85365e58508e/oauth2/authorize

token url

https://login.microsoftonline.com/b2588042-9bd0-44d8-827b-85365e58508e/oauth2/token

CData REST ODBC Driverのセットアップ

CData REST ODBC Driver は以下のURLからトライアルが取得できます。ダウンロード・インストールを事前に実施しておいてください。

https://www.cdata.com/jp/drivers/rest/odbc/

インストール後、ODBC DSN Administratorを立ち上げて、ユーザーDSN（システムDSNでも可）一覧からCData REST Sourceを選択し構成を変更します。

f:id:sugimomoto:20181217145135p:plain

今回、APIに接続するに当たって必要なプロパティは以下の通りです。

URIは取得したいReports APIの一覧からURLを採取しておきます。

今回は以下の「getEmailActivityUserDetail」から選びました。JSONとCSVタイプがありますが、JSONタイプを取得するようにしてください。URLを調整すればもう少し細かな取得アプローチも可能ですが、ここでは割愛します。

docs.microsoft.com

Generate Schema Files=「OnStart」を選択

InitiateOAuth=「GETANDREFRESH」を選択

URI=「https://graph.microsoft.com/beta/reports/getEmailActivityUserDetail(period='D7')?$format=application/json」任意のOffice365 Report APIのURLを入力

JSONPath=「/value」と入力

OAuthAuthorizationURL=「例：https://login.microsoftonline.com/b2588042-9bd0-44d8-827b-85365e58508e/oauth2/authorize?resource=https://graph.microsoft.com」事前に取得しておいたAuthorizationURLに対象リソースとして「?resource=https://graph.microsoft.com」を付与したものを入力

OAuthAccessTokenURL=「例：https://login.microsoftonline.com/b2588042-9bd0-44d8-827b-85365e58508e/oauth2/token」事前に取得しておいたTokenURLを入力

OAuthRefreshTokenURL=「例：https://login.microsoftonline.com/b2588042-9bd0-44d8-827b-85365e58508e/oauth2/token」事前に取得しておいたTokenURLを入力

OAuthParams=「scope="Reports.Read.All"」と入力

CallbackURL=「http://localhost:33333」アプリ登録時に設定したサインオンURLを入力

OAuthVersion=「2.0」を選択

OAuthClientId=「例：6f614783-233b-41ee-beea-7c85f9d48a45」事前に生成したClientIDを指定

OAuthClientSecret=「例：Fxj0VFJ3lZuurV3UkjQCRsX8JYkDCTuYlg/mpKiUxn0=」事前に生成したClientSecret（Key）を指定

Location=任意のフォルダを指定。設定ファイルが生成されます。

以下、設定値例です。

f:id:sugimomoto:20181217142634p:plain

f:id:sugimomoto:20181217142643p:plain

f:id:sugimomoto:20181217142650p:plain

入力後、接続テストをクリックするとOAuth認証が実施されます。対象のユーザーでログインし、以下のようにダイアログが表示されれば接続完了です。

f:id:sugimomoto:20181217142703p:plain

なお、接続が完了すると、Locationに指定したフォルダへスキーマファイルが生成されます。（環境によっては一度データを取得してから）

f:id:sugimomoto:20181217142727p:plain

このファイルを開くと、データを取得する際のカラム情報やテーブル名が指定されていることが見て取れるかと思います。なお、テーブル名はvalueとなりますが、これはこのRSBファイルを変更することで調整可能です。

f:id:sugimomoto:20181217142806p:plain

Power Shell から接続

Power Shellから接続する際は、以下のようなサンプルコードのようにOdbcConnectionクラスを利用して接続するといいと思います。SELECT文は対象のAPIによって変わるので注意してください。

DSN名もODBC DSNで構成した情報を元に指定してください。

Set-StrictMode -Version Latest

$connectionsString = "DSN=CData REST Office365 Reports"
$odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionsString)
$odbcCon.Open();

$odbcCmd = New-Object System.Data.Odbc.OdbcCommand
$odbcCmd.Connection = $odbcCon

# コマンド実行（SELECT）
$odbcCmd.CommandText = "SELECT assignedProducts,deletedDate,displayName,isDeleted,lastActivityDate,readCount,receiveCount,reportPeriod,reportRefreshDate,sendCount,userPrincipalName FROM value"
$odbcReader = $odbcCmd.ExecuteReader() 

while ($odbcReader.Read()) {
    "assignedProducts" + ":" + $odbcReader["assignedProducts"].ToString() 
    "deletedDate" + ":" + $odbcReader["deletedDate"].ToString()
    "displayName" + ":" + $odbcReader["displayName"].ToString()
    "isDeleted" + ":" + $odbcReader["isDeleted"].ToString()
    "lastActivityDate" + ":" + $odbcReader["lastActivityDate"].ToString()
    "readCount" + ":" + $odbcReader["readCount"].ToString()
    "receiveCount" + ":" + $odbcReader["receiveCount"].ToString()
    "reportPeriod" + ":" + $odbcReader["reportPeriod"].ToString()
    "reportRefreshDate" + ":" + $odbcReader["reportRefreshDate"].ToString()
    "sendCount" + ":" + $odbcReader["sendCount"].ToString()
    "userPrincipalName" + ":" + $odbcReader["userPrincipalName"].ToString()
    ""
}

# コマンドオブジェクト破棄
$odbcCmd.Dispose()

# DB切断
$odbcCon.Close()
$odbcCon.Dispose()