Vulnerability management is the end-to-end process of identifying vulnerabilities, assessing their risk to an organisation, and remediating or mitigating the vulnerability. There are several proprietary vulnerability scanners in the market, and to make matters more complicated, organisations typically deploy seven different security appliances as part of their overall defence program¹.  Instead of purchasing another appliance, organisations should take advantage of the capabilities already available in existing toolsets, such as querying languages. 
 
A growing trend in the Managed Defence and Response (MDR) world is the use of querying languages such as OSQuery in MDR platforms. OSQuery is straight forward to deploy, is compatible with Windows, MacOS, Linux, and FreeBSD, and crafting queries requires only limited knowledge in SQL. In this article, we will examine the use of OSQuery in being able to do the following, on predominantly Windows systems:

• General Operating System (OS) Enumeration
• Identifying Potentially Unwanted Programs
• Discover Security Misconfigurations.

As a quick primer before we jump into the queries, OSQuery works by treating the target computer as a relational database. This is how we can use SQL queries, as the tables defined by OSQuery’s schema represent core operating system concepts and then pull out that data for us to analyse. For example, if you wanted to query for all currently running processes, you would use OSQuery’s ‘processes’ table. 

For more information, you can find the SQL explanation here, and the full list of OSQuery tables here. OSQuery is pretty particular with the type of apostrophes used too; if you are given an error with any of the queries below, be sure to check this syntax.

1. OPERATING SYSTEM ENUMERATION

There are some simple OSQuery commands you can run to get a better idea of your environment. These queries can provide some quick wins to determine whether malicious actors are already in your network. The output of the following command will demonstrate whether endpoints have recently been patched, running an outdated version, or an unsupported operating system (such as Windows Server 2003, Windows 7). 

All OS Types: SELECT name, version, major, minor, patch, build FROM os_version;

To obtain more details on what patches have been implemented throughout Windows endpoints, use the ‘patches’ table; this will determine if any recent patches have not been installed yet.

Windows: SELECT hotfix_id, installed_on, caption FROM patches;

Next, we can run the following commands to check who is currently logged in, and what user accounts exist on the endpoint. This might be useful to check if accounts have been disabled once employees leave the company, or to identify unknown user acconts that require further investigation.

All OS Types: SELECT type, user, tty, host FROM logged_in_users;
All OS Types: SELECT uid, gid, username, description, directory, shell FROM users;

As part of routine account auditing, it would be beneficial to know what user accounts have been granted administrative access, or if a privileged account has been created to maintain persistence In Windows, this is determined if the user is part of the group with group ID 544; in Linux it is group ID 27, and macOS it is 80.

Windows: SELECT users.uid, users.username, users.shell, users.type FROM user_groups INNER JOINusers ON user_groups.uid = users.uid WHERE user_groups.gid = 544

With even the most common intrusions, the adversary will create their own service to execute malicious commands or software. In the event that a user logs off or restarts their computer, the adversary will maintain access, or in other words, persistence, to the system. By running the below query, we can identify if such a malicious service was installed:

Windows: SELECT name, display_name, status, path, user_account FROM services WHERE start_type = “AUTO_START” AND path NOT LIKE “%System32\svchost.exe%”;

Generally, adversaries want to run their malware as soon as they run the service to reduce the amount of noise being produced on the system. This explains the ‘AUTO_START’ parameter in the previous query, as it filters for services that will execute automatically upon system reboot.

Further to this, we expect services running from the exact path and executable of C:\Windows\System32\svchost.exe to be legitimate almost all of the time. By removing these from the query, it will greatly reduce the number of results that are presented to us for analysis, and limit the results to potential malicious activity, such as renamed executables (i.e., scvhost.exe) or services running from a temporary path.

2. IDENTIFYING POTENTIALLY UNWANTED PROGRAMS

POTENTIALLY UNWANTED PROGRAMS

OSQuery makes it straightforward to determine whether unwanted or unapproved applications are being run within an organisation. These tools are often installed by employees to create efficiencies in their daily work yet are often maliciously used by adversaries to ‘live off the land’ and reduce their footprint. As a common “cyber hygiene” hunt, these queries can be useful to identify any instances of “shadow IT” or non-authorised remote access into networks and systems. The queries in this section will allow an organisation to identify if unapproved applications have been installed or if allowed applications are running an out-of-date version.   

Windows: SELECT name, version, install_location, publisher, install_date FROM programs WHERE name LIKE ‘%insert app name%’;

There are many applications that are misused by adversaries to live off the land. These could include:
Remoting utilities such as AutoIT, TeamViewer, TightVNC, RManService, LogMeIn, AnyDesk.
Miscellaneous utilities such as Advanced IPScanner, SDelete, DumpSec, AmmyyAdmin, FileZilla, PatchMyPC, CCleaner, and TunnelBlick.

In some Windows environments, organisations or employees may choose to install software via a package manager such as Chocolatey. OSQuery has created a table to view software downloaded through this method:

Windows: SELECT name, version, author, path FROM chocolatey_packages;

In the MacOS, Linux and FreeBSD world, you can discern the same information using tables such as deb_packages, homebrew_packages, npm_packages, pkg_packages, portage_packages, python_packages, and rpm_packages. Most of these tables provide the same fields and data, so here are sample queries to run:

MacOS: SELECT name, version, path FROM homebrew_packages;
Linux: SELECT name, version, source FROM deb_packages;
FreeBSD: SELECT name, version FROM pkg_packages;

WEB BROWSER EXTENSIONS AND ADD-ONS

OSQuery have correctly highlighted the need for web browser visibility, as malicious extensions are an efficient way to steal user data, or further compromise an endpoint. Web browsers are becoming more of a target by adversaries as they bypass security scanning from common EDR tools, and allow them remote access to the system. 

The following queries will return all extensions running on major web browsers such as Google Chrome, Mozilla Firefox, Opera, Microsoft Edge, and Safari. For each browser (except IE) extension there is an identifier, in which a simple web search will provide you with further details on the author and the intended purpose of the extension.

With these details, you will be able to determine whether the extension is legitimate, and whether it has been updated to the most recent version available.

Chrome: SELECT users.username, chrome_extensions.name, chrome_extensions.description, chrome_extensions.version, chrome_extensions.identifier, chrome_extensions.path FROM chrome_extensions JOIN users ON chrome_extensions.uid = users.uid

Safari: SELECT users.username, safari_extensions.name, safari_extensions.description, safari_extensions.version, safari_extensions.identifier, safari_extensions.path FROM safari_extensions JOIN users ON safari_extensions.uid = users.uid

Opera: SELECT users.username, opera_extensions.name, opera_extensions.description, opera_extensions.version, opera_extensions.identifier, opera_extensions.path FROM opera_extensions JOIN users ON opera_extensions.uid = users.uid

Internet Explorer: SELECT name, version, path FROM ie_extensions;

3. DISCOVER SECURITY MISCONFIGURATIONS 

Misconfigurations in an organisation’s network is one of the many ways adversaries can access the environment, laterally move across the network, and gain access to user or administrator credentials. This section will explore how to identify these misconfigurations using a variety of OSQuery tables.

SELF-SIGNED CERTIFICATES

There are several issues with using self-signed certificates in an enterprise environment, the worst of all being that it potentially allows an attacker to intercept confidential data and instils poor security practices amongst employees. To identify self-signed certificates, we can run something like this:

Windows: SELECT common_name, subject, issuer, path, username FROM certificates WHERE self_signed = 1;

LEGACY PROTOCOLS

This section will outline queries to identify legacy protocols that may be in use within a Windows environment. Over the years, security researchers have discovered ways to exploit these protocols, which could potentially give a malicious actor access to privileged data or the credentials of users on a system. For this reason, they are now deemed unsafe to use, and safer versions of the protocols have since been released.

SMB V1

Windows: SELECT name, type, data FROM registry WHERE path LIKE  ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\%’;

TLS 1.0 AND 1.1

Windows: SELECT name, type, data FROM registry WHERE path LIKE ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\%’;. 
This will only return results if TLS 1.0 has been implemented.

LM HASH 

Windows: SELECT name, type, data FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\NoLmHash';
The data field will be set to 0 if LM Hash authentication protocol has been implemented.

NTLMV1 

Windows: SELECT name, type, data FROM registry WHERE path LIKE ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\LMCompatibilityLevel’;
If NTMLv2 has been implemented, the data field will be set to 5.

DIGEST/WDIGEST

Windows: SELECT name, type, data FROM registry WHERE path LIKE ‘HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\WDigest\%’;
The value ‘UseLogonCredential’ should be set to 0, meaning WDigest is disabled. 

EXPOSED SECRETS

Often, to create efficiencies, applications and users reveal passwords or secrets in plaintext over the command line during process execution. If an adversary is listening, they will be able to capture these credentials.

Windows: SELECT name, path, cmdline FROM processes WHERE cmdline LIKE “%SECRET%” OR cmdline LIKE “%password%”;

SECURITY CONFIGURATIONS

A Windows feature allows the ability to store logon credentials from interactively logged on users. The default is to store the previous 10 logon credentials, in which it could range from 0 to 50. If the value is set to 10 or this registry key does not exist, it means that the cached logon ability has been disabled. We can check if this is the case by running the following query:

Windows: SELECT path, name, data FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\Cached%';

If you are able to run OSQuery under a Domain Administrator account, you can simplify this by checking if the following key has sub-entries:

Windows: SELECT name, data FROM registry WHERE path LIKE ‘HKEY_LOCAL_MACHINE\Security\Cache\%’;

The next set of queries looks at the enablement and configuration of Windows security features. The following checks to see if Windows Firewall and Microsoft Defender Antivirus have been enabled:

Windows: SELECT name, state, type, signatures_up_to_date FROM windows_security_products;

Similarly, the windows_security_center table will be able to provide an insight on the status of security products. The ‘health values’ range from Good, Poor, Snoozed, Not Monitored, or Error.

Windows: SELECT * FROM windows_security_center;

The next query will show what Windows features have been enabled. Conversely, you can change the WHERE statement to only give Windows features that have been ‘Disabled’. 

Windows: SELECT caption, name FROM windows_optional_features WHERE statename = “Enabled”

OPEN SOCKETS

Lastly, an examination of processes and services that are listening to an open port will highlight services that should not be connected to the internet, such as Remote Desktop Protocol (RDP). There are two tables we can extract this data from, listening_ports and process_open_sockets. Listening_ports will provide all processes with listening (bound) network sockets or ports, where process_open_socket provides further details into the local and remote IP address and port, and the state of the connection (such as ‘listen’, ‘established’ etc.). 

All OS Types: SELECT processes.name, processes.path, processes.cmdline, listening_ports.address, listening_ports.pid, listening_ports.port, listening_ports.protocol FROM listening_ports JOIN processes ON listening_ports.pid = processes.pid WHERE listening_ports.address NOT LIKE "0.0.0.0";

All OS Types: SELECT sockets.pid, processes.name, sockets.path, sockets.remote_address, sockets.remote_port, sockets.state FROM process_open_sockets sockets JOIN processes ON sockets.pid = processes.pid WHERE sockets.remote_port NOT LIKE “0” ORDER BY sockets.remote_port ASC;

Network shares and named or anonymous pipes are regularly utilised by adversaries to enumerate files, hosts, and even stage data for exfiltration. This next query will provide insight into whether suspicious SMB or named pipes have been established within the environment for malware or C2 beacons:

Windows: SELECT proc.path as processPath, proc.pid, proc.parent as ppid, proc.cwd as currentWorkingDirectory, pipe.pid as pipePid, pipe.name AS pipename FROM processes proc JOIN pipes pipe ON proc.pid=pipe.pid;

Endpoints or users should only have access to the necessary drives, printers, or other network shares required for their day-to-day business function. The OSQuery table for this information is quite simple, as is the query:

Windows: SELECT name, path, description FROM shared_resources;
If this yields too many results, you can filter the query to only show administrative or hidden shares with the statement: WHERE name LIKE “%$%”; 

These are just some of the queries you can run across your environment to detect malicious behaviour and security vulnerabilities. We hope it will assist security analysts and teams in the fight against evolving cyber threats.

[1] https://www.carbonblack.com/press-releases/vmware-releases-cybersecurity-threat-survey-report-detailing-increased-attack-volume-and-breach-levels-in-australia/

---