こんにちは。CDataリードエンジニアの杉本です。
今回は最近要望がありました、Microsoft Graph Office365 Report API を CData Driver経由で取得する方法を紹介したいと思います。
Office365 Reports API って何?
Office365 管理センターにログインするとメニューに表示される「レポート」のデータを取得するためのAPIです。
実は私も問い合わせをもらうまで知らなかったのですが、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から取得したり
ExcelのPower Queryからアクセスできたりします。
必要なもの
・Office365 アカウント
・Azure AD
・CData REST ODBC Driver
Office365 Reports API アクセス用のAzure AD アプリケーション登録
まずはじめに、Office365 Graph API にアクセスするために、Azure ADのアプリケーション登録を行い、ClientIDやClientSecretなどの必要な情報を取得します。
Office365 管理センターからAzure ADに移動し
アプリ登録を行います。
今回はローカルホストから接続しますが、今後の使い勝手も考えて、WEB アプリを選択しました。
サインオンURLはローカルホストの空いているポート、例えば「http://localhost:33333」を指定しておきます。
作成後に表示されるアプリケーションIDがOAuthのClientIDになります。後ほど使用するので記録しておいてください。
次に「設定」からアプリケーションのアクセス許可設定を変更します。
今回必要なアクセス許可設定は、以下のリファレンスにもある通り「Microsoft Graph」の「Reports.Read.All」です。
まず、APIの一覧から「Microsoft Graph」を選択し
「Read all usage reports」にチェックを入れます。この権限は委任された権限にもあるので、2つチェックを入れるのを忘れないでください。
これでアプリのアクセス設定は完了です。
最後にClientSecretを取得するために、「設定」のキーから新しく任意のKeyを作成してください。ここで生成されるClientSecretもClientIDと同様に控えておきます。
なお、後々必要となる認証用URL(Authorize URL)とトークンURLはアプリ一覧のエンドポイントから確認できます。
以下のように対象テナントの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を選択し構成を変更します。
今回、APIに接続するに当たって必要なプロパティは以下の通りです。
URIは取得したいReports APIの一覧からURLを採取しておきます。
今回は以下の「getEmailActivityUserDetail」から選びました。JSONとCSVタイプがありますが、JSONタイプを取得するようにしてください。URLを調整すればもう少し細かな取得アプローチも可能ですが、ここでは割愛します。
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=任意のフォルダを指定。設定ファイルが生成されます。
以下、設定値例です。
入力後、接続テストをクリックするとOAuth認証が実施されます。対象のユーザーでログインし、以下のようにダイアログが表示されれば接続完了です。
なお、接続が完了すると、Locationに指定したフォルダへスキーマファイルが生成されます。(環境によっては一度データを取得してから)
このファイルを開くと、データを取得する際のカラム情報やテーブル名が指定されていることが見て取れるかと思います。なお、テーブル名はvalueとなりますが、これはこのRSBファイルを変更することで調整可能です。
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()
Excelから接続
Excelからも、以下のようにPower Query経由で取得できます。
「データ」→「その他のデータソース」から「Micrisoft Query」を選択し
DSN一覧から事前に作成したODBC DSNを選択
列の選択では、任意の列が選べますが、「@odata.type」だけ、選択できませんので注意してください。
あとはデフォルトのまま、次へ進めていきます。
以下のようにデータを取得することができました。
おわりに
いかがでしたでしょうか。
ODBC経由であれば、Tableauから接続したり、Accessから接続したりすることもできます。
また、GraphAPIにも現在様々な種類のAPIが提供されていますので、ほかにもこのAPIに接続したい、というご要望があれば、どしどしご相談ください。