Results 1 to 2 of 2

Thread: Using VBA to convert JSON, with multi-level dictionaries

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Using VBA to convert JSON, with multi-level dictionaries

    Hi All,

    I am trying to use the NVD database JSON files (available to download from https://nvd.nist.gov/vuln/data-feeds - ) so I can construct a sheet of all the vulnerailities and then I can sort them in Excel. Yes, I realise that NVD does offer a search function so I could limit it to Oracle or SAP, but then I have to go into each vulnerability to find out the score and the attack paths etc. The JSON file contains it all (I am using the recent file)

    I found https://codingislove.com/excel-json/ and used their .bas file and turned on the ms scripting flag and had some success. I was able to pull out the ID, the explanation and the time of submisison and update. Then I hit an issue. Its to do with the fact that I need to check the vendor name and sometimes vendor name is there and sometimes it isnt. So I did some more research and found https://stackoverflow.com/questions/...-exists-in-vba which showed me how to handle nested .exists statements, except I can't get it to work for this particular one. It keeps erroring with Error 05. Essentially it works for the first 3 entries as they have a vendor_name, but item 4 does not, although it has a vendor_data which is the level above. I've tried numerous if checks with a .exists but it just won't seem to take.

    Code:
    Sub getJSON()
    
    Dim FSO As New FileSystemObject
    Dim JsonTS As TextStream
    
    Set JsonTS = FSO.OpenTextFile("C:/Users/pt/Documents/ExcelJSON/test.json", ForReading)
    JsonText = JsonTS.ReadAll
    JsonTS.Close
        
    Set Json = ParseJson(JsonText)
    
    i = 1
    
    For Each Item In Json("CVE_Items")
    
    Worksheets("Sheet1").Cells(i, 1).Value = Item("cve")("CVE_data_meta")("ID")
    'Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")  <--- this errors if vendor_name does not exist, so I tried an if
    
    'MsgBox TypeName(Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")) <---- also errors if vendor_name does not exist, otherwise returns vendor name
    
    'If Not Item("cve")("affects")("vendor")("vendor_data")(1).Exists("vendor_name") Then <--- check to see if it doesnt exist doesnt work
    
    If Item("cve")("affects")("vendor")("vendor_data").Exists("vendor_name") Then  ' <----works for 1st 3 items in list as they have vendor_names then it crashes as 4th does not have vendor name
    Worksheets("Sheet1").Cells(i, 2).Value = Item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
    End If
    Worksheets("Sheet1").Cells(i, 3).Value = Item("cve")("description")("description_data")(1)("value") <--- works
    Worksheets("Sheet1").Cells(i, 4).Value = Item("publishedDate") <--- works
    Worksheets("Sheet1").Cells(i, 5).Value = Item("lastModifiedDate") <--- works
    
    i = i + 1
    Next
    
    End Sub
    The JSON schema I have checked with http://json.parser.online.fr/

    one without looks like this

    Code:
    "cve":{
    
    • "data_type":"CVE",
    • "data_format":"MITRE",
    • "data_version":"4.0",
    • "CVE_data_meta":{
      • "ID":"CVE-2017-3164",
      • "ASSIGNER":"cve@mitre.org"
      },
    • "affects":{
      • "vendor":{
        • "vendor_data":[]
    and a good one looks like this

    Code:
    "cve":{
    
    • "data_type":"CVE",
    • "data_format":"MITRE",
    • "data_version":"4.0",
    • "CVE_data_meta":{
      • "ID":"CVE-2017-12447",
      • "ASSIGNER":"cve@mitre.org"
      },
    • "affects":{
      • "vendor":{
        • "vendor_data":[
          1. {
            • "vendor_name":"gnome",

    [/CODE]

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,837
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Using VBA to convert JSON, with multi-level dictionaries

    Are you still stuck on this? I think the problem is that the vendor_data item exists, but its associated array (collection) is empty, signified by the empty square brackets:

    "vendor_data" : [ ]

    Here's one way to handle this:

    Code:
            If item("cve")("affects")("vendor")("vendor_data").Count > 0 Then
                Worksheets("Sheet1").Cells(i, 2).Value = item("cve")("affects")("vendor")("vendor_data")(1)("vendor_name")
            End If
    Last edited by John_w; Apr 22nd, 2019 at 11:00 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •