DMARC Reports を自前で集計する方法を備忘録として残しておく
方法
GAS(Google Apps Script) を使用して集約する
-
新規で Spread Sheet を作成
-
「拡張機能」>「Apps Script」を選択
-
「ファイル」>「コード.gs」のデフォルトで記載されているソースを削除して以下を貼り付ける
let today = new Date(); let targetDate = new Date(today); let nextDate = new Date(today); targetDate.setDate(today.getDate() - 1); nextDate.setDate(targetDate.getDate() + 1); const folderId = '<<Folder ID>>'; // レポートファイルを保存する親フォルダIDを指定 const sheetId = '<<Sheet ID>>'; // レポートを出力するスプレッドシートIDを指定 // 実行日の前日のメールを検索 // DMARC レポートを取得できる検索条件を指定する const query = 'From:noreply-dmarc-support@google.com after:' + Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyy/MM/dd') + ' before:' + Utilities.formatDate(nextDate, Session.getScriptTimeZone(), 'yyyy/MM/dd'); function executeAll(){ saveAttachmentsFromEmails(); parseDMARCReports(); calculateAndAppendHeaderFromTotals(); } function saveAttachmentsFromEmails() { // 日付のフォーマットをYYYYMMDDに変換 let formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd'); let threads = GmailApp.search(query); let parentFolder = DriveApp.getFolderById(folderId); if (threads.length === 0) { return; } // 日付別のフォルダを作成 let dateFolder = parentFolder.createFolder(formattedDate); let totalAttachments = threads.reduce((sum, thread) => sum + thread.getMessages().reduce((sum, message) => sum + message.getAttachments().length, 0), 0); let processedAttachments = 0; for (let i = 0; i < threads.length; i++) { let messages = threads[i].getMessages(); for (let j = 0; j < messages.length; j++) { let attachments = messages[j].getAttachments(); for (let k = 0; k < attachments.length; k++) { let attachment = attachments[k]; let file = dateFolder.createFile(attachment); Logger.log('File saved: ' + file.getName()); // ZIPファイルの解凍処理 if (attachment.getContentType() === 'application/zip') { let zipBlob = attachment.copyBlob(); let unzippedFiles = Utilities.unzip(zipBlob); for (let m = 0; m < unzippedFiles.length; m++) { dateFolder.createFile(unzippedFiles[m]); Logger.log('Unzipped file saved: ' + unzippedFiles[m].getName()); } // ZIPファイル自体は削除する file.setTrashed(true); } // GZファイルの解凍処理 else if (attachment.getContentType() === 'application/gzip' || attachment.getName().endsWith('.gz')) { let gzBlob = attachment.copyBlob(); gzBlob.setContentType("application/x-gzip"); let decompressedData = Utilities.ungzip(gzBlob); let gzFileName = attachment.getName().replace('.gz', ''); let decompressedBlob = Utilities.newBlob("", 'application/xml', gzFileName).setDataFromString(decompressedData.getDataAsString(), "UTF8");; dateFolder.createFile(decompressedBlob); Logger.log('Decompressed file saved: ' + gzFileName); // GZファイル自体は削除する file.setTrashed(true); } processedAttachments++; let progress = ((processedAttachments / totalAttachments) * 100).toFixed(2); Logger.log('Progress: ' + progress + '%'); } } } } function parseDMARCReports() { let parentFolder = DriveApp.getFolderById(folderId); let spreadsheet = SpreadsheetApp.openById(sheetId); let formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd'); let dateFolder = parentFolder.getFoldersByName(formattedDate); let folder = dateFolder.hasNext() ? dateFolder.next() : null; if (!folder) { Logger.log('Folder for ' + formattedDate + ' does not exist.'); return; } // 新しいシートを作成 ※左から3番目にシートを作成 // 既にシートが存在する場合は追記 let sheet = spreadsheet.getSheetByName(formattedDate) || spreadsheet.insertSheet(formattedDate, 2); // シートのヘッダーを設定 let headers = ['Source IP', 'Count', 'DMARC', 'Disposition', 'DKIM', 'SPF', 'Envelope To', 'Envelope From', 'Header From', 'DKIM Result', 'SPF Result']; sheet.appendRow(headers); let files = folder.getFiles(); while (files.hasNext()) { let file = files.next(); if (file.getMimeType() === 'text/xml') { let xmlContent = file.getBlob().getDataAsString(); let document = XmlService.parse(xmlContent); let root = document.getRootElement(); // 解析ロジック let records = root.getChildren('record'); let rowsData = []; for (let i = 0; i < records.length; i++) { let record = records[i]; let row = record.getChild('row'); let identifiers = record.getChild('identifiers'); let auth_results = record.getChild('auth_results'); let sourceIp = row.getChildText('source_ip'); let count = row.getChildText('count'); let disposition = row.getChild('policy_evaluated').getChildText('disposition'); let dkim = row.getChild('policy_evaluated').getChildText('dkim'); let spf = row.getChild('policy_evaluated').getChildText('spf'); let dmarc = (dkim === 'pass' || spf === 'pass') ? 'OK' : 'NG'; let envelopeTo = identifiers ? identifiers.getChildText('envelope_to') : ''; let envelopeFrom = identifiers ? identifiers.getChildText('envelope_from') : ''; let headerFrom = identifiers ? identifiers.getChildText('header_from') : ''; let dkimResultNode = auth_results.getChild('dkim'); let spfResultNode = auth_results.getChild('spf'); let dkimResult = dkimResultNode ? dkimResultNode.getChildText('result') : ''; let spfResult = spfResultNode ? spfResultNode.getChildText('result') : ''; let rowData = [sourceIp, count, dmarc, disposition, dkim, spf, envelopeTo, envelopeFrom, headerFrom, dkimResult, spfResult]; rowsData.push(rowData); } if (rowsData.length > 0) { sheet.getRange(sheet.getLastRow() + 1, 1, rowsData.length, headers.length).setValues(rowsData); } } } } function calculateAndAppendHeaderFromTotals() { let spreadsheet = SpreadsheetApp.openById(sheetId); let formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd'); let sheet = spreadsheet.getSheetByName(formattedDate); if (!sheet) { Logger.log('Sheet for ' + formattedDate + ' does not exist.'); return; } let data = sheet.getDataRange().getValues(); let headerFromTotals = {}; // 集計処理 for (let i = 1; i < data.length; i++) { // ヘッダー行をスキップするためにiを1から開始 let cValue = data[i][2]; // C列の値 let bValue = data[i][1]; // B列の値 let hValue = data[i][8]; // H列の値 let dValue = data[i][3]; // D列の値 if (!headerFromTotals[hValue]) { headerFromTotals[hValue] = { passTotal: 0, failTotal: 0, noneCount: 0, quarantineCount: 0, rejectCount: 0 }; } if (cValue === 'OK') { headerFromTotals[hValue].passTotal += bValue; } else { headerFromTotals[hValue].failTotal += bValue; } // D列の値をカウント if (dValue === 'quarantine') { headerFromTotals[hValue].quarantineCount += bValue; } else if (dValue === 'reject') { headerFromTotals[hValue].rejectCount += bValue; } else if (dValue === 'none') { headerFromTotals[hValue].noneCount += bValue; } } // 結果をスプレッドシートに書き込む let resultSheet = spreadsheet.getSheetByName('Results') || spreadsheet.insertSheet('Results'); if (resultSheet.getLastRow() === 0) { resultSheet.appendRow(['Header From', 'Date', 'Pass Total', 'Fail Total', 'Success Rate', 'None Count', 'Quarantine Count', 'Reject Count', 'Quarantine + Reject Rate']); } for (let headerFrom in headerFromTotals) { let passTotal = headerFromTotals[headerFrom].passTotal; let failTotal = headerFromTotals[headerFrom].failTotal; let successRate = Math.floor((passTotal / (passTotal + failTotal)) * 10000) / 100; // 切り捨て let successRateStr = successRate.toFixed(2) + '%'; // 小数点第2位まで表示 let noneCount = headerFromTotals[headerFrom].noneCount; let quarantineCount = headerFromTotals[headerFrom].quarantineCount; let rejectCount = headerFromTotals[headerFrom].rejectCount; let quarantineRejectRate = Math.floor(((quarantineCount + rejectCount) / failTotal) * 10000) / 100; // 切り捨て let quarantineRejectRateStr = Number.isNaN(quarantineRejectRate) ? '0.00%' : quarantineRejectRate.toFixed(2) + '%'; // 小数点第2位まで表示 resultSheet.appendRow([headerFrom, formattedDate, passTotal, failTotal, successRateStr, noneCount, quarantineCount, rejectCount, quarantineRejectRateStr]); } } -
「トリガー」>「トリガーを追加」ボタンを押下
-
以下の設定でトリガーを追加する
- 実行する関数を選択: executeAll
- 実行するデプロイを選択: Head
- イベントのソースを選択: 時間主導型
- 時間ベースのトリガーのタイプを選択: 日付ベースのタイマー
- 時間の間隔を選択（時間）: 実行したい時刻を指定
- エラー通知設定: 任意の設定を指定