DMARCのレポートのメールを Googleスプレッドシートに記録していく（改変）
0. はじめに
DMARCレポートのメールが、定期的に私のGmailに届く。（ちなみに、GoogleがSPAMと判断する理由がわからない。）これをGASで処理したい、と思って検索すると、Qiita に見つけた。これをありがたくパクらせていただき、docomo, ezweb(KDDI) にも対応するように改変した。ChatGPTを使って。ただ、ChatGPT は「GASで添付の gz ファイルは解凍できないよ。」という。そこで、gz で固められたファイルの解凍もQiitaの記事をありがたくパクらせていただいた。
1. コード
ということで、詳細は上記のページを参照してください！
GoogleスプレッドシートのId を入れれば動くと思います。トリガーで、１日１回動作させると良いでしょう。
※ さらに改変した。（2024-03-21)
- すでに存在しているReport-IDがある場合にはスプレッドシートに追加しない。重複を許さないことで、1日1回、2日分を処理すれば十分。
- XMLの要素がない場合にも対応できるようにした。
- begin と end は人が読める形式にした。ただし、JST。
// オリジナルは 「Gmailに届いたDMARCレポートをGAS(Google Apps Script)でGoogleスプレッドシートに取り込む」
// https://qiita.com/sakamoto_koji/items/d8730110ccc0cf99db42
function myFunction() {
// スプレッドシート指定
let sheet = SpreadsheetApp.openById("GoogleスプレッドシートのId").getSheetByName("dmarc_report");
// 既存のReport-IDを取得する
const existingIds = sheet.getRange("D2:D" + sheet.getLastRow()).getValues().flat(); // Report-IDがD列に格納されていると仮定
let xmls = []; // スプシ書き出し用配列
let messages; // メールスレッド
let blob; // 添付ファイル（解凍前）
let fileblob; // 添付ファイル（解凍後）
// メール抽出条件指定 Google に限らず、"Report domain" "Report-id"がタイトル文字に含まれていて、 直近２日分
let query = 'subject: "Report domain" "Report-id" newer_than:2d ';
let threads = GmailApp.search(query);
// 取得したスレッドを古いものから新しいものへと並び替える
threads = threads.reverse();
for (var i = 0; i < threads.length; i++) {
messages = threads[i].getMessages();
console.log("メール件名：" + threads[i].getFirstMessageSubject());
for (let j = 0; j < messages.length; j++) {
// ここでメール情報を表示
// console.log("Date: " + messages[j].getDate()); // メールの日付
// console.log("Subject: " + messages[j].getSubject()); // メールの件名
blob = messages[j].getAttachments()[0].copyBlob();
let fileName = blob.getName();
try {
if (fileName.endsWith('.zip')) { // Google の場合
fileblob = Utilities.unzip(blob);
} else if (fileName.endsWith('.xml.gz')) { // Docomo, Ezweb(KDDI) の場合
blob.setContentType("application/x-gzip"); // gzip の解凍については、「GASでgzipファイルを解凍する」 https://qiita.com/uca/items/8a5af72eec087b125fd6 参照
let ungzippedBlob = Utilities.ungzip(blob); // Corrected method name
fileblob = [ungzippedBlob];
} else {
continue;
}
for (let k = 0; k < fileblob.length; k++) {
xmls = parseXml(fileblob[k].getDataAsString());
xmls.forEach(item => {
const itemArray = item.split(",");
const reportId = itemArray[3]; // Report-IDが配列の4番目にあると仮定
// Report-IDが既存のものでない場合のみ追加
if (!existingIds.includes(reportId)) {
sheet.appendRow(itemArray);
}
});
}
} catch (e) {
// Logger.log(e);
continue;
}
}
}
}
// 要素がなくても動作するようにする。
function safeGetChildText(parentElement, childName) {
var childElement = parentElement.getChild(childName);
if (childElement != null) {
return childElement.getText();
} else {
// Logger.log("no child : " + childName);
return ""; // 子要素が存在しない場合は空文字列を返す
}
}
// start, end のUNIXタイムスタンプをISO 形式に。
function unixTimestampToJstIsoString(unixTimestamp) {
// UNIXタイムスタンプをミリ秒単位に変換してDateオブジェクトを作成
var date = new Date(unixTimestamp * 1000);
// JSTのタイムゾーン（'GMT+0900'）を指定して、ISO 8601形式で日時をフォーマット
// 注意: 'Z' はUTCを示すため、JSTであることを明示するには '+09:00' を使用する
var formattedDate = Utilities.formatDate(date, 'GMT+0900', "yyyy-MM-dd'T'HH:mm");
return formattedDate;
}
// XML データを１行のデータでスプレッドシートに格納する。
function parseXml(fileblob_string) {
let document = XmlService.parse(fileblob_string);
let root = document.getRootElement();
let report_metadata = "";
let policy_published = "";
let record_txt = "";
let rtn_line = [];
var reportMetadataElement = root.getChild("report_metadata");
var dateRangeElement = reportMetadataElement.getChild("date_range");
report_metadata =
safeGetChildText(reportMetadataElement, "org_name")
+ "," + safeGetChildText(reportMetadataElement, "email")
+ "," + safeGetChildText(reportMetadataElement, "extra_contact_info")
+ "," + safeGetChildText(reportMetadataElement, "report_id")
+ "," + unixTimestampToJstIsoString(safeGetChildText(dateRangeElement, "begin"))
+ "," + unixTimestampToJstIsoString(safeGetChildText(dateRangeElement, "end"));
// + "," + safeGetChildText(dateRangeElement, "begin")
// + "," + safeGetChildText(dateRangeElement, "end");
var policyPublishedElement = root.getChild("policy_published");
policy_published =
safeGetChildText(policyPublishedElement, "domain")
+ "," + safeGetChildText(policyPublishedElement, "dkim")
+ "," + safeGetChildText(policyPublishedElement, "aspf")
+ "," + safeGetChildText(policyPublishedElement, "p")
+ "," + safeGetChildText(policyPublishedElement, "sp")
+ "," + safeGetChildText(policyPublishedElement, "pct")
+ "," + safeGetChildText(policyPublishedElement, "np");
let records = root.getChildren("record");
records.forEach(record => {
var rowElement = record.getChild("row");
var policyEvaluatedElement = rowElement.getChild("policy_evaluated");
var authResultsElement = record.getChild("auth_results");
var dkimElement = authResultsElement.getChild("dkim");
var spfElement = authResultsElement.getChild("spf");
record_txt =
safeGetChildText(rowElement, "source_ip")
+ "," + safeGetChildText(rowElement, "count")
+ "," + safeGetChildText(policyEvaluatedElement, "disposition")
+ "," + safeGetChildText(policyEvaluatedElement, "dkim")
+ "," + safeGetChildText(policyEvaluatedElement, "spf")
+ "," + safeGetChildText(record.getChild("identifiers"), "header_from")
+ "," + (dkimElement ? safeGetChildText(dkimElement, "domain")
+ "," + safeGetChildText(dkimElement, "result")
+ "," + safeGetChildText(dkimElement, "selector") : ",,,")
+ "," + (spfElement ? safeGetChildText(spfElement, "domain")
+ "," + safeGetChildText(spfElement, "result") : ",,");
// １行ずつ配列に入れてく
rtn_line.push(report_metadata + "," + policy_published + "," + record_txt);
}); // Fixed: added missing closing parenthesis for forEach loop
return rtn_line; // Fixed: corrected return statement placement
}