2020-12-30

IPSのテストベッドをつくる際のTIPS ― デバイスの保護とスマート電源

お知らせ

　屋内測位システム『TPC_IPS Ver1.0』を2022/4/1にリリースしました。

　同製品に関する質問またはデモを希望されるお客様はこちらよりお申し込みください。

　尚、外部のIPS/RTLSと連携して動作するIPSアプリケーション・テンプレート『QuickIPS』及び『TPC_IPS Web API』も順次リリースを予定しています。

　当社では Raspberry Pi とビーコンを使用した IPS(Indoor Positioning System)を開発しています。ソフトウェア開発自体や機器の設定管理等も大変ですが、テストベット（ソフトウェアのテスト・実証環境、長期間、継続的に使用するのが前提）の構築・維持もそれなりに大変です。今回は当社のような小規模組織がIPSテストベットを作る際のTIPSをご紹介します。

テストベッド

　下図のようなスペースを常時確保できるような大きな企業や研究機関であれば事は簡単ですが、小社のような小規模な会社では日常の労働生活空間とテストベッドを共用することになります。　多数のビーコンのスキャンテストを行う度にRaspberry端末やビーコンを敷設するのは労力も時間も要します。　また、ビーコンや端末を床や机等に長時間放置するのは他の業務の妨げにり、無断で移動されたりすれば、テスト結果が不正確なものとなってしまいます。また、機器の破損も心配です。

図1：当社テストベッド

　今回はテストベッドで使用するビーコンや Raspberry Pi 設置方法や、電源に関するTIPSをご紹介します。

ビーコンの保護

　ビーコンは屋内と屋外に常設していますが、屋内、屋外を問わず、携帯電話用防水のケースにクッションを入れた上で、その中に収納しています。クッションにより、誤って落下した際に床からの衝撃を吸収させ、ビーコンを守ります。ストラップはスタンド、壁、天井にフックする際に便利です。防水性能ですが、IP規格を取得しているケースを調達します。

IP規格を取得している防水ケース

ビーコンを屋外に常設する

　ビーコンを屋外に常設する際は上記のように防水ケースに入れて、スタンドや地面に設置します。台風、梅雨、秋の長雨の際も放置して良いと思いますが、定期的にチェックするようにしましょう。

地面に設置する際はカラコンを被せるとさらに安心

ビーコンを屋内に常設する

　ビーコンを屋内に常設する場合は他の業務の邪魔にならないように、蹴飛ばされないように、天井から吊り下げます。

誤って落下した場合、ケースとクッションで衝撃を吸収

Raspberry Pi を屋外に常設する

　Raspberry Pi を屋外に常設する場合、IP規格対応の防水ケース入れて電源コードを引きます。

未来工業製WB-10DM、屋外用コンセントの引き込みも可

Raspberry Pi の電源

　図1のような十数台の Raspberry Pi の電源のオン/オフにも意外に手間がかかります。開発・デバッグ中は頻繁に電源のオン/オフが発生することもあり、これが数十台、数百台の規模になると、開発者には地獄です。 Raspberry Pi はWOL (Wakeup On Lan)に対応していないため、マジックパケット送信による遠隔起動はできません。

　そこでスマート電源を使い、iPhone/Android から電源自体をオンオフできるようにしてあります。

Meross社製スマート電源、Amazonで1500円程度

スマート電源は奥行があるので蹴飛ばされないように配置、ルンバが来ても大丈夫

屋外でスマート電源を使用する

　スマート電源は大きく、屋外の電源コンセントに直接差し込むのは難しいです。その場合、以下のような防水ケースに入れて収納します。複数のコンセントはその先の延長コードに繋がり、その延長コードが Raspberry Pi に接続されます。

外に配置

NuckyT

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

2020-12-26

機械学習によりIPSの位置測位精度を改善する

[English page]

お知らせ

　弊社では Raspberry Pi とビーコンを利用した IPS（Indoor Positioning System / 屋内位置測位システム ― 屋内のモノの位置を推定するシステム）を開発しており、昨年、独自のアルゴリズム・TCOT（二円指向三点測位）に基づくシステムのプロトタイプを本ブログと YouTube にて紹介しました。

　TCOTを含む三点測位系の位置推定は、フィンガプリント方式に比べ事前のデータ蓄積・データ更新が不要な点が大きなメリットですが、反面、測位精度が低いのが弱点です。

　今回、Raspberry Pi（以下、端末と呼ぶことがあります）とビーコンをテスト環境に敷設し、予めビーコンと端末の位置座標と、端末が取得したビーコンのRSSIをデータベースに記録（これを「フィンガプリント」と言います）し、そのフィンガプリントデータを機械学習にかけてることにより、測位精度がどの程度向上するのかを検証しました。

　機械学習ツールは多くありますが、今回はノープログラミングで利用できる Orange と、Pythonの機械学習ライブラリとして有名な scikit-learn を使用し、それぞれで得た推定位置の結果を matplotlib によりプロットしています。

テスト環境
データと特徴量について
機械学習による位置推定
今後の課題
Varista について
IPS関連のBlog記事

テスト環境

　テスト環境は当社事務所にAplix社製ビーコン54台とRaspbery Pi 端末13台を下図のように配置し、構築しています。端末13台は4mを辺とする正方形の４頂点上に配置し（これをグリッドと呼びます）、さらに各正方形の中心にも端末を配置しています。位置測位の対象となるビーコンは図の[R]と[B]の両方に配置しています。

目次へ戻る

データと特徴量について

　フィンガプリントを記録するデータベーステーブルには多くのフィールドがありますが、機械学習で使用する特徴量には端末の名称（下図のN1T、N2T、N3T・・・）、各端末が取得したRSSIに基づき算出したが端末からビーコンまでの距離（N1R、N2R、N3R・・・）を取り、目的変数（ラベル）はビーコンの位置座標（ax, ay）となります。　

データベーステーブルに記録されたフィンガプリント（一部）をExcelで表示

目次へ戻る

機械学習による位置推定

TCOTによる位置推定

　機械学習に入る前に、当社の二円指向三点測位（TCOT）により算出した推定位置を matplotlib によりプロットした結果を以下に提示します。

図1：TCOTによる位置推定のプロット

　各矢印の始点が実際のビーコンの位置、終点がTCOTにより算出された推定座標となります。　

　以下では今回使用する機械学習のツールについて説明すると共に、TCOTで使った同じデータを機械学習に適用し、どの程度、推定位置が改善するかを見ていきます。

目次へ戻る

Orange Data Mining による位置推定

　今回使用した機械学習ツールの1つが Orange Data Mining で、スロベニアのリュブリャナ大学が開発提供するオープンソースのデータマイニングツールです。どなたでも無料ダウンロードして使用することができます。
　本ツールでは、データ読み込み、モデル定義、予測、出力までの流れを GUI で組み合わせていくことにより、ノープログラミングで機械学習を実行できます。

　操作画面のサンプルは以下のようになります。

　今回は数多くある機械学習の手法の中のk近傍モデル（kNN）を用いてビーコン位置の推定を行いました。kNNでは、あらかじめ教師となるデータを収集し、データは全て数値に変換されます。次に運用データ（機械学習の教科書では、"テストデータ"と呼ばれます）をkNNに渡すと、各テストデータが最も近接する教師データに分類され、それが持つ目的変数（ここでは教師データのxとy座標）を返してきます。

　下図が Orange kNNの返してきたビーコンの推定位置をプロットしたものです。予め教師データを作成するというコストはかかりますが、図１のTCOTに比べると推定位置の誤差は改善されいます。

Orange kNN で指定した主なパラメータ：

k：1
メトリック：Manhattan
Weight:Distance

注：
kNNは教師データに基づき分類を行うアルゴリズムであり、本例では教師が持つ目的変数（x、ｙ座標）に分類されます。従って、上図でビーコンが座標(0.5,0.5)に位置する場合でも、アルゴリズム的には座標（0, 0）が正解値となります。

scikit-learn による位置推定

　次に、Google が提供するオープンソースの Python 用機械学習ライブラリ scikit -learn を使用します。

　ここでは Orange Data Mining と同様に k近傍法（最近傍法）を採用します。ビーコン座標の予測モデル関数として KNeighborsClassifier、KNeighborsRegressor の二つを使います。

目次へ戻る

KNeighborsClassifier による位置推定

　ここでは K近傍法のクラス分類となる KNeighborsClassifier 関数を使い、教師あり学習によりビーコンの位置を推定します。ラベル（分類クラス、目的変数）は X座標、Y座標の多クラスを与えます。

パラメータ指定：

nbrs = KNeighborsClassifier(n_neighbors=1, metric='manhattan', algorithm ='brute', weights='distance')
nbrs.fit(teacherData, teacherXYLabel)
prediction = nbrs.predict( testData )

パラメータ指定：

k：1
アルゴリズム：brute
メトリック：manhattan
Weights:distance

　プロット結果は Orange とほぼ同等となりました。ただ、Orange と scikit-learn で同じ kNNを使いながら、個々のビーコンの推定座標に違いがありました。kNNは比較的単純なアルゴリズムなので Orange と scikit-learnで同じ結果を返すものと思っていたのですが、少し意外でした。この理由は、Orange では指定できない、brute（総当り）にあるのかもしれません。

目次へ戻る

KNeighborsRegressor による位置推定

　ここでは K近傍法の回帰分析となる KNeighborsRegressor 関数を使うことによって、教師あり学習により位置を推定します。ラベル（分類クラス、目的変数）として X座標、Y座標の多クラスを与えます。

パラメータ指定：

nbrs = KNeighborsRegressor(n_neighbors=1, metric='manhattan', algorithm ='brute', weights='distance' )
nbrs.fit( teacherData, teacherXYLabel )
prediction = nbrs.predict( testData )

主なパラメータ指定：

k：1
アルゴリズム：brute
メトリック：manhattan
Weights:distance

　KNeighborsClassifier 関数と同一のプロット結果となりました。

参考リンク：
k近傍法
 2.4 アルゴリズム1 k-最近傍法

今後の課題

　今回、いくつかの方法でビーコンの位置推定を試みました。scikit-learn のkNN関数で指定したパラメータは、GridSearchCV により得られた最適解を使用していますが、特徴量スケーリングの改良などにより位置推定の精度をもう少し上げられるかも知れません。ニューラルネットワーク（MLPClassifier）も試してはいるのですが、まだ良い結果を得られていません。今後はMLPClassifierを含むその他のエスティメータも試用していく予定です。

　また、エスティメータ以前に、取得したデータを見るとRSSIの振れ幅が大きい端末があり、この振れ幅が位置推定に悪影響を及ぼしていると思われます。この振れ幅を抑えるハードウェア的なアプローチの検討の方がより重要かもしれません。

目次へ戻る

Varista について

　Webで上でプログラミング無しで利用できる機械学習ツールに Varista があります。以前に無償版を試用してみたのですが、上記の同じデータをアップロードできず、しばらく放置していました。年末、ダメ元で Varista に問い合わせたところ、思いがけず迅速・適格な回答を貰い、アップロードができるようになり、位置推定の結果（CSVファイル）を取得できました。そのファイルをプロットしたのが下図です。

　Varista は決定木ベースのアルゴリズムを使用しており、設定可能なパラメータが多数あります。今回はほぼデフォルトのまま使用しましたが、上述の kNN と遜色ない結果が得られました。　このことから IPS では決定木関連のアリゴリズムも有望と思われます。

Varistaの「エキスパート」画面 ― 多数のパラメータを設定できる

　Varista には API は無いようですが、WebからテストデータをPostすると、即結果を返してくれるようなAPIがあればウレシイ、というユーザもいるのではないでしょうか。

2021/01/05追記(亀)

（亀）

目次へ戻る

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

目次へ戻る

2020-11-11

東京ドームのような広い場所で少ない受信機によりビーコンのおおよその位置を推定する

　IPS（Indoor Positioning System、屋内測位システム ― 屋内で人・モノの位置を把握するシステム）には、三点測位、フィンガプリント、AoA/AoD、GMM、機械学習等々、様々な方式があり、測位の精度を競い合っています。ネットを検索すると関連の記事や論文が多数見つかります。

　ただ、すべてのシステムの要求仕様が、誤差1m未満の精度を求めているわけではなく、位置は大雑把にわかれば良い、その代わり（ビーコン信号の）受信機を減らしたい、というモノもあります。
　例えば、老人ケアセンターであれば、入居者の存在確認（施設から黙って抜け出していないか）が最重要で、位置については大体の位置を把握できれば十分ということもあります。
　また、大規模な工場、資材置場、倉庫などで、フォークリフトや運搬機などの大きな機材にビーコンを取り付けてその所在地を把握するようなシステムでは、誤差1mの精度は要求されないことも多いでしょう。

　今回は、東京ドームかそれ以上に広いエリアにあるビーコンのおおよその位置(エリア)を、できるだけ少ない受信機（Raspberry Pi）で推定する方法について考えて見みます。
　尚、このエリアは障害物がほとんど無い、大規模な工場や倉庫のような場所を想定しています。

受信機の配置を考える

　下図でピンクが受信機（Raspberry Pi）でブルー(B)がビーコン（iBeacon）です。ビーコンは枠内にあり、ビーコンの信号を遮る障害物はほぼ無いもとします。

【グリッド】

　この時、ビーコンの「おおよそ」の位置を把握するためには、受信機をどの位の数、配置すればよいでしょうか？　結論から言うと、それはユーザの求める推定位置の精度、「おおよそ」に依存します。　基本的にエリア内の受信機が多ければ多いほど、推定位置の精度は上昇します。　今回は受信機の数を少なく抑えて、「おおよそ」の位置を把握する（精度は重視しない）というテーマで考えていきます。

グリッドについて

　上図をグリッド（但し、ビーコンは除く）と呼びます。　受信機（ピンク）は正方形の4つの角と中心に置きます。　この正方形の一辺の長さをグリッドサイズと呼びます。広大なエリアをカバーする場合、複数のグリッドを置くことになるので、グリッドサイズが大きければ大きいほど、使用する受信機の数は減少します。

使用ビーコン

　使用するビーコンの信号の最大到達距離は100m以上とします。ちなみに、Aplix社のビーコンの信号最大到達距離は100m、丸紅情報システムズ社のビーコンは150mとWebサイトに書かれています。

グリッドサイズ50ｍ

　ビーコンが受信機から離れれば離れるほど、受信機はそのビーコンの信号を検知できない（非検知）可能性が高くなります。　すべての受信機がビーコンを検知しない可能性が最も高いのは図の4つのビーコンの位置です（検知困難地点）。ここでビーコンBについて考えます。ビーコンBは受信機A、B、Cから25mずつ離れていますが、障害物が無い場所ではA、B、Cのいずれもがビーコン信号を受信する可能性は高いです。各受信機がビーコンから信号を受信すると、信号の強度（RSSI）からビーコンまでの距離を算出します。　受信機を円の中心とし、算出した距離を半径とする円周上近辺にビーコンが存在すると推定されます。ここでは3つの受信機が信号を受信しているので、3つの円が交わった黄色のエリアにビーコンが存在すると推定されます。

グリッドサイズ150m

　次にグリードサイズ150mのビーコンB（検知困難地点）について考えます。このケースでは受信機からビーコンBまでの距離は75mです。75m離れていると非検知の可能性が高まります。ここでは受信機AがビーコンBを検知できず、受信機BとCが信号を検知したと仮定します。このとき受信機BとCを中心とする円の交差する黄色エリアにビーコンBがあると推定されます。

グリッドサイズ200M

　最後にグリッドサイズ200mのケースです。この場合、受信機A、B、CからビーコンBまでの距離は100ｍとなります。100mとなると受信機がビーコンの存在を検知しない可能性がさらに高まります。下図では、ビーコンBを検知した受信機がCのみだったと想定しています。この場合、ビーコンはCを中心とする円の円周付近上にあると推定されます。
但し、100mになると元々低いRSSIの精度がさらに低くなるため、円周の外側と内側に大きく逸脱して存在する可能性が高くなります。下図で黄色エリアがドーナツ型なのはそれを表しています。

　　尚、受信機とビーコン間の距離が100mある場合、どの受信機にも検知されない可能性も十分ある点にもご留意ください。

受信機からビーコンまでの距離について

　RSSIの精度はもともと低いので、それを元に算出される距離の精度も低いです。特に30m以上になると、RSSIが「0」(RSSI測定不能)になったり、値が想定される値とはまったく違ったり、あるいは信号自体が検知されないことが普通にあります。　
　下図はAplix社のビーコン2台と丸紅情報システムズのビーコン3台のRSSIの計測結果です。この時は、Aplix社のビーコンの1台は25m以上になると検知されないことが多く、丸紅はより頻繁に検知されましたが、RSSIは想定される値と大きく異なっています。

　RSSIまたは距離の精度を上げるには、なんらかの補正が必要となります。

※RSSIが継続的に0になる、或いはRSSI補正が不能なほどRSSIの精度が低い状況では、グリッドサイズの短縮を検討しましょう。

BLE 5 対応ビーコンの到達距離

　上述のAplix社と丸紅社のビーコンは BLE4 の規格に準拠していますが、現在は BLE5 の対応のチップも Nordic Semiconductor 等からリリースされています。 BLE5対応ビーコンの信号到達距離は、"理論的は"BLE4対応ビーコンの4倍となります。2020年11月現在、BLE5に準拠した長距離ビーコン（Long range beacon）で技適を取得している製品は市場にありませんが、今後対応製品が発売されると、障害物が少ないところであれば400m超のビーコン探知が可能になるとものと期待されます。

人や障害物が少なからず存在するエリアについて

　繰り返しとなりますが、上記は障害物がないエリアを想定しています。人が多くいたり、壁や棚などの障害物がある場合、適切なグリッドサイズは4m～15m程度になります。また、人が密集する展示場やイベント会場、金属製の背の高い棚が多く置かれている倉庫などでは、IPSによる測位がそもそも困難・不能なケースもあります。

NuckyT

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

2020-09-04

テレワークのセキュリティ対策

　コロナウイルスの蔓延により、人々の働き方に変化が表れました。労働者のウイルス感染リスクを低減させるために、テレワーク（在宅勤務、モバイルワーク、サテライトオフィス）を導入する企業も増えてきました。

「出社3割以下」もテレワーク再強化の動きコロナ感染拡大で
 「リモートワークできず倒産」というニュースが流れる日

　テレワークを導入するにあたり、企業側として最も気がかりになることはセキュリティですね。つまりウイルス・マルウェア対策と重要データの取り扱いとなります。

安易なテレワーク導入は危険がいっぱい

　スタッフの身の安全を確保しつつ業務を遂行するために、社内で以下のようなテレワーク構成を行ったとします。

社内ルータをVPN対応のものに変更し、ファイアウォールで公開ポート制限
各スタッフのPCにはアンチウイルスソフトを導入済み
各スタッフは社内ネットワークにVPN接続し、リモートデスクトップで社内のコンピュータやサーバにアクセス
スタッフAは会社支給のPCを使い、リモートデスクトップ経由で社内業務の管理を担当
スタッフBはBYOD(自前)の自作PCを使って開発を行い、成果物をリモートデスクトップ経由で社内サーバ環境にコピーしたり、データベース管理を行ったりする
スタッフCはBYOD(自前)の mac PC を使っているが、社内で使っている経理ソフトが Windows 版のため、リモートデスクトップ経由で社内の Windows コンピュータにアクセスして経理ソフトウェアを操作

　一見、ひととおりテレワークで業務がこなせるようになってはいますが、これではまだセキュリティ的に問題のある構成となっています。この問題点は何だかわかりますか？

テレワーク環境に潜む問題

　在宅スタッフに起因する基本的なセキュリティ対策が不足しているのです。
　それでは問題を見ていきましょう。

各PCのセキュリティ状態が把握できない

　各 PC にはアンチウイルスソフトウェアがインストールされてはいますが、各人バラバラのメーカーのものを導入しています。これではウイルス感染した PC に関しての情報を会社側で把握することが難しく、問題の発生した PC から漏れたウイルスが社内ネットワークに広まってしまう可能性があります。

社内ネットワーク側にウイルス検知システムが導入されていない

　社内の VPN 機能付きルータ自身には基本的なファイアウォール機能が搭載されており、公開ポートを限定できるようになっていますが、リモートデスクトップ経由やその他の手段で到達したウイルスやマルウェアは、そのまま社内ネットワークに入り込んでしまうことになります。

　在宅スタッフが VPN 経由でウイルス付きのファイルを社内サーバーに配置してしまったり、ウイルスが添付されたメールを社内スタッフ向けに転送してしまったというトラブルが発生しています。

BYOD(自前)PC の用途が厳格化されていないため、セキュリティリスクが高まる

　上図の例を取ると、スタッフB とスタッフC は自前 PC を使用してテレワークを行っていますが、自前ということで業務以外の目的に PC を使うことが前提となります。つまり、業務目的以外に PC を使うというだけで、セキュリティリスクに晒されやすくなってしまうのです。会社はスタッフがどんな私用ソフトを自前の PC にインストールしているかも把握できませんし、業務時間外のネット利用を禁止するわけにもいきません。私用でインストールしたソフトウェアによって PC がウイルス感染したり、私用メールを開いた途端に PC がウイルス感染したというケースも多く発生しています。

　取り急ぎテレワーク環境を整えた企業は、上記のようなセキュリティの甘い構成になっていることが多いのではないでしょうか？

　最近では、テレワーク化が原因でデータ流出やウイルス感染が発生したことがニュースにもなっています。
　VPN 経由だからこそデータが流出しやすくなることがあることも忘れないようにしてください。

　参考ニュース記事：
　テレワークで情報流出　ＶＰＮの脆弱性を悪用
　在宅勤務時にＰＣウイルス感染　不正アクセス、従業員情報が流出―三菱重工
　

セキュリティの高いテレワーク構成には、セキュリティの一元管理が必要

　テレワークを安全に行うためには、企業ネットワーク側から各 PC の状態をある程度把握できる仕組みが必要となります。
　たとえば、クラウド管理型のアンチウイルスシステムを導入し、在宅スタッフの PC でウイルスが検出された場合にその通知が社内のスタッフに送信されるようにします。また、社内のルータを侵入検知機能付きのものに変更し、在宅スタッフ側で防げなかったウイルスやマルウェアを社内ネットワークの入り口で完全にシャットアウトします。

　以下のように構成します。

テレワーク環境の改善

クラウド型アンチウイルスシステム Panda Endpoint Security の導入

　弊社では、クラウド型アンチウイルスシステムとして、Panda Endpoint Security を導入しています。各クライアント PC に Panda Antivirus というソフトウェアをインストールすることにより、各PC のウイルス対策はもちろんのこと、感染や検疫が行われた記録はすべてクラウド上に通知され、担当者にその旨メールが送信されます。

　https://www.pandasecurity.com/en/business/solutions/ [英語]

SonicWall ルーターによるファイアウォール、アンチウイルス、サービス遮断

　SonicWall はルーター機能の他に、より柔軟性の高いファイアウォール機能が搭載さてれいます。不要ポートの遮断はもちろんのこと、各PC の IPアドレスやMACアドレス別に接続を許可したり、アクセス許可の範囲を決定したりできます。

　また、アンチウイルスおよび侵入検知機能も用意されているため、VPN 経由で侵入したウイルスやマルウェアも SonicWall ルータによって検疫の対象となり、社内ネットワーク上のウイルス蔓延を未然に防ぐことができます。

　さらに、特定のサービス（チャット、ゲーム、P2Pなど）を遮断したり、特定のサーバへのアクセスを遮断したりすることができるため、業務中にそれらのサービス使用によるトラブルを回避できます。

　https://www.sonicwall.com/ja-jp/

　弊社では Panda Cloud Security および Sonicwall によるネットワーク構築を支援するサービスを承っております。
　詳細はこちらをご覧ください。
　セキュリティとネットワーク構築

Amazon Workspaces の一部導入

　上図のスタッフC 経理は自前PC の OS 互換性の理由により、社内の Windows PC にリモートデスクトップ接続して経理ソフトウェアを操作しています。このため、経理一人のために本社の PC/仮想マシンを稼働させておく必要があります。
　将来的に経理担当を増やしたり、不要な稼働マシンの削減を考慮して、オンデマンドで起動できる Amazon Workspaces というクラウド型の Windows 仮想マシンを用意することによって、必要時にのみ Amazon Workspaces 上で経理ソフトを操作させる選択肢もあります。
　Amazon Workspaces はデスクトップ越しのファイル送信やコピー＆ペーストが許可されないため、操作ミスによる情報漏洩のリスクも低減できます。

　弊社では Amazon Workspaces の導入に関するコンサルティングから導入まで支援するサービスを承っております。
　詳細はこちらをご覧ください。
　AWS導入サービスについて

その他考慮すること

　企業によってテレワークのセキュリティポリシーをスタッフ別に考慮する必要も出てきます。たとえば、ネットワーク管理者であれば自宅から遠隔操作でほぼすべての社内リソースにアクセスできる必要があるでしょうし、社員の印刷やファイル交換の可否のセキュリティポリシーをグループまたは担当者別に設定する必要があるでしょう。
　また、外注スタッフに一部のシステム操作を許可する場合にも、アクセス可能時間を限定したり、IP/MAC アドレス別にアクセスを制御したり、機能が制限された Amazon Workspaces 上のみで操作を許可するようにしたりと、不正操作を未然に防止する必要もあるでしょう。

テレワークのセキュリティポリシー

　また、各スタッフの PC のドライブや外付けデータドライブ等も暗号化しておくことも重要です。これにより、万が一 PC や外付けデータドライブが盗難にあったとしても、データを復元できないため、データ自体は機密情報であっても、その中身を覗かれてしまうリスクを回避できます。
　ドライブの暗号化は、Windows 10 なら標準搭載の BitLocker、macOS も標準搭載の FileVault を使うことができます。

テレワークを成功させるには、各人の心がけありき

　上記のテレワーク構成は、それぞれのスタッフが万が一ネットワーク越しにうっかりミスをやらかしたときの基本的なセーフティネットを提供するにすぎません。

　いくらスタッフA 管理者が会社支給の PC を使用しているからといって、個人情報の取り扱いに問題のあるネット対戦ゲームソフトを勝手にインストールしたり、業務中や業務時間外に怪しいサイトを閲覧したりして、セキュリティリスクを高めてしまっているようでは元も子もありません。

スタッフB 開発者が、最強のPCを自作したとしても、その PC が故障してしまったら、業務はそこで停止しますし、VPN 経由で気を付けて本社サーバにアクセスしても、サーバの設定ミスでうっかり機密情報が外部に漏洩してしまうこともあります。

スタッフC の経理は自前PC の OS 互換性の理由により、社内の Windows PC にリモートデスクトップ接続して経理ソフトウェアを操作していますが、経理一人のために本社の PC/仮想マシンを稼働させておく必要があります。将来的に経理担当を増やしたり変更したりすることも考えて、オンデマンドで起動できる Amazon Workspacesというクラウド型の Windows 仮想マシンを用意し、必要時にのみ Amazon Workspaces 上で経理ソフトを操作するようにする選択肢もあります。

　企業の中には、きちんと成果さえ出せばいつ、どこで勤務しても良いという、労働条件の自由度が高いところもあるかもしれませんが、だからといって安易に喫茶店の無料 wi-fi を使って作業をすれば、個人情報や機密情報が外部に漏洩するリスクが発生しますし、赤の他人に横から情報を覗き見される危険性もあります。また、ワーケーションと称して観光地のホテルの一室で作業をしたりする際にも、機器の盗難リスク、紙の資料紛失、セキュリテイの甘い wi-fi 環境によるデータ漏洩のリスクも発生しやすくなります。働き方は自由でも、スタッフに課される責任として企業データや個人情報の保護を考慮すると、セキュリティ対策に自信のある人のみに許された自由ということになるのかもしれません。

土屋企画のテレワーク関連サービス
　弊社ではテレワークを検討されている企業様向けに検討から導入まで支援するサービスを提供しております。詳細は以下のリンクをご参照ください。
セキュリティとネットワーク構築
リモート開発/テレワークについて
AWSとテレワーク

2020-07-06

IIS6 + FileMaker Web Companion/Web Server Connector 構成の Web サーバ機で TLS1.2 が動作するようにリバースプロキシを設定する

Web ブラウザの TLS1.0/1.1 無効化により Web ブラウザで警告が発生

2015/10/05 に大手4社のWebブラウザが 2020年にTLS 1.0と1.1を無効化すると発表し、2020/07/01 より順次対応が始まった模様です。

参考リンク：大手4社のWebブラウザ、2020年にTLS 1.0と1.1を無効化

今回の Web ブラウザのセキュリティ強化により、最新版の Web ブラウザを使って TLS1.2 非対応の Web サーバにアクセスすると、以下のような警告が Web ブラウザに表示されることがあります。

（以下は最新のFirefox でアクセスしたときのエラー）

TLS1.2 に対応していないときに Web ブラウザに表示されるセキュリティエラー（Firefox）

Firefox で表示されるエラーコード: SSL_ERROR_UNSUPPORTED_VERSION

Windows Server 2003 IIS6 は TLS1.2 非対応のため、別途対策が必要

　新たなセキュリティホールや脆弱性は日々発見されており、サーバOS も Webサーバもアップデートが提供されているものを使用し、できるだけ迅速にアップデートを行うことが推奨されます。しかし、OSやWebサーバのアップデートを行うにはシステムの修正や再構築が必要となり多額の費用が発生することが多々あり、このため止むを得ずレガシーシステムを使わざるを得ない、ということもあります。

　さて、FileMaker 5.5 Unlimited に付属する Web Companion や Web Server Connector は、FileMakerデータベースとIISの橋渡しをしてWebアクセスを可能にするものですが、Web Companion/Web Server Connector は Windows Server 2008のIIS7では動作しません。このため、Windows Server 2000/2003 IIS5/6 と FileMaker Pro 5.5 Unlimited（Web Companion/Web Server Connector）により、Webサイトを運用している企業はいまだにあると思われますが、ここで問題が発生します。IIS5/6 は TLS1.0 までしか対応していないため、この7月以降は上図のようなエラー/警告が発生します（エラーや警告はブラウザによって異なります）。

Windows Server 2003 IIS6 のサーバ構成（TLS1.2 非対応）

リバースプロキシサーバ + IIS6 で Web サーバを構築

　さて、ここからが本稿の本題です。IIS5/6 + WebCompanion/Web Server Connector を使用しながら、どうすればTLS1.2以降による暗号化を可能にするかということですが、TLS1.2以降に対応した リバースプロキシサーバを立てることによって実現できます。今回は nginx 1.18.0 によりリバースプロキシサーバを構成しました（下図）。

　この構成により、既存の IIS6 の設定を若干変更するだけで、セキュリティが強化され、Web ブラウザに警告・エラーが表示されなくなります。

nginx リバースプロキシ + II6 によるサーバ構成

　以下、設定方法となります。

　なお、本稿はボランティアで提供しており、その内容や結果は保証できません。「書いてある通りにやったら、サーバが壊れた！　責任取れ(怒)！！！」とか、ホントにやめてくださいね。ただ、実行結果をコメントで残して頂く、とかなら大歓迎です。

既存の証明書の購入元から、PEM 形式の証明書とその秘密鍵を入手
取得方法はサーバ証明書発行サイトによって異なりますので、詳細は証明書発行会社にお問い合わせください。
入手した証明書ファイルと秘密鍵ファイルは、C ドライブ以外のできるだけ安全なフォルダに配置しておきます。
IIS6 にインストールされている証明書を削除
インターネットインフォメーションサービスマネージャを開き、「規定のWebサイト」まで展開し、マウスを右クリックしてプロパティを開きます。

図のように“サーバー証明書(S)”をクリックし、処理の一覧から「現在の証明書を削除する(R)」をクリックし、“次へ”をクリックします。
削除前の確認メッセージが出ますので、“次へ”をクリックすると、IIS から証明書が削除されます。
IISの Web サーバポートを変更
引き続き「Webサイト」タブをクリックし、ポート番号として以下のように入力します。

IIS6 側のサーバポート変更

TCPポート：8080 (80以外であれば何でも可)
SSL ポート：（空欄）
nginx をダウンロードし、任意の場所に配置（インストール）

https://nginx.org/en/download.html にアクセスし、Windows 用の最新安定バージョンをダウンロードし、解凍して任意の場所に配置します。
今回は nginx/Windows-1.18.0 をダウンロードします。
※ Windows Server 2003 環境で解凍を行うと、実行ファイルが自動削除される可能性があります。その場合は、別の PC 上で解凍した後に Windows Server 2003 環境に配置しなおしてください。

nginx.conf ファイルの修正

nginx フォルダ配下の conf フォルダに配置されている nginx.conf ファイルをリバースプロキシとして動作させるために、以下のように修正します。

※事前にnginx.cfg ファイルのバックアップをお取りください。

以下、環境別に設定が異なる部分は適宜調整してください。

nginx.conf


#user  nginx;
worker_processes  1;

events {
    worker_connections  1024;
}


http {

    include       mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  logs/access.log  main;

    server_tokens off;
    sendfile        on;
    tcp_nopush     on;

    keepalive_timeout  65;

　　gzip on;
    gzip_types text/css application/javascript application/json 
               application/font-woff application/font-tff image/gif 
               image/png image/jpeg application/octet-stream;
    
    add_header X-XSS-Protection "1; mode=block";
    add_header X-Frame-Options SAMEORIGIN;
    add_header X-Content-Type-Options nosniff;


    server {
 
        listen 80;
  
        server_name  yourdomain.co.jp;

        location / {
           proxy_pass http://127.0.0.1:8080;
        }
        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

    }

    # HTTPS server
    #
    server {
        
        listen 443 ssl;
        server_name yourdomain.co.jp;

        ssl_certificate     d:\\app\\cert\\fullchain.pem;
        ssl_certificate_key d:\\app\\cert\\privkey.pem;
  
        ssl_session_cache    shared:SSL:1m;
        ssl_session_timeout  5m;
        ssl_ciphers ECDHE+RSAGCM:ECDH+AESGCM:DH+AESGCM:DH+AES256:DH+AES:
                   !EXPORT:!DES:!3DES:!MD5:!DSS;
        ssl_prefer_server_ciphers on;

        location / {
           proxy_pass http://127.0.0.1:8080;
       }
       
    }

}

proxy_pass で示された URL のポート番号は、手順 3. で指定したものを入力します。
HTTPS サーバブロックの proxy_pass にも同じものを入力します。

ここまで修正したら設定ファイルを上書き保存します。

nginx 起動テストを実行

コマンドプロンプトを開き、nginx フォルダまで移動してから、以下のように入力します。

nginx

※ 起動直後にポートアクセスへの許可を求めるダイアログが表示されたら、すべて許可しておきます。

Web ブラウザを開き、従来の URL にアクセスします。
警告メッセージが出ずに従来の Web ページが表示されれば成功です。

前述の nginx.conf 設定で証明書の設定も行っていますので、http://、https://　ともにアクセステストを行ってください。
nginx を停止

nginx 起動テストに成功したら、いったん nginx を停止させます。
コマンドプロンプトをもう一つ開き、以下のように入力します。
nginx -s stop

nginx プロセスが終了します。
nginx を Windows サービスとして登録

nginx を Windows サービスとして登録して常駐させるには、winsw という外部ツールを使います。

以下のサイトより　winsw というツールをダウンロードします。
https://repo.jenkins-ci.org/releases/com/sun/winsw/winsw/

ここでは最新版の winsw-2.9.0-bin.exe をダウンロードします。
ダウンロード済みの実行ファイルは任意の場所に配置してもよいのですが、nginx と同じフォルダに配置したほうが管理はしやすいかと思います。

winsw-2.9.0-bin.exe ファイルを配置したら、同じフォルダの中に winsw-2.9.0-bin.xml という名称で空のファイルを作成し、テキストエディタで開きます。

winsw-2.9.0-bin.xml ファイルに以下のように入力します。
以下、nginx.exe へのパスは適宜調整してください。
winsw-2.9.0-bin.xml
```
<service>
  <id>nginx</id>
  <name>nginx</name>
  <description>nginx</description>
  <logpath>D:\Program Files\nginx-1.18.0\logs</logpath>
  <logmode>roll</logmode>
  <depend></depend>
  <executable>D:\Program Files\nginx-1.18.0\nginx.exe</executable>
  <startargument></startargument>
  <stopexecutable>D:\Program Files\nginx-1.18.0\nginx.exe</stopexecutable>
  <stopargument>-s</stopargument>
  <stopargument>stop</stopargument>
</service>    
```
修正が終わったらファイルを保存します。

コマンドプロンプトを開き、winsw-2.9.0-bin.exe　を配置したフォルダまで移動し、以下のように入力すると、nginx がサービスとして登録されます。
winsw-2.9.0-bin.exe install

インストールに成功すると、以下のような結果が表示されます。

2020-07-04 00:06:46,000 INFO - Installing the service with id 'nginx'
nginx サービスの開始
Windows サービスに nginx が登録されていることを確認し、“開始”をクリックすると nginx が起動し、稼働状態となります。
サーバ動作最終チェック
Web ブラウザを開き、従来どおりに Web サーバにアクセスし、無事にページが表示されれば終了です。

お疲れさまでした。

おまけ：

nginx を Windows サービスから削除する場合は、コマンドプロンプトで以下のように入力します。

winsw-2.9.0-bin.exe uninstall

■ FileMaker 5/6等レガシーシステム関連記事

太古の FileMaker システムを延命させる！ ― 後日談FileMaker（17/09/07投稿）
下記記事のレガシー延命スキームの実施と結果について。

太古の FileMaker システムを延命させる！（17/04/25投稿）
Remote Desktop Server/FileMaker Pro 5.5 搭載の Windows Server 2008 物理マシンを P2Vし、Hyper-Vに移行することにより、レガシーシステムの延命を図る。

FileMaker 5.5/6 をモバイルで使う（16/05/25投稿）
Android/iOS に Remote Desktop Client を載せて、FileMaker Go のようなことをしてみます。

今なお輝くFileMaker 5.5/6（16/05/23投稿）
レガシーFileMaker の意外な利点。

（亀）

2020-06-01

TPC FM-AConnect CTI ― Amazon Connect と FileMaker WebDirect 19 による CTI

TPC FM-AConnect CTI について

　本稿では、Amazon Connect と FileMaker WebDirect 19 を連携させた CTI（Computer Telephony Integration）プロトタイプ・TPC FM-AConnect CTI の仕組みと機能をご紹介します。

　Amazon Connect は、AWS上で構築するクラウドベースの PBX/コールセンターシステムです。レガシーPBXを使用したコールセンターシステムに比べ、初期費用に抑えてシステムを構築できます。

　これによりオフィス内のスタッフはもちろん、テレワーカー（在宅勤務者）でもCTIを利用できるようになります。

TPC FM-AConnect CTIの主要機能

FileMaker WebDirect 19のアプリ（FM-AConnect WD）を使用した着信時顧客情報ポップアップ（着信した電話番号により顧客データベースを検索し、その顧客情報をオペレータのPC画面に表示する機能）
FileMaker DB に登録された電話番号とオペレータIDに基づく、オペレータへの配信（着信番号別オペレータ割当、例えば顧客DBで顧客AとAを担当するオペレータXが紐付けて登録されている場合、Aの電話番号を着信するとXに転送する
チャット　―　テキストによるチャット
通話記録　―　音声通話を記録・再生する機能

　WebDirect とは FileMaker で作成したアプリをそのままWeb公開する機能です。FileMaker のデスクトップアプリと高い互換性を有しており、そのほとんどの動作をWebブラウザ上で再現可能です。また、バックエンドとなるデータベースには FileMaker に限らず、ODBC経由で Oracle、MySQL、SQL Server、DB2^*1、PostgreSQL^*1 を使用することができ、他の開発ツールに比べ短い時間で CTI のフロントエンドを開発することが可能です^*2。

　Amazon Connect の電話端末となるソフトフォン・CCP もブラウザで動作する Webアプリであるため、WebDirect との親和性、JavaScript による相互の制御性は高いといえます。

注：
*1 DB2、PostgreSQL を使用するには、Actual社の ESSアダプタが必要。

*2 WebDirect については、当社の過去のBlog記事（一覧）をご覧ください。

仕組みと Amazon Connect の問い合わせフローについて

　以下が TPC FM-AConnect CTI のシステム構成図となります。

図でCCP (Contact Control Panel)とあるのが Amazon が提供するソフトフォンで、Windows/Mac の Firefox あるいは Chrome 上で動作します。残念ながら、スマホやタブレット、SIPフォンには対応していません。

　顧客からの電話が入ると、まず Amazon Connect が1つ目のCCPに電話を転送し、転送先の CCP はPCのスピーカーから呼び出し音を発生させます。

　上記の処理の流れは Amazon Connect の問い合わせフローによって制御されます。
問い合わせフローとは、問い合わせの一連の処理の流れをシナリオ化したものです。
TPC FM-AConnect CTI には、以下の 3 つの主要問い合わせフローによって構成されています。

メインフロー

　窓口となる Amazon Connect の問い合わせフローです。顧客からの着信が音声通話要求なのか、チャット要求なのかによって各サブフローを呼び出します。

　上記の流れを簡易フローチャート化すると、以下のようになります。

サブフロー：音声通話応答用問い合わせフロー

　メインフローにより、音声通話要求と判断された場合に実行される Amazon Connect の問い合わせフローです。通話着信があってからCCPでの通話が開始されるまでの問い合わせフローは次のようになります。

上図は1画面に表示するため、エラー時のフロー転送とハングアップを増やしてます

　上図を簡易フローチャートにすると以下のようになります。　

　このとき FM-AConnect WD は着信した電話番号を取得し、その番号によりデータベースを検索し、顧客情報をポップアップ表示します。

　上図を補足解説します。

ログ収集開始では、Amazon Kinesis Stream というデータストリームを有効にし、通話開始時のタイムスタンプを取得します。このタイムスタンプは、Amazon S3 に保存される通話記録ファイル(後述)名をデータベースに保存するために必要となります。なお、Transcribe（AWSの音声→テキスト変換モジュール）を利用する場合も、この Kinesis が必要となります。
Amazon Connect のオペレーション時間設定を使用し、会社の営業時間を設定します。営業時間内であれば次に進み、時間外であれば顧客にその旨を告げる音声アナウンスを行い、通話を終了します。
AWS の Lambda 関数に定義されている FileMaker Data API により、着信電話番号に該当する顧客担当者（担当エージェント）を検索し、通話要求を担当者の CCP に転送を試みます。以下は、転送時の分岐処理。
- 顧客担当者検出 --- その担当者に通話要求を送信
- 顧客担当者を検出したが不在 --- 通話可能な担当者に通話要求を送信
- 顧客担当者未検出(DBに登録無) --- 通話可能な担当者に通話要求を送信
- 顧客担当者不在 --- 未達メッセージを自動音声アナウンス → 通話終了
AWS の Lambda 関数を使用し、受信した電話番号をJSON形式でWebクライアント（ここでは FM-AConnect WD）に渡します。FileMakerのファイルには「電話番号による顧客検索スクリプト」を用意し、電話番号は引数で渡せるようにしてあります。
Webサーバ上に配置するHTMLページには上記のCCP を埋め込んでおき、Lambda が生成したJSON形式の電話番号を取得し、Webビューアに設定してある FileMaker.PerformScript (電話番号で顧客検索スクリプト, 電話番号) を実行します。

この FileMaker.PerformScript( ) は FileMaker 19 で導入された新機能で、Webビューア内の JavaScript から FileMaker のスクリプトを実行できます。これにより、CCPに着信があると、FileMaker WebDirect のアプリで顧客情報が検索されるようになります。

CCP(右)に着信すると、WebDirect(左)は自動的に電話番号で顧客を検索・表示する
担当者の CCP まで通話要求が転送されると、CCP が呼び出し状態となり、✓ボタンをクリックすると通話が開始されます。

　以下の動画では、上記のフローに基づき実装したCTIの着信時顧客情報ポップアップ機能をご覧いただけます。

通話記録再生機能について

　通話記録は音声ファイルとして AWS S3 サーバに保存されます。通話記録ファイルは FileMaker WebDirect アプリケーションの「交信履歴」タブのリストから再生することができます。

サブフロー：チャット応答用問い合わせフロー

　TPC FM-AConnect CTI にはチャット機能も用意されています。チャット機能のシステム構成図は以下のようになります。

　メインフローにより、チャット要求と判断された場合に実行される Amazon Connect の問い合わせフローです。チャット要求があってから CCPでのチャットが開始されるまでの問い合わせフローは次のようになります。

上図は1画面に表示するため、エラー時のフロー転送とハングアップを増やしている

　上図を簡易フローチャートにすると以下のようになります。　

　上図を補足解説します。

貴社のWeb サイトにチャット開始用のページを用意し、顧客はこのページからチャットを開始します。

　顧客の上図の"Start Chat"をクリックするとチャット用の API Gateway にアクセスが行われ、Lambda 関数によりチャットセッションが開始し、Amazon Connect のチャット応答用問い合わせフローに入ります。
Amazon Connect のオペレーション時間設定を使用し、営業時間をチェックします。時間内であれば次に進み、時間外であれば顧客にテキストでアナウンスを行い、チャットを終了します。
切断フローとは、エージェント側がチャットを終了したときの処理を定義したフローです。たとえば、チャットのお礼メッセージ、顧客への案内メッセージ、企業プロモーション情報などを送信したり、Amazon Connect の内部処理などを定義したりできます。
担当エージェントの CCP が呼び出し状態となりますので、✓をクリックするとチャットが開始されます。
チャットが終了すると、上記の 3. の切断フローが実行されます。たとえば、以下のようなメッセージが顧客側に表示されます。

　Amazon Connect でシステムを構築するのは上記のようにそれなりの工数が発生しますが、ハードウエアの初期費用が不要で、運用コストも廉価です。また、システム構成もハードウェアを気にすることなく柔軟に変更することが可能です。

　コロナ等の感染症や台風・洪水などの自然災害が多発する昨今、オペレータの居場所に拘束されない Amazon Connect の導入は今後増えるものと予想されます。

以上

2020-03-16

FileMaker データベースをSQLスクリプトステップを使用して更新する

　FileMaker は大分以前のバージョンからExecuteSQL関数により、SQLのCRUD（Create、Read、Upudate、Delete）の内、 Read、つまりSELECT 句のみは実行できるようになっています。

　開発者、特に他言語の開発者の中には「なぜ Read はできるのに、Create、Update、Deleteはいつまで経ってもできないのか？」とお腹立ちの向きも多いと思います。　小生もその一人です。

　実は（というほどのことではありませんが）、FileMaker ODBC を使用すると、「SQLを実行する」スクリプトステップを使用してFMのテーブルを Create(Insert)、Update、Delete することができます。本稿では触れませんが、CRATE/ALTER TABLE といった DDL ― Data Definition Language も一部サポートされていて、テーブルの作成・変更も限定的に実行できます。

　ただ、開発者にとって非常にありがたいこの Create、Update、Delete を活用しているという話は寡聞にして知りません。これは、昔の FileMaker のODBC機能がお粗末で、新しい ODBC を踏み込んで使ってみようと思う人が少ないのが一因かもしれません。小生もその一人です。

　以前、医療レセプト開発のコンサルティング業務を請け賜わったことがあるのですが、数百万行のテーブルがあり、1つのレイアウト上に多数のテーブルからデータを引っ張るといった複雑なシステムで、FileMaker のテーブルオカランスのみではすべての要求情報を1つのレイアウトに表示することはできませんでした。元々はJavaの技術者だったご担当者はいくつもの ExecuteSQL 関数を複数のテーブルに対して実行、結果を変数に格納し、Loopに新レコード作成と値貼り付けを挟み込みむ、といった大変な開発をされていました。この時、上記のようなスクリプトステップによるバッチ処理ではなく、INSERT ~ SELECT 等の利用を薦めたのですが、当方においても本手法の採用実績やテストが十分とは言えず、強くは推せませんでした。

　Insert や Update を利用すれば一発でできるところを、ウインドウを開いて、レイアウトを切り替えて、ループして書き込んで、ウインドウを閉じるといった FileMaker 特有の残念な処理を黙々と組み込んでおられたわけです。

SQLを実行するステップによりFileMaker DBを更新するテスト

　ということで今回、FileMaker ODBC、FileMaker Server 18、 FileMaker Pro 18 の「SQLを実行する」スクリプトステップを使用して、いくつかのテストをしてみました。その結果が下表です。

表の0:Script batch は FileMaker の通常のスクリプトステップを複数使用した場合

1:Client SQL は FileMaker の「SQLを実行する」をクライアントからサーバに対して実行

2:Server SQL はクライアントから「サーバ上のスクリプトを実行」ステップを使用し、サーバ上で「SQLを実行する」ステップを実行

「1:Client SQL」と「2:Server SQL 」では、今回のような単純なSQLクエリにおいては速度の違いはほぼありませんでしたが、サーバサイドでSQLを行う場合、サーバだけに ODBC を設定しておけば、クライアント上にODBCをインストール・設定する必要が無いのはメリットです。

1列目は実行したテスト内容で、以下で解説します。

0:Script batch

1:Client SQL

2:Server SQL

1. Update 1 field of 10K records

7.2sec

3.0sec

2.8sec

2. Update 5 fields of 10K records

33.0sec

3.2sec

2.9sec

3. Simple import of 10K records

11.3sec

6.2sec

6.1sec

※各テストはそれぞれ5回ずつ実行し、所要時間を平均値を載せています。

	0:Script batch	1:Client SQL	2:Server SQL
1. Update 1 field of 10K records	7.2sec	3.0sec	2.8sec
2. Update 5 fields of 10K records	33.0sec	3.2sec	2.9sec
3. Simple import of 10K records	11.3sec	6.2sec	6.1sec

1万件のレコードの1フィールドを更新

上表の「1.Update 1 field of 10K records」では、1万レコードの1フィールドを更新しています。SQLで記述すると以下になります。

update product set "note1" = 'client sql'

これをFileMaker のスクリプトでやる場合、product テーブルが設定してあるレイアウトを開き、すべてのレコードを選択して、 note1フィールドを選択後に、「client sql」で全置換するというように、複数のステップが必要になります。全置換を使用しない場合、フィールド値設定ステップを Loop させることになりますが、これは置換ステップよりも時間を要します。

　結果的にはSQLを使用した方が2倍以上高速でした。

1万件のレコードの5つのフィールドを更新

　「2.Update 5 fields of 10k records」では1万レコードの5つのフィールドを更新しています。SQLで記述すると以下になります。

update product set note1 = 'sql 10', note2 = 'sql 20', note3 = 'sql 30', note4 = 'sql 40', note5 = 'sql 50'

「0:Script batch」では、置換ステップを5回実行しています。結果はSQLを使用した方が5倍高速でした。

1万件のレコードを別テーブルにインポート

　「3.Simple import of 10K records」では1万件のレコードを別テーブルにインポートしています。SQLで記述すると以下になります。

insert into invoice (prodId, prodName, price, unit, note) select ID, name, salesPrice, unit, note1 from product

「0:Script batch」のコアとなるステップは「レコードをインポートする」だけですが、このような単純な処理であっても、SQLの方が2倍弱高速でした。

過去のテスト

　小社では以前、いくつかの FileMaker 用 Web API を使用し、CRUDのテストを行っており（下記リンク参照）、その際も Create、Update、Delete については ODBC（PDO）が良い（＝高速である）結果を得ています。

参考：FileMaker API別にCRUDのテストをしてみた（2016/12/01）
　今回、ODBCとSQLを実行スクリプトステップを使用しても、3年前と同様に良い結果が得られました。

SELECT ～ FOR UPDATE について

　SELECT ～ FOR UPDATE は直接データベースを更新する構文ではありませんが、更新の前処理として重要な意味があるので、ここで触れておきたいと思います。
　FileMaker®16 SQL リファレンスガイドによると、FileMaker ODBC/SQL は「FOR UPDATE 句」をサポートしており、select ～ where ～ for update を実行すれば、where で指定された「各レコードは取得時にロックされ」る筈ですが、「SQLを実行する」ステップでは以下のようなSQLを実行しても、他ユーザは where 句で指定されたレコードを更新できてしまいます（つまりロックされない）。

select * from product where ID =1 for update [of note1, note2]

　もともと「SQLを実行する」ステップで select を実行しても、文字列などの結果が返りません。サーバのログを見ても、実行直後に接続が切れています。ということで「SQLを実行する」ステップは select ～ for update （レコードロック）をサポートしていないようです。

　ただ、他ユーザがあるレコードを編集している際に、当該レコードが対象となる select ～ for update 実行すると「[FileMaker][FileMaker] (301): Record is locked by another user.」が返ります。　つまり、FOR UPDATE句により、ユーザは WHERE句で指定するレコードセット中に編集中のモノがあるかどうかを事前に知ることだけはできます。

参考：https://wiki.myfmbutler.com/index.php?title=DoSQL_3#more_optional_parameters

　ちなみに、ExecuteSQL関数はこの FOR UPDATE をサポートしていません。

更新時の他ユーザ競合エラー

　複数レコードのフィールド値を更新する場合、他ユーザが更新対象のレコードを編集しているとエラーとなります。ただし、フィールド内容を置換スクリプトステップと SQL の UPDATE ではその内容が異なります。

　フィールド内容を置換ステップでは、置換対象のレコードを他のユーザが編集していると、そのレコードを除くロックされていない全レコードが置換され、FileMaker標準エラーの201（フィールドを変更できません）が返ります。置換ステップの欠点はエラーが起こったレコードが特定できないことで、これは場合によっては致命的です。このため、エラーを放置できない場合は置換ステップに替えて、処理に時間がかかりますが、Loop により各レコードを逐一更新し、エラーを起こしたレコードの主キーを記録して、ユーザに通知するか、エラーを解消するための処理を別途用意することになります。

　これに対し、FileMaker SQL の UPDATE は、1レコードでもロックが発生していると WHERE句で指定されたすべてのレコードが更新されません。あたかもエラーが発生して ROLLBACKされたようにみえます。このときFileMakerは1408（拡張エラー (ODBC)、Get(最終外部エラー詳細)関数は「[FileMaker][FileMaker] (301): Record is locked by another user.」を返します。

　FOR UPDATE句によりレコードセットをロックできれば、更新時の他ユーザ競合によるエラーの懸念も減少すると思うのですが、前述のような状況なのが残念です。

　FileMaker の開発案件もマルチユーザ対応が要件であることが多いと思います。マルチユーザ対応であれば、競合発生時のエラー処理は必須です。この辺の地味な処理の重要性を発注サイドにも認識して頂き、工数を見込んで頂くと共に、検収時のチェックリストにいれましょう。

SQL実行ステップにより更新を行うメリットと課題

SQLによる更新を行うメリットをまとめると以下のようになります。

高速化

スクリプトの簡略化、可読性向上

他言語からの移行組技術者は使い慣れたSQLにより開発ができる

アクティブレイアウトとは無関係にコマンドを実行できる

　今回は単純な処理のみでテストを行っていますが、多数行に及ぶ複雑な更新・バッチ処理を行う場合、SQLを使用する速度的メリットはさらに大きいと思います。

　FileMaker は過度にレイアウト依存したシステムであるため、上記4のメリットも大きいと思います。スクリプトはレイアウトからフィールドが消去されるとエラーを起こす可能性がありますが、SQLであればその心配はありません。

スクリプトで複数レコード、複数テーブルに及ぶ処理を実装すると、いちいちテーブルが割り当ててあるレイアウトに移動し、LOOPでグルグル回しながらレコードを作成したり、更新したりするのはコーディング的にも残念ですし、画面がチラチラ無駄に切り替わるのはユーザから見ても美しくありません。

課題

　FileMaker社はWebサイトでアクセス方法別の許容ユーザ数を公開しており、ODBC/JDBC接続の最大許容値（理論値）は無制限、検証値は50（ユーザ）となっています。
今後は100～500ユーザ位を想定し、負荷が高い処理でも確実に実行されるか、テストを行いたいと思います。

以上

NuckyT

登録: 投稿 (Atom)

メニューバー

2020-12-30

テストベッド

ビーコンの保護

ビーコンを屋外に常設する

ビーコンを屋内に常設する

Raspberry Pi を屋外に常設する

Raspberry Pi の電源

屋外でスマート電源を使用する

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

2020-12-26

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

2020-11-11

受信機の配置を考える

グリッドについて

使用ビーコン

グリッドサイズ50ｍ

グリッドサイズ150m

グリッドサイズ200M

受信機からビーコンまでの距離について

BLE 5 対応ビーコンの到達距離

人や障害物が少なからず存在するエリアについて

IPS関連のBlog記事

土屋企画のIPS製品について/IPS product of TPC

2020-09-04

安易なテレワーク導入は危険がいっぱい

各PCのセキュリティ状態が把握できない

社内ネットワーク側にウイルス検知システムが導入されていない

BYOD(自前)PC の用途が厳格化されていないため、セキュリティリスクが高まる

セキュリティの高いテレワーク構成には、セキュリティの一元管理が必要

クラウド型アンチウイルスシステム Panda Endpoint Security の導入

SonicWall ルーターによるファイアウォール、アンチウイルス、サービス遮断

Amazon Workspaces の一部導入

その他考慮すること

テレワークを成功させるには、各人の心がけありき

2020-07-06

Web ブラウザの TLS1.0/1.1 無効化により Web ブラウザで警告が発生

Windows Server 2003 IIS6 は TLS1.2 非対応のため、別途対策が必要

リバースプロキシサーバ + IIS6 で Web サーバを構築

おまけ：

2020-06-01

TPC FM-AConnect CTI について

TPC FM-AConnect CTIの主要機能

仕組みと Amazon Connect の問い合わせフローについて

メインフロー

サブフロー：音声通話応答用問い合わせフロー

通話記録再生機能について

サブフロー：チャット応答用問い合わせフロー

2020-03-16

SQLを実行するステップによりFileMaker DBを更新するテスト

1万件のレコードの1フィールドを更新

1万件のレコードの5つのフィールドを更新

1万件のレコードを別テーブルにインポート

「3.Simple import of 10K records」では1万件のレコードを別テーブルにインポートしています。SQLで記述すると以下になります。

insert into invoice (prodId, prodName, price, unit, note) select ID, name, salesPrice, unit, note1 from product

過去のテスト

SELECT ～ FOR UPDATE について

SQL実行ステップにより更新を行うメリットと課題

今回は単純な処理のみでテストを行っていますが、多数行に及ぶ複雑な更新・バッチ処理を行う場合、SQLを使用する速度的メリットはさらに大きいと思います。

課題

　「3.Simple import of 10K records」では1万件のレコードを別テーブルにインポートしています。SQLで記述すると以下になります。

　今回は単純な処理のみでテストを行っていますが、多数行に及ぶ複雑な更新・バッチ処理を行う場合、SQLを使用する速度的メリットはさらに大きいと思います。