Help with JSON import from NIST

[anf1sher]

I'm trying to import the CVEs from NIST using this page.

https://nvd.nist.gov/vuln/data-feeds#JSON_FEED

I'm trying to get the ZIP feed

CVE-2018       7/6/2018

But when I bring it into Power BI it looks like this

CVE_data_type       CVE

CVE_data_format   MITRE

CVE_data_version  4.0 

I expect it to show all of the CVEs instead

[v-yulgu-msft]

Hi @anf1sher,

Here are some similar examples for your reference:

Call web API, receive ZIP, decompress multiple JSON files, Load Them

import gzip csv from web to power bi.md

Regards,

Yuliana Gu

[anf1sher]

Either I didn't understand what they were trying say or it didn't help my problem. 

[squiddly]

Hi anf1sher,

I've gone down this road -- it's a LOT of data for Power BI to chew through.  The following code will download the JSON in its .GZ format, unzip it, and parse it.  If you're going to be doing this for all of the CVE data (2002-2018) then you might want to make a PowerShell script to download those files and extract them to a local folder, then point Power BI to the folder contents.  If that's something you want to do, let me know and I'll share the script I wrote.

let
    NvdUrl = "https://static.nvd.nist.gov/feeds/json/cve/1.0/nvdcve-1.0-2018.json.gz",
    Source = Json.Document(Binary.Decompress(Web.Contents(NvdUrl),Compression.GZip)),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{5}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"cve", "configurations", "impact", "publishedDate", "lastModifiedDate"}, {"Column1.cve", "Column1.configurations", "Column1.impact", "Column1.publishedDate", "Column1.lastModifiedDate"})
in
    #"Expanded Column1"

You could also create a function to loop through and get 2002 thru 2018 but I don't know enough about that to be of help.

[diederd]

Afternoon squiddly

Would your PS script still be available for review? I'm looking to implement a similar solution; however as you have already eluded to there is a lot of data for PBI to import and as such am looking for an alternate solution.

Thank you in advance.

Denver

[Bennos82]

Hi Squiddly,

Im trying to download and extract all the CVE data from NIST (2002-2019) but am not having much luck.

You mentioned you may be able to share your script - If you could it would solve a massive headache for me.

Many thanks in advance

Ben