Power BI Desktop や Excel のクエリ エディター では JSON形式で記述されたデータの解析が簡単にできるようになっています。ここでは Office 365 監査ログ(csv)を 手早くExcel テーブルに変換してみます。ポイントは JSON形式の AuditData 列 をパースすることです。また、Office 365 監査ログの AuditData 列は、提供されているサービスごとで取得できるフィールド(列)は異なります。なので、ログを取得したいサービス別にエクスポートすることは解決策をシンプルにする方法ではありますが、依然、AuditData 列から必要な値を参照するための "列の分割" では困難というかスマートではないなぁと思うのです。
第20回 Office 365 勉強会 のLTでちょっとだけ触れましたがその補足です。
Office 365 監査ログ
監査ログを参照できるユーザーは限られるのでその設などの解説はしない。
- 監査ログを検索し、Office 365 でのユーザーと管理者のアクティビティを確認する - Office 365
- Office 365 のセキュリティ センターとコンプライアンス センターで監査ログを検索する Office サポート
- Office 365 監査ログの詳細なプロパティ- Office 365
Office 365 監査ログを Excel テーブルとして読み込む
まずは インポート
1 - 該当のログファイル(csv) をインポートする。
2 - Unicodeであることなど自動で認識されるが異なるようなら調整する。[編集]でクエリ エディターに。
AuditData 列の解析
3 - AuditData 列を選択
4 - [解析] → [JSON]
解析済みの AuditData 列から参照
5 - [カスタム列] の追加
6 - "新しい列名"と"カスタム列の式" を設定。式は "= [AuditData][Workload]"
AuditData 列から フィールド Workload の値が抽出できた。同じ手順で フィールド ResultStatus を 抽出.
7 - 同じ手順で ResultStatus を抽出。式は "= [AuditData][ResultStatus]"
AuditData に フィールド ResultStatus が存在しない場合は Error となるので、
8 - 式を "= [AuditData][Workload]?" に修正
フィールド アクセスの "[]" に "?" を付けると、フィールドが存在する場合は該当の値を、フィールドが存在しない場合 null を返す。
9 - AuditData 列を選択
10 - 列を削除
Excel テーブルにロード
11 - クエリ エディターを [閉じて読み込む]
複数値を持つフィールドの参照
JSON にパースしたところから始めてみる。
RecordType が 15 であるものを対象とするフィルターを適用
ExtendedProperties 列を [カスタム列]の追加で抽出
それぞれの値は record の list であることが確認できる。そして その要素数が異なる。
record 型の値リストは table に変換
Name - Value の組合せを表すテーブルになる
行と列を入れ替え、
1行目を列名に
必ず1行のテーブルになるので record に変換する。これでフィールド値を参照しやすくなる。
let
FullName = "hogehoge\AuditLog_0000-00-00_0000-00-00.csv",
Source = Csv.Document(
File.Contents(FullName),
[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]
),
#"Promoted Headers" = Table.PromoteHeaders(
Source,
[PromoteAllScalars=true]
),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"CreationDate", type datetime}
, {"UserIds", type text}
, {"Operations", type text}
, {"AuditData", type text}
}
),
#"Parsed JSON" = Table.TransformColumns(
#"Changed Type"
, {
{"AuditData", Json.Document}
}
),
Custom1 = Table.SelectRows(
#"Parsed JSON"
, each [AuditData][RecordType] = 15
),
Custom2 = Table.AddColumn(
Custom1
, "ExtendedProperties"
, each [AuditData][ExtendedProperties]
),
Custom3 = Table.TransformColumns(
Custom2
, {"ExtendedProperties", each Table.FromRecords(_)}
),
Custom4 = Table.TransformColumns(
Custom3
, {"ExtendedProperties", each Table.Transpose(_)}
),
Custom5 = Table.TransformColumns(
Custom4
, {"ExtendedProperties", each Table.PromoteHeaders(_)}
),
Custom6 = Table.TransformColumns(
Custom5
, {"ExtendedProperties", each _{0}}
)
in
Custom6
table 型の値は 列の展開ができるのだけど、この場合は 監査ログに存在するプロパティのみとなるので用途に合わせて使えばよい。