VirusTotal Lookups in Microsoft Power BI - Processing JSON

 By Tony Lee

So we are on this Power BI binge... and figured a VirusTotal lookup report and dashboard would be useful as a stand alone tool as well as a drill through fed by other reports. Presented below are the results of our efforts, steps we took, and limitations we encountered (as newbz). In typical form, we released the work for free so everyone can use, contribute, and modify as they see fit.  Enjoy!:

https://github.com/TonyLeeVT/VirusTotal-PowerBI-Lookup/

Figure 1:  Resulting VT Lookup report in Power BI

Features

Let's start with what this report provides -- from top left to bottom right:

• Slicer (filter) to filter on engines that hit or missed the sample
• Permalink to the results
• Gauge to indicate number of hits vs engines
• Verbose message from VT
• What Resource we actually queried
• MD5, SHA1, and SHA256 hash values
• Table of engines, conviction results, malware classification, engine update, and engine version
• Treemap of conviction results - useful for determining the highest malware family consensus

Process

At a high-level, here is what we did to create the dashboard:

1. Created a parameter that holds the Resource we want to look up
2. Used "Get Data" to query the VT API to pull in the JSON response
3. Used Power Query Editor to manipulate and extract the nested JSON as needed
4. Built a cool dashboard in Power BI Desktop

Since there are some intricate details in processing JSON data, we figured we would break down the steps in a bit more detail.

Creating the Parameter

We created two parameters to prompt users for data.  The first is the VT API key and the second is the hash they want to query.  Parameters are created in Power Query Editor by clicking on the down arrow for Manager Parameters > New Parameter

Name the field, Add a description, select Text for the Type, and enter a hash you know VT has for the Current Value.  In our example, we used the hash:  0a52fdc2219346d25295c8d6838122bcdd7ddf119386cd1030458b035cd97999

Figure 2:  Creating and managing the parameter that stores the query value

Note:  We also created a parameter for the API key so the user is prompted for the key when opening the Power BI template for the first time. When this is saved as a .pbix, the parameter value is also saved making it easy to use by the team.

Creating the VirusTotal Web API call for Get Data

To create the Web API call perform the following:

• Get Data > Web
• Advanced
• URL parts:
• https://www.virustotal.com/vtapi/v2/file/report?
• apikey=
• Select Parameter from the dropdown and use API_Key
• Note:  The free VT API key can be used
• &resource=
• Select Parameter from dropdown and use "Resource"

Figure 3:  Creating API call to VirusTotal to get the JSON data

Note:  The data returned will be JSON.  Now we need to make it columnar.

Use Power Query Editor to Manipulate the JSON

When the JSON formatted data is returned from the VirusTotal API query, follow these steps to make it columnar:

Convert > Into Table

Figure 4:  Convert JSON to a table

Transform > Transpose > Use First Row as Headers

Figure 5:  Transposed the table and then used the first row as the header

Take note!  The field called scans has a value of: "Record".  When clicking on this object, it expands below revealing the results of each scan vendor. Notice that those values are also "Record" which means we have two nested JSON objects, one inside the other.  We will need to make both columnar data to use it in Power BI.

Figure 6:  The scans field is a JSON object that contains more JSON objects

Expand the scans column

Here is a trick that we learned from a Guy in a Cube video -- thanks for the videos fellas! There is a little double arrow box in the far right portion of the header for scans. If you click this, it will allow you to expand (convert) the JSON to columnar data once again.  Be sure to uncheck the box at the bottom to avoid prepending a prefix that we would strip away later.

Figure 7:  First expansion of JSON object called "scans"

Highlight the antivirus vendor columns > Unpivot Columns

Figure 8:  Unpivoting the antivirus vendor columns to make each entry its own row

Rename Attribute field to Engine

Expand the Value field that contains more nested JSON

Figure 9:  Renaming column header and expanding second JSON object

Rename new columns to remove "Value." in front of detected, version, result, and update.

Building the dashboard

The dashboard consists of a filter, some tables, a gauge, and a treemap. Please see our other Power BI articles on building visualizations. The toughest portion of this task was taking JSON input and making it columnar so it can be processed by Power BI. As a bonus, we are also templating this project and making it available to everyone.

Template

As we just mentioned, we saved this work as a template and will distribute it for free. When opening the template, simply insert your VT API Key (which can be the free API key) and the hash that you want to query (called a resource).

Figure 10:  Templated VirusTotal lookup report in Power BI

Limitations

Currently we believe we have the following limitations, but these may be easy to overcome depending on how many ideas and solutions we can pull from our readers:

1. Unable to modify hash parameter from report / dashboard to refresh data feed with new data natively from Power BI
2. Drill through lookup using hash from previous report may be limited due to parameter update issue
3. Potential for AV column rename to fail due to hard coded AV vendor names in power query
4. Error handling could be improved for instances where VirusTotal does not have the data
• "response_code": 0 vs. "response_code": 1

Conclusion

We hope you enjoyed this article and have some ideas for us to correct some of the limitations. Feel free to contribute ideas and help us make this report better by using the comments feature below or submitting pull requests via Github. Or just give us a shout out if you are using this solution.  Enjoy!

Fun with Microsoft Power BI - Part IV - Improving Workflow

  By Tony Lee

Welcome to part IV in this series of going from zero to hero using Power BI to ingest, process, and make amazing reports.  If you have read some of our other articles you can probably tell by now that we enjoy making data actionable. Honestly, it doesn't matter what type of data or even where the data ends up. As long as we can make informed decisions using the data -- we love it. Following in this theme we are going to make BlackBerry (formerly known as Cylance) Protect Threat Data Report (TDR) CSVs actionable using Power BI and Power BI Desktop. You can use any data source to follow along in this series, but our example BlackBerry Protect report is shown below which we are happily sharing the Power BI template (.pbit) at the github link below so you can load and analyze your own data!

https://github.com/TonyLeeVT/BlackBerryProtectTDR-PowerBIViewer

Figure 1:  Our Policy Explorer Power BI report using BlackBerry Protect TDR data

In the first article, we covered:

• Getting Started
• Data Ingest
• Adjusting Fields
• Visualizations
• Saving Your Work

In the second article, we covered:

• Tabs
• More Visualizations
• Text box
• Slicer
• Table
• Pie Charts
• Treemap
• Using Reports and Dashboards
• Uploading Reports to Power BI Service (Online)

In the third article, we covered:

• Question and Answer (Q&A) Feature
• Power BI for Mobile
• Changing the Data Source
• Scheduling Data Refresh

In this article, we will cover:

• Matrix and Decomposition Visualizations
• Inserting Images with Actions
• Tooltips
• Parameters
• Power BI Templates

Matrix and Decomposition Visualizations

We just wanted to give a shout out to two very cool visualizations that help with analyst workflow. The first being the matrix visualization which allows you to hierarchically dig into data by expanding rows or columns.  To create the Matrix, we used Policy Name, Section, and Key as the Row value + Last Value as the Values. 

Figure 2:  Interactive matrix of policy configurations

The second visualization is Decomposition which allows you to visually analyze large amounts of data by viewing and summing common values.  To create the Decomposition, we use the distinct count of Policy Name for the Analyze field and Explaining by the Section, Key, and Value.

Figure 3:  Interactive decomposition visualization of policy configurations

Both of these visualizations ended up being perfect for our Policy Explorer Report shown as the first image in this article. Note: This test environment has more policies than what is typically found in large and complex environments, but it further proves that this solution scales.

Inserting Images with Actions

This feature may seem like a no-brainer, but it can really spice up a report. You can add images/logos and assign actions to them by going to Insert > Image > selecting the image. With the image selected, enable action and select type. In the example shown below, we chose a type of Web URL and added a link back to the BlackBerry website.

Figure 4:  Inserting the BlackBerry logo and creating a hyperlink back to the product page

Tooltips

The best way to describe a tooltip is the ability to add additional context to data via a simple action of hovering over an item in a report. In our screenshot below, we used an example of getting host information context by hovering over an event. This prevents us from having to dig into a completely separate table of devices data to discover information about the host in that event. The information displayed in the popup is quite powerful since it includes:  Operating system, Agent version, MAC address, Policy name, IP address, and more.

To enable this, we performed the following high-level steps:

• Created a new page via the tabs at the bottom
• Hid that page from view
• Changed the page size and type to Tooltip
• Created the multi-row card with our host data
• Set the Tooltip field to be Serial Number (which is common to Devices and Events)

Figure 5: Powerful tooltip created by using data from related data feed

A good detailed reference is found here:  https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips 

Parameters

In the process of getting ready to convert our work to a Power BI template to share with the community, we converted our BlackBerry Protect Threat Data Report data sources to use a parameter for the TDR token. This allows the user to change a single field (ex: TDR Token) and pull data from a different tenant. As a bonus, when a user opens our Power BI template, Power BI prompts the user to enter their TDR token to pull down data from the BlackBerry TDR API and populate the reports. Then when the report is scheduled for updates (as covered in part III of our series), all of the required information is already included to access the data and update the reports.

Figure 6:  Token parameter used to make these data feeds easily configurable for each customer's token

Power BI Templates

This is the topic most have probably been waiting for because it means that we are going to share the BlackBerry Protect report we have been building to explore Power BI.

Creating a template (.pbit) is super easy in Power BI Desktop.  Simply perform the following:

File > Export > Power BI template

Now, when you download our Power BI Template, you simply enter the TDR token from your BlackBerry protect console (Settings > Application > Threat Data Report) and it will start downloading the information.

Figure 7:  Entering the TDR parameter after opening the Power BI Template

Conclusion

This brings us to the end of our series on Fun with Microsoft Power BI (using our BlackBerry Protect example). We hope you enjoyed the topic and learned something new. Please feel free to leave any pro-tips or questions in the comment section below. As a bonus, if you are a BlackBerry Protect customer, please feel free to download our template, enter your TDR token and check out the visualizations. Let us know what you think--thanks for reading!