Business Intelligence tools

Tidelift’s APIs and reports make it easy to combine Tidelift’s data with other data sources in the tools your organization uses to organize work efforts.

Integrating Tidelift data with other data sources requires matching on one or more facets, a piece of data to identify a record among a large set of records. Tidelift’s APIs and reports focus on the following facets:

• Releases – A combination of ecosystem (RubyGems, npm, etc), package name (active_admin, vue), and version number (1.2.3, 3.2.25) – npm vue 3.2.25
  • Releases can also be represented as purls – Package URLs – pkg:npm/vue@3.2.25
• Packages – Just the ecosystem and package name part of a release – npm vue
  • Packages can also be represented as purls — Package URLs ­­— pkg:npm/vue
• Projects – A repository or application in your organization that contains open source software – libraries-io

Ensuring developers have a concise set of package migration work

It’s quite possible for a package or version of a package to have one or more violations, and even more than one of the same type of violation! Additionally, more violations may be discovered for the package or version as time moves on. To ensure you’re not creating duplicate tickets or counting packages more than they need to be in calculations, it’s best to build a unique composite key based on your organization’s needs.

For example, if your use case is to group actions by the following:

• Transitive package + version
• Direct package + version (if found)
• Project name

You could construct the unique identifier using these report fields and code that looks like this:

identifier = [violating_purl, direct_purl, project].join('-')
identifier = sha256(identifier)

The purl fields contain the package URL for the violating or direct package, which includes the ecosystem, package name, and version.

Prerequisites 

For these examples, you will need a Tidelift subscription, as well as:

• A catalog with at least one catalog standard enabled
• A project associated with that catalog
• A saved alignment with at least one violation

Keep in mind that the interfaces for the tools below may change unexpectedly. Adjust your use of them accordingly.

Targeting the solutions that will have the most risk impact in Microsoft Power BI

Power BI is a business intelligence tool that runs on Windows desktop and on the web. It can ingest and dig into JSON data like Tidelift’s All Project Violations report, which means you don’t need to expand individual JSON records into separate columns, making database table schema easier to work with. This assumes you have access to the web interface of Power BI and are comfortable with:

• Working with KQL databases, and that you have one already created
• Uploading CSV or JSON files into those databases and creating schemas for them
• Writing KQL queries
• Building a report from the results of a KQL query

(Writer’s note: I’m not the most experienced of Power BI users. If you know what you’re doing, you should be able to get a much better result and workflow than this!)

Steps

1. Download a copy of the All Projects Violations report (in JSON format) from your catalog’s Reports page:

2. Load the local JSON report data into a new table called tl_all_projects_violations. Ensure the format is JSON and that, under Advanced Options, Nested levels is set to 1:

3. Run the following KQL query to examine how you can filter Tidelift JSON data for vulnerabilities that are rated High or Critical:

tl_all_projects_violations
| where violation_type  == "vulnerabilities" and (
  violation_details.vulnerability.severity_rating == "High" or
  violation_details.vulnerability.severity_rating == "Critical"
  )
| extend cve_id = tostring(violation_details.vulnerability.id)
| extend severity_rating = tostring(violation_details.vulnerability.severity_rating)
| project action_id, ['project'], platform, violating_package, violating_version,
  cve_id, severity_rating

4. Build a Power BI report for the results:

• Include all the fields in a Table component
• Create a Stacked Bar Chart to filter on Projects
• Create a bar chart to filter on Severity Rating