Table: virustotal_file - Query VirusTotal File Reports using SQL

VirusTotal is a free service that analyzes suspicious files and URLs to detect types of malware, including viruses, worms, and trojans. It aggregates numerous antivirus products and online scan engines to check for viruses that the user's own antivirus solution may have missed, or to verify against any false positives. VirusTotal inspects items with over 70 antivirus scanners and URL/domain blacklisting services.

Table Usage Guide

The virustotal_file table provides insights into file reports within VirusTotal. As a security analyst, explore file-specific details through this table, including scan results, positives found, and scan date. Utilize it to uncover information about files, such as those with potential threats, the detection ratio, and the verification of scan results.

Important Notes

You must specify either the path (ocal path to a file) or the id (hash of the file) in the where clause to query this table.

Examples

Get VirusTotal information for a local file

Determine the safety of a local file by analyzing it with VirusTotal. This is useful for verifying downloaded files and avoiding potential security threats. Uses a local file to generate the hash to query VirusTotal for information about the file.

The file will not be uploaded for scanning, but just used to generate the hash to search existing results.

select
  *
from
  virustotal_file
where
  path = '/Users/michael/Downloads/terraform_1.0.1_darwin_amd64.zip';

select
  *
from
  virustotal_file
where
  path = '/Users/michael/Downloads/terraform_1.0.1_darwin_amd64.zip';

Get file information by ID

This query allows you to pinpoint the specific details of a file using its unique identifier. It is particularly useful when you need to analyze a file's properties or assess its integrity in a security-focused scenario.

select
  *
from
  virustotal_file
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85';

select
  *
from
  virustotal_file
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85';

List alternate names for a file

This query helps you uncover the alternate names associated with a specific file, which can be useful in identifying potential risks or anomalies associated with that file. This can aid in cybersecurity efforts, ensuring that files are properly identified and assessed for potential threats.

select
  jsonb_array_elements_text(names) as name
from
  virustotal_file
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85'
order by
  name;

select
  json_each.value as name
from
  virustotal_file,
  json_each(names)
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85'
order by
  name;

Find all scanner results by engine

Explore the variety of scanner results associated with a specific file. This query is useful in identifying and understanding the different categories of analysis results generated by various engines, aiding in comprehensive security assessment.

select
  analysis.key as engine,
  analysis.value ->> 'category' as result
from
  virustotal.virustotal_file,
  jsonb_each(last_analysis_results) as analysis
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85'
order by
  engine;

select
  analysis.key as engine,
  json_extract(analysis.value, '$.category') as result
from
  virustotal.virustotal_file,
  json_each(last_analysis_results) as analysis
where
  id = '8739c76e681f900923b900c9df0ef75cf421d39cabb54650c4b9ad19b6a76d85'
order by
  engine;

Schema for virustotal_file

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
capabilities_tags	jsonb		List of representative tags related to the file's capabilities. Only available for Premium API users.
creation_date	timestamp with time zone		Extracted when possible from the file's metadata. Indicates when it was built or compiled. It can also be faked by malware creators. UTC timestamp.
crowdsourced_ids_results	jsonb		IDS (Snort and Suricata) matches for the file. If the file it's not a PCAP, the matches are taken from a PCAP generated after running the file in a sandbox. Results are sorted by severity level, there is only one item per matched alert and every item on the list contains:
crowdsourced_ids_stats	jsonb		Result statistics by severity level.
crowdsourced_yara_results	jsonb		YARA matches for the file.
downloadable	boolean		True if the file can be downloaded, false otherwise. Only available for Premium API users.
first_submission_date	timestamp with time zone		UTC timestamp of the date where the File was first submitted to VirusTotal.
id	text	=	ID of the File.
last_analysis_date	timestamp with time zone		UTC timestamp representing last time the File was scanned.
last_analysis_results	jsonb		Result from File scanners. dict with scanner name as key and a dict with notes/result from that scanner as value.
last_analysis_stats	jsonb		Number of different results from this scans.
last_modification_date	timestamp with time zone		Date when any of IP's information was last updated.
last_submission_date	timestamp with time zone		UTC timestamp representing last time it was sent to be analysed.
main_icon	jsonb		Icon's relevant hashes.
md5	text		File's MD5 hash.
meaningful_name	text		The most interesting name out of all file's names.
names	jsonb		All file names associated with the file.
path	text	=	File path to check with VirusTotal.
reputation	bigint		File's score calculated from the votes of the VirusTotal's community.
sandbox_verdicts	jsonb		A summary of all sandbox verdicts for a given file.
sha1	text		File's SHA1 hash.
sha256	text		File's SHA256 hash.
sigma_analysis_stats	jsonb		Dictionary containing the number of matched sigma rules, grouped by its severity.
sigma_analysis_summary	jsonb		Dictionary containing the number of matched sigma rules group by its severity, same as sigma_analysis_stats but split by ruleset. Dictionary key is the ruleset name and value is the stats for that specific ruleset.
size	bigint		File size in bytes.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tags	jsonb		List of representative attributes.
times_submitted	bigint		Number of times that File has been checked.
total_votes	jsonb		Unweighted number of total votes from the community, divided into harmless and malicious.
type_description	text		Describtes the file type.
type_extension	text		Specifies the file extension.
type_tag	text		Tag representing the file type. Can be used to filter by file type in VirusTotal intelligence searches.
unique_sources	bigint		Indicates from how many different sources the file has been posted from.
vhash	text		In-house similarity clustering algorithm value, based on a simple structural feature hash allows you to find similar files.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- virustotal

You can pass the configuration to the command with the --config argument:

steampipe_export_virustotal --config '<your_config>' virustotal_file