Google Sheets has a lot of great features for filtering, sharing, consuming, and formatting data as pointed out in this blog post from our partner Cloud Sherpas.

Technopedia has a lot of great data.  Technopedia has every software product and version published to the marketplace, the corresponding end of life dates for many of those products, and the number of known vulnerabilities linked to those products to name a few of the collected attributes within our catalog.

Pulling Technopedia data from the Technopedia API into Google sheets gives you a fast way to analyze and collaborate with others on software (or hardware) market data.  For example if you work with a distributed team or a team comprised of partners from various companies to assist you with the Software Asset Management or IT Security and Risk facets of your business then pulling this data into a Google Sheet is an easy way to collaborate on the data together.

In this example I pull information from Technopedia regarding the number of vulnerabilities linked to the various versions of Oracle Database.  This could easily have been any or all products.  For sake of this example I narrowed the focus of the Technopedia API query to a single product.  This example leverages the great work of the open source community.  I leveraged an open source function called ImportJSON.    This script is licensed under the GNU General Public License, version 3 (GPL-3.0) and is free to use.  BDNA actually provides via Technopedia API and our partner nexB the ability to identify the various types of open source licenses (e.g., MIT, GNU, BSD, etc.) associated to the myriad of open source projects.

To pull the JSON data into Google Sheets from the Technopedia API I leveraged the steps outlined in this helpful blog post.  Since the Technopedia API requires an api key sent via an authorization header for access a slight modification to the importJSON function is required.  Find the function ImportJSONAdvanced and add a parameter to UrlFetchApp.fetch.  The updated call should look like this (substituting in your API key and user):

UrlFetchApp.fetch(url,{
headers: {

‘Authorization’: ‘apikey ‘ +
‘YOUR_API_USER_HERE:YOUR_API_KEY_HERE’
}
});

In the first cell of my Google Sheet I added this call to the ImportJSON function:

=ImportJSON(“http://api.technopedia.com/api/v1/cpe_uri/?cat_sw_product_id=1142809&limit=500”,
“/cpe_uri_list/cpe_uri,/cpe_uri_list/definition,/cpe_uri_list/taxonomy2012,/cpe_uri_list/manufacturer,
/cpe_uri_list/sw_product,/cpe_uri_list/sw_release,/cpe_uri_list/sw_edition,/cpe_uri_list/sw_version_group,
/cpe_uri_list/sw_version,/cpe_uri_list/cvss_score_max,/cpe_uri_list/cvss_severity_max,/cpe_uri_list/cve_count”, “noInherit, noTruncate”)

This resulted in populating this spreadsheet with the fields I specified above:

tp-api-to-sheets-1

Once in a Google sheet you can export to Microsoft Excel if you prefer or do common spreadsheet actions like filtering by a specific version of the product (in this example Oracle Database) to see the number of known vulnerabilities (Cve Count column) linked to that version.

 

Filter on Oracle Database Version

Filter on Oracle Database Version