CSPのAzure明細をPowerShellで整形する

新年度になりましたね。私も業務の所掌範囲とか変わって色々と新しいことに取り組み始めようと思います。

早速、MicrosoftのCSP事業者向けに毎月発行される請求書の明細のCSVファイルをExcelで加工するということを業務マニュアル見ながらしたのですが、面倒くさい上にピボットで集計したところ何かおかしい。

主に以下の点がおかしいという結論に。

  1. 調整ファイルのCSVの中に2種類の情報が入っていて、途中でカラムが変わる
  2. 調整ファイルの合計額が請求書の額と微妙に一致しない

前者に関しては単に分割すれば良いだけですが、後者に関しては過去の請求書も含めて調べた結果、以下のロジックであることが分かりました。

  • 税抜利用額において、小数点以下が存在するレコードが有るが、実際それは利用されない。
  • 2019年の秋くらいまでは多くの小数点以下の値があったが、以降は1円未満の利用(つまり0円請求)の場合にのみ小数点以下が存在
  • 税抜利用額の各項目に対して消費税が計算され、それが切り捨て
  • 切り捨てされた税抜合計額+消費税合計額がCSP事業者への請求

明細書のCSVが請求書と合わないのは問題なので、補正した値を経理処理上作成しないといけないのですが、数十万レコードとか有るとExcelでやるのも一苦労なので、PowerShellでやっちゃうことにしました。

PowerShellのImportExcelモジュールを使うと、PowerShellからxlsxファイルをそのまま作れます。アウトプットが複数シート必要なExcelなので今回はこれを利用します。

自分用のスクリプトなので汚いですが…。

$inputcsv = ".\AzureBillingUsage.csv"
$outxlsx = ".\CSPAzureBill.xlsx"
$tempfile = $env:TMP + "\_" + (Get-Date -Format yyyyMMddHHmmss) + ".csv"

### CSP請求書を上部のSummaryと下部のDaily Usageの2つのCSVに分ける ###
$bills = Get-Content $inputcsv -Encoding UTF8
$lines = $bills.Count

# Daily Usageの前の行まで出力
for ($i=1;$i -lt $lines;$i++){
    if($bills[$i] -eq "Daily Usage"){
        break
    }
    if($bills[$i] -ne ""){
        $bills[$i] | Out-File $tempfile -append
    }
}
$usages = Import-csv $tempfile
Remove-Item $tempfile

# Daily Usageの次の行から出力
for ($i++;$i -lt $lines;$i++){
    if($bills[$i] -ne ""){
        $bills[$i] | Out-File $tempfile -append
    }
}

### [Summary]シートの作成 ###
# PretaxChargesに小数点以下が存在するが、請求書上は切り捨てた額の総額で計算されているので、
# それを考慮したPreTax、PostTaxを計算
foreach ($usage in $usages){
    $pre  = [Math]::Truncate($usage.PretaxCharges)
    $post = $pre + $usage.TaxAmount
    $usage | Add-Member PreTax  $pre -force
    $usage | Add-Member PostTax $post -force
}

$usages | Export-Excel -Path $outxlsx -WorksheetName Summary

### [Daily_Usage]シートの作成 ###
# Subscription ID毎にマージして、PostTaxとPreTaxの値を合計する
Import-csv $tempfile | Export-Excel -Path $outxlsx -WorksheetName Daily_Usage
$subs = $usages | group "SubscriptionId" | select `
  @{Name="DomainName";Expression={($_.group | select -first 1).DomainName}}, `
  @{Name="CustomerCompanyName";Expression={($_.group | select -first 1).CustomerCompanyName}}, `
  @{Name="CustomerId";Expression={($_.group | select -first 1).CustomerId}}, `
  @{Name="SubscriptionId";Expression={($_.group | select -first 1).SubscriptionId}}, `
  @{Name="TotalPostTax";Expression={($_.group | Measure-Object -sum "PostTax").sum}}, `
  @{Name="TotalPreTax";Expression={($_.group | Measure-Object -sum "PreTax").sum}} `

# 新規開通分に関して DomainName が空欄なケースがあり、その場合の対応。
# (事前に Install-Module -Name PartnerCenter -AllowClobber が必要)
if((($subs | ? {$_.DomainName -eq ""}) | measure).count -gt 0){
	Connect-PartnerCenter
	foreach ($sub in $subs){
		if($sub.DomainName -eq ""){
			$sub.DomainName = (Get-PartnerCustomer -CustomerId $sub.CustomerId).Domain
		}
	}
}

### [Subscriptions]シートの作成 ###
$subs | sort DomainName | Export-Excel -Path $outxlsx -WorksheetName Subscriptions -AutoSize -Show

### テンポラリファイル削除 ###
Remove-Item $tempfile
2020/05/07修正(2020年04月利用分の明細ファイルに対応)
・既定のファイル名を変更
・細かいフォーマット変更に対応
・文字コード変更

最初はPivotで作ろうかと思ったのですが、[小計の非表示]や[表として表示]が上手くいかなかったので、そのままPowerShellで整形させちゃいました。

PowerShellを利用してレポート用のCSVを作ってExcelで整形という業務って結構あるかと思いますが、Import-Excelモジュールを使うとその部分までコードで書けるようになるのでとても便利です。

皆さんも是非試してみて下さい。

Office 365 の障害と信頼性

この記事は Office 365 Advent Calendar 2019 に参加しています

2019年の11月18日と19日、Office 365でExchange OnlineとTeamsを中心とした大規模な障害が立て続けに発生しました。

数日後、Blogサイトに設置してある Google Analytics経由で以下の様な通知メールが来ました。

サイトにログインしてみると、確かに18日と19日に普段の5倍近いのアクセスが来ているようです。

Office 365の障害に関しての情報を有用だと感じている方も多いようですので、既にソーシャルで色々な意見も出ていましたが、今回は私のOffice 365サービスの品質に関して感じていることを項目毎にいくつか書いてみたいと思います。

なお、なるべくエビデンスを示しつつ書こうと思ったのですが、古い記事などで見当たらなくなった物も多く、私の記憶を頼りに書いている部分も多いです。誤っている項目に関しては直ちに修正させて頂きますのでご指摘頂けると幸いです。

凄いと思っていること

私自身、クラウド事業者で似たようなHostedクラウド(Exchange Server、SharePoint Server、Skype for Business Server)の開発運用に携わっていますが、ここはどうしても敵わないと思っていることがいくつかあります。

まず、基本的に全て自分で作っていること。ベースのクラウドも、そこで動くOSも、その上で動かすアプリケーションも自分たちで作って管理しているというのは有事の時の対応速度や、品質向上の観点からすると天と地ほどの差があります。

以前は、Office 365も月に1度だったり主立った物は 3ヶ月に1度の更新頻度でしたので、バグが見つかってもその次の更新のタイミングに合わせないと…などが有りましたが、今は全体に影響の出るような物は随時修正、更新されています。

また、「誤っていたら直す」「より正しい物が出てきたらそれに変更する努力をいとわない」という体質も凄いと感じてます。

具体的に言うと、例えば私の記憶では Exchange Online は最初リリース時はシンガポールDCがメインのデータセンタで、香港DCはバックアップセンタとして位置づけられていたと思います。

サービスイン当初は、シンガポールDCが

  • 207.46.4.128/25
  • 207.46.58.128/25
  • 207.46.198.0/25
  • 207.46.203.128/26

だったのに対して、香港DCは

  • 111.221.69.128/25

のCIDRだけ利用していました。開始半年くらいして、影響がないと想定していたネットワーク作業でサービス影響がでました。こちら、恐らくプライマリDCからバックアップDCへの切り替えの試験を裏でしていたと思うのですが、主にDNS回りなどで想定外の事象になったようです。

その後、Exchange Onlineはシンガポールと香港を 50:50 で両 Active として利用するようになり、障害やメンテナンスに起因したデータセンタの切り替わりもシームレスに行われるようになりました。(オンプレミスの Exchange Server も同様の設計にすることがベストプラクティスになりました。)

また、同じく初期の方の障害の中でデータセンタ内部での大規模なネットワーク障害がありました。細かいメカニズムは説明されませんでしたが、L2ドメインの制御に起因する障害のようで、その後の構成変更でL2ドメインを極小化する施策をとりました。

[すごいネットワーク]DC内は「小型インターネット」

既に何百万ユーザーもいた中で、この規模の根幹からのアーキテクチャ変更を基本的にダウンタイム無しで行うことができたというのは本当に凄いことだと思います。

日本企業で同じ事をやろうとすると、そもそも社内でも止められるケースが多いでしょうし、いざやろうとメンテナンス通知を出したとしても、お客様から「今の状態に満足しているからそんなリスクのあることして変更しないでくれ」という強い要求が来て押し切られてしまう、仕方なく「このサービスは何とかこの構成で乗り切って、次期サービスは新しいアーキテクチャでやろう」となるケースが多いのではないかと。

Microsoftも、BPOS → Office 365と3年周期で来たところでしたので、また3年で新しいブランドを作ってそちらで実現するという選択肢もあったかと思うのですが、「Office 365」というブランドで今後もずっと継続して行きたい、継続的に改善・改良を繰り返して行きたいという強い意志を感じました。

最後に、SLA がきちんとしていること。これは、単なる努力目標&返金規定として機能しているのではなく、ユーザーとの間で現実的に達成可能な数値として定められている点です。

こちら、特に Microsoft Azure の SLA の方が顕著ですが、おそらく「何でこの稼働率を保証できるのか」と聞かれた場合に、 Microsoft はかなり明確にそれを如何に達成できるのかというのを論理的に説明ができるのだと思います。この為、達成できなかった場合の返金率は日本の企業の物に比べると高いです。

月間稼働率サービスクレジット(返金率)
99.9%未満25%
99%未満50%
95%未満100%

ちなみに、今回は Exchange Online の方はきっと99%未満になると思うのですが、結構な財務インパクトですね。これだと本気で品質改善しようと思うでしょうね。

余談ですが、日本のホスティング事業者は例えお客様と合意したSLAの範囲内の故障でも、画一的に決められた影響数と影響時間に基づき、障害発生時に総務省報告の義務があります。大規模障害時はお客様対応も勿論非常に大変なのですが、この監督官庁対応も非常に大変な稼働の発生するお仕事です。

なので、正直どうせ完璧を目指す再発防止策を出し続けないといけないなら、 SLAを非常に高い数字にしてしまって、万一の場合は返金すれば…と思って、例えば稼働率 100%の SLA を定めるというのも少し仕方ないかなという気もします。

ただ、ソフトウェアを使っているので、勿論想定外のバグは発生する物であり、その想定発生率が0%、例え発生したとしても100%冗長化の切り替えが成功するという前提はちょっと苦しいですよね。

ここは改善して欲しいと思っていること

色々と問題が発生する度に徐々に良くなってきているので、正直あまり残っていないのですが、少しだけ書きます。

まず、障害の認識のトリガーがですが、自社で設定したモニタリングで引っかからず、複数ユーザーからの障害申告をもって障害検知しているケースがまだ結構あると感じています。

申告に関しても「複数のユーザーからの」という条件が未だにあるらしく、今回の Exchange Online の障害に関しても、結果として発生から検知・アナウンスまで結構な(個人的な感覚で1時間以上)タイムラグがあったのではないかと思っています。

正直、その辺のデータは一杯取っているのでしょうから、地域毎のコールセンターのコール数、サポートリクエストの起票数、Web(BingやGoogle)の検索数、Twitterなどのソーシャルの発言のトレンドから「何か起こっているかも」の兆候も見いだすことができるでしょうし、RPAなどを活用して、より広い範囲で End-to-End のクライアント接続試験なども行う事ができるのはないかと期待しています。

また、メンテナンスや構成変更などの実行時間についてですが、以前は告知はしないものの、タイムゾーン毎に毎週金曜日の夜間帯がメンテナンスウィンドウとして定められていて、その時間に更新していたような記憶があります。

よくサポートリクエストの調査結果から、クラウド側のバグであることが分かったケースも、修正プログラムは完成したが、全世界に適用するまでに2-3週間かかると言われるケースなどもあり、そういった決まった短い時間だけだと対応が難しいのかも知れませんが、何か有った場合の検知やその切り戻しを迅速にする意味からもクラウド内への適用はもう少し安全かつ迅速にできる仕組みにしていって欲しいですね。

「障害かな」と思ったら自分でやっていること

今回の障害発生時に、自分でやった動作の振り返りです。

  1. 検証環境、もしくは別PCや別ブラウザから再現試験を実施してみて「影響範囲」「発生条件」「再現性の有無」を確認する
  2. サポートリクエストを上げる準備をする
  3. Twitterで似たような症状のつぶやきが無いか検索する(例: 「Office365」「メール」「遅延」)
  4. 自分でもつぶやく
  5. FacebookのOffice365コミュニティを見る
  6. 正常性ダッシュボード([サービスの正常性])を見る
  7. サポートリクエストを上げる

今回は、Teamsの障害の方は [サービスの正常性] ごと落ちるという凄い展開でしたが、Twitterでの情報収集は総じて有用でした。

定期的に国毎で検索してトレンド見ていくと、結構エンドユーザー観点でも有用なデータがプロアクティブに取れるかも知れませんね。

そのうちチャレンジしてみたいと思います。

6月の月例更新でのイベントビューアーの問題

久々にローカル環境でExchangeのトラブルシュートをしていたところ、特定の条件でイベントビューアーがクラッシュする事に気がつきました。

  • 2019年6月の更新プログラムを適用したExchange環境で発生
  • [カスタムビュー]またはイベントビューアーから [現在のログをフィルター] を選択すると発生
  • エラー内容は以下の通り
別のプロセスで使用されているため、プロセスはファイル 'C:\ProgramData\Microsoft\Event Viewer\Views\msexchangerepl_events.xml' にアクセスできません。

 例外の種類:
  System.IO.IOException

 例外のスタックトレース:
    場所 System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
    場所 System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
    場所 System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.get_IsReadOnlyView()
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.UpdateReadOnly()
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.InitializeQueryNode(FileInfo fileInfo)
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.AddSubNodes(DirectoryInfo dir, EventNodeType nodeType, Boolean userQuery, String standardViewConfig)
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.AddSavedQueryNodes()
    場所 Microsoft.Windows.ManagementUI.CombinedControls.EventsNode.CreateChildNodes()
    場所 Microsoft.EventViewer.SnapIn.MMCEventsNode.ExpandNode()
    場所 Microsoft.EventViewer.SnapIn.MMCEventsNode.OnExpand(AsyncStatus status)
    場所 Microsoft.ManagementConsole.NodeSyncManager.ProcessRequest(NodeRequestInfo info, IRequestStatus requestStatus)
    場所 Microsoft.ManagementConsole.SnapIn.ProcessRequest(Request request)
    場所 Microsoft.ManagementConsole.Internal.SnapInClient.Microsoft.ManagementConsole.Internal.IMessageClient.ProcessRequest(Request request)
    場所 Microsoft.ManagementConsole.Internal.IMessageClient.ProcessRequest(Request request)
    場所 Microsoft.ManagementConsole.Executive.RequestStatus.BeginRequest(IMessageClient messageClient, RequestInfo requestInfo)
    場所 Microsoft.ManagementConsole.Executive.SnapInRequestOperation.ProcessRequest()
    場所 Microsoft.ManagementConsole.Executive.Operation.OnThreadTransfer(SimpleOperationCallback callback)

これは、6月の更新プログラムの適用により、イベントビューアーからExchangeをインストールした際に自動的に作られるイベントビューアーのカスタムフィルタのxmlファイル(msexchangerepl_events.xml)が読み込めなかった為に発生しています。

7月の更新プログラムで解消されているので、7月の更新プログラムを当てれば解消されますが、当然再起動を伴いますので、運用中の環境の場合はすぐには難しいことも有ると思います。

暫定回避したい場合は、このファイルの実体を別のフォルダに一旦移動してあげれば事象は解消可能です。

Move-Item "C:\ProgramData\Microsoft\Event Viewer\Views\msexchangerepl_events.xml" c:\temp

7月以降の更新プログラムを当てた後、待避していたファイルをまた元の場所に戻してあげればカスタムビューが復活します。

Token2でのAzure ADの多要素認証

以前紹介した Yubikey の多要素認証には、Yubico AuthenticatorでのOTPの表示が必要だったため、ソフトウェアのインストールが制限されている環境(特にサーバ環境や Thin Client環境など)など利用ができないケースが有ります。

今回は、OTPの表示機能を備えた一般的なハードウェアトークンで、Azure ADに対応しているToken2のデバイスを利用した多要素認証について紹介します。

Token2のプロダクト群の特徴は、従来型のハードウェアトークン(Classic Token)に加えて書き換え可能トークン(Programmable Token)が有るという点です。また、形状もよく見かけるようなキーホルダー型の物とカード型の物が有ります。 価格も1個あたり €10 ~ €20 程度と比較的リーズナブルです。

Continue reading

Express Route従量制プランのススメ

Office 365やAzureとインターネットを経由せずに接続できる Express Route ですが、料金プランは無制限データプランを選ばれてますか? それとも従量制課金データプランですか?

私の知っているユーザーですと、9割以上が無制限データプランを選択されているように感じます。主な理由としては「従量制だと料金がいくら課金されるか分からないし、試算するにも実際にどれくらいデータ転送量が分からない」「毎月課金がぶれると予算措置をするのが大変」というケースがほとんどです。

ただ、敢えて私は個人的に従量制プランの方を強くお勧めさせて頂きます。理由は主に以下の3つからです。

Continue reading

YubiKeyでの多要素認証:管理者による合鍵管理

YubiKeyでの運用で気を付けなければならないのは、キー自体を忘れたときの管理です。代替の方式として個人のスマートフォンでの電話やMicrosoft Authenticatorや固定電話などが設定できる環境であればそれで代替してもいいですが、今回はセキュリティの強度が高いとして推奨されている予備のYubiKeyを用意する方法について、どう利用するかの例を紹介します。

もちろん、各個人に2個ずつ YubiKey を配布し、一つは会社の机の中に入れておくなども可能かと思いますが、5000円程度するデバイスなので数がまとまると結構な「合鍵」代となってしまい、二の足を踏んでいるところをよく見かけます。

ただ、ここで合鍵代をケチったばかりに職場へのスマートフォン端末持ち込みを許可させたりというのも本末転倒ですよね。

Continue reading

YubiKeyでの多要素認証:管理者による登録

以前の投稿で紹介したように、Yubikey (+ yubico Authenticator)による登録は各ユーザーがセルフサービスで実施することができて、気軽に利用できます。

しかしながら、この手順は一般ユーザーにとっては若干複雑です。また、いくつか問題が発生する可能性のあるポイントもあります。例えば、以下の様な物です。

Continue reading

ハードウェアトークン(YubiKey)での多要素認証

この blog は Office 365 Advent Calendar 2018 に参加しています。

Office 365の管理者アカウントの利用に多要素認証を検討されている(もしくは既に実装済み)というユーザーは多いかと思います。

現状、Microsoftでは利便性を重視してスマートフォンでのアプリケーション Microsoft Authenticator を利用したアプリ通知による認証を推していますが、環境によっては利用が難しいケースがあります。

Continue reading

第23回 Office 365勉強会に登壇しました

11/23に開催された第23回Office 365 勉強会に登壇しました。

今回のテーマは「O365勉強会的、AzureAD勉強会」。主催者メンバーの中での企画段階では、色々とBlogでお世話になっていて、今年Microsoft MVPになった @Miya_Microsoft さんにOffice 365勉強会に来て貰いたいというのが最初で、以前そういえば国井さんの事をインスパイアしてたなぁ…と思い、2人セットでブッキングできそうなテーマとして選びました。(毎回ではないですが、結構ユルい感じでテーマ決めとかしてます)

当日の様子はこちら : 第23回 Office 365 勉強会 #o365jp まとめ

私のセッションでは、「Azure ADのテナント設計」ということで、Office 365管理者としてはおそらく意識せずに使っているAzure ADのテナントについて、今後AzureやD365などの他のサービスが利用するようになってきた場合にも、どうしていったら良いかきちんと考えられるよう、注意事項や指針などについて話をさせて頂きました。

アンケートなどでも良い反応を頂けたようですので、またこのBlogなどでもこの辺の話題について詳細やアップデートなど発信していければと思っています。

Office 365関連の書籍を執筆しました

いつもお世話になっている北端さん、太田さんと共著させて頂いた書籍「Office 365管理者のための逆引きPowerShellハンドブック」が11/15に発売されることになりました。

コマンドの説明であればオンラインヘルプや Technet を見れば十分に事足りると思い、サンプルスクリプトなどもなるべく実務よりで Tips が多くなるように書きました。

書き切れなかったことや補足、訂正したいことなども今後色々出てくると思うので、内容については今後この Blog 内でフォローしていきたいと思っていますので、こちらも参照いただければと思います。

11/5-7 の Tech Summit 2018で先行販売されるとのことですので、イベントに参加される方はよろしければ手に取ってみて下さい。