DMARCを始めてみたはいいものの大量に送られてくるレポートメールをなんとかしないといけないと思いました。レポートメールに添付されているXMLファイルをパースしてDBに取り込むプログラムを開発いたしました。DBに取り込めればあとはなんとかなる！
DMARCの設定方法はこちら
gzip(gz)やzipを展開(解凍)する方法はこちら
流れ
XMLをパースするps1ファイルと同階層にExpandというフォルダを作成します。そこにXMLファイルが毎日保存されていくイメージです。あと同階層にArchiveという名前のフォルダを作成します。パースが終わったXMLファイルをそのフォルダに移動します。データを登録するDBはSQLServerを使用します。流れは以下の通りです。
- ExpandフォルダにあるXMLファイルすべてを取得する
- 取得したXMLファイルをパースする
- パースしたデータをDBにInsertする
- Insertが終わったらXMLファイルをArchiveフォルダへ移動させる
ソース
以下おソースです。セキュリティなど考えていないソースなので実装には十分注意してください。
[object]$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
[object]$ConnectionString['Data Source'] = "ホスト名"
[object]$ConnectionString['Initial Catalog'] = "DB名"
[object]$ConnectionString['user id'] = "ユーザー名"
[object]$ConnectionString['password'] = "パスワード"
$expand_path = (Split-Path $myInvocation.MyCommand.Path -Parent) + "\Expand"
$archive_path = (Split-Path $myInvocation.MyCommand.Path -Parent) + "\Archive"
# SQL
$conn = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
try {
$conn.Open()
$transaction = $conn.BeginTransaction()
$cmd = $conn.CreateCommand()
$cmd.Connection = $conn
$cmd.Transaction = $transaction
# ExpandフォルダのXMLファイルを取得する
$items = Get-ChildItem $expand_path -File
foreach ($item in $items) {
$xml = [xml](Get-Content $item.FullName)
$org_name = $xml.feedback.report_metadata.org_name
$email = $xml.feedback.report_metadata.email
$report_id = $xml.feedback.report_metadata.report_id
$date_begin = $xml.feedback.report_metadata.date_range.begin
$date_end = $xml.feedback.report_metadata.date_range.end
$date_begin = [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1970/1/1').AddSeconds($date_begin))
$date_end = [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1970/1/1').AddSeconds($date_end))
$domain = $xml.feedback.policy_published.domain
$adkim = $xml.feedback.policy_published.adkim
$aspf = $xml.feedback.policy_published.aspf
$p = $xml.feedback.policy_published.p
$sp = $xml.feedback.policy_published.sp
$pct = $xml.feedback.policy_published.pct
$fo = $xml.feedback.policy_published.fo
$now = Get-Date
$sql = "INSERT INTO DMARC_report_metadata (org_name, email, report_id, date_begin, date_end, domain, adkim, aspf, p, sp, pct, fo, created, modified)
VALUES
('$org_name', '$email', '$report_id', '$date_begin', '$date_end', '$domain', '$adkim', '$aspf', '$p', '$sp', '$pct', '$fo', '$now', '$now')"
$cmd.CommandText = $sql
$sql
$cmd.ExecuteNonQuery()
# <record>は複数あるためforeachで回す
foreach ($record in $xml.feedback.record) {
$source_ip = $record.row.source_ip
try{
$source_host = [System.Net.Dns]::GetHostByAddress($source_ip).Hostname
}catch{
$source_host = ""
}
$count = $record.row.count
$disposition = $record.row.policy_evaluated.disposition
$dkim = $record.row.policy_evaluated.dkim
$spf = $record.row.policy_evaluated.spf
$envelope_to = $record.identifiers.envelope_to
$envelope_from = $record.identifiers.envelope_from
$header_from = $record.identifiers.header_from
$dkim_domain = $record.auth_results.dkim.domain
$dkim_selector = $record.auth_results.dkim.selector
$dkim_result = $record.auth_results.dkim.result
$spf_domain = $record.auth_results.spf.domain
$spf_scope = $record.auth_results.spf.scope
$spf_result = $record.auth_results.spf.result
$now = Get-Date
$sql = "INSERT INTO DMARC_record (report_id, source_ip, source_host, count, disposition, dkim, spf, envelope_to, envelope_from, header_from,
dkim_domain, dkim_selector, dkim_result, spf_domain, spf_scope, spf_result, created, modified)
VALUES
('$report_id', '$source_ip', '$source_host', '$count', '$disposition', '$dkim', '$spf', '$envelope_to', '$envelope_from', '$header_from',
'$dkim_domain', '$dkim_selector', '$dkim_result', '$spf_domain', '$spf_scope', '$spf_result', '$now', '$now')"
$cmd.CommandText = $sql
$sql
$cmd.ExecuteNonQuery()
}
}
$transaction.Commit()
#コミット完了後、xmlファイルを移動する
Move-Item ($expand_path + "\*.xml") $archive_path
} catch {
$transaction.Rollback()
Write-Error $_.Exception.ToString()
} finally {
$conn.Close()
}解説
PowerShellでXMLファイルを扱うのは本当に楽でした。XMLオブジェクトが作成されたらそのまま簡単に辿っていけます。
$xml = [xml](Get-Content $item.FullName)<date_begin>と<date_end>に保存されている日付情報はシリアル値です。シリアル値を日付型に変換してからDBに取り込んでいます。UNIXTIMEですので1970/1/1を基準にそこから何秒後なのかを計算して日付を取得しています。
$date_begin = [timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1970/1/1').AddSeconds($date_begin))GetHostByAddressはIPアドレスからホスト名を取得する関数です。source_ipはメール送信サーバーのIPアドレスでとても大事な情報です。IPアドレスだけですとイメージしにくいのでホスト名を取得するようにしました。
ただホスト名が取得できない時にエラーを吐いてしまうのでミニtry catchを作成しています。エラーになったら（ホスト名が取得できなかったら）catch側へ移動し$source_ipには何も値を入れません。
try{
$source_host = [System.Net.Dns]::GetHostByAddress($source_ip).Hostname
}catch{
$source_host = ""
}DBのレイアウトはテーブルを2つ作成しました。DMARC_report_metadataテーブルとDMARC_recordテーブルです。report_idで結びつけています。XMLファイルの中に＜record＞は複数回出現するため別テーブルにしました。DBレイアウトは結構適当ですので参考になさらずでお願いいたします。
まとめ
DBに入れさえすればあとはなんとか解析できますのでとりあえず入れておきましょう！どうやって解析するか…まずはレポートを正しく理解できないといけませんね。勉強します。
コメント