Using VBA to convert JSON, with multi-level dictionaries

[oitbc]

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/21936044/checking-if-a-nested-dictionary-key-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.

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

"cve":{
[LIST]
[*]"data_type":"CVE", 
[*]"data_format":"MITRE", 
[*]"data_version":"4.0", 
[*]"CVE_data_meta":{
[LIST]
[*]"ID":"CVE-2017-3164", 
[*]"ASSIGNER":"cve@mitre.org" 
[/LIST]
}, 
[*]"affects":{
[LIST]
[*]"vendor":{
[LIST]
[*]"vendor_data":[] 
[/LIST]
  
[/LIST]
  
[/LIST]

and a good one looks like this

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

[/CODE]

 

[John_w]

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:

        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