【GAS】macOSのバージョンとCVE情報を紐づけてみる

なぜ作成したのか

• せっかく脆弱性情報の見方をまとめたので、何かしら役に立たないか試行したい気分

技術スタック

• Google Spreadsheet
• GAS
• macos_data_feed.json
• NVD CVE API

やりたいこと

• SoFaのJSONファイルからmacOSのバージョン情報、関連CVE番号の情報を取得する
• 登場するCVE番号についてNVDのAPIで詳細情報を取得する
• AppsheetでOSバージョンとCVE情報を紐づけて見えるようにする

GAS

• JSONで記録されているすべてのバージョンを取得する場合は「Latest」ではなく「SecurityReleases」で取得し、バージョン数文繰り返し処理する
• 今回はめんどくさかったので取り合えず最新のバージョン情報だけ取得する「Latest」の情報でテストする（件数多いとNVDのAPI部分の403エラーが頻発する）

// スクリプトプロパティからスプレッドシートIDを取得
const SPREADSHEET_ID = PropertiesService.getScriptProperties().getProperty('SPREADSHEET_ID');
const JSON_URL = 'https://sofafeed.macadmins.io/v1/macos_data_feed.json';
const CVE_API_BASE = 'https://services.nvd.nist.gov/rest/json/cves/2.0?cveId=';

function main() {
  const data = fetchJsonData();
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);

  updateOsVersionSheet(spreadsheet, data);
  updateOsVersionCVEsSheet(spreadsheet, data);
  updateOsVersionExploitedCVEsSheet(spreadsheet, data);
  updateWorkCVEsSheet(spreadsheet, data);
  updateCVEsSheet(spreadsheet);
}

function fetchJsonData() {
  const response = UrlFetchApp.fetch(JSON_URL);
  return JSON.parse(response.getContentText());
}

function updateOsVersionSheet(spreadsheet, data) {
  const sheet = getOrCreateSheet(spreadsheet, 'OsVersion');
  sheet.clear();
  sheet.appendRow(['OSVersion', 'ProductVersion', 'ReleaseDate', 'ExpirationDate', 'SecurityInfo']);

  data.OSVersions.forEach(entry => {
    sheet.appendRow([
      entry.OSVersion,
      entry.Latest.ProductVersion,
      entry.Latest.ReleaseDate,
      entry.Latest.ExpirationDate,
      entry.Latest.SecurityInfo
    ]);
  });
}

function updateOsVersionCVEsSheet(spreadsheet, data) {
  const sheet = getOrCreateSheet(spreadsheet, 'OsVersion-CVEs');
  sheet.clear();
  sheet.appendRow(['id', 'ProductVersion', 'CVE', 'validation']);

  let id = 1;
  data.OSVersions.forEach(entry => {
    if (entry.Latest.CVEs) {
      Object.entries(entry.Latest.CVEs).forEach(([cve, isValid]) => {
        if (cve.startsWith('CVE-')) {
          sheet.appendRow([id++, entry.Latest.ProductVersion, cve, isValid]);
        }
      });
    }
  });
}

function updateOsVersionExploitedCVEsSheet(spreadsheet, data) {
  const sheet = getOrCreateSheet(spreadsheet, 'OsVersion-ActivelyExploitedCVEs');
  sheet.clear();
  sheet.appendRow(['id', 'ProductVersion', 'ActivelyExploitedCVE']);

  let id = 1;
  data.OSVersions.forEach(entry => {
    if (entry.Latest.ActivelyExploitedCVEs) {
      entry.Latest.ActivelyExploitedCVEs.forEach(cve => {
        if (cve.startsWith('CVE-')) {
          sheet.appendRow([id++, entry.Latest.ProductVersion, cve]);
        }
      });
    }
  });
}

function updateWorkCVEsSheet(spreadsheet, data) {
  const sheet = getOrCreateSheet(spreadsheet, 'work-CVEs');
  sheet.clear();
  sheet.appendRow(['CVE']);

  const uniqueCVEs = new Set();
  data.OSVersions.forEach(entry => {
    if (entry.Latest.CVEs) {
      Object.keys(entry.Latest.CVEs).forEach(cve => {
        if (cve.startsWith('CVE-')) {
          uniqueCVEs.add(cve);
        }
      });
    }
  });

  Array.from(uniqueCVEs).forEach(cve => sheet.appendRow([cve]));
}

function updateCVEsSheet(spreadsheet) {
  const workSheet = spreadsheet.getSheetByName('work-CVEs');
  const cveSheet = getOrCreateSheet(spreadsheet, 'CVEs');

  const existingCVEs = new Set(cveSheet.getRange('A2:A').getValues().flat());
  const newCVEs = workSheet.getRange('A2:A').getValues().flat().filter(cve => !existingCVEs.has(cve));

  newCVEs.forEach(cve => {
    const response = UrlFetchApp.fetch(`${CVE_API_BASE}${cve}`);
    const cveData = JSON.parse(response.getContentText()).vulnerabilities[0].cve;

    var tcve =  cve;
    var tpublished =  cveData.published;
    var tlastModified = cveData.lastModified;
    var tvulnStatus =  cveData.vulnStatus;
    if (cveData.descriptions){ 
      var tdescription =  cveData.descriptions[0].value;
    } else { 
      var tdescription =  '';
    } 
    if (cveData.metrics.cvssMetricV31){
      var tvectorString = cveData.metrics.cvssMetricV31[0].cvssData.vectorString;
      var tbaseScore = cveData.metrics.cvssMetricV31[0].cvssData.baseScore;
      var tbaseSeverity = cveData.metrics.cvssMetricV31[0].cvssData.baseSeverity;
      var tattackVector = cveData.metrics.cvssMetricV31[0].cvssData.attackVector;
      var tattackComplexity = cveData.metrics.cvssMetricV31[0].cvssData.attackComplexity;
      var tprivilegesRequired = cveData.metrics.cvssMetricV31[0].cvssData.privilegesRequired;
      var tuserInteraction = cveData.metrics.cvssMetricV31[0].cvssData.userInteraction;
      var tconfidentialityImpact = cveData.metrics.cvssMetricV31[0].cvssData.confidentialityImpact;
      var tintegrityImpact = cveData.metrics.cvssMetricV31[0].cvssData.integrityImpact;
      var tavailabilityImpact = cveData.metrics.cvssMetricV31[0].cvssData.availabilityImpact;
      var texploitabilityScore = cveData.metrics.cvssMetricV31[0].exploitabilityScore;
      var timpactScore = cveData.metrics.cvssMetricV31[0].impactScore;
    } else {
      var tvectorString = '';
      var tbaseScore = '';
      var tbaseSeverity = '';
      var tattackVector = '';
      var tattackComplexity = '';
      var tprivilegesRequired = '';
      var tuserInteraction = '';
      var tconfidentialityImpact = '';
      var tintegrityImpact = '';
      var tavailabilityImpact = '';
      var texploitabilityScore = '';
      var timpactScore = '';
    }
    if (cveData.metrics.cvssMetricV2){
      var t2VectorString = cveData.metrics.cvssMetricV2[0].cvssData.VectorString;
      var t2baseScore = cveData.metrics.cvssMetricV2[0].cvssData.baseScore;
      var t2accessVector = cveData.metrics.cvssMetricV2[0].cvssData.accessVector;
      var t2accessComplexity = cveData.metrics.cvssMetricV2[0].cvssData.accessComplexity;
      var t2authentication = cveData.metrics.cvssMetricV2[0].cvssData.authentication;
      var t2confidentialityImpact = cveData.metrics.cvssMetricV2[0].cvssData.confidentialityImpact;
      var t2integrityImpact = cveData.metrics.cvssMetricV2[0].cvssData.integrityImpact;
      var t2availabilityImpact = cveData.metrics.cvssMetricV2[0].cvssData.availabilityImpact;
      var t2baseSeverity = cveData.metrics.cvssMetricV2[0].baseSeverity;
      var t2exploitabilityScore = cveData.metrics.cvssMetricV2[0].exploitabilityScore;
      var t2impactScore = cveData.metrics.cvssMetricV2[0].impactScore;
      var t2acInsurfInfo = cveData.metrics.cvssMetricV2[0].acInsurfInfo;
      var t2obtainAllPrivilege = cveData.metrics.cvssMetricV2[0].obtainAllPrivilege;
      var t2obtainUserPrivilege = cveData.metrics.cvssMetricV2[0].obtainUserPrivilege;
      var t2obtainOtherPrivilege = cveData.metrics.cvssMetricV2[0].obtainOtherPrivilege;
      var t2userInteractionRequired = cveData.metrics.cvssMetricV2[0].userInteractionRequired;
    } else {
      var t2VectorString = '';
      var t2baseScore = '';
      var t2accessVector = '';
      var t2accessComplexity = '';
      var t2authentication = '';
      var t2confidentialityImpact = '';
      var t2integrityImpact = '';
      var t2availabilityImpact = '';
      var t2baseSeverity = '';
      var t2exploitabilityScore = '';
      var t2impactScore = '';
      var t2acInsurfInfo = '';
      var t2obtainAllPrivilege = '';
      var t2obtainUserPrivilege = '';
      var t2obtainOtherPrivilege = '';
      var t2userInteractionRequired = '';
    }

    const row = [
      tcve,
      tpublished,
      tlastModified,
      tvulnStatus,
      tdescription,
      tvectorString,
      tbaseScore,
      tbaseSeverity,
      tattackVector,
      tattackComplexity,
      tprivilegesRequired,
      tuserInteraction,
      tconfidentialityImpact,
      tintegrityImpact,
      tavailabilityImpact,
      texploitabilityScore,
      timpactScore,
      t2VectorString,
      t2baseScore,
      t2accessVector,
      t2accessComplexity,
      t2authentication,
      t2confidentialityImpact,
      t2integrityImpact,
      t2availabilityImpact,
      t2baseSeverity,
      t2exploitabilityScore,
      t2impactScore,
      t2acInsurfInfo,
      t2obtainAllPrivilege,
      t2obtainUserPrivilege,
      t2obtainOtherPrivilege,
      t2userInteractionRequired
    ];

    cveSheet.appendRow(row);
  });
}

function getOrCreateSheet(spreadsheet, sheetName) {
  const sheet = spreadsheet.getSheetByName(sheetName);
  return sheet || spreadsheet.insertSheet(sheetName);
}

SpreadSheet

• Sheet: CVEs
  
• Sheet: OsVersion-ActivelyExploitedCVEs
  
• Sheet: OsVersion-CVEs
  
• Sheet: work-CVEs
  
• Sheet: OsVersion
  

Appsheet

所感

• ChatGPTくんに基礎実装してもらったけど、JSONのインプットが甘かったせいかエラーが頻発してお話にならなかったので、値の取得部分はほとんど作り直した。うまく指示できるひとになりたい
• このあたりを毎日回しておけば、今回のアップデートでどれくらいヤバい脆弱性対応されてるのかなーというのが件数として把握できそう
• 同様にWindows側もMSRCのRSSFeed情報を基にCVE情報との紐づけできるような気がする。明日暇だったら実装してみようかな