Osquery is my favorite open-source security tool and Python is my favorite programming language so fusing them together allows us to engineer tools to detect threats. This blog post will build an Osquery-python extension to calculate the CommunityID of a network connection utilizing the Osquery-polylogyx extension pack to monitor network connections. In blog posts to follow, we will correlate network-based events generated by Zeek and host-based events generated by Osquery using the CommunityID. So follow me as your adventure guide on this development journey to make an Osquery extension with osquery-python.

NOTICE

Osquery version 4.20 was released on February 10th 2019 which contains zwass PR to include communityID generation in the Osquery project. This blog post should be viewed as a learning exercise on how to create an Osquery extension with osquery-python.

NOTICE

Background

What is Threat Detection Engineering?

In a nutshell, Threat Detection Engineering is related but not limited to the following high-level activities:

• Drive proactive identification of threats to the environment with the rapid deployment of detection controls.
• Liaise with internal teams and especially data owners, establishing relationships focused on spotting new detection opportunities.
• Make threat intelligence consumable by integrating it with SIEM and other tools that are part of the security arsenal (SOAR, EDR, etc).
• Create custom code to aid the detection of malicious activity via security alerts (rules), reports (dashboards) or simply automation (scripting).
• Establish a continuous quality assurance process, with special attention to internal users/customers feedback (SOC, monitoring teams — in special).

What is Osquery?

Osquery exposes an operating system as a high-performance relational database. This allows you to write SQL-based queries to explore operating system data. With Osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events or file hashes.

What is communityID?

CommunityID is a new feature being implemented by networking security applications such as Zeek and Suricata. A CommunityID is a hash of the tuple (destination IP address, source IP address, destination port, source port, protocol)  and this tuple defines a unique connection. For example, let’s say Suricata detects malicious activity and when you examine the details of the alert it will include a unique hash as the value of communityID.

What is osquery-python?

In Osquery, SQL tables, configuration retrieval, log handling, etc are implemented via a simple, robust plugin and extensions API. This capability provides the ability to create Osquery extensions in Python.

What is Osquery-polylogyx exetnsions?

PolyLogyx OSQuery Extension (plgx_win_extension.ext.exe) for Windows platform extends the core osquery on Windows by adding real time event collection capabilities to osquery on Windows platform. The capabilities are built using the kernel services library of PolyLogyx. The current release of the extension is a ‘community-only’ release. It is a step in the direction aimed at increasing osquery footprint and adoption on Windows platform. With the extension acting as a proxy into Windows kernel for osquery, the possibilities can be enormous.

Install/Setup Osquery-python environment on macOS

Create a directory and install Python packages

1. git clone https://github.com/CptOfEvilMinions/osquery-py-communityid
2. cd osquery-py-communityid
3. virtualenv -p python3 venv
4. source venv/bin/activate
5. pip3 install -r requirements.txt

Code breakdown

def name(self):

First, is the “@osquery.register_plugin” Python decorator which basically calls additional functionality to register this code as an Osquery plugin. Next, a class is defined for this plugin using the “osquery.TablePlugin” object and this class contains all the code necessary for this plugin. Lastly, for this section, we have the “name” function which simply returns the name of the table to be registered within Osquery.

def columns(self):

This section defines the columns of the table and the variable type of each column.

Test extension

1. Open a Terminal instance
2. osqueryi --nodisable_extensions
3. SELECT value FROM osquery_flags WHERE name = 'extensions_socket';
   1. Copy value returned
4. Open a new Terminal instance
5. python ./osquery_community_id.py --socket <Value returned above by Osquery>
6. Return back to the Osquery Terminal instance
7. SELECT DISTINCT p.local_address, p.local_port, p.remote_address, p.remote_port, p.protocol, p.state, c.community_id FROM process_open_sockets as p JOIN community_id as c ON c.src_ip= p.local_address AND c.src_port=p.local_port AND c.dst_ip=p.remote_address AND c.dst_port=p.remote_port AND p.protocol !=0 AND p.remote_port !=0 AND c.protocol=p.protocol;

Install/Setup Osquery and Osquery extension on Windows 10

Install Python3 and PIP modules

1. Open browser and browse to https://www.python.org/ftp/python/3.8.1/python-3.8.1-amd64.exe
2. Install Python3
   1. Check “Install launcher for all users”
   2. Check “Add Python to PATH”
3. Select “Install Now”
4. Open a Powershell instance
5. pip install osquery==3.0.6
6. pip install communityid=1.1
7. pip install pywin32

Install Osquery on Windows 10

1. Open a browser and browse to https://pkg.osquery.io/windows/osquery-4.0.2.msi
   1. Polyglogyx – “It has been built and tested with 3.2.6. It also works with 3.3.0 and 4.0.x”
2. Install Osquery

Test extension on Windows 10

1. Open a Powershell instance as Administrator
2. cd 'C:\Program Files\osquery'
3. New-Item -Path "C:\Program Files\osquery" -Name "Extensions" -ItemType "directory"
4. Invoke-WebRequest -Uri https://raw.githubusercontent.com/CptOfEvilMinions/osquery-py-communityid/master/osquery_community_id.py -OutFile 'C:\Program Files\Extensions\osquery_community_id.py;
5. .\osqueryi.exe --nodisable_extensions
6. SELECT value FROM osquery_flags WHERE name = 'extensions_socket';
7. Open a Powershell instance
8. cd 'C:\Program Files\osquery\Extensions'
9. python .\osquery_community_id.py --socket <value from query above>
10. Go back to PowerShell instance running Osqueryi
11. SELECT * FROM community_id WHERE src_ip='1.1.1.1' AND src_port=5858 AND dst_ip='2.2.2.2' AND dst_port=80 AND protocol=6;
12. .quit

CommunityID and Polylogyx extensions on Windows 10

Add Osquery-polylogy extensions

1. DISABLE WINDOWS DEFENDER
2. Open Powershell instance as Administrator
3. cd $home\Downloads
4. [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
5. Invoke-WebRequest -Uri https://github.com/polylogyx/osq-ext-bin/archive/master.zip -OutFile osq-ext-bin.zip -MaximumRedirection 3
6. Expand-Archive .\osq-ext-bin.zip -DestinationPath .
7. cd osq-ext-bin-master
8. Copy-Item .\plgx_win_extension.ext.exe -Destination 'C:\Program Files\osquery\Extensions\plgx_win_extension.ext.exe'
9. (Get-Content -path .\osquery.flags -Raw) -replace "ProgramData","Program Files" | Set-Content -Path .\osquery.flags
10. Copy-Item .\osquery.flags -Destination 'C:\Program Files\osquery\osquery.flags'
11. Set-Content -Path 'C:\Program Files\osquery\extensions.load' -Value 'C:\Program Files\osquery\Extensions\plgx_win_extension.ext.exe'

Test Osquery-polylogy and win_socket_events table

1. Stop-Service -Name osqueryd
2. cd 'C:\Program Files\osquery'
3. .\osqueryi.exe --flagfile .\osquery.flags
4. .tables
5. SELECT * FROM win_socket_events;
6. .quit

Joining win_socket_events  and CommunityID  tables

1. .\osqueryi.exe --nodisable_extensions --flagfile .\osquery.flags
2. SELECT value FROM osquery_flags WHERE name = 'extensions_socket';
3. Open a Powershell instance
4. cd 'C:\Program Files\osquery\Extensions'
5. python .\osquery_community_id.py --socket <value from query above>
6. Go back to Osquery Powershell instance
7. SELECT s.local_address, s.local_port, s.remote_address, s.remote_port, s.protocol, c.community_id, s.pid, s.process_name
FROM win_socket_events as s JOIN community_id as c
ON s.local_address=c.src_ip AND
s.local_port=c.src_port AND
s.remote_address=c.dst_ip AND
s.remote_port=c.dst_port AND
s.protocol=c.protocol
WHERE action='SOCKET_CONNECT';
8. .quit

OsqueryD setup

Compile Python extension with PyInstaller for OsqueryD

1. Open a Powershell instance as Administrator
2. pip install pyinstaller==3.6
3. cd 'C:\Program Files\osquery\Extensions'
4. pyinstaller osquery_community_id.py
   
5. ls
6. Open Notepad as Administrator
7. Open C:\Program Files\osquery\extensions.load
8. Paste the following:
   C:\Program Files\osquery\Extensions\dist\osquery_community_id\osquery_community_id.exe
C:\Program Files\osquery\Extensions\plgx_win_extension.ext.exe
9. Save changes
10. cd 'C:\Program Files\osquery'
11. icacls .\Extensions /setowner Administrators /t
12. icacls .\Extensions /grant Administrators:f /t
13. icacls .\Extensions /inheritance:r /t
14. icacls .\Extensions /inheritance:d /t
15. .\osqueryi.exe --nodisable_extensions --flagfile .\osquery.flags
16. SELECT s.local_address, s.local_port, s.remote_address, s.remote_port, s.protocol, c.community_id, s.pid, s.process_name FROM win_socket_events as s JOIN community_id as c ON s.local_address=c.src_ip AND s.local_port=c.src_port AND s.remote_address=c.dst_ip AND s.remote_port=c.dst_port AND s.protocol=c.protocol WHERE action='SOCKET_CONNECT';
17. .quit
18. Invoke-WebRequest -Uri https://raw.githubusercontent.com/CptOfEvilMinions/osquery-py-communityid/master/conf/osquery.conf -OutFile 'C:\Program Files\osquery\osquery.conf'
19. Start-Service -name osqueryd

Discussion

Github PR for a communityID table

On Tuesday February 4th 2020, I participated in the Osquery office hours where zwass debuted his new PR to the Osquery project for communityID generation. So the timing of my blog post and this PR are very timely. In the hardware resources section below, I show the performance of my extension with regards to system resources being utilized. Unfornately, my Osquery-python implementation of CommunityID on Windows utilzies alot of system resources, so my hope is the PR above will be more preformant. Nonetheless, this exercise was a good learning experience and I hope this blog post is helpful for developing Osquery extensions on Windows.

Not easily cross-platform

As you probably noticed above, to get this osquery-python extension working it requires Python on the machine. Luckily, Linux and macOS come with Python pre-installed so it’s a matter of installing the proper PIP libraries to run this Python module. However, for Windows + OsqueryD, you need Python on the machine and you need to compile the Python code. This may require an elaborate build pipeline containing Windows machines to build new Osquery extensions. However, an alternative, osquery-go exists which allows cross-platform compilation, on any platform for any platform.

Dependency on Polylogyx for Windows

This blog post relies on the Osquery-Polylogyx extension pack to monitor Windows socket events because Osquery does not provide this functionality. The Osquery-Polylogyx extension pack is a work of art in my opinion and a pinnacle example of threat detection engineering. However, the project only works with version 3.2.6 and 4.0.2 of Osquery and it requires Windows Defender to be disabled. I will make the assumption that if your environment has an EDR product like Carbon Black it will block this for the same reasons as Windows Defender. However, if you can’t afford an EDR product this will get you as close as possible for free but without the prevention capabilities.

Hardware resources

Below are a couple of screenshots of the Python extension and the AMOUNT of hardware resources it consumes on Windows and macOS.

Windows system resources

The first screenshot is how many resources the Python module consumers on a Windows machine at the start and that is before ANY query is executed. The second screenshot is how many resources are consumed on a Windows machine when we run a simple query using the “community_id” table. The last screenshot is showing how many resources are consumed on a Windows machine when the Python module is compiled and is an extension of OsqueryD.

For reference, this is a Windows 10 VM running on 2 virtual cores and 4 GBs of RAM on a 2019 Macbook Pro. The resource consumption of this Python module could be due to my implementation but even so, those numbers are frightening. My hope is that when I re-write this extension using osquery-go I will get better performance.

 

 

macOS system resources

Below is a single screenshot of the Python extension and the amount of hardware resources it consumes on macOS. The Python extension consumes considerably fewer resources than it’s Windows counterpart.

References

• Wikipedia – IPv4
• Osquery schema
• Github – osquery/osquery-python
• Powershell – Set-Content
• Powershell – Copy-Item
• How To Replace Text in a File with PowerShell
• How To Install Python 3 On Windows 10
• SQL introduction for osquery
• Powershell – Stop-Service
• DIY: In-house Threat Detection Engineering