DMARC Reports を自前で集計する方法

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
- イベントのソースを選択: 時間主導型
- 時間ベースのトリガーのタイプを選択: 日付ベースのタイマー
- 時間の間隔を選択（時間）: 実行したい時刻を指定
- エラー通知設定: 任意の設定を指定
Oct 29, 2024
POST
Nov 12, 2024
POST
comments powered by Disqus