ServiceNowの複数のテーブルを結合する

ServiceNowのデフォルト機能でテーブル毎にデータのエクスポートやレポート機能を使ったレポートの作成が可能だ。

しかし実際のレポートやデータのエクスポートにあたってはそれだけでは不足するシーンも多い。

通常のRDB等であればSQLでjoinやら where（※）やらやればよいところかもしれないが、ServiceNowではデータベースビューという機能が用意されている。

※筆者がSQLにそれほど詳しくないのでご了承ください。

Data Base Viewを使うには例によってフィルタナビゲータから、System Definition -> Database Viewsを開く。

するとデフォルトでいくつかすでに定義されているのでちょっと見てみる。

「application_on_servers」というデータベースビューの定義を開いてみると次のようになっている。

どうやらcmdb_ci_applとcmdb_rel_ciというテーブルを結合するViewの様だ。

細かく設定を見てもいいが、とりあえずどんな動きをするか理解したいので「application_on_servers.list」とフィルタナビゲータに入れてみよう。

データベースビューも通常のテーブル同様のやり方でリスト表示できる。

左から二番目のNameにある「apache linux den 200」というのはcmdb_ci_applに存在するアプリケーションで、一番右のChildにある「linux100」はcmdb_rel_ciのchildフィールドに存在するハードウェアCIを参照している。

※childフィールドは参照フィールドであるため、linux100のリンクをクリックするとcmdb_ci_linux_serverテーブルへ飛ぶ。

なんとなくつかめてきたのでグループとユーザの対応付け一覧をこの機能で作成してみたい。フィルタナビゲータから、System Definition -> Database Viewsを開きNewをクリック。

まずはNameを適当に「list of user group」などとし、上部灰色のバー右クリック→Saveする。

すると自動的に「u_list_of_user_group」と名前が付けられ下部に結合対象のテーブルを定義する欄が出てくる。

ServiceNowではグループとユーザは多対多の関係になるため、

・グループを定義する sys_user_group　①
・ユーザを定義する sys_user　②
・①と②の関係性を定義する sys_user_grmember

という3つのテーブルで全体としての関係性が定義されている。

下部のView TablesのNewから、これらのテーブル用の定義を追加していく。

まずは sys_user_group。下の様にしてSubmit。Variable prefixのところはこのデータベースビューの　Where clause　を定義するときに後で使う文字列で、適当な文字列でよい（試していないが恐らく記号は含まない英字にしておくのが無難だろう）。ここでは仮に grp とする。

Where clause 欄は後で定義するので今は空欄にしておく。

同様にNewから sys_user も追加する。Variable prefix は usr とでもしておく。

3つめ sys_user_grmember も追加。Variable prefix は rel とした。

次のようになっていれば追加はOK。

ここまで来たら Where clause の定義に入る。

まず　sys_user_grmember の order を 200、sys_user_group の order を 300 にそれぞれ設定。リスト画面上で値をダブルクリックすると↓の様に直接編集、緑色のチェックで値が反映できる。

次に sys_user_grmember の Where clauseに usr_sys_id=rel_user のように入力する。
Variable prefix で定義した usr テーブル＝sys_user の sys_id カラムを、rel テーブル＝sys_user_grmember の user カラムと結合する という意味合いになる。
※sys_id はServicenowのテーブルにおける主キーである。

次に　sys_user_group の Where clause に rel_group=grp_sys_id のように入力する。

作業の結果次のようになっていればまずはOKである。

さて細かい設定はまだあるのだが、ひとまず今回はこの状態でどのような結果が得られるか見てみよう。
Related Links の Try It で 次のような画面になれば成功。
※フィルタナビゲータから u_list_of_user_group.list でもOK。

・・・が、ちょっとこのままだとあまりにもよくわからないので左上の歯車マークから Personalize List Columns メニューを出して表示する情報を整理してみる。

試しにSelectedに、Name(usr_name), User ID, Email, Name(grp_name), Group email, Manager(grp_manager)あたりを追加してみる。

すると次のような一覧が得られる。

この状態で試しにNameで昇順ソートしてみると、

ATF_TestItilUser1 というユーザが2行データ表示されていることがわかる。これはこのユーザが、ATF_TestGroup_Network ATF_TestGroup_ServiceDesk の2つのグループにアサインされているということになる。

このようにテーブルの結合を駆使すると、多対多の関係も一覧のリストで閲覧することができるようになり、多様な分析が可能となる。

もちろん元々ある sys_user_grmember テーブルも多対多の関係を見れるのだが、これだけでは（ほかの2テーブルと結合しないと）sys_user や sys_user_group テーブル内部のカラムの情報を含んだ一覧のリストとして取得することは出来ない。

当然ながらテーブルと同じくレポートのデータソースとしても作成したデータベースビューを活用できるため、分析の幅も広がる。

是非マスタ―しておきたい機能だ。

もちろんExcelへのエクスポートも可能なので様々なテーブルを跨いだデータ提供を求められた時にも使える。

ただしデフォルトではadminしか使えないが・・・。
　　追記：データベースビューの機能を特定のユーザにだけ使用可能にする

メニュー上はどうやらインポートもできる様だ。実際複数のテーブルを結合したビューにインポートするテストは行っていないが、そのうち上手くいくか試してみたい。

執筆予定：複数のテーブルを結合したビューから一括でデータをインポート（仮）