CO-Client Opeartion 1.1 利用履歴データベースの設計 (スキーマ バージョン 1.1.0.0 対応) 第3版 2014/04/03 株式会社 CO-CONV

概要 端末の利用履歴を記録するデータベースの記録方式について説明します 2019年5月18日

データベース設計の特長 SQL Server Express をもちいた環境で1日10万件の情報記録をできる性能設計とする 端末の突然死に対応する 終了時刻を推測してデータベースに登録する機能を持つ セッションの接続状態を記録する 特に、セッション接続元の情報を記録する点を特徴とする テーブルを日付別にすることで、過去の記録を消去することを容易にする 2019年5月18日

info テーブル システムの情報を保持する。 特に、schema_version の値はテーブルの構造が変化したときに変わるため重要です。この文章はschema_versionの ‘1.1.0.0’ についての仕様を記載しています。 利用履歴データベースを参照するアプリケーションは、参照前に info テーブルの schema_version が期待した値であることを確認することを推奨します。 2019年5月18日

info テーブルのカラム カラム名 意味 name 設定の名前。 value 設定の値。 2019年5月18日

info テーブルのSQL の例 schema_version の値を取得する SELECT [value] FROM [dbo].[info] WHERE [name] = ’schema_version’; 2019年5月18日

state テーブル SQLで問い合わせた時点での「端末の起動状態・ログオン状態・セッションの状態やアプリケーション実行状態」を保持する 開始ログ記録時にレコードが作成され、終了ログ記録時に消去される Logテーブルへの終了ログ記録時にも、logテーブル上の終了ログを書き込むべきレコードを取得するために、このテーブルを検索する 2019年5月18日

state テーブルのカラム 2019年5月18日 カラム名 Typeによるフィールドの有無 意味 Power Logon Connection Application Id ○ テーブル内でのID。Ｓｅｒｉａｌ 。 time レコードの作成時間。将来的には「あまりにも古い記録は削除する」といった用途に利用する可能性がある。 type 0(Power): 端末の電源状態・コンソールの状態 4(Logon): ユーザーのログオン状態 2(Connection): リモートコネクションの接続状態 3(Application): アプリケーションの動作状態 Logon が 4 である点にご注意ください。 hostname ホスト名 username Ｘ ユーザ名 user_domain ユーザのドメイン （記録するかどうかは端末側設定 send_user_domain による) ip 端末のIPアドレス session_id 端末上でのセッションID。 （同一ユーザが複数のセッションにログオンしていた場合に、ログオフ処理を個別に行えるようにするために必要。） app_filename アプリケーションのファイル名 process_id アプリケーションのプロセスID log_table_name 開始時刻を記録した logのテーブル名 log_id log テーブルにおける開始ログの ID 2019年5月18日

state テーブルのSQL の例 現在ログオン中の端末名・ユーザー名を列挙する 現在起動中の端末名を列挙する SELECT [hostname] ,[username] FROM [dbo].[state] WHERE [type] = 4; 現在起動中の端末名を列挙する SELECT [hostname] FROM [dbo].[state] WHERE [type] = 0; 2019年5月18日

log テーブル 利用履歴が蓄積される 日付別のテーブルに記録される (log_YYYYMMDD) 開始ログ記録時に「終了時刻不明」で追加される。 終了ログ記録時に終了時刻が更新される 2019年5月18日

log_YYYYMMDDテーブルのカラム カラム名 Type によるフィールドの有無。 意味 Power Logon Connection Application Id ○ テーブル内でのＩＤ。Ｓｅｒｉａｌ 。 start_time 開始時間。対応する開始ログが送付されなかったときには NULL になることがある。 end_time 終了時間。終了ログなどにより更新された時間でもある。終了ログを送付されたとき、サーバー側で終了を検知したときの時刻が記録される。 end_status end_timeの状態として、次のいずれかを記録する ・ 0(Open): End_timeの記録はまだなし （start状態） ・ 1(Closed): 端末からのstop通知による記録 ・ 2(Dead): ポーリングにより端末が死んでいたことによる記録 ・ 3(Zombie): いつの間にか死んでいて、端末からの起動通知が来たことによる記録 hostname 端末のホスト名 username Ｘ ユーザー名 user_domain ユーザーのドメイン名。（記録するかどうかは端末側設定 send_user_domain による) ip 端末のIPアドレス type 0(Power): 端末の電源状態・コンソールの状態 4(Logon): ユーザーのログオン状態 2(Connection): リモートコネクションの接続状態 3(Application): アプリケーションの動作状態 Logon が 4 である点にご注意ください。 session_id 端末上でのセッションID。 remote_ip コネクションの接続元のIPアドレス （デフォルトでは Connection はリモートからの接続のみを記録する。端末側設定の [log_agent] にsend_local_connection=1 を追加すると、コンソール上での接続を記録する。このとき、コンソールからの接続の場合には “” となる） app_path アプリケーションのパス （パスを記録するかどうかは端末側設定send_process_full_pathによる。検索時の利便性を考え、パスとファイル名は分けて記録） app_filename アプリケーションのファイル名 process_id プロセスID 2019年5月18日

log テーブルへSQL の例 2014年4月2日におけるログオンの記録を抽出する 2014年4月2日における端末起動の記録を抽出する SELECT [start_time] ,[end_time] ,[hostname] ,[username] FROM [dbo].[log_20140402] WHERE [type] = 4; 2014年4月2日における端末起動の記録を抽出する SELECT [start_time] ,[end_time] ,[hostname] FROM [dbo].[log_20140402] WHERE [type] = 2; 2019年5月18日

内部設計 以下のスライドは、CO-COがDBを操作する際の内部動作に関する資料です。 2019年5月18日

開始ログ記録時の内部動作 state・log更新時には、端末ごとの排他処理を行う サーバーの時刻と極端に異なるログは無視する 電源の切断通知なしに起動した場合の対策 stateテーブルに該当端末のレコードがないことを確認 ある場合には適切に終了扱いにする （logの end_timeは現在時刻、end_statusは Zombie） logテーブルにレコードを作る start_timeは端末が通知する時刻(端末の時計で計測) end_timeは空欄、ログの end_statusはOpen logテーブルの「テーブル名・ID」を取得 stateテーブルに記録 2019年5月18日

終了ログ記録時の内部動作 先にstateを検索, そのあとlog を更新後、stateを消去する。 ホスト名はCase insensitiveで比較する(IPには頼らない) ログのtypeにより必要な追加処理をする Application ・Logon ・Connection 特になし Power その端末のApplication, Logon, Connection があるときには Closed 扱いにする log の end_time と end_status をUpdate stateを消す 2019年5月18日

定期的な端末ポーリングの内部動作 ポーリング対象は、state テーブルの hostname カラムに存在するホスト一覧 端末が死んでいることを確認したら、その端末に対する次の処理を行う 端末をシャットダウン状態にする stateテーブルに該当端末の記録があれば、終了扱いにする。その際logのend_statusはDeadにする。 時刻はサーバ側での調査時間。調査してからDBを変更するまでの時間は極力短くする。 DB更新中は排他処理をする。 2019年5月18日

利用履歴記録処理の詳細な仕様 ログ記録に伴うDB操作と 端末のポーリング後のDB操作とが、特定のホストについて競合することがないように、排他処理を行っている。 上記条件のもと、特定のホストの情報に対するDB の更新処理は、同時1セッションしかアクセスがないような設計とする。 stateテーブルの検索時に hostnameは信じるが、IPは信じない。 端末は電源起動時に、電源オンの通知を送る前には、それ以外の通知を送らない。 ホスト名・ユーザ名はCase Insensitiveに比較できるようカラムを設定している。 端末側ではサービス起動時に、本当に電源オンによるサービス起動かどうかの判定を厳密に行い、本当に電源オンのときにのみ、電源オン通知を送る。 コンソールからのログオンかどうかは remote_ip が””かどうかで判断する。 2019年5月18日