dmarc-report

gs

var today = new Date();
var targetDate = new Date(today);
var nextDate = new Date(today);
targetDate.setDate(today.getDate() - 1);
nextDate.setDate(targetDate.getDate() + 1);
const folderId = 'YOUR_PARENT_FOLDER_ID'; // レポートファイルを保存する親フォルダIDを指定
const sheetId = 'YOUR_SPREADSHEET_ID'; // レポートを出力するスプレッドシートIDを指定
// 実行日の前日のメールを検索できるようになっているので、dmarcのレポートを取得できる検索条件を指定する
const query = 'ここにクエリを書く 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に変換
  var formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd');
  
  var threads = GmailApp.search(query);
  var parentFolder = DriveApp.getFolderById(folderId); 

  // 日付別のフォルダを作成
  var dateFolder = parentFolder.createFolder(formattedDate);

  var totalAttachments = threads.reduce((sum, thread) => sum + thread.getMessages().reduce((sum, message) => sum + message.getAttachments().length, 0), 0);
  var processedAttachments = 0;

  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var attachments = messages[j].getAttachments();
      for (var k = 0; k < attachments.length; k++) {
        var attachment = attachments[k];
        var file = dateFolder.createFile(attachment);
        Logger.log('File saved: ' + file.getName());
        
        // ZIPファイルの解凍処理
        if (attachment.getContentType() === 'application/zip') {
          var zipBlob = attachment.copyBlob();
          var unzippedFiles = Utilities.unzip(zipBlob);
          for (var 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')) {
          var gzBlob = attachment.copyBlob();
          gzBlob.setContentType("application/x-gzip");
          var gzData = gzBlob.getBytes();
          var decompressedData = Utilities.ungzip(gzBlob);
          var gzFileName = attachment.getName().replace('.gz', '');
          var decompressedBlob = Utilities.newBlob("", 'application/xml', gzFileName).setDataFromString(decompressedData.getDataAsString(), "UTF8");;
          dateFolder.createFile(decompressedBlob);
          Logger.log('Decompressed file saved: ' + gzFileName);
          // GZファイル自体は削除する
          file.setTrashed(true);
        }
        processedAttachments++;
        var progress = ((processedAttachments / totalAttachments) * 100).toFixed(2);
        Logger.log('Progress: ' + progress + '%');
      }
    }
  }
}

function parseDMARCReports() {
  var parentFolder = DriveApp.getFolderById(folderId);
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd');
  
  var dateFolder = parentFolder.getFoldersByName(formattedDate);
  var folder = dateFolder.hasNext() ? dateFolder.next() : null;
  
  // 新しいシートを作成
  var sheet = spreadsheet.getSheetByName(formattedDate) || spreadsheet.insertSheet(formattedDate);
  
  // シートのヘッダーを設定
  var headers = ['Source IP', 'Count', 'DMARC', 'Disposition', 'DKIM', 'SPF', 'Envelope To', 'Envelope From', 'Header From', 'DKIM Result', 'SPF Result'];
  sheet.appendRow(headers);

  if (folder) {
    var files = folder.getFiles();
    var totalFiles = folder.getFiles().length;
    var processedFiles = 0;

    while (files.hasNext()) {
      var file = files.next();
      if (file.getMimeType() === 'application/xml') {
        var xmlContent = file.getBlob().getDataAsString();
        var document = XmlService.parse(xmlContent);
        var root = document.getRootElement();
        
        // 解析ロジック
        var records = root.getChildren('record');
        var rowsData = [];
        for (var i = 0; i < records.length; i++) {
          var record = records[i];
          var row = record.getChild('row');
          var identifiers = record.getChild('identifiers');
          var auth_results = record.getChild('auth_results');
          
          var sourceIp = row.getChildText('source_ip');
          var count = row.getChildText('count');
          var disposition = row.getChild('policy_evaluated').getChildText('disposition');
          var dkim = row.getChild('policy_evaluated').getChildText('dkim');
          var spf = row.getChild('policy_evaluated').getChildText('spf');
          var dmarc = (dkim === 'pass' || spf === 'pass') ? 'OK' : 'NG';
          var envelopeTo = identifiers ? identifiers.getChildText('envelope_to') : '';
          var envelopeFrom = identifiers ? identifiers.getChildText('envelope_from') : '';
          var headerFrom = identifiers ? identifiers.getChildText('header_from') : '';
          var dkimResultNode = auth_results.getChild('dkim');
          var spfResultNode = auth_results.getChild('spf');
          var dkimResult = dkimResultNode ? dkimResultNode.getChildText('result') : '';
          var spfResult = spfResultNode ? spfResultNode.getChildText('result') : '';
          
          var 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);
        }
      }
      processedFiles++;
      var progress = ((processedFiles / totalFiles) * 100).toFixed(2);
      Logger.log('Progress: ' + progress + '%');
    }
  }
}

function calculateAndAppendHeaderFromTotals() {
  var spreadsheet = SpreadsheetApp.openById(sheetId);
  var formattedDate = Utilities.formatDate(targetDate, Session.getScriptTimeZone(), 'yyyyMMdd');
  
  var sheet = spreadsheet.getSheetByName(formattedDate);
  if (!sheet) {
    Logger.log('Sheet for ' + formattedDate + ' does not exist.');
    return;
  }

  var data = sheet.getDataRange().getValues();
  var headerFromTotals = {};
  
  // 集計処理
  for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップするためにiを1から開始
    var cValue = data[i][2]; // C列の値
    var bValue = data[i][1]; // B列の値
    var hValue = data[i][8]; // H列の値
    var 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;
    }
  }

  // 結果をスプレッドシートに書き込む
  var 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 (var headerFrom in headerFromTotals) {
    var passTotal = headerFromTotals[headerFrom].passTotal;
    var failTotal = headerFromTotals[headerFrom].failTotal;
    var successRate = Math.floor((passTotal / (passTotal + failTotal)) * 10000) / 100; // 切り捨て
    var successRateStr = successRate.toFixed(2) + '%'; // 小数点第2位まで表示
    var noneCount = headerFromTotals[headerFrom].noneCount;
    var quarantineCount = headerFromTotals[headerFrom].quarantineCount;
    var rejectCount = headerFromTotals[headerFrom].rejectCount;
    var quarantineRejectRate = Math.floor(((quarantineCount + rejectCount) / failTotal) * 10000) / 100; // 切り捨て
    var quarantineRejectRateStr = quarantineRejectRate.toFixed(2) + '%'; // 小数点第2位まで表示
    resultSheet.appendRow([headerFrom, formattedDate, passTotal, failTotal, successRateStr, noneCount, quarantineCount, rejectCount, quarantineRejectRateStr]);
  }
}