12-27-2021 02:07 PM
Are there any recommendations on which queries from the Orbital catalog should be executed on a recurring basis to populate data into the Threat Response Private Intelligence remote data store and the frequency for which they should be run?
Also, is there any pre-configured content in SecureX Orchestration (or other Cisco products) that can leverage the Orbital results in the Threat Response Private Intelligence remote data store?
01-04-2022 02:37 AM
Interested to see what comes back on this question, sorry can't offer any insight myself. Thanks for posting.
01-20-2022 02:01 PM
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;
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: