COCOAについて

こんにちは。
今回は新型コロナウィルス接触感染アプリCOCOAについて調べました。

COCOAとは?

日本の厚生労働省が提供するスマートフォン向けCOVID-19アプリである。
略称はCOCOA。
濃厚接触の疑いのある本アプリ利用者間の接触をBluetoothによって検知・記録し、接触者から新型コロナウイルスの陽性者が発生したときに、その旨を通知する。アプリの通知を受けた利用者は、自主的な隔離生活や、医療機関への受診を各自で検討できるようになる。

仕組みについて

COCOAは、接触を確認する仕組みとしてBLE(BluetoothLow Energy)を使います。COCOAはBLEを使って定期的に近くのデバイスに信号を送り、信号の強さからお互いの距離を推測します。この距離を推測する機能はBLEの一部として既に実装されています。そして、1m以内に15分以上接近したデバイスとID(接触符号)を交換します。接触符号は、1日に一度ランダムに生成される日次鍵から(ハッシュ関数を用いて)生成されます。

※BLEについて
近距離無線通信技術Bluetoothの拡張仕様の一つで、極低電力で通信が可能なもの。
2010年7月に発表されたBluetooth 4.0規格の一部として策定されたものです。

調べて思った事

様々な記事を見たのですが接触通知があったからといって検査が受けられる訳ではないようです…地域、県によっては受けられる所もあるようです。
濃厚接触していたとしても症状が出ないと受けられないようです。
この事についてはなんで受けられないのだろうと考えたのですが症状が出てからではないと陽性と反応されないのではと考えました。

使用感について

個人的に思った事は、不具合は多いが入れていて損のないアプリだと思います。
アプリ入れてる人はまだあまり多くはないと思うが医療機関やイベント会場など様々なところで呼びかける事により沢山の人に使用されていくアプリになるのではないかと思います。

今回は、この辺で失礼いたします。
最後まで閲覧いただきありがとうございます。

iOS14について

こんにちは。白瀬です。
 つい先日、米国時間9月16日にリリースされたios14についてまとめてみました。

<対応機種について>

  • iPhone 6s ~ iPhone 11(Pro,ProMax,Plusを含む)
  • iPhone SE(1世代,2世代)
  • iPod touch(7世代)

<新機能について>

  • ウィジェット

 今まで一番左側にあったウィジェットをAndroidのようにホーム画面の好きな位置に置けるようになりました。また、時間帯によって自動で切り替わるウィジェットもホーム画面に置くことが可能になりました。

  • Appライブラリ

ホーム画面の最後のページにダウンロードした全てのアプリが表示するAppライブラリの追加。

 目的のアプリをシンプルな操作で探しやすいようにiOSが自動でカテゴライズしてフォルダに収納されます。フォルダ内のアプリでも、よく利用するアプリはワンタップで起動可能。アルファベット順および50音順に並べたリストから探したり、検索ワードで探すことも可能です。
 また、ホーム画面の特定ページを非表示にすることも可能になりました。

  • ピクチャ・イン・ピクチャ

 ビデオ通話のFaceTimeや動画を見ながら他のアプリを操作可能になりました。

  • 翻訳アプリ

 日本語を含む11言語のテキスト入力と音声入力に対応。iPhoneを横に向けると会話モードに移行し、言語を自動検出して会話内容をリアルタイムに翻訳し、オンライン機能も備えているため、ネット環境がない場所や重要な会話でもiPhoneで翻訳可能になりました。

  • CarKey

iPhoneとAppleWatchが車の鍵になる新機能。iPhoneをドアノブに近づけるだけでロックを解除できます。そのまま車のリーダーかワイヤレス充電器に乗せるだけでエンジンもかけられるため、自宅に車の鍵を忘れても安心。

鍵はiMessageを使って友だちや家族とも共有できるため、車の鍵を直接会って渡さなくても運転できます。紛失した場合はiCloud経由で鍵を無効にすることも。iPhoneのバッテリーが切れたあとも最大5時間動作します。
まずは今年中にNFCを使ったCar Keyの提供がスタート。はじめに来月発売(現在2020年9月)のBMW5シリーズが対応します。来年にはU1チップを搭載したiPhoneでポケットやカバンから取り出さずにロックを解除できるようになります。

  • AirPods

新機能「自動切り替え」では、利用しているデバイスに合わせて接続先が自動で切り替わります。例えばiPhoneで通話を終えてMacでテレビ会議に参加するとiPhoneからMacに自動で接続先が切り替わり、Macでテレビ会議を終えてiPadで映画を見ると自動でMacからiPadに接続先が変わります。

最大のアップデートは、上下左右前後から音を飛ばして臨場感のあるサウンドを実現する「空間オーディオ」と「ダイナミックヘッドトラッキング」の追加です。

声や音が飛んでくる方向に横に向けると、通常は音の出力位置も変わるため何も変わりませんが、ダイナミックヘッドトラッキングでは顔を横に向けると音が自分に向かって飛んでくるため、リアルなサウンドを楽しめるようになります。

※この機能は『AirPods Pro』のみ利用可能。

ダイナミックヘッドトラッキングについては説明がとても難しいため是非、ご自身で体験してみてください。

そのほかに追加された機能もありますので気になった方はぜひ、チェックしてみてくださいね。

今回は、この辺で。
最後まで閲覧いただきありがとうございます。

AWS Lambdaを使った外形監視

福岡拠点の野田です。
AWSを使ったサーバー構築が最近増えてきました。今回は、AWS Lambdaを使った外形監視の方法を紹介しようと思います。

AWS Lambdaは、軽量なアプリケーションを動作するときに活躍するサービスです。5分おきのバッチサービス、管理画面でのSSRサービスなど、 常に常駐しておく必要がないプログラムを動かすのに適しています。 今回紹介する監視系のバッチもLambdaと相性のよいアプリケーションの1つとして考えています。

AWS Lambdaを使った外形監視の作成を以下の手順ですすめていきます。

  1. 関数の作成
  2. 関数コードの作成
  3. トリガーの追加

関数の作成

「1から作成」を選択します。関数名は「check_http」のような名前を付けます。ランタイムは、javascriptサーバーの「node.js12.x」を選択して関数を作成します。

アクセス権限については、「基本的な Lambda アクセス権限で新しいロールを作成」を選ぶとAmazon CloudWatch Logsの権限が付与されるので、これを選びます。

関数コードの作成

index.jsに以下をコピペします。

function getNativeRequest(url) {
  const https = require('https')
  const options = {timeout: 1000};
  const promise = new Promise(function(resolve, reject) {
    const req = https.get(url, options, (res) => {
        resolve(res.statusCode)
      }).on('error', (e) => {
        reject(Error(e))
      }).on('timeout', () => {
        reject("Timeout Error")
      })
  })
  return promise
};

exports.handler = async (event, context, callback) => {
  const url = process.env['ENV_URL'];
  return getNativeRequest(url);
};

ページ末尾の環境変数に以下のような監視対象URLを指定します。

ENV_URL https://(監視対象URL)

トリガーの 追加

EventBridge(CloudWatchEvents)を追加します。
ルールは、新規ルールでルール名を「every5min」を指定します。ルールタイプはスケジュール式で以下を設定します。

説明: 5分毎
イベントバス: default
スケジュール式: cron(0/5 * * * ? *)

あとは、CloudWatch側で以下のようなアラートルールを設定することで監視完了です。

5 分内の1データポイントのErrors > 0

Lambdaはいろいろな使い方ができると思うので、引き続き使っていこうと思います。

[bug]マルチプロセスでlog4netのファイルローテーション時に一部ログが欠損して困った

福岡の香月です。

log4netのRollingFileAppenderを使って日付で出力ファイルが切り替わる設定をしていたところ、日付が変わって新しいファイルへの出力が始まると先頭に出力されるであろうログがなぜか出力されない現象に遭遇しました。
設定はこうです。
複数のプロセスで同じ設定を用いて、同じファイルに対してログ出力します。

<appender name="SystemLogAppender" type="log4net.Appender.RollingFileAppender">
  <file value="log/system.log" />
  <appendToFile value="true" />
  <datePattern value="_yyyyMMdd" />
  <preserveLogFileNameExtension value="true" />
  <staticLogFileName value="false" />
  <lockingModel type="log4net.Appender.FileAppender+InterProcessLock" />
  <encoding value="Shift_JIS"/>
  <maxSizeRollBackups value="-1" />
  <maximumFileSize value="2GB" />
  <layout type="log4net.Layout.PatternLayout">
    <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss.fff} %-5p %m%n"/>
  </layout>
</appender>

AppenderはRollingFileAppenderを指定しています。複数プロセスで使用するため、lockingModelはInterProcessLockを指定します。これによりファイルストリームを開きっぱなしにして、mutexで排他制御しながら末端にシークし書き込みを行うことで、複数プロセスから効率的にログ出力を行ってくれます。
複数プロセスから使う場合はこれ以外にもMinimalLockを指定できますが、書き込むたびにファイルのオープンクローズを実施するため速度が求められる場合は不向きでしょう。さらに複数プロセスからの出力が同時に走った場合、片方はオープンできないためログが出力されません。

この設定でビルドし、業務終わりにアプリを起動して一晩中実行させ、翌朝出社して確認しても日付が変わった後のログの内容が足りませんでした。
最初はlog4netの使い方が悪いのか、自分のプログラムにバグがあるのかさんざん悩んだんですがさっぱりわからない。トレース用に埋め込んだログも当然のように出ていないのでどうしたものかと思いました。

しかし、プロセスが1つの場合はちゃんと期待通りのログが出ていました。問題があるのは複数プロセスの場合だけだったのです。

そこでlog4netのロジックを確認することにしました。 幸いなことにソースはこちらから取得できます。
https://github.com/apache/logging-log4net
すると、ファイルが切り替わったあとは最後に書き込んだプロセスのログが先頭に来て、それ以前のログが破棄される作りになっていたのです。

少しずつソースを見てみましょう。今回使っているのはRollingFileAppenderです。日付が変わるときにファイルを切り替える部分は、RollOverTime()という関数であることがわかりました。こうなっています。
https://github.com/apache/logging-log4net/blob/master/src/Appender/RollingFileAppender.cs

protected void RollOverTime(bool fileIsOpen) 
{
    :
    :
    if (fileIsOpen)
    {
        // This will also close the file. This is OK since multiple close operations are safe.
        SafeOpenFile(m_baseFileName, false);
    }
}

ふむふむ、SafeOpenFile()で新しいファイルをオープンしに行くわけね。そして第二引数がfalseと。そしてSaveOpenFile()の実態はベースクラスFileAppenderにありました。
https://github.com/apache/logging-log4net/blob/master/src/Appender/FileAppender.cs
ここから次の順番で呼び出されていきます。

  • RollingFileAppender.OpenFile()
  • FileAppender.OpenFile()
  • FileAppender.InterProcessLock.OpenFile()
  • FileAppender.LockingModelBase.CreateStream()
protected Stream CreateStream(string filename, bool append, FileShare fileShare)
{
	using (CurrentAppender.SecurityContext.Impersonate(this))
	{
		// Ensure that the directory structure exists
		string directoryFullName = Path.GetDirectoryName(filename);

		// Only create the directory if it does not exist
		// doing this check here resolves some permissions failures
		if (!Directory.Exists(directoryFullName))
		{
			Directory.CreateDirectory(directoryFullName);
		}

		FileMode fileOpenMode = append ? FileMode.Append : FileMode.Create;
		return new FileStream(filename, fileOpenMode, FileAccess.Write, fileShare);
	}
}

ここでSafeOpenFile()の第二引数で指定されたfalseは、CreateStreamのappendに代入されていることがわかりました。この場合FileMode.Createが指定され、FileStreamインスタンスが作成されます。
ではこのFileMode.Createはどのような動きをするかというと、

オペレーティング システムが新しいファイルを作成することを指定します。ファイルが既に存在する場合は上書きされます。これには Write 許可が必要です。
FileMode.Create は、ファイルが存在しない場合は CreateNew を使用した要求、ファイルが存在する場合は Truncate を使用した要求と等価です。

https://docs.microsoft.com/ja-jp/dotnet/api/system.io.filemode?view=netcore-3.1

複数のプロセスでログ出力を行う場合、FileStreamを最後に作成するプロセスによりそれまでのプロセスが作成したログファイルの中身がTruncateされて、つまり削除されてしまうわけです。
発生している現象とも合致するのでこれが問題であることを確信しました。

対応するためにはRollingFileAppender.RollOverTime()から呼んでるSafeOpenFile()の第二引数をtrueに知ればよいわけです。実際に修正して確認してみると、見事欠損することなく必要なログが出力されていました。
めでたしめでたし。

なお、SafeOpenFile()はRollingFileAppender.RollOverSize()内からも呼び出しているのでここも忘れずにtrueに変更しましょう。

S3+CloudFront+Route 53を使った静的コンテンツ配信 Part 2 (lambda@edge編)

福岡拠点の野田です。

前回、S3を使った静的コンテンツ配信を実現しましたが、ちょっとカッコ悪い点がありました。

ドメイン直下については、 Default Root Object 設定すると https://サイト名/でアクセスしたときindex.html を参照するようにできます。ただし、サブディレクトリ配下はDefault Root Objectの設定が効きません。サブディレクトリ配下で/news/とアクセスしたとき、/news/index.htmlを参照するためにはlambda@edgeを使う必要があります。

/で終わるuriの場合に/index.htmlを参照する設定について、今回は以下の流れで設定を行います。

  1. lambdaを追加
  2. CloudFrontで使えるようにIAMを修正
  3. lambdaにトリガーを追加し、CloudFrontと関連付け

lambdaを追加

リージョンus-east-1のlambda画面から関数を追加します。ほかのリージョンではCloudFrontへのトリガーを作成できないため、正しいリージョンが選択されているか確認してください。

https://console.aws.amazon.com/lambda/home?region=us-east-1

lambdaは1から作る形で進めます。

・関数名:subdir-redirect (適宜適当な名前を設定してください)
・ランタイム:nodejs (バージョンはデフォルトでOK)
・アクセス権限:AWSポリシーテンプレートから新しいロールを作成
・ロール名:cloudfront-lambda (適宜適当な名前を設定してください)
・ポリシーテンプレート:基本的なlambda@edgeのアクセス権限

関数詳細ページが表示されるので、関数コードに以下を追加します。

'use strict';
exports.handler = (event, context, callback) => {
    var request = event.Records[0].cf.request;
    request.uri = request.uri.replace(/\/$/, '\/index.html');
    return callback(null, request);
};

画面右上の「保存」を押下してコードを保存します。

CloudFrontで使えるようにIAMを修正

そのままでは使うことができないため、関数詳細ページ「アクセス権限」のタブを選択します。実行ロール「 cloudfront-lambda 」を編集し、末尾の「 IAM コンソールで cloudfront-lambda ロールを表示します。」のリンクをクリックします。

ロールの詳細ページから「信頼関係」のタブをクリックします。「信頼関係の編集」ボタンを押下して、以下のようにedgelambdaの設定を追加します。

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "lambda.amazonaws.com",
          "edgelambda.amazonaws.com"
        ]
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

これにより信頼されたエンティティにedgelambdaが追加されます。

信頼されたエンティティ
ID プロバイダー lambda.amazonaws.com
ID プロバイダー edgelambda.amazonaws.com

続いて「アクセス権限」のタブに再度戻ります。+インラインポリシーの追加から以下のポリシーを追加します。

  • Lambda: GetFunction, EnableReplication (対象リソースは、先ほど登録したsubdir-redirect lambda のARNを指定)
  • IAM: CreateServiceLinkedRole(対象リソースはすべて)
  • CloudFront: UpdateDistribution(対象リソースは、展開するCloudFrontのARNを指定)

ここまでやってようやくlambda@edgeが使えるようになります。

lambdaにトリガーを追加し、CloudFrontと関連付け

仕上げにlambdaの関数詳細画面に戻ります。画面上の「アクション」から新しいバージョンを発行します。コメントは必要あれば適宜入力してください。

そののちにデザイナーから「トリガー」を追加します。トリガーの種類は、CloudFrontを選択します。ここでCloudFrontが選択できない場合はlambdaのリージョンが間違っていますので、最初からやり直してください。設定はデフォルトのままで以下のチェックボックスにチェックを入れます。

 Lambda@Edge へのデプロイを確認 
関数のこのバージョンが上記のトリガーと関連付けられ、利用可能なすべての AWS リージョン間でレプリケートされることに同意します。 

追加ボタンを押下するとCloudFrontへの更新が入ります。Distributionの更新が終わるまでしばし待つと、晴れてサブディレクトリのリダイレクト処理を利用することができるようになります。

まとめ

lambdaというと単機能のAPIや軽量サーバーとして使うイメージが強いですが、実はいろいろなところに組み込めます。lambda@edgeを利用することでCloudFrontに対してヘッダーのカスタマイズ、 BASIC認証など多岐にわたって処理を組み込むことができます。静的コンテンツに対してちょっとした動的処理を行いたいな、というときはlambda@edgeの出番です。是非ご活用いただければと思います。

面倒なところもありますが、ひと手間かけるといろいろなことができるのがAWSの良いところ。いろいろエンジニアとしていろいろHackしていければと思います。

S3+CloudFront+Route 53を使った静的コンテンツ配信

福岡拠点の野田です。

WordPressで運用していた個人サイトをメンテしなくなったので、S3とCloudFrontとRoute 53を使って静的コンテンツ配信方式に切り替えてみました。手順の大きな流れは以下のようになります。

  1. S3にコンテンツを配置
  2. CloudFrontを設定
  3. Route53でCloudFrontへ振り分け

S3 にコンテンツを配置

まずは、wget で既存サイトを取得します。

wget --mirror --page-requisites --span-hosts --quiet --show-progress --no-parent --convert-links --no-host-directories --adjust-extension --execute robots=off (サイトURL)

日本向けに配信することを考え、 S3 の東京リージョンにて新規バケットを作成して、上記取得したファイルを配置します。

S3における設定ですが、アクセス権限の設定を行います。静的コンテンツとして公開するため、以下のバケットポリシーのブロックをオフにすることで外部からのアクセスを行えるようにします。

  • 新規のパブリックバケットポリシーまたはアクセスポイントポリシーを介して付与されたバケットとオブジェクトへのパブリックアクセスをブロックするオフ
  • 任意のパブリックバケットポリシーまたはアクセスポイントポリシーを介したバケットとオブジェクトへのパブリックアクセスとクロスアカウントアクセスをブロックするオフ

バケットポリシーは、以下のようなCloudFrontからの接続を許可する設定を行いますが、CloudFront側から設定ができるため、ひとまずスキップで大丈夫です。

{
     "Version": "2012-10-17",
     "Statement": [
         {
             "Sid": "2",
             "Effect": "Allow",
             "Principal": {
                 "AWS": "arn:aws:iam::cloudfront:user/CloudFront Origin Access Identity OAIのID"
             },
             "Action": "s3:GetObject",
             "Resource": "arn:aws:s3:::バケット名/"
         }
     ]
 }

CloudFrontを設定

Create DistributionでCDNを新規作成します。

  1. Web/RTMPの選択でWebを選択
  2. Origin Domain NameにS3のバケットを選択
  3. Origin Pathは空欄でOK
  4. Origin IDは任意のIDを設定(S3-バケット名みたいな感じで設定しました)
  5. Restrict Bucket Accessは、YES
  6. Origin Access Identityは、 Create a New Identity。
  7. Grant Read Permissions on Bucketは Yes, Update Bucket Policy (これが先ほどのS3バケットポリシーに反映されますので、一応S3側でも設定されているか確認)
  8. Viewer Protocol Policyは、Redirect HTTP to HTTPS (httpからhttpsリダイレクト)
  9. Allowed HTTP Methodsは、GET/HEADのみで対応(CORSを考えるとOPTIONSまでやってもいいかもしれません)
  10. Compress Objects Automaticallyは、true(圧縮化。転送量削減)
  11. Price Classはベストパフォーマンス
  12. AWS WAF Web ACLは、None
  13. Alternate Domain Namesは割り当てるドメイン名を改行区切りで入力。
  14. 証明書については、独自ドメインで割り当てる場合、ACMに登録したものを選択。
  15. 残りはデフォルトで登録

Distribution作成後、 GeneralタブでEditボタンを押下して、以下を設定します。

  1. Default Root Objectにindex.htmlを設定

続いて Restrictionsタブを選択して、GeoRestrictionをEditします。

今回は、日本のみを対象とします。全世界を対象とするとコストと直結します。1日1000円以上かかってもいい!どんな攻撃もどんとこい!という方以外は、対象を絞ったほうが良いと思います(私もこれで当初1日放置して1000円かかってしまい冷や汗、急遽制限を追加しました)。

Route53でCloudFrontへ振り分け

仕上げにRoute53からCloudFrontへ振り分けします。A(IPv4アドレス)およびAAAA(IPv6アドレス)のエイリアス指定でCloudFrontにつなげることができます。

まとめ

Cloudは設定をミスると高額な請求が発生してしまうリスクはありますが、うまく使えば個人で使っても安く運用することができます。最近では予算設定や請求が高額になりそうなときにアラートも出せる機能もありますので、そうしたものを組み合わせて、安全に運用すると良いと思います。先月からの運用の感じだとアクセス数次第なところがありますが、100円~300円/月ぐらいで運用できそうな感じでした。

初心者にはおすすめはしませんが、興味ある方は是非チャレンジしてみてください。

何度も同じフォーマットをコピペして編集したくない人の為に

沖縄拠点の久保田です。

何度も同じフォーマットをコピペして編集したくない人向けのテクニックを紹介したいと思います。

用意するソフトウェアは以下となります。

■必要な環境
Microsoft Excel 2016

まずは完成形から見ていきましょう。

※完成図

  1. [@雛形]セルの1つ下のセルに雛形を書きます。置き換えた対象となる部分に[@01]や[@02]のように設定します。
  2. [@値]セルの1つ下のセルに雛形で使用するパラメータ[@01]や[@02]を設定します。行を増やしながら埋め込みたい値を設定します。
  3. [実行]ボタンを押下後、クリップボードに出力結果が設定されます。

■クリップボードへの出力結果

$param = array(
    "corp"    => "06",
    "account" => "19000001",
    "id"      => "487012345",
    "phoneNo" => "0746669999",
    "pay" => "1",
    "verify"  => "6654"
);
$param = array(
    "corp"    => "06",
    "account" => "19000002",
    "id"      => "487012346",
    "phoneNo" => "0746670000",
    "pay" => "2",
    "verify"  => "7777"
);
$param = array(
    "corp"    => "39",
    "account" => "19000006",
    "id"      => "487012350",
    "phoneNo" => "0746670004",
    "pay" => "1",
    "verify"  => "1111"
);

こんな感じのツールです。

何故このツールを作ろうと思ったのか

何故このツールを作ろうと思った経緯ですが、getter や setter を大量に半手動で作らないといけない事があり(今では便利なプラグインがあるのですが、)さらに、コメントも決まったフォーマットで書かないといけないという事がありました。

この時はインターネットにも接続できない環境で、必要なプラグインの入手までに時間がかかりました。(プラグインやツールの申請に1週間程かかりました)

例えば↓のような場合です。

  /** 画面ID */
  private String screenId;

  /**
   * 画面IDを取得します。
   * @return 画面ID
   */
  public String getScreenId() {
    return screenId;
  }

  /**
   * 画面IDを設定します。
   * @param screenId 画面ID
   */
  public void setScreenId(String screenId) {
    this.screenId = screenId;
  }

あー、もうこんなの手動で書くなんて信じられませんね。

getter, setter までは自動で出力してくれますが、コメントの形式までプロジェクトで決められていて、IDEの内包ツールではそこまで対応していませんでした。
手動でなんてやってたら時間かかるしミスは出るしで良い事なんて1つもありません。

そこで限られた環境で効率化を図る事にしたのです。

実際に作ってみよう

と、いうわけでここからは本ツールの作成方法を書きたいと思います。

※[開発]タブを使用しますので、表示されていない方は 、[ファイル] → [オプション] → [リボンのユーザー設定] の[リボンのユーザ設定]の[開発]チェックボックスをオンにして、事前に [開発]タブ を表示して下さい。

まず、下のような感じでシートを作成します。

[開発]タブ → 挿入 → フォームコントロール[ボタン]を選択して、任意の場所にボタンを配置します。

ボタンを配置するとマクロの登録画面になりますので、何も変更せず[新規作成]ボタンを押して下さい。

[ Visual Basic Editor ]が起動しますので、以下のソースを貼り付けて保存して下さい。
私の環境では Subプロシージャ [ボタン10_Click()]ですので、自身の環境に合わせて修正して下さい。

Sub ボタン10_Click()

    Dim Rng As Range
    Set Rng = Cells.Find(What:="@値", LookIn:=xlValues, LookAt:=xlWhole)
    If Rng Is Nothing Then
        MsgBox "@値セルが存在しません。"
    End If
    
    Dim tempRng As Range
    Set tempRng = Cells.Find(What:="@雛形", LookIn:=xlValues, LookAt:=xlWhole)
    If tempRng Is Nothing Then
        MsgBox "@雛形セルが存在しません。"
    End If
    
    Dim valueRow As Integer
    Dim valueCol As Integer
    
    valueRow = Rng.Row
    valueCol = Rng.Column
    
    If Not Cells(valueRow + 1, valueCol).Value = "@01" Then
        MsgBox "@値セルの直下に@01セルを定義してください。"
        Exit Sub
    End If
    
    valueRow = valueRow + 1
    
    Dim cValueArray As New Collection
    
    Do
        If Cells(valueRow, valueCol) = "" Then
            Exit Do
        End If
    
        cValueArray.Add Cells(valueRow, valueCol)
    
        valueCol = valueCol + 1
    Loop
    
    valueRow = valueRow + 1
    
    Dim result As String
    Dim tempStr As String
    Dim tempFlg As Boolean
    
    Do
        valueCol = Rng.Column - 1

        tempFlg = False
        
        ' 値存在チェック
        For i = 1 To cValueArray.Count
            If Not Cells(valueRow, valueCol + i) = "" Then
               tempFlg = True
               Exit For
            End If
        Next
        
        ' 値が無い場合終了
        If Not tempFlg Then
            Exit Do
        End If
        
        tempStr = Cells(tempRng.Row + 1, tempRng.Column).Value
        
        ' templateを置換文字で実行
        For i = 1 To cValueArray.Count
            tempStr = Replace(tempStr, cValueArray(i), Cells(valueRow, valueCol + i))
        Next
        
        ' 置換後の文字列を
        result = result + tempStr + vbLf
        
        ' 次の行へ移動
        valueRow = valueRow + 1
    Loop
    
    result = Replace(result, vbLf, vbCrLf)
    
    Dim CB As New DataObject
    With CB
        .SetText result
        .PutInClipboard
    End With
    
    MsgBox "実行結果をクリップボードへコピーしました。"
End Sub

[ Visual Basic Editor ] 画面 を閉じます。
では、先ほど配置したボタンを押してみましょう。メッセージボックスに「実行結果をクリップボードへコピーしました。」と表示されれば完成です。テキストエディター等に貼り付けして出力結果を確認して下さい。

■メモ帳に貼り付け結果

シートをコピーして増やすことで、雛形を多数準備する事も可能です。

作成にはVBAの知識が少し必要ですが、[マクロの記録]で必要なコードを収集しながら作成する事も可能ですので、是非VBAを使っていろいろ作成してみて下さい。

便利な使い方

便利な使い方をするには、まずインプットデータを本ツールにあまり加工せずに貼り付けできる状態が好ましいです。

例えばテーブル定義書です。

テーブル定義書のカラムの 物理名と論理名を使用して、コードとコメントを書く時に役に立ったりします。

エクセルなので当然セル内に関数を埋めこんだり、別シートのセルを参照したりできるわけで、それだけでも利用方法が広がると思います。

いろいろ工夫して使ってみて下さい。

<免責事項と注意事項>

本ページの内容について

  • 発生した一切の損害(機会の損失、結果的損害等を含む)について、いかなる責任も負いません。
  • ご利用は各個人での私的な使用に限るものとし、商用又は、営利を目的として、また公共の場での使用を一切禁止します。

さくらクラウドVPCルータ(サイト間VPN)とOpenVPNを利用したVPN拠点の構築

昨今、コロナ禍により業務を遂行するにあたり、テレワークを余儀なくされてます。

社内のNASやら、運用・保守業務で「IPアドレス制限されている」サーバにアクセスするために、会社ルータ(RTX-1210)にVPN接続をすることが多々発生しています。

そうした中、RTX-1210にVPN(L2TP)接続ができないというスタッフが続々と発生するという問題が顕著化しました。(以前よりそういった問題がちょくちょくありましたが、特に業務への影響は軽微であったことから、放置していました。)

ログ、コンフィグなどを見直して、ある程度あたりはつけましたが、業務に影響がでるかもしれない作業を実施する時間的余裕もないのと、本当にそれで問題が解消する保証もないのと、別件で使ってる拠点間接続だと、通信が安定しているという事もあり、 外部にVPNサーバを立てて、そいつと、会社のRTX-1210を拠点間接続でつないでしまえばいいのじゃなかろうかと思い立ったが吉事。さっそくやってみました。

さくらクラウドのVPCルータにもVPN(L2TP)サービスがあるので、最初これが使えるか?と試したのですが、VPCルータから全パケットを拠点間接続の接続先に転送するルーティングを入れると、VPN(L2TP)サービスに接続できなくなってしまうという問題(というかあたりまえなんですが。。)があり、以下のようなネットワークを作ってみました。

簡単に説明すると、さくらVPCルータを拠点間接続だけに利用して、VPNサーバは別にサーバを立てて、VPNクライアントから接続されたパケットはすべて、VPCルータ経由・拠点間VPN接続を得て会社のRTX-1210から出ていくルーティングを設定するというものです。

つまり、以下のような通信ラインを確保しようという試みです。

以下構築の手順メモ

OpenVPNを設定する

【設定前提情報】

ローカルスイッチネットワーク情報: 192.168.100.0/23
接続先スイッチ: local-switch(1)
サーバのNetwork: eth0 (Global側) 30.30.30.1
サーバのNetwork: eth1 (Loal側) 192.168.100.2
OS: CentOS 7.6

【手順】

STEP1) 必要パッケージをインストールする

yum -y update
yum install epel-release -y
yum --enablerepo=epel -y install openvpn easy-rsa

STEP2) 各種設定準備を行う
※パスワード、CAのcommonnameは好きな値を設定の事

mkdir /etc/openvpn/easy-rsa
cp /usr/share/easy-rsa/3.0.7/* /etc/openvpn/easy-rsa/ -R

cd /etc/openvpn/easy-rsa

./easyrsa init-pki
./easyrsa build-ca
./easyrsa gen-dh
./easyrsa build-server-full server_r nopass
./easyrsa build-client-full client1 nopass
openvpn --genkey --secret /etc/openvpn/ta.key
cd /etc/openvpn/
cp /usr/share/doc/openvpn*/sample/sample-config-files/server.conf server_r.conf

STEP3) server_r.confの設定を行う

vi /etc/openvpn/server_r.conf

デフォルト値から以下を変更する

ca /etc/openvpn/easy-rsa/pki/ca.crt
cert /etc/openvpn/easy-rsa/pki/issued/server_r.crt
key /etc/openvpn/easy-rsa/pki/private/server_r.key  # This file should be kept secret
dh /etc/openvpn/easy-rsa/pki/dh.pem
push "route 192.168.0.0 255.255.255.0"
push "redirect-gateway def1 bypass-dhcp"
comp-lzo
user nobody
group nobody
status /var/log/openvpn-status.log
log         /var/log/openvpn.log
log-append  /var/log/openvpn.log

STEP4) net.ipv4.ip_forward の設定を行う

vi /etc/sysctl.d/10-ipv4_forward.conf

net.ipv4.ip_forward = 1

sysctl --system

STEP5) iptablesの設定を行う

yum -y install iptables-services
systemctl disable firewalld
systemctl stop firewalld
systemctl enabled iptables
systemctl start iptables

vi /etc/sysconfig/iptables

*filter
 :INPUT ACCEPT [0:0]
 :FORWARD ACCEPT [0:0]
 :OUTPUT ACCEPT [6:813]
 -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
 -A INPUT -p icmp -j ACCEPT
 -A INPUT -i lo -j ACCEPT
 -A INPUT -p udp -m udp --dport 1194 -j ACCEPT
 -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
 -A INPUT -j REJECT --reject-with icmp-host-prohibited
 -A FORWARD -d 192.168.100.0/23 -i tun+ -j ACCEPT
 -A FORWARD -o eth0 -m state --state NEW -j ACCEPT
 -A FORWARD -o eth1 -m state --state NEW -j ACCEPT
 -A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
 -A FORWARD -j REJECT --reject-with icmp-host-prohibited
 COMMIT
 *nat
 :PREROUTING ACCEPT [51:3492]
 :INPUT ACCEPT [0:0]
 :OUTPUT ACCEPT [2:152]
 :POSTROUTING ACCEPT [3:212]
 -A POSTROUTING -s 10.8.0.0/24 -o eth1 -j MASQUERADE
 COMMIT

※ボールドの部分がポイント

systemctl restart iptables

STEP6) OpenVPN起動

systemctl enable openvpn@server_r
systemctl start openvpn@server_r

STEP7) ルーティングの設定

ip rule add from 10.8.0.0/24 table 100 prio 100


ip route add 10.8.0.0/24 dev eth0 proto kernel scope link metric 100 table 100

ip route add default via 192.168.100.1 table 100

※このルーティングがポイント(10.8.0.0/24からのパケットは全部、192.168.100.1へルーティングするという内容を用意する)

STEP7) ルーティングの永続化

※設定方法確認中
 とりあえずいまは起動時にスクリプトで実行させておく。

STEP8) クライアントで利用するcert / keyは以下ファイルを利用する

/etc/openvpn/easy-rsa/pki/ca.crt
/etc/openvpn/ta.key
/etc/openvpn/easy-rsa/pki/issued/client1.crt
/etc/openvpn/easy-rsa/pki/private/client1.key

.ovpnファイルを作ると接続設定するのが楽になります。
以下のようなファイルを作る

client
 dev             tun
 proto           udp
 remote          30.30.30.1 1194
 resolv-retry infinite
 nobind
 persist-key
 persist-tun
 remote-cert-tls server
 tls-client
 key-direction 1
 cipher AES-256-CBC
 comp-lzo
 verb 3
<ca>
 ここに、「/etc/openvpn/easy-rsa/pki/ca.crt」の内容を張り付ける 
</ca>
<key>
 ここに、「/etc/openvpn/easy-rsa/pki/private/client1.key」の内容を張り付ける 
</key>
<cert>
 ここに、「/etc/openvpn/easy-rsa/pki/issued/client1.crt」の内容を張り付ける 
</cert>
<tls-auth>
 ここに、「/etc/openvpn/ta.key」の内容を張り付ける 
</tls-auth>

さくらクラウド VPCを利用した「拠点間VPN接続」の設定を行う。

さくらクラウドVPCの設定を行う

■さくらクラウド ネットワーク情報

【設定前提情報】
ローカルスイッチネットワーク情報: 192.168.100.0/23
接続先スイッチ: local-switch(1)
VPCルーター(インターフェース:プライベート1)に割り当てるIP: 192.168.100.1
対向IPアドレス: 10.10.10.1
対向ID: 192.168.100.1
Pre Shared Secret: unko123
対向Prefix: 0.0.0.0/0
ローカルPrefix: 192.168.100.0/23

【手順】

STEP1) VPCルーターを追加する
    プラン: スタンダード
    インターネット接続: 有効
    そのほかの項目は任意で入力する
    
STEP2) VPCルーターのインターフェースの設定
    スイッチ: 既存スイッチを接続
    接続先スイッチ: local-switch(1)
    IPアドレス: 192.168.100.1
    プレフィックス: /23

STEP3) サイト間VPNの設定
    対向IPアドレス: 10.10.10.1
    対向ID: 192.168.100.1
    Pre Shared Secret: unko123
    対向Prefix: 0.0.0.0/0
    ローカルPrefix: 192.168.100.0/23

STEP4) 「反映」を行う

RTX-1210側の設定を行う

【設定前提情報】
■RTX-1210ネットワーク情報
Global-IP:  10.10.10.1
LAN-IP: 172.16.100.1 (NetworkAddress: 172.16.100.0/23)
■さくらクラウド VPC(サイト間VPN設定情報)
Global-IP: 20.20.20.1
対向IPアドレス: 10.10.10.1
対向ID: 192.168.100.1
Pre Shared Secret: unko123
対向Prefix: 0.0.0.0/0
ローカルPrefix: 192.168.100.0/23

【手順】

★RTX-1210側への設定手順
参考: https://knowledge.sakura.ad.jp/7051/

1) RTX-1210のGUIツールにて、拠点間VPNの設定を行う。 (簡単設定→VPN→拠点間接続)
 接続種別の選択: IPSec
 ネットワーク環境: 自分側と接続先の両方とも固定のグローバルアドレスまたはネットボランチDNSホスト名を持っている
 自分側の設定(設定名): SAKURA VPN
 接続先の情報(接続先のホスト名またはIPアドレス): 10.10.10.1
接続先と合わせる設定(認証鍵 (pre-shared key)): unko123
接続先と合わせる設定(認証アルゴリズム): HMAC-SHA
接続先と合わせる設定(暗号アルゴリズム): AES-CBC
 経路に関する設定: 接続先のLAN側のアドレス
192.168.100.0 / 255.255.254.0(23bit)
10.8.0.0 / 255.255.254.0(24bit)
2) CONFIGを取得して、「description tunnel “SAKURA VPN”」の設定部分を探して、設定情報を変更する

※例えば、

tunnel select 16
 description tunnel "SAKURA VPN"
 ipsec tunnel 9
  ipsec sa policy 9 9 esp aes-cbc sha-hmac
  ipsec ike keepalive log 9 off
  ipsec ike keepalive use 9 on heartbeat 10 6
  ipsec ike local address 9 172.16.100.1
  ipsec ike nat-traversal 9 on
  ipsec ike pre-shared-key 9 text unko123
  ipsec ike remote address 9 20.20.20.1
 ip tunnel tcp mss limit auto
 tunnel enable 16

であった場合、以下のような設定情報を作成。

tunnel select 16
  description tunnel "SAKURA VPN"
  ipsec tunnel 9
   ipsec sa policy 9 9 esp aes-cbc sha-hmac
   ipsec ike always-on 9 on
   ipsec ike duration ipsec-sa 9 1800
   ipsec ike duration ike-sa 9 28800
   ipsec ike encryption 9 aes-cbc
   ipsec ike group 9 modp1024
   ipsec ike hash 9 sha
   ipsec ike keepalive log 9 off
   ipsec ike keepalive use 9 on dpd 15 2
   ipsec ike local address 9 172.16.100.1
   ipsec ike local id 9 172.16.100.0/23
   ipsec ike nat-traversal 9 off
   ipsec ike pfs 9 on
   ipsec ike pre-shared-key 9 text unko123
   ipsec ike remote address 9 20.20.20.1
   ipsec ike remote id 9 192.168.100.0/23
   ipsec auto refresh 9 on
  ip tunnel mtu 1280
  ip tunnel tcp mss limit auto
  tunnel enable 16

これを、RTX-1210の「コマンド実行」で実行する。

以上