Basic Orbital Search Queries for Threat Analysis

Introduction

This document describes the basic orbital search queries for threat analysis.

Requirements

Cisco recommends that you have knowledge of the interest in the understanding of threats and malware and a basic understanding of Structured Query Language (SQL) tables.

Components Used

The information in this document is based on these software and hardware versions:

• Secure Endpoint Connector version 7.1.5 or later for Windows
• Secure Endpoint Connector version 1.16 or later for Mac
• Secure Endpoint Connector version 1.17 or later for Linux
• Secure Endpoint user must be assigned the role of admin to deploy Orbital

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, ensure that you understand the potential impact of any command.

Background Information

The Custom Queries are leveraged which must help you quickly learn the power of Orbital and osquery for threat hunting.

Orbital makes use of osquerys stock tables in addition to Orbital-specific tables. The results returned through Orbital can be sent to other applications, such as Secure Endpoint, Secure Malware Analytics, and SecureX Threat Response, and can be stored in remote data stores (RDS), such as Amazon S3, Microsofts Azure, and Splunk.

Use the Orbital Investigate page in order to construct and execute live, live queries on endpoints in order to gather more information from them. Orbital uses osquery, which allows you to query your devices like a database with basic SQL commands.

Here is a simple example: SELECT column1, column2 FROM table1, table2 WHERE column2='value'.

In this example, column1 and column2 are the field names of the table from which you want to choose data. In order to choose all fields available in the table, use this syntax: SELECT * FROM table1.

Access

Open Orbital directly at these sites:

North America - https://orbital.amp.cisco.com

Europe - https://orbital.eu.amp.cisco.com

Asia Pacific - https://orbital.apjc.amp.cisco.com

Or

On the Secure Endpoint Console, choose the impacted Host system and click Investigate in Orbital.

There are options for using the Orbital Catalogue (Click Browse) or Enter the Custom Queries under Custom SQL section as mentioned:

Custom Queries

Note: Host system is in the lab network and it is tried to keep the system/network unharmed.

1. Startup Items

Startup items can be exploited by attackers to maintain persistence on a compromised system, which means that the malicious software will continue to run or be re-launched automatically with each system restart. In the next example, Qakbot.exe is running in the host system.

SELECT name, path, args, source, type, status, username
FROM startup_items;

2. Sha256 Hashes of Running Processes

SHA256 hashes are not inherently associated with running processes in their natural state. However, security software and system monitoring tools can calculate the SHA256 hash of a running process of the executable file in order to help verify its integrity and authenticity.

 SELECT
p.pid, p.name, p.path, p.cmdline, p.state, h.sha256
FROM processes p
INNER JOIN hash h
ON p.path=h.path;

if the associated hash of a File is malicious, you will be able to identify with this query.

3. Process with Network Connections

Processes with network connections are programs or system services that are actively using the network interface in order to communicate with other devices on a network or over the internet.

SELECT
DISTINCT pos.pid, p.name, p.cmdline, pos.local_address, pos.local_port, pos.remote_address, pos.remote_port
FROM processes p
JOIN process_open_sockets pos USING (pid)
WHERE
pos.remote_address NOT IN ("", "0.0.0.0", "127.0.0.1", "::", "::1", “0");

4. Privileged Process with Non-Localhost Network Connection

Running program or service that has elevated permissions (like those of an administrator or system account) and is communicating over the network with an external device or service—meaning any IP address other than 127.0.0.1 (localhost) or ::1 (IPv6 localhost).

SELECT DISTINCT p.name, p.cmdline, pos.pid, pos.local_address, pos.local_port, pos.remote_address, pos.remote_port
FROM processes p JOIN process_open_sockets pos USING (pid)
WHERE pos.remote_address NOT IN ("", "0.0.0.0", "127.0.0.1", "::", "::1")

Once you have the Packet Identifier (PID) list, you can add it accordingly in the Custom Queries.

SELECT DISTINCT p.name, p.cmdline, pos.pid, pos.local_address, pos.local_port, pos.remote_address, pos.remote_port
FROM processes p JOIN process_open_sockets pos USING (pid)
WHERE pos.remote_address NOT IN ("", "0.0.0.0", "127.0.0.1", "::", "::1") and p.uid=1436 

5. Backup/Restore Registry Monitoring

Tracking of events where changes are made to the Windows Registry through backup or restore operations. The Windows Registry is a hierarchical database that stores configuration settings and options on Microsoft Windows operating systems.

SELECT key AS reg_key, path, name, data, DATETIME(mtime, "unixepoch") as last_modified
FROM registry
WHERE key LIKE "HKEY_LOCAL_MACHINE\system\currentcontrolset\control\backuprestore\filesnottosnapshot";

SELECT data, path FROM registry
WHERE key LIKE 'HKEY_USERS\%\Control Panel\Accessibility\StickyKeys’;

SELECT username, data, split(path, '\', 1) AS sid
FROM
(SELECT data, path FROM registry
WHERE key LIKE 'HKEY_USERS\%\Control Panel\Accessibility\StickyKeys’)
JOIN users ON users.uuid = sid;

6. File Search

Allows users to locate files and folders on their computer using various criteria such as file name, content, properties, or metadata.

SELECT
f.directory, f.filename, f.uid, f.gid,
f.mode, f.device, f.size,
f.atime,
f.mtime,
f.ctime,
f.btime,
f.hard_links, f.symlink, f.file_id, h.sha256
FROM file f
LEFT JOIN hash h on f.path=h.path
WHERE
f.path LIKE (SELECT v from __vars WHERE n="file_path") AND
f.path NOT LIKE (SELECT v from __vars WHERE n=“not_file_path");

Navigate to PARAMETERS > File Path and click %.dll or %.exe or %.png.

7. Powershell History Monitoring

Practice of keeping track of the commands that have been executed in PowerShell sessions. Monitoring PowerShell history can be particularly important for security and compliance reasons.

SELECT time, datetime, script_block_id, script_block_count, script_text, script_name, script_path
FROM orbital_powershell_events
ORDER BY datetime DESC
LIMIT 500;

8. Prefetch Query

Performance feature that speeds up the loading of applications. Prefetching involves analyzing the way software is loaded and run on a system and then storing information about this in specific files.

select datetime(last_run_time, "unixepoch", "UTC") as last_access_time,* 
from prefetch 
ORDER BY last_access_time DESC;

Prefetch is a mechanism with which SQL Server can fire up many I/O requests in parallel for a Nested Loop join.

9. Address Resolution Protocol (ARP) Cache Inspection

Involves examining the contents of the ARP cache on a computer or network device. The ARP cache is a table that stores mappings between IP addresses and their corresponding MAC addresses.

SELECT address, mac, count(*) as count
FROM arp_cache GROUP BY mac,address;

The next example figures out the Suspicious MAC address and its count from the ARP Cache.

SELECT address, mac, count(*) as count
FROM arp_cache GROUP BY mac,address
HAVING COUNT(mac) >= (SELECT count FROM arp_cache WHERE count>=1)
AND mac LIKE (SELECT mac FROM arp_cache WHERE mac=“52:54:00:12:35:02");