Creating my first Osquery extension to generate CommunityIDs with Osquery-python on Windows

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.


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.



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
  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.

def generate(self, context):

This section contains all the logic for an Osquery extension. The context variable being passed into the function contains the values being passed into Osquery. For example, if we query the communityID table (which doesn’t exist yet) with the following query: select * from community_id WHERE src_ip='' AND src_port=5858 AND dst_ip='' AND dst_port=80 AND src_port=5858 and protocol=6; the context variable will contain (first screenshot below) the table name, the key-value pairs of dst_ip, dst_port, src_ip, src_port, and protocol from the query.

First, our plugin converts the context variable which is a string into a Python dictionary using the builtin JSON module. Next, the code iterates over the dictionary and extracts the necessary values such as dst_ip, dst_port, src_ip, src_port, and protocol. If the dictionary contains a key-value pair that we don’t need, Python is instructed to skip it.

Next, our code initiates the CommunityID Python object. Then the code evaluates the protocol variable to numerical integers which represent a protocol type, 6 is TCP and 17 is UDP. If the protocol value matches an integer, the communityID is calculated based on the tuple of (src_ip, src_port, dst_ip, dst_port) and a specific function is called based on the protocol (make_udp(), make_tcp()). If the protocol is unknown an error is printed which can be used for debugging. Lastly, the code constructs a dictionary to be returned which consists of the key-values passed into the table and the newly calculated communityID.

if __name__ == “__main__”:

This section is pretty simple but the important piece is the name and version keys. The name key is the name used to register the extension by Osquery and the version key will specify the iteration of the extension.

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 ./ --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
  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
    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 -OutFile 'C:\Program Files\Extensions\;
  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 .\ --socket <value from query above>
  10. Go back to PowerShell instance running Osqueryi
  11. SELECT * FROM community_id WHERE src_ip='' AND src_port=5858 AND dst_ip='' AND dst_port=80 AND protocol=6;
  12. .quit

CommunityID and Polylogyx extensions on Windows 10

Add Osquery-polylogy extensions

  2. Open Powershell instance as Administrator
  3. cd $home\Downloads
  4. [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
  5. Invoke-WebRequest -Uri -OutFile -MaximumRedirection 3
  6. Expand-Archive .\ -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 .\ --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.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
  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
  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.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 -OutFile 'C:\Program Files\osquery\osquery.conf'
  19. Start-Service -name osqueryd


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.


3 thoughts on “Creating my first Osquery extension to generate CommunityIDs with Osquery-python on Windows

  1. Nice work! Osquery 2.2.0 has been released and includes the Community ID support on all platforms. I would be very curious to know if it provides acceptable performance for you.

    • spartan2194 says:

      Hey Zach, I actually did a shoutout to your contribution in the discussion section! I haven’t been able to test your implementation yet but I would assume it’s better.

      Also, the Polylogyx extension pack only supports certain versions of Osquerey and I haven’t tested them with Osquery v4.2.0.

  2. And that’s 4.2.0 *whoops*

Leave a Reply

Your email address will not be published.