情シスは何度でも甦るさ。

ＶＢＡ！ＶＢＡ！

閉域網で使ってるiPhoneがMDMと通信できなくてハマった話

ハマった

MDMの指令を無視するiPhone

うちの閉域網に、業務端末としてiPhoneを導入した。
うちの閉域網から、インターネットに出る口は開いているので、一般のios アプリも許可したのは使える構成。

いざ、キャリアから初期設定された端末たちがぞろぞろと納品してきたのだが、社内の細かいキッティングをしようと思いMDMから指令を与えるも、うんともすんとも言わない状態に。

しかし、社内Wifiからだと繋がる不思議

今回閉域網をキャリアのサービスを使っていた。 経路としては、こうなる。

iPhone => （LTE) => キャリアの設備 => うちの閉域網 => インターネット（MDM)

切り分けを行うために、色々試行錯誤をすると、

①社内Wifiにつなぐ → MDMと通信できない
②社内Wifiにつないで、モバイルデータ通信をOffにする → MDMと通信できる

という謎な事象が発生。

①、②も経路は
iPhone => （Wifi) => うちの閉域網 => インターネット（MDM)

となり、当然インターネットに出る経路は同じなのでつながらないはず。
なのに、②の場合なんでモバイルデータ通信を切ってWifiオンリーにしたらMDMに繋がるねん。

ますます、混迷を深める。 切り分けようとして、理由が全く異なる事象にぶち当たってしまったのだろうか。

そもそもMDMとiPhoneってどういう通信してるの？

インターネットにあるMDMは、どのようにiPhoneに指示を送っているのだろうか。

ネット上の情報によると、以下の経路でiPhoneに指示が飛ぶ。

MDM => APSN（Apple Push Notificationサービス） => iPhone

で、指示を受けたiPhoneでシステムサービスが起動し、MDMとhttpsで通信し、アプリの配信などを行う仕組みだそうだ。

今回のケースでは、MDMからの指示が届いていない模様。

しかし、インターネットから閉域網のiPhoneに向けていきなり通信が行えるはずもない。 ということで調べていくと、iPhoneは通信が可能になると、APSNに対して通信を行い、通信を確立するとAPSNとのセッションを基本的には張りっぱなしにするそうだ。

今回、キャリア側の経路の問題の可能性もあったので、そちらにも確認をお願いしていたが、そちらでは問題がないらしく、どうやらうちの環境に起因している可能性が高そう。

ということで、iPhoneから、MDMへの通信あたりが怪しいということで、調べていくと、見事にiPhoneからインターネットの出口にあるFireWallにて、ポート5223の通信が遮断されているのが、ログから判明。

これだ。

結局のところ

FireWallの設定を変更することで、無事MDMからの指示も届くようになった。

しかし、まだ疑問は残る。なぜ、社内Wifiにつないで、モバイルデータ通信をOffにすると、MDMと通信ができたのか？

FireWallのログを丁寧に調べていくと、どうやらiPhoneは、443のポートでMDMと通信をしていた形跡が出てきた。

iOSの仕様で、「5223 で APNS に接続できなかった場合のフォールバック用」として、443で通信を行うと記載がある。
FireWallも、443のアウトバウンドは許可していたため、そのときだけMDMとの通信に成功していた。

いやでもなんで、モバイルデータ通信OFFが、フォールバックとみなすトリガーなんだよう。

原因は、結構簡単なんだけど、切り分けの調査のために、違う事象が出てきて余計に混乱したというお話でした。

余談

FireWallのログをその後見てると、5223、5224、5228、そして、10006の通信が発生している。

5224は、仕様上使わないってことになってるが、5223と同様にAppleのサーバ向けにあまりに通信してる回数が多かったので、通してみた。

5228は、Androidのプッシュ通知で使用するポートだそうだ。頻度が少ないので拒否にしてるが、実際業務上問題は発生していない（実際は何らかのエラーが起こってる？）

10006も同様。なんの通信なんだこれは。

あと、NTPの123もやりとりしている模様。iPhoneって、NITZ(Network Identify and Time Zone)って3G回線で時間調整してるんじゃなかったの？と思ったけど、NTPでも時刻調整するのね。ってことで、ここはFireWallの通信を許可した。

あと、2195-2196は、全然使ってる様子がない。どういうこっちゃねん。

公開されてる情報と実装が違う気がするのだが、そんなものなのだろうかねぇ。

C#でNPOIを使ってExcelを編集するとフォームボタンに割り当てたマクロが消える

dev ハマった

最近、社内で１人しかSQLがわからないために、日々SQLをシコシコと実行する作業に嫌気がさして、 誰でもできるようにWindowsフォームアプリをC#で開発して、運用作業を他の人に振ることにした。

で、DBから抜いた値をExcelに取り込む → ユーザーに渡してユーザー側で編集 → 編集後ExcelをもらってDBの値を洗い替えるって、運用作業があるんですが、

Excel上のマクロを割り当てたフォームボタンが、いざ、NPOIを通して出力するとマクロの割り当てが初期化されるという事象にめぐり当たった。

これが

f:id:ryoben:20180428103139p:plain

↓

こう

f:id:ryoben:20180428103144p:plain

色々と調べてStackOverFlowにも投稿があったのだが、NPOIの仕様で、Excelに直接貼り付けたフォームオブジェクトのマクロは消えるから、フォームにボタン貼り付けたりしようね。

とか、書いてた。

うーん、しかし、ボタンでさくっとマクロを起動したいのですよ。

色々と悩んだ挙句、Excelオープン時に、ボタンにマクロを割り当てるマクロを起動することで、とりあえず難を逃れることに。

Private Sub Auto_Open()
    ActiveSheet.Shapes.Range(Array("ボタン 1")).Select
    Selection.OnAction = "InputCheck"
End Sub

なんかもやっとするよね。

ちなみに、拡張子xmlのファイルだと、普通にボタンに割り当てたマクロはそのままになってたりした。

WindowsでJRubyでsinatraでRest API

やってみた dev

既存システムとのリアルタイム連携

既存システムのリプレースにあたり、販売管理システムの在庫データをリアルタイムに覗きたいという要望がでてきた。

社内システムの連携は基本はファイルベースでバッチ処理で行っているのだが、それではリアルタイムという要件に合致しない。

なので、ここは、販売管理システムにAPI生やすのが良さそうだな。と考え至る。

販売管理システムの開発・保守してるベンダーにその旨を告げると、

「レスト？」「エーピーアイ？」

あぁ、ダメだ。人のこと言えないけど、これは、無理だ。

自分で作るか。ならば、Ruby、キミに決めた！

ただ、単純にSQLでSELECTして返すだけなので、自分でAPI作る方が、まだましか。お金かからんし。 とりあえず、動けばいいし、問題が出たらそん時考えるべ。ということで、自分のスキル的にはRubyでSinatraでちゃちゃっとプロトタイプ作ってみよう。と思ってみたが、

あ、うちの会社OS Windows縛りあるわ。

Windows Server で Rubyのサービス動かすのは、地雷源をコサックダンスしながら通り抜けるようなものだ。

という程度の認識はあったので、悩む。うーん。。。

帰り道、電車の窓から外を眺めたら、一羽の鳥が。

f:id:ryoben:20171223161432p:plain

「はっ、その手があったか！」

Windows上で、JRubyでSinatraまで

前置き長かったが、WindowsでJavaのWebアプリなら普通に動くだろ。ということで、JRuby試してみる。 JRuby触ったこと無いから、色々検索してみるが、欲しい情報はなかなかないので、手探りでやることに。 なるべく、CRubyを操作するのと同じ流れを意識する。

事前準備
JDKをインストール
そもそも入れてたので、不要だったが。バージョンは既存のシステムの影響でJDK7（古っ）

Jrubyをインストール
Jrubyのサイトから JRuby 9.1.15.0 Windows Executable (x64) を選択してインストール。

bundler をインストール

 jruby -S gem install bundler

jrubyフォルダを作成し、移動して、Gemfile作成。

cd jruby

jruby -S bundle init

Gemfile を開いてsinatraを追加

 gem "sinatra"

bundle install

jruby -S bundle install --path vendor/bundle

app.rbを新規作成

require 'sinatra'

get '/' do
  'Hello Sinatra on Jruby'
end

ここで、 jruby -S bundle exec jruby app.rb

で動くかと想いきや、エラーにもならず、沈黙するのみ。 ので、rackupで動かすことに。

config.ru を新規作成

require 'sinatra'
require File.expand_path '../app.rb', __FILE__

run Sinatra::Application

起動

jruby -S bundle exec rackup

動いた。

f:id:ryoben:20171225141932p:plain

DBに接続する *1

MSのサイトからjdbcのドライバを落としてきて、解凍して中のjarファイルをコピってくる。

JDKのバージョンによって、２種類あるので、自分の環境に適した方のファイルをjrubyフォルダ直下にコピる（JDK9はないみたい・・・）

mssql-jdbc-6.2.2.jre7.jar
mssql-jdbc-6.2.2.jre8.jar

Gemfileに追記

gem "activerecord-jdbcmssql-adapter"

bundle install

jruby -S bundle install

動作検証も兼ねてapp.rb にDB接続のコードをハードコーディング。

app.rb 全書き換え

require 'activerecord-jdbcmssql-adapter'
require './mssql-jdbc-6.2.2.jre7.jar'

# define hash of connection properties
config = {
  url: 'jdbc:sqlserver://192.168.0.1\インスタンス名;databaseName=データベース名',
  adapter: 'jdbc',
  username: 'ユーザー名',
  password: 'パスワード',
  driver: 'com.microsoft.sqlserver.jdbc.SQLServerDriver',
}

# establish connection
ActiveRecord::Base.establish_connection( config )

# define model and inherit from ActiveRecord Base
class Stock < ActiveRecord::Base
  # override table name as necessary
  self.table_name = 'M_STOCK' #テーブル名
end

get '/' do
# query model table, examples:
  stock = Stock.first #何も考えず１件取得
  "倉庫コード：" + stock.WH_CODE + "  数量：" + stock.STOCK_QT.to_s
end

で、起動して、アクセスして、テーブルの値が取れてればOK。
f:id:ryoben:20171225234222p:plain

データ取れた。

接続設定を外部ファイルに切り出したり、ロジックをmodelに切り出したりは、やっとく。

API設計する

ココらへん読んで、URLのエンドポイントについては/stock とし、条件はパラメータとして設定する。 （バージョンはとりあえず、含めてない）
今回でいうと、倉庫と商品コードという条件がパラメータがあるので、以下のようなイメージとなる

localhost:9292/stocks?shop=999999&product=1755

Jsonについては、データを配列として返す。
検索結果の件数とかは、Jsonに書くのではなく、HTTPレスポンスヘッダーに書くのが流行りらしいのでそうする。
sinatraでは、以下のようにセットするとレスポンスヘッダーを返してくれる。

response.headers['X-Stock-Count'] = stock_count.to_s

Jsonの描画については、DBのカラム名とJson上の定義名にギャップがあるので、to_jsonなどは使わず、viewsフォルダの配下にerbファイルを作成して値をセットするようにした。

エラーについては、ココらへんにある通り、httpのステータスコードを設定して返す。さらに、Json上でもシンプルなメッセージをセットして返すことにした。

スロットリングは、社内でしか使わない仕組みなので今のとこ対応しない。

warに固めてTomcat上で動かす

まず、この時点でのフォルダ構成はこんな感じになってる。

jruby
├app
│ └app.rb  ・・・main処理
│   
├config
│ └database.yml ・・・データベースの接続情報を外出し
│   
├lib
│ └mssql-jdbc-6.2.2.jre7.jar  ・・・sqlserver用のjdbcドライバ
│   
├log  ・・・ アプリケーションログ用フォルダ
│   
├models
│ └stock.rb ・・・ビジネスロジックを記述
│   
├vendor 
├views
│ ├errors.erb ・・・ エラーを返すJsonのテンプレート
│ └stocks.erb ・・・ stockを返すJsonのテンプレート
│   
├config.ru ・・・ 起動用スクリプト
├Gemgile
└Gemfile.lock

DBの接続情報は外出しして、環境によってDev環境かProductionかを自動で変わるようにした。

で、warblerを使って、warファイルを作成していく。

Gemfileに追加

gem "warbler"

bundle install実行

jruby -S bundle install

完了後、warblerのconfigファイルを作成する。

jruby -S bundle exec warble config

デフォルトで config/warble.rb が作成されるので、これを編集する。以下の記述を追加

  config.dirs = %w(app config lib views models log) #warに含むフォルダを指定。gemファイルのフォルダは不要
  config.bundle_without = []  # bundler用のおまじない

war作成

jruby -S bundle exec warble

で、実行フォルダに、jruby.war （フォルダ名.war）が作成される。

次にTomcatをダウンロード＆インストール。
公式サイトから、32-bit/64-bit Windows Service Installer を選択してダウンロード。 今回は、Java7なので、8.x系を選択。 インストールは、exeを実行して、次へ次へで。

インストール完了後、管理画面を使うために、conf/tomcat-users.xml を編集。以下の記載を追記して、TOMCAT再起動。

  <role rolename="manager-gui"/>
  <user username="tomcat" password="tomcat" roles="manager-gui"/>

http://localhost:8080 にアクセスして、Manager Appを選択し、でID/Passに先ほど設定ファイルに追記した 値 を入力する。
デプロイ画面に遷移するので、war ファイルを選択し、アップロード＆配備ボタン実行！

そして、いざアクセス
http://localhost:8080/jruby/stocks?shop=999999&product=2075

にブラウザでアクセスすると、以下のJsonがちゃんと返ってきた。

{
  "shop": 999999,
  "stocks": [
    {
      "product": "207554013",
      "color": "47",
      "size": "38",
      "count": 1
    },
    {
      "product": "207554013",
      "color": "47",
      "size": "40",
      "count": 1
    }
  ]
}

レスポンスヘッダーにも、ちゃんと件数が表示されてた。

Content-Length   1608
Content-Type    application/json
Date    Wed, 10 Jan 2018 09:43:21 GMT
X-Content-Type-Options  nosniff
X-Stock-Count   2

めでたしめでたし

*1:参照 http://ericlondon.com/2014/06/10/jruby-connect-to-a-mssql-database-via-activerecord-and-create-activerecord-base-classes.html