Office 365 ログの入れ子になった配列をテーブルに変換する

Office 365 ログの入れ子になった配列をテーブルに変換する

いくつかの一般的なOffice 365統合監査ログイベントを表形式のレポート形式に変換する単純なjqベースのツールを公開したいと思いましたが、いくつかのキー配列が入れ子になる方法に問題があります。特に、ID、パスを含むセット、メッセージID、およびサイズ行を含むフォルダ[]セットをドリルダウンするときに、同期/組み合わせで配列の関連値を維持する方法が見つかりません。あたかも意図せず繰り返すように、各値の多数の組み合わせを取得します。

以下は、いくつかのサンプルデータです。

{"CreationTime":"2024-02-06T12:13:14","Id":"abcdabcd-1234-1234-5555-888888888888","Operation":"MailItemsAccessed","ResultStatus":"Succeeded","UserId":"[email protected]","ClientIPAddress":"5.5.5.5","Folders":[{"FolderItems":[{"InternetMessageId":"<[email protected]>","SizeInBytes":12345},{"InternetMessageId":"<[email protected]>","SizeInBytes":11122},{"InternetMessageId":"<[email protected]>","SizeInBytes":88888}],"Id":"EEEEEEEE","Path":"\\Outbox"},{"FolderItems":[{"InternetMessageId":"<[email protected]>","SizeInBytes":44444},{"InternetMessageId":"<[email protected]>","SizeInBytes":100000},{"InternetMessageId":"<[email protected]>","SizeInBytes":109000},{"InternetMessageId":"<[email protected]>","SizeInBytes":22000},{"InternetMessageId":"<[email protected]>","SizeInBytes":333333}],"Id":"FFFFFFFFFFFFFFFFFAB","Path":"\\Inbox"}]}
{"CreationTime":"2024-02-06T20:00:00","Id":"abcdabcd-1234-1234-6666-9999999999999","Operation":"MailItemsAccessed","ResultStatus":"Succeeded","UserId":"[email protected]","ClientIPAddress":"7.7.7.7","Folders":{"FolderItems":[{"InternetMessageId":"<[email protected]>","SizeInBytes":77777},{"InternetMessageId":"<[email protected]>","SizeInBytes":888888},{"InternetMessageId":"<[email protected]>","SizeInBytes":99999}],"Id":"12341234","Path":"\\Temp"}}

希望の出力:

製作時間 ID ユーザーID クライアントIPアドレス フォルダID フォルダパス インターネットメッセージID サイズ(バイト)
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 ええええええええええ \送信トレイ [Eメール保護] 12345
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 ええええええええええ \送信トレイ [Eメール保護] 11122
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 ええええええええええ \送信トレイ [Eメール保護] 88888
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 FFFFFFFFFFFFFFFFFFAB \受信トレイ [Eメール保護] 44444
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 FFFFFFFFFFFFFFFFFFAB \受信トレイ [Eメール保護] 100000
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 FFFFFFFFFFFFFFFFFFAB \受信トレイ [Eメール保護] 109000
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 FFFFFFFFFFFFFFFFFFAB \受信トレイ [Eメール保護] 22000
2024-02-06T12:13:14 abcdabcd-1234-1234-5555-8888888888888 [Eメール保護] 5.5.5.5 FFFFFFFFFFFFFFFFFFAB \受信トレイ [Eメール保護] 333333
2024-02-06T20:00:00 12341234 [Eメール保護] 7.7.7.7 12341234 \温度 [Eメール保護] 77777
2024-02-06T20:00:00 12341234 [Eメール保護] 7.7.7.7 12341234 \温度 [Eメール保護] 888888
2024-02-06T20:00:00 12341234 [Eメール保護] 7.7.7.7 12341234 \温度 [Eメール保護] 99999

要素は.Folders時々文字列形式であるかもしれませんがfromjson。たとえば、

[...]"Folders": "[{\"FolderItems\":[{\"InternetMessageId\":\""Fo<[email protected]>\",\"SizeInBytes\":12345},[...]

これまでのコード:

cat | jq '
    if has("Folders") then
        if(.Folders | type=="string") and .Folders != "" then .Folders |= fromjson  end |
        if(.Folders | type=="string") and .Folders == "" then .Folders = null end
    end | .' |     # works up to here at least
    jq '
if has("Item") then .Item |= (if type=="string" and .!="" then fromjson else {} end) else .Item|={}  end |
    if has("Item") then
            if .Item | has("Id") then .ItemId = .Item.Id else .ItemId={} end |
            if .Item | has("ParentFolder") then
                .ItemParentFolderId=.Item.ParentFolder.Id? |
                    .ItemParentFolderPath=.Item.ParentFolder.Path? |
                    .ItemParentFolderName=.Item.ParentFolder.Name?
            end
        end | . ' | cat # works up to here at least
    jq '
    if has("Folders") then
        if (.Folders | select(type=="array")) then
            .Folders[].Id? |
            .FoldersPath=.Folders[].Path? |
            .FoldersFolderItems=.Folders[].FolderItems?
        else . end
    end
    ' |
jq -r '. | (.TimeGenerated // .CreationTime) as $EventTime |
.ClientIP = if .ClientIP == "" then null else .ClientIP end |
.ClientIP_ = if .ClientIP_ == "" then null else .ClientIP_ end |
.Client_IPAddress = if .Client_IPAddress == "" then null else .Client_IPAddress end |
.ClientIPAddress = if .ClientIPAddress == "" then null else .ClientIPAddress end |
.ActorIpAddress = if .ActorIpAddress == "" then null else .ActorIpAddress end |
(.ClientIP // .ClientIP_ // .Client_IPAddress // .ClientIPAddress // .ActorIpAddress) as $IPAddress |
(.UserId // .UserId_) as $LogonUser |
.FFIIMI as $InternetMessageId |
.FFISIB as $SizeInBytes |
{EventTime: $EventTime, IPAddress: $IPAddress, LogonUser: $LogonUser, InternetMessageId: $InternetMessageId, SizeInBytes: $SizeInBytes} + . |
[.Id, .EventTime, .IPAddress, .LogonUser, .MailboxOwnerUPN, .Operation, .InternetMessageId, .SizeInBytes] | @csv'

答え1

サンプルデータとして提供されたJSONから始めました。このJSONがどのような方法で前処理されたかはわかりません。

最上位Foldersの配列が見えるのでいいえ単一の項目が含まれている場合は配列で、まだ配列でない場合は、まず配列に変換する必要があります。でjq我々はこれを行うことができます

.Folders |= (if type == "array" then . else [.] end)

残りの変換の一般的なアイデアは、上位レベルのデータ(つまり、最上位レベルのいくつかのキーと値のペアと各要素のキー)をId下位レベル(つまり要素)にコピーすることです。その後、各要素をCSVレコードに変換できます。PathFoldersFolderItemsFolderItems

キーの名前が重複するのを防ぐには、要素のキーFolders名をに変更する必要があります(そして一貫性のためにId同じレベルの名前をに変更する必要があります)。FolderIdPathFolderPath

必要な最上位データを選択し、内部変数を使用して下に転送できます。

pick(.CreationTime, .Id, .UserId, .ClientIPAddress) as $record

これは$record生成されます

{
  "CreationTime": "2024-02-06T12:13:14",
  "Id": "abcdabcd-1234-1234-5555-888888888888",
  "UserId": "[email protected]",
  "ClientIPAddress": "5.5.5.5"
}

...最初のJSONオブジェクトです。得られたキーは最終CSV出力に必要な順序ではありません。

Foldersその後、要素のみを抽出して.Folders[]各要素のId合計を選択できますPath。再利用できないように名前を変更するので、pick()より実践的な作業が必要です。

.Folders[] | { FolderId: .Id, FolderPath: .Path } as $folder

その後、それを使用して、合計の前に追加できる要素の.FolderItems[]セットを取得できます。FolderItems$record$folder

.FolderItems[] | $record + $folder + .

単一jq表現で:

.Folders |= (if type == "array" then . else [.] end) |
pick(.CreationTime, .Id, .UserId, .ClientIPAddress) as $record |
.Folders[] | { FolderId: .Id, FolderPath: .Path } as $folder |
.FolderItems[] | $record + $folder + .

質問のデータを考慮すると、結果は次のようになります。

{
  "CreationTime": "2024-02-06T12:13:14",
  "Id": "abcdabcd-1234-1234-5555-888888888888",
  "UserId": "[email protected]",
  "ClientIPAddress": "5.5.5.5",
  "FolderId": "EEEEEEEE",
  "FolderPath": "\\Outbox",
  "InternetMessageId": "<[email protected]>",
  "SizeInBytes": 12345
}
{
  "CreationTime": "2024-02-06T12:13:14",
  "Id": "abcdabcd-1234-1234-5555-888888888888",
  "UserId": "[email protected]",
  "ClientIPAddress": "5.5.5.5",
  "FolderId": "EEEEEEEE",
  "FolderPath": "\\Outbox",
  "InternetMessageId": "<[email protected]>",
  "SizeInBytes": 11122
}

(等)

個人的には、次の方法を使用してこのJSONオブジェクトのセットをCSVに変換します。ミラー:

$ jq '.Folders |= (if type == "array" then . else [.] end) | pick(.CreationTime, .Id, .UserId, .ClientIPAddress) as $record | .Folders[] | { FolderId: .Id, FolderPath: .Path } as $folder | .FolderItems[] | $record + $folder + .' file | mlr --j2c cat
CreationTime,Id,UserId,ClientIPAddress,FolderId,FolderPath,InternetMessageId,SizeInBytes
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,EEEEEEEE,\Outbox,<[email protected]>,12345
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,EEEEEEEE,\Outbox,<[email protected]>,11122
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,EEEEEEEE,\Outbox,<[email protected]>,88888
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,FFFFFFFFFFFFFFFFFAB,\Inbox,<[email protected]>,44444
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,FFFFFFFFFFFFFFFFFAB,\Inbox,<[email protected]>,100000
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,FFFFFFFFFFFFFFFFFAB,\Inbox,<[email protected]>,109000
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,FFFFFFFFFFFFFFFFFAB,\Inbox,<[email protected]>,22000
2024-02-06T12:13:14,abcdabcd-1234-1234-5555-888888888888,[email protected],5.5.5.5,FFFFFFFFFFFFFFFFFAB,\Inbox,<[email protected]>,333333
2024-02-06T20:00:00,abcdabcd-1234-1234-6666-9999999999999,[email protected],7.7.7.7,12341234,\Temp,<[email protected]>,77777
2024-02-06T20:00:00,abcdabcd-1234-1234-6666-9999999999999,[email protected],7.7.7.7,12341234,\Temp,<[email protected]>,888888
2024-02-06T20:00:00,abcdabcd-1234-1234-6666-9999999999999,[email protected],7.7.7.7,12341234,\Temp,<[email protected]>,99999

jqMillerを使用してCSVに変換するには、mlrコマンドを次のように置き換えます。

jq -s -r '(first|keys|@csv), map([.[]]|@csv)[]'

これにより、最初の入力オブジェクトでキーがCSVヘッダーとして選択され、すべての値の形式がオブジェクトごとに1つのレコードとして指定されます。

関連情報