🦆
【GAS】WindowsOSのバージョンとCVE情報を紐づけてみる（途中２）

2025/03/22に公開
 なぜ作成したのか昨日macOSで実装した機能をWindows側でも実装してみようという試み（続き）

 技術スタックGoogle Spreadsheet
GAS
MSRC-Microsoft-Security-Updates-API
NVD CVE API

 やりたいことMSRCのAPIからWindowsOSのバージョン情報、関連CVE番号の情報を取得する
登場するCVE番号についてNVDのAPIで詳細情報を取得する
AppsheetでOSバージョンとCVE情報を紐づけて見えるようにする

 GAS（いったん処理は実装、仕組み上は動く）処理的には一通り動くけれど、GASの動作時間上限6分を超えちゃうので実質役に立たない
重複排除は蓄積データから不要データ削除すれば多少改善するけれど、脆弱性情報とOSバージョンの紐づけ情報がタイムアウトするのはどうしたものか
// スクリプトプロパティからスプレッドシートIDを取得
const SPREADSHEET_ID = PropertiesService.getScriptProperties().getProperty('SPREADSHEET_ID');
const MSRC_API_UPDATES_URL = 'https://api.msrc.microsoft.com/cvrf/v3.0/updates/';

function main() {
  // Updateリストを取得
  const updateData = fetchMsrcUpdateData();
  const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);

  updateWindowsVersionSheet(spreadsheet, updateData);

  updateWorkWinCVEsSheet(spreadsheet);
  updateCVEsSheet(spreadsheet);
}

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

function updateWindowsVersionSheet(spreadsheet, data) {
  const sheet = getOrCreateSheet(spreadsheet, 'WindowsVersion');
  // シートにタイトル行がない場合はヘッダ行を追加
  if(sheet.getLastRow() === 0){
    sheet.appendRow(['ID', 'Alias', 'DocumentTitle', 'Severity','InitialReleaseDate','CurrentReleaseDate', 'CvrfUrl']);
  }
  // シートにタイトル行がない場合はヘッダ行を追加
  const cveSheet = getOrCreateSheet(spreadsheet, 'WindowsVersion-CVEs');
  if(cveSheet.getLastRow() === 0){
   cveSheet.appendRow(['id', 'CVE', 'ProductID']);
  }
  // シートにタイトル行がない場合はヘッダ行を追加
  const productSheet = getOrCreateSheet(spreadsheet, 'WindowsProduct');
  if(productSheet.getLastRow() === 0){
    productSheet.appendRow(['ProductID', 'FullProductName']);
  }

  // 既存行とマッチングし、新規レコードの場合のみ処理を行う
  const existingRecords = new Set(sheet.getRange('A2:A' + sheet.getLastRow()).getValues().flat());

  data.value.forEach(entry => {
    const ID = entry.ID || '';
    const Alias = entry.Alias || '';
    const DocumentTitle = entry.DocumentTitle || '';
    const Severity = entry.Severity || '';
    const InitialReleaseDate = entry.InitialReleaseDate || '';
    const CurrentReleaseDate = entry.CurrentReleaseDate || '';
    const CvrfUrl = entry.CvrfUrl || '';
    
    if (!existingRecords.has(ID)) {
      sheet.appendRow([
        ID,
        Alias,
        DocumentTitle,
        Severity,
        InitialReleaseDate,
        CurrentReleaseDate,
        CvrfUrl
      ]);
      if(entry.CvrfUrl){
        const urlResponse = UrlFetchApp.fetch(entry.CvrfUrl);
        var xmlDoc = XmlService.parse(urlResponse.getContentText());
        var rootDoc = xmlDoc.getRootElement();
        // 新規レコードが検出された場合のみ各種シートの追加を行う
        updateWindowsProductSheet(productSheet,rootDoc)
        updateWindowsVersionCVEsSheet(cveSheet,rootDoc,entry.ID);
     }
    }
  });


}

function updateWindowsProductSheet(sheet, data) {
  // Namespaceの定義
  var ns = XmlService.getNamespace("prod", "http://www.icasi.org/CVRF/schema/prod/1.1");
  var products  = data.getChildren("ProductTree",ns);
  products.forEach(entry => {
    var rootBranch = entry.getChildren("Branch",ns);
    // var windowsFamilys = rootBranch.getChildren("Branch",ns);
    rootBranch.forEach(branch => {
      if (branch.getAttribute('Type').getValue() === 'Vendor' && branch.getAttribute('Name').getValue() === 'Microsoft') {
        const subBranches = branch.getChildren('Branch', ns);      
        subBranches.forEach(subBranch => {
          if (subBranch.getAttribute('Type').getValue() === 'Product Family' && subBranch.getAttribute('Name').getValue() === 'Windows') {
            const products = subBranch.getChildren('FullProductName', ns);            
            products.forEach(product => {
              const productID = product.getAttribute('ProductID').getValue();
              const productName = product.getText();
              sheet.appendRow([
                productID,
                productName
              ])
            });
          }
        });
      }
    });
  });
}

function updateWindowsVersionCVEsSheet(sheet, data, id) {
  var ns = XmlService.getNamespace("vuln", "http://www.icasi.org/CVRF/schema/vuln/1.1");
  var vulnerabilitys  = data.getChildren("Vulnerability",ns);
  vulnerabilitys.forEach(entry => {
    var cve = entry.getChildren("CVE",ns)[0].getValue();
    var productIDs = entry.getChildren("ProductStatuses",ns)[0].getChildren("Status",ns)[0].getChildren("ProductID",ns);
    productIDs.forEach(pdata => {
      sheet.appendRow([
        id,
        cve,
        pdata.getValue()
      ])
    });
  }) ;
}

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

  const cveSheet = spreadsheet.getSheetByName('WindowsVersion-CVEs');
  const uniqueCVEs = new Set(cveSheet.getRange('B2:B' + cveSheet.getLastRow()).getValues().flat());

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


function updateCVEsSheet(spreadsheet) {
  const workSheet = spreadsheet.getSheetByName('work-CVEs');
  const cveSheet = getOrCreateSheet(spreadsheet, 'CVEs');
  if(cveSheet.getLastRow() === 0){
    sheet.appendRow([
      'id',	
      'published',	
      'lastModified',	
      'vulnStatus',	
      'descriptions.value',	
      'metrics.cvssMetricV31.cvssData.VectorString',	
      'metrics.cvssMetricV31.cvssData.baseScore',	
      'metrics.cvssMetricV31.cvssData.baseSeverity',	
      'metrics.cvssMetricV31.cvssData.attackVector',	
      'metrics.cvssMetricV31.cvssData.attackComplexity',	
      'metrics.cvssMetricV31.cvssData.privilegesRequired',	
      'metrics.cvssMetricV31.cvssData.userInteraction',	
      'metrics.cvssMetricV31.cvssData.confidentiaityImpact',	
      'metrics.cvssMetricV31.cvssData.integrityImpact',	
      'metrics.cvssMetricV31.exploitabilityScore',	
      'metrics.cvssMetricV31.impactScore',	
      'metrics.cvssMetricV2.VectorString',	
      'metrics.cvssMetricV2.baseScore',
      'metrics.cvssMetricV2.accessVector',
      'metrics.cvssMetricV2.accessComplexity',
      'metrics.cvssMetricV2.authentication',	
      'metrics.cvssMetricV2.confidentiaiityImpact',
      'metrics.cvssMetricV2.integrityImpact',	
      'metrics.cvssMetricV2.availabilityImpact',	
      'metrics.cvssMetricV2.baseSeverity',	
      'metrics.cvssMetricV2.exploitabilityScore',	
      'metrics.cvssMetricV2.impactScore',	
      'metrics.cvssMetricV2.acInsurfInfo',	
      'metrics.cvssMetricV2.obtainAllPrivilege',	
      'metrics.cvssMetricV2.obtainUserPrivilege',
      'metrics.cvssMetricV2.obtainOtherPrivilege',	
      'metrics.cvssMetricV2.userInteractionRequired'
    ]);
  }

  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);
}


 所感見直しててProductのシートの重複排除処理入れるの忘れてるのに気づく
過去データのセットアップは前提として、毎月のデータ処理を繰り返し処理で保管していくように実装を考えないととてもじゃないが使えない
また気が向いたら改修しよう
GitHubで編集を提案
なぜ作成したのか

技術スタック

やりたいこと

GAS（いったん処理は実装、仕組み上は動く）

所感

Discussion
