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 AWS CLI - Cost Explorer (ce) API

By Tony Lee

If you are an avid Amazon Web Services (AWS) consumer and have been thinking about ways to integrate these services into third party-tools (such as CyBot - https://github.com/cylance/CyBot), you have come to the right place. In this article we will cover how to setup and use the aws2 client (AWS CLI) to easily interact with AWS' APIs.  Per Amazon:  "AWS Command Line Interface (AWS CLI) is an open source tool that enables you to interact with AWS services using commands in your command-line shell."  Our example below will illustrate just one possibility by using the Cost Explorer (ce) API determine your current and past AWS bills via the command line or third-party tool.

In other words, we are ditching the Web interface shown below:

Figure 1:  AWS Billing Web Interface

In favor of querying the information via the command line:

Figure 2:  AWS Billing query via command line

Or better yet, a ubiquitous tool such as your favorite chat application using CyBot:

Figure 3:  Enabling CyBot to query the AWS API

We will cover the following tasks to enable this integration:

• Configuring AWS Permissions
• Policy creation
• Group creation
• User creation
• Installing the aws2 client
• Configuring the aws2 client
• Using the aws2 client

Cost Estimation Requirements

Before we dive into the configuration steps, there are two main requirements:

• Access to the following endpoint:  https://ce.us-east-1.amazonaws.com
• aws2 client
• A while back, we tried version 1 of the aws client, but it lacked the cost estimator (ce) command, thus we had to upgrade to version 2 (both referenced via the aws command)

Configuring AWS Permissions

Note:  This step generates your AWS Access Key ID and AWS Secret Access Key.  Copy these down in a safe place because you will need this info in the AWS client setup section

First create and retrieve your AWS credentials via IAM:
https://console.aws.amazon.com/iam/home

Policy Creation
Once, authenticated, create a policy to allow only cost estimation queries (following the principle of least privilege it may be possible to restrict this even more, but that is an exercise left up to the reader):

1. Click "Customer Managed Policies"
2. Create policy button
3. JSON tab, and copy and paste the following:

{
"Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "ce:*"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

Figure 4:  Policy Creation via JSON

Group Creation
Now that the policy exists, create a Cost Estimator Group and assign it the newly created policy by performing the following steps:

1. Click "Groups"
2. Click Create New Group Button
3. Name the group: CostEstimatorAPI
4. Attach a policy:  CostExplorerPolicy
5. Next
6. Apply

User Creation

Now that the group exists, create a user and assign it to the newly created group by performing the following steps:

1. Click "Users"
2. Add user button
3. Name the user
4. Access type:  Programmatic access
5. Select the CostEstimatorAPI Group we created
6. Click next button
7. Click Create user button

Figure 5:  User is created and tied to the group that has cost estimator permissions

Installing the aws2 Client

We installed the aws client on an Ubuntu Linux VM.  If you are using a different operating system, feel free to follow the instructions contained in the following documentation:  https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2-linux.html

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"

unzip awscliv2.zip

sudo ./aws/install


Now check the version, you might see something like the
/usr/local/bin/aws --version

You might see something like the following:
aws-cli/2.0.33 Python/3.7.3 Linux/4.4.0-31-generic botocore/2.0.0dev37

Note:  This needs to be version 2.x or higher for the cost explorer functionality to exist.

Configuring the aws2 Client

Note:  Have your AWS Access Key ID and AWS Secret Access Key ready from the Setup AWS Permissions section

aws configure

AWS Access Key ID [None]: AK[redacted]YY
AWS Secret Access Key [None]: vn[redacted][redacted][redacted]Rm
Default region name [None]: 
Default output format [None]: 

Using the aws2 Client

When using the aws client, you can use the following syntax to get help in trying to figure out how to achieve your goals.

  aws help

  aws <command> help

  aws <command> <subcommand> help

Be sure to also consult the docs for syntax help and example usage.  In our case, we are looking at cost explorer functionality:

https://awscli.amazonaws.com/v2/documentation/api/latest/reference/ce/index.html#cli-aws-ce

Specifically for our example, we will look to retrieve the cost and usage information:
https://awscli.amazonaws.com/v2/documentation/api/latest/reference/ce/get-cost-and-usage.html

After a bit of trial an error to get the exact format of required parameters, we have the following:

aws ce get-cost-and-usage --time-period Start=2020-07-01,End=2020-08-01 --granularity MONTHLY --metrics "BlendedCost" "UnblendedCost" "UsageQuantity"

Which yields the following output:

{
    "ResultsByTime": [
        {
            "TimePeriod": {
                "Start": "2020-07-01",
                "End": "2020-08-01"
            },
            "Total": {
                "BlendedCost": {
                    "Amount": "xx.6272352241",
                    "Unit": "USD"
                },
                "UnblendedCost": {
                    "Amount": "xx.6272352241",
                    "Unit": "USD"
                },
                "UsageQuantity": {
                    "Amount": "xxx.1172177568",
                    "Unit": "N/A"
                }
            },
            "Groups": [],
            "Estimated": true
        }
    ]
}

Two things to note about the time period parameter (--time-period Start=2020-07-01,End=2020-08-01)

1. It would be ideal if the aws client accepted terms such as:  "This year", "this month", "this week", or "today", but now that this is integrated into our third party tool, we can create that logic.
2. But at least the client allows you to select a date in the future (ex:  The first of next month) for calculating the present time period.

Conclusion

We hope this article serves as a quick introduction and jumpstart to your interactions with the AWS API using the freely available client. While not perfect, this client is quite powerful and can be used to quickly interact with the Amazon Web Services API to build integrations into third-party tools.  Feel free to leave any comments in the section below.  Happy Hacking.

References

These are references which we found to be useful:

• https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2-linux.html
• https://awscli.amazonaws.com/v2/documentation/api/latest/reference/ce/index.html
• https://awscli.amazonaws.com/v2/documentation/api/latest/reference/ce/index.html#cli-aws-ce
• https://awscli.amazonaws.com/v2/documentation/api/latest/reference/ce/get-cost-and-usage.html

osquery - Part V - Integration

By Tony Lee and Matt Kemelhar

This series on osquery will take us on a journey from stand-alone agents, to managing multiple agents with Kolide Fleet, and then finally onto more advanced integrations and analysis.  We already covered the following topics:

Part I - Local Agent Interaction:  http://securitysynapse.blogspot.com/2019/05/osquery-part-i-local-agent-interaction.html
Part II - Kolide Centralized Management:  http://securitysynapse.blogspot.com/2019/05/osquery-part-ii-kolide-centralized.html
Part III - Queries and Packs:  http://securitysynapse.blogspot.com/2019/05/osquery-part-iii-queries-and-packs.html
Part IV - Fleet Control Using fleetctl - http://securitysynapse.blogspot.com/2019/05/osquery-part-iv-fleet-control-using-fleetctl.html


Even though we now have a centralized management platform that we can manage, reading the query output in the Kolide Fleet UI does not scale to hundreds of thousands of hosts -- thus we need to integrate with a big data analytics platform so we can stack and perform statistical analysis on the data.  In this article, we will examine Kolide Fleet output + Splunk integration.  As a bonus, we are releasing a Kolide Fleet App for Splunk -- free of charge in Splunkbase.  In the first version of the app, it will be able to parse, normalize, and display the following information:

• Overview information
• Status Log
• osquery_info query
• programs query
• process_open_sockets query
• users query

Screenshots of the app are shown below.  Splunk app is available here:  https://splunkbase.splunk.com/app/4518/

Figure 1:  Overview page

Figure 2:  Status Log

Figure 3:  osquery_info page

Expected Kolide Packs and Queries

The first version of the Kolide App for Splunk needs the pack, query names, and output to conform to what is shown in the fleetctl get commands below. For this reason, we are sharing our exported packs and queries here:

https://github.com/TonyLeeVT/kolide_fleet_app_for_splunk

Remember in Part IV of this series, we covered how to import the queries and packs using fleetctl:

fleetctl apply -f kolide_splunk_app.yaml 
[+] applied 4 queries
[+] applied 4 packs

Pack and Query details

fleetctl get p
+---------------------------+----------+-------------------------------+
|           NAME            | PLATFORM |          DESCRIPTION          |
+---------------------------+----------+-------------------------------+
| users pack                |          | Query all users               |
+---------------------------+----------+-------------------------------+
| osquery_info pack         |          | Query the version of osquery  |
+---------------------------+----------+-------------------------------+
| process_open_sockets pack |          | Pack for process_open_sockets |
+---------------------------+----------+-------------------------------+
| programs pack             |          | pack for programs             |
+---------------------------+----------+-------------------------------+


fleetctl get q
+----------------------------+------------------------------+--------------------------------+
|            NAME            |         DESCRIPTION          |             QUERY              |
+----------------------------+------------------------------+--------------------------------+
| users query                | Query all users              | SELECT * FROM users            |
+----------------------------+------------------------------+--------------------------------+
| osquery_info query         | Query the version of osquery | SELECT * FROM osquery_info     |
+----------------------------+------------------------------+--------------------------------+
| process_open_sockets query | Query process_open_sockets   | SELECT DISTINCT proc.name,     |
|                            |                              | proc.path, proc.cmdline,       |
|                            |                              | pos.pid, pos.protocol,         |
|                            |                              | pos.local_address,             |
|                            |                              | pos.local_port,                |
|                            |                              | pos.remote_address,            |
|                            |                              | pos.remote_port FROM           |
|                            |                              | process_open_sockets AS pos    |
|                            |                              | JOIN processes AS proc ON      |
|                            |                              | pos.pid = proc.pid;            |
+----------------------------+------------------------------+--------------------------------+
| programs query             | query for programs           | SELECT * FROM programs         |
+----------------------------+------------------------------+--------------------------------+

Kolide Output

Once the Packs and Queries above are imported using the fleetctl apply command above, applied to targets, and scheduled to run, we need to gather the output and send it to Splunk. You might remember that in Part III of this series, we mentioned that we added a statement to our fleet.yaml configuration file to send the results and status output to the following path with log rotation:

filesystem:
  status_log_file: /data/osquery/status.log
  result_log_file: /data/osquery/results.log
  enable_log_rotation: true

This sets us up perfectly to use a Splunk forwarder to send the data to Splunk. If not already completed, download and install the Splunk forwarder here:

https://www.splunk.com/en_us/download/universal-forwarder.html

Once installed, configure the forwarder to send data to your indexers.

Install the Splunk App and Create the Index

In order to prepare for the data's arrival, we now install the Splunk app and create an index for the osquery data:

1) Install the following Kolide Fleet App For Splunk: https://splunkbase.splunk.com/app/4518/
2) Create an index called osquery

If you already had osquery data going to Splunk to a different index and sourcetype, all is not lost.  You can modify the eventtypes.conf file to account for it.

(Optional) Modify eventtypes.conf as Needed

If you already had Kolide setup and sending data to Splunk under a different index and sourcetype name, that's not a problem.  As long as the data is being parsed correctly, we can just modify eventtypes.conf within Splunk to still make all the dashboards function correctly for your index and sourcetype names.  Modify index=osquery to match your index.  Modify sourcetype=osquery:results and osquery:status to match your sourcetypes.

cat eventtypes.conf

[osquery_index]
search = index=osquery

[osquery_status]
search = eventtype=osquery_index sourcetype=osquery:status

[osquery_results]
search = eventtype=osquery_index sourcetype=osquery:results

(Optional) Modify props.conf as Needed

Currently the only two stanzas in props.conf that are used are osquery:results and osquery:status shown below.  Feel free to change the stanza to match the sourcetype if needed.  Minimal parsing is accomplished below:

cat props.conf

## Results log

[osquery:results]

TRUNCATE = 50000

KV_MODE = json

SHOULD_LINEMERGE = 0

category = osquery

pulldown_type = 1

MAX_TIMESTAMP_LOOKAHEAD = 10

TIME_FORMAT = %s

TIME_PREFIX = unixTime\"\:

EVAL-vendor_product = "osquery"

FIELDALIAS-user = decorations.username as user

FIELDALIAS-username = username as user

FIELDALIAS-dest = decorations.hostname as host

## Status log

[osquery:status]

KV_MODE = json

SHOULD_LINEMERGE = 0

category = osquery

pulldown_type = 1

MAX_TIMESTAMP_LOOKAHEAD = 10

TIME_FORMAT = %s

TIME_PREFIX = unixTime\"\:

EVAL-vendor_product = "osquery"

FIELDALIAS-user = decorations.username as user

FIELDALIAS-dest = host as dest

Send logs to Splunk via Splunk forwarder (inputs.conf)

Once our app is installed on the search head, Splunk forwarder is installed on the Kolide host and Kolide is writing the status and results logs to disk, we need to let the fowarder know where to gather the logs. For this, we use the following inputs.conf file:

cat inputs.conf 
[monitor:///data/osquery/results.log]
index = osquery
sourcetype = osquery:results
disabled = 1

[monitor:///data/osquery/status.log]
index = osquery
sourcetype = osquery:status
disabled = 1

If all went as planned, you should see data populating in the Splunk app.  :-)

Conclusion

This article covered how to import the required queries to populate the current version of the Splunk app.  It then explained where the Kolide Fleet logs should appear and how to forward those logs to Splunk.  We covered installing the newly created Kolide Fleet App for Splunk and optionally configure the eventtypes.conf and/or props.conf for any deviation in expected index or sourcetype.  At the end of this effort, you should have data flowing from Kolide Fleet to Splunk properly ingested, parsed, and displayed.  For any questions, please post in the comments section below.  Otherwise, stay tuned additional integration efforts!

Props to the osquery TA for getting us started.

Bonus for the curious reader -- Splunk Magic

Normally, JSON is not the prettiest of data to table in Splunk.  However, we discovered a series of tricks that makes panel and dashboard development scale a little easier.  Our searches in many cases end up looking something like this:

eventtype=osquery_results  name="pack/network_connection_listening/Windows_Process_Listening_Port" | dedup host, _time | spath output=data path=snapshot{} | mvexpand data | rename data as _raw | extract pairdelim="," kvdelim=":" | eval pname=mvindex(name,1) | table _time, host, pname, path, protocol, address, port 


It is a lot of digest all at once, so let's break it down:

1. Find the data we want:  eventtype=osquery_results  name="pack/network_connection_listening/Windows_Process_Listening_Port"
2. Get the latest result by host and time:  dedup host, _time
3. Convert multivalue field into data field, one per line kv pair:  spath output=data path=snapshot{}
4. Expand multivaue fields into separate events:  mvexpand data
5. Rename data as _raw since extract only works on _raw:  rename data as _raw
6. Extract key/value pair (regardless of key names):  extract pairdelim="," kvdelim=":"
7. Avoid conflict for "name" variable:  eval pname=mvindex(name,1)
8. Table remaining values extracted:  table <extracted field names>

Figure 4:  Pure joy of JSON data in Splunk

Figure 5:  Data is ready to table after our SPL trickery