I have been looking to create sightings for untrusted files in the Cisco Threat Intelligence Private Store and I can get Orbital to return a list of files that I would like to have sightings for, but when the Sighting is created there is no relationships information, and the hostname is not listed as one of the targets.
Ideally the hostname would be listed as a target and there would be relationships between the file_name, file_path, md5, sha1, sha256, certificate_issuer, certificate_common_name, and certificate_serial when those values are not empty.
Does anyone know how to make that happen directly from Orbital to the Private Store without having to do custom scripting to transform the output?
Here is a sample of the Orbital query for the userassist table. And there is probably an easier way to write that in SQL, but this is what I could do.
/*===================================================
Find untrusted files to feed Cisco Threat
Intelligence Private Store
userassist
===================================================*/
WITH
-- Gather path names from the userassist table.
tbl_results AS (
SELECT DISTINCT
path,
'userassist' AS table_name
FROM userassist
WHERE path LIKE '_:\%%'
),
-- If the path is to a link file, then lookup the referenced path for the link.
tbl_link_paths AS (
SELECT
sf.local_path AS path,
'tbl_link_paths' AS table_name
FROM tbl_results path
LEFT JOIN shortcut_files sf USING(path)
WHERE
path.path LIKE '%.lnk'
AND sf.local_path != ''
),
-- Add the link paths to the path table listing.
tbl_paths_with_links AS (
SELECT DISTINCT
LOWER(path) AS path,
table_name
FROM tbl_results
UNION SELECT LOWER(path), table_name FROM tbl_link_paths
),
-- Group the table_name field.
tbl_paths AS (
SELECT
group_concat(table_name) AS table_name,
path
FROM tbl_paths_with_links
GROUP BY path
),
-- Add the file name and file size to the list of paths
tbl_files AS (
SELECT
paths.table_name,
file.filename,
file.size,
paths.path
FROM tbl_paths paths
LEFT JOIN file USING(path)
WHERE NOT paths.path LIKE '%~%'
),
-- Get file hashes for files that are under the 50MB limit.
tbl_hashes AS (
SELECT
files.path,
hash.md5,
hash.sha1,
hash.sha256
FROM tbl_files files
INNER JOIN hash USING(path)
WHERE files.size < 52428800
),
-- Validate file with Authenticode
tbl_auth_code AS (
SELECT
files.path,
a_code.result,
a_code.issuer_name,
a_code.subject_name,
a_code.serial_number
FROM tbl_files files
INNER JOIN authenticode a_code USING(path)
WHERE
files.filename != ''
AND NOT files.filename LIKE '%.lnk'
)
-- Create results list
SELECT DISTINCT
(SELECT REGEX_MATCH(hostname, '^(.+?)\.(.+?)$', 1) FROM system_info) AS hostname,
files.table_name AS source_name,
files.filename AS file_name,
files.path AS file_path,
hashes.md5,
hashes.sha1,
hashes.sha256,
a_code.result AS certificate_result,
a_code.issuer_name AS certificate_issuer,
a_code.subject_name AS certificate_common_name,
a_code.serial_number AS certificate_serial
FROM tbl_files files
LEFT JOIN tbl_hashes hashes USING(path)
LEFT JOIN tbl_auth_code a_code USING(path)
WHERE a_code.result IS NOT 'trusted'
ORDER BY file_path;
