0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pbjpkas

Excel VBAでEUVDのJSONデータを取得する

0
Posted at

1. はじめに

Excel VBAでKEVのJSONデータを取得するで

Excel VBAでWebからJSON形式のデータを取得しパースできるようになりました。KEVだけでなくNational Vulnerability Databaseが提供しているVulnerability APIsでCVE一覧を取得するのにも応用できると思います。

と書きましたが、本稿ではEUVDのsearch APIで脆弱性一覧を取得します。

2. EUVD

EUVDはENISA（The European Union Agency for Cybersecurity1）が提供する脆弱性データベースで2025年5月13日に正式公開されました2。EUVDはAPIを提供しておりEUVD API Documentationがその公式ドキュメントです。

EUVDの詳しい解説はENISAのConsult the European Vulnerability Database to enhance your digital security!やEUVDのFAQ、EUVDとはで検索して得られる記事に譲ります。

3. EUVDのJSONデータを取得する

Excel VBAでKEVのJSONデータを取得すると同様、JSONのパースにVBA-JSONのJsonConverter.bas v2.3.1を使用させていただきます。

  1. Excelファイル（euvdList.xlsm）に"euvdList"シートをあらかじめ用意します
  2. Excel VBAで次の3つの関数を作ります
    • GetEUVDList()：EUVDのsearch APIで検索を行ってJSONデータを取得しExcelのシートへ展開する
    • GetHttp()：引数で与えられたURLからデータをGetし、レスポンスヘッダとレスポンステキストを取得するとともにHTTPステータスコードをリターンする
    • SaveTextData()：引数で与えられたファイルパスとテキストデータをファイルへ保存する
  • GetHttp()とSaveTextData()はExcel VBAでKEVのJSONデータを取得するで作成したものと一緒です。
  • vendorNameやproductNameなど決め打ちとなっています。実用上はセルに入力された値を引数で受け取るような改修が必要です。
  • EUVD API Documentationの "Query vulnerabilities with flexible filters" の説明がパラメータのみでレスポンスがないため、本稿執筆時点での実動作にもとづいて作成しています。
  • APIを叩くとHTTPステータスコード「429」が返ってくることがありますがリトライ処理は入っていません。
  • 正規表現でRegExpクラスを使用しています。VBScriptの段階的廃止の影響によりWindowsやExcelのバージョンによって実装方法が異なります。詳しくはPrepare your VBA projects for VBScript deprecation in Windowsをご覧ください。

筆者の環境は以下の通りです。

  • Windows 11 Pro 25H2
  • Microsoft Office Home and Business Premium
  • Microsoft® Excel® 2016 MSO (バージョン 2606 ビルド 16.0.20131.20154) 64 ビット
euvdList.xlsm
'euvdList.xlsm by ka's@pbjpkas 2026
' MIT License
' 動作確認
'   Windows 11 25H2 26200.8875
'   Microsoft Office Home and Business Premium
'     Excel 2606 20131.20154

Option Explicit

Sub GetEUVDList()
    Dim vendorName As String
    vendorName = "" '"Microsoft"
    
    Dim productName As String
    productName = "log4j" '"Excel"
    
    Dim fromScore As Long
    fromScore = 7
    
    Dim toScore As Long
    toScore = 10
    
    Dim exploited As Boolean
    exploited = False
    
    Dim sizeNum As Long 'size (integer, default 10, e.g., size=100 (max))
    sizeNum = 10
    
    '------------------------------
    ' 出力先シートの初期化
    '------------------------------
    Dim sheetName As String
    sheetName = "euvdList"
    Sheets(sheetName).Cells.Clear
    Sheets(sheetName).Cells(1, "A") = "No."
    Sheets(sheetName).Cells(1, "B") = "id"
    Sheets(sheetName).Cells(1, "C") = "enisaUuid"
    Sheets(sheetName).Cells(1, "D") = "description"
    Sheets(sheetName).Cells(1, "E") = "datePublished"
    Sheets(sheetName).Cells(1, "F") = "dateUpdated"
    Sheets(sheetName).Cells(1, "G") = "baseScore"
    Sheets(sheetName).Cells(1, "H") = "baseScoreVersion"
    Sheets(sheetName).Cells(1, "I") = "baseScoreVector"
    Sheets(sheetName).Cells(1, "J") = "references"
    Sheets(sheetName).Cells(1, "K") = "aliases"
    Sheets(sheetName).Cells(1, "L") = "CVE ID" 'aliasesからCVE ID部分を切り出したもの
    Sheets(sheetName).Cells(1, "M") = "assigner"
    Sheets(sheetName).Cells(1, "N") = "epss"
    Sheets(sheetName).Cells(1, "O") = "exploitedSince"
    Sheets(sheetName).Cells(1, "P") = "enisaIdProduct-id"
    Sheets(sheetName).Cells(1, "Q") = "enisaIdProduct-product-name"
    Sheets(sheetName).Cells(1, "R") = "enisaIdProduct-product-vendor-name"
    Sheets(sheetName).Cells(1, "S") = "enisaIdProduct-product_version"
    Sheets(sheetName).Cells(1, "T") = "enisaIdVendor-id"
    Sheets(sheetName).Cells(1, "U") = "enisaIdVendor-vendor-name"
    
    '------------------------------
    ' baseURLの作成
    '------------------------------
    Dim baseURL As String
    baseURL = "https://euvdservices.enisa.europa.eu/api/search?fromScore=" & CStr(fromScore) & "&toScore=" & CStr(toScore) & "&size=" & CStr(sizeNum)
    
    ' exploited=trueが指定されたときは&exploited=trueを付加する
    If exploited Then
        baseURL = baseURL & "&exploited=true"
    End If
    
    ' vendorNameが指定されたときは&vendor=...を付加する
    If Len(vendorName) Then
        baseURL = baseURL & "&vendor=" & vendorName
    End If
    
    baseURL = baseURL & "&product=" & productName
    
    '------------------------------
    ' EUVDリストの取得
    '------------------------------
    Dim httpStatus As Long
    Dim allResponseHeaders As String
    Dim responseText As String
    Dim url As String
    
    ' pageNum
    ' pageのカウンタ
    ' 初期値は0
    Dim pageNum As Long
    pageNum = 0
    
    ' pageMax
    ' APIを叩いて返ってきたtotalの値をsizeNumで割って上書きする
    ' 初期値は1
    Dim pageMax As Long
    pageMax = 1
    
    ' row
    ' 出力先シートの何行目か
    ' 初期値は2; 1行目はカラムのタイトルで使用のため
    Dim row As Long
    row = 2
    Dim euvdList As Object
    
    Dim i As Long ' 何番目のitemsか
    Dim j As Long ' 何番目のenisaIdProductか
    
    ' aliasesからCVE IDを切り出すためにRegExpを使用
    'Dim rExp As Object
    'Set rExp = CreateObject("VBScript.RegExp")
    Dim rExp As New RegExp
    rExp.Pattern = "CVE-\d{4}-\d+"
    rExp.IgnoreCase = True
    
    Do While pageNum <= pageMax
        ' APIを叩いてresponse header と response text をファイルに出力する
        url = baseURL & "&page=" & CStr(pageNum)
        httpStatus = GetHttp(url, allResponseHeaders, responseText)
        Call SaveTextFile(ThisWorkbook.path & "\euvd_" & vendorName & "_" & productName & "_page" & CStr(pageNum) & "_allResponseHeaders.txt", allResponseHeaders)
        Call SaveTextFile(ThisWorkbook.path & "\euvd_" & vendorName & "_" & productName & "_page" & CStr(pageNum) & "_responseText.txt", responseText)
        
        If httpStatus <> 200 Then
            MsgBox ("HTTP response is not 200 OK: " & httpStatus)
            Exit Sub
        End If
        
        ' responseTextをパースする
        Set euvdList = JsonConverter.ParseJson(responseText)
        
        ' 期待値は2；itemsとtotalの2つ
        If euvdList.Count = 0 Then
            MsgBox "No responseText"
            Exit Sub
        End If
        
        ' pageMaxを上書きする
        pageMax = Int((euvdList("total") - 1) / sizeNum)
        
        For i = 1 To euvdList("items").Count
            For j = 1 To euvdList("items")(i)("enisaIdProduct").Count
                Sheets(sheetName).Cells(row, "A") = pageNum * sizeNum + i
                Sheets(sheetName).Cells(row, "B") = euvdList("items")(i)("id")
                Sheets(sheetName).Cells(row, "C") = euvdList("items")(i)("enisaUuid")
                Sheets(sheetName).Cells(row, "D") = euvdList("items")(i)("description")
                Sheets(sheetName).Cells(row, "E") = euvdList("items")(i)("datePublished")
                Sheets(sheetName).Cells(row, "F") = euvdList("items")(i)("dateUpdated")
                Sheets(sheetName).Cells(row, "G") = euvdList("items")(i)("baseScore")
                Sheets(sheetName).Cells(row, "H") = euvdList("items")(i)("baseScoreVersion")
                Sheets(sheetName).Cells(row, "I") = euvdList("items")(i)("baseScoreVector")
                Sheets(sheetName).Cells(row, "J") = euvdList("items")(i)("references")
                Sheets(sheetName).Cells(row, "K") = euvdList("items")(i)("aliases")
                Sheets(sheetName).Cells(row, "L") = rExp.Execute(euvdList("items")(i)("aliases"))(0).Value
                Sheets(sheetName).Cells(row, "M") = euvdList("items")(i)("assigner")
                Sheets(sheetName).Cells(row, "N") = euvdList("items")(i)("epss")
                If euvdList("items")(i).Exists("exploitedSince") Then
                    Sheets(sheetName).Cells(row, "O") = euvdList("items")(i)("exploitedSince")
                Else
                    Sheets(sheetName).Cells(row, "O") = "Not Exploited"
                End If
                Sheets(sheetName).Cells(row, "P") = euvdList("items")(i)("enisaIdProduct")(j)("id")
                Sheets(sheetName).Cells(row, "Q") = euvdList("items")(i)("enisaIdProduct")(j)("product")("name")
                Sheets(sheetName).Cells(row, "R") = euvdList("items")(i)("enisaIdProduct")(j)("product")("vendor")("name")
                Sheets(sheetName).Cells(row, "S") = euvdList("items")(i)("enisaIdProduct")(j)("product_version")
                Sheets(sheetName).Cells(row, "T") = euvdList("items")(i)("enisaIdVendor")(1)("id")             ' 1で決め打ち
                Sheets(sheetName).Cells(row, "U") = euvdList("items")(i)("enisaIdVendor")(1)("vendor")("name") ' 1で決め打ち
                row = row + 1
            Next j
        Next i
        
        Set euvdList = Nothing
        pageNum = pageNum + 1
    Loop
    
End Sub

Function GetHttp(url As String, httpAllResponseHeaders As String, httpResponseText As String) As Long
    
    Dim httpObj As Object
    Set httpObj = CreateObject("MSXML2.XMLHTTP")
    
    httpObj.Open "GET", url, False
    httpObj.Send
    
    httpAllResponseHeaders = httpObj.getAllResponseHeaders()
    httpResponseText = httpObj.responseText
    
    GetHttp = httpObj.Status
    
End Function

Sub SaveTextFile(path As String, textData As String)
    
    Dim fileNum As Long
    fileNum = FreeFile()
    
    Open path For Output As #fileNum
    Print #fileNum, textData
    Close #fileNum
    
End Sub

4. おわりに

Excel VBAでEUVDの脆弱性一覧を取得できました。

  1. 2004年の設立から2019年6月27日に改称するまで「 European Network and Information Security Agency」という名称でした。 ↩

  2. Consult the European Vulnerability Database to enhance your digital security! ↩

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?