Using Raijin Database Engine to aggregate and analyze Windows security events

In this post, we will look at how to use Raijin Database Engine as a backend in a centralized logging environment for collecting and aggregating Windows security events. We will also show you how to integrate Raijin with an open source data exploration tool. Finally, you will see how you can track suspicious network activity and identify specific types of intrusion on Windows hosts using these tools.

A low-cost, lean and mean data discovery solution

Although the combination of tools we present here cannot compete with a full-fledged SIEM solution, they do offer quite a few advantages for security analysts who need a responsive, highly customizable data discovery solution that accepts ad hoc SQL. When contrasted with a cloud-based SIEM, you are not sharing resources with thousands of other customers. These tools and data are on-prem, self-managed, and there are no costs attached to the volume of ingested data.

Raijin Database Engine has been designed specifically around the needs of high volume, event-based logging. What other database engine has all of these 10 key features? But what about a data visualization? Don’t we need a frontend for dashboards, charts, analytics, and ad hoc queries? Fortunately, Apache Superset fits the bill nicely. Like Raijin, it is SQL-based and thus can integrate with any database that speaks SQL. With its intuitive UI and versatile query and chart-building capabilities, its popularity is growing. Apache Superset has become Airbnb’s preferred BI solution and Dropbox’s data exploration platform of choice.

Creating a Windows intrusion detection test environment

Generating every imaginable type of Windows intrusion in an enterprise environment would be a very time-consuming project. However, there is a comprehensive collection of Windows security events for this very purpose available on GitHub. With well over 7,000 events stored in 266 .evtx files, there are plenty of events available for developing whatever types of aggregation queries you might need. NXLog Enterprise Edition can read Windows Event Log files natively and send them to Raijin.

Installing Raijin Database Engine and Server

You can install Raijin on either Debian-based or RedHat-based (CentOS, SuSE, etc.) systems. See the Raijin Downloads page for details. The Raijin Quickstart page provides enough information to get you up and running.

Using your web browser for accessing the Rajin Server’s web-based UI via http://localhost:2500 create a new database and table for storing Windows security events:

create database security;
use security;
create table windows("EventTime" DATETIME, "EventReceivedTime" DATETIME);

The Raijin User Guide is bundled with the installation and can found under /opt/raijin/doc/html/raijin-user-manual.html or as a PDF under /opt/raijin/doc/raijin-user-manual.pdf if you used the default installation path.

Sending Windows security events to a Raijin server

For reading native Windows Event Log files in .evtx format, we need the NXLog Enterprise Edition agent. If you are not already an NXLog customer, visit the NXLog Enterprise Edition Downloads page and select NXLog Enterprise Edition v5 trial from the dropdown menu.

With NXLog EE installed and running on a Windows machine, we can update its configuration file shown below to load the Windows (im_msvistalog) input module that supports .evtx files. In this configuration, the 266 .evtx files representing our test sample of Windows security events were copied from their respective directories in the Git repository to a single directory on our Windows host.

Note

Changes to an existing nxlog.conf file will not take effect until you manually restart the Windows nxlog service under Windows Services.

The JSON (xm_json) JSON extension module is loaded to enable the procedure call to be invoked for converting the events on the fly to JSON. The Raijin (om_raijin) output module receives the JSON records and sends them to the Raijin server, database, and table specified in the module instance.

nxlog.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<Extension _json>
    Module          xm_json
</Extension>

<Input evtx_samples>
    Module          im_msvistalog
    File            "C:\\Program Files\\nxlog\\data\\EVTX-ATTACK-SAMPLES\\*.evtx"
    ReadFromLast    FALSE
#    SavePos         FALSE
    Exec            to_json();
</Input>

<Output raijin>
    Module          om_raijin
    URL             http://raijin-1.example.com:2500
    DBName          security
    DBTable         windows
</Output>
Note

In this post, we are only looking at a groomed collection of Windows events specific to intrusion detection. Once our setup is fully configured to detect a wider variety of Windows events associated with cyberattacks, we would add an additional Windows input module (im_etw) that is capable of directly connecting to Event Tracing for Windows (ETW). This module can collect Windows Debug and Analytical channels that are otherwise not available to regular, non-ETW modules and software which are limited to reading only the standard Windows Event Log channels.

Installing Apache Superset

The easiest way for you to get and use Apache Superset is to install Docker Desktop on your workstation.

Once you have Docker Desktop installed and running, we have created a special Apache Superset 1.2.0 Docker image that already contains the Rajin plugin needed for connecting to your Raijin Server. For instructions on how to download this image and run it in a Docker container, see superset-raijin and follow the steps listed in the Readme section.

Connecting Apache Superset to a Raijin server

The Apache Superset web interface

With the Apache Superset image running in a Docker container and listening on port 8080, you can now access the Apache Superset in your browser using http://localhost:8080

The login credentials have already been set to Username: admin and Password: admin.

Configuring the Raijin database connection

Navigate to Databases and look for the security database in the table of configured databases, then select Edit under Actions.

superset database actions edit

The connection string shown here contains the same Raijin hostname and port as found in the Windows nxlog.conf file: raijin-1.example.com:2500.

If your Raijin installation is running on localhost, you would change the stored value for SQLALCHEMY URI to raijin+http://root:root@localhost:2500/security

superset edit database

NOTE: The Edit database modal always obfuscates the actual password with XXXXXXXXXX.

Datasets

In Apache Superset, datasets represent the workspace for database tables. Since this Docker image is already populated with databases, tables, and charts, we have already defined the windows dataset. Because different data visualizations often require different columns, or additional computed columns, a one-to-many relationship can exist between a database table and the various datasets that have been created from it.

superset datasets

Clicking on the windows dataset will open up the Explore UI which is used for creating a brand new chart as well as for editing existing charts.

Creating queries and charts in Apache Superset

The superset-raijin Docker image we have provided already contains the optional set of Apache Superset sample data (the examples sqlite database), its datasets, charts, stored queries, and its dashboards. Additionally, the windows dataset and its six charts contained within the Windows Security dashboard are also bundled in this Docker image. By opening these charts for editing, you will be able examine their schemas and how their query sections were configured to render the charts as they appear in your Apache Superset UI.

By navigating to Dashboards and opening Windows Security from the list of available dashboards, you can see the charts that are rendered in real time by querying the Windows security events that NXLog EE has collected and forwarded to Raijin. The workflow for each of these charts was the same:

  1. Navigate to Datasets

  2. Click on the windows dataset to open the Explore UI

  3. Edit the dataset to add any calculated columns the chart might need

  4. Choose the chart type

  5. Choose the query type

  6. Select the columns and any filters that are needed

superset windows security dashboard

Viewing or editing a specific chart

Find the chart you are interested in and expand its options. Selecting View chart in Explore from the chart’s options list will open a new browser tab where you can view or edit the attributes and query that define the selected chart as shown below.

superset dashboard options view chart in explore

The Explore UI

Here we see the internal definition of the Most Active Hostnames chart in the Explore UI which consists of a web page with a three-column layout:

  1. In the left column, the Dataset Name, metrics (for aggregate queries), and columns (names, data types identified as icons or stylized text)

  2. In the middle column, Chart actions and attributes, along with Query components and definitions

  3. In the right column, the rendered Chart with a tabular view of the Data returned from the query

View chart in Explore

Editing the dataset to create calculated columns

Expand the options to the right of database.table, in this example security.windows, then select Edit dataset from the list:

Edit Dataset

This will open a modal with five headings, one of which is Calculated Columns.

Creating a calculated column

In this example, UNIX epoch time is calculated to create an epoch column. This will give us a time granularity of 1 second for aggregate queries that generate charts where events per second (EPS) are needed.

Edit Dataset

Detecting defense evasion: RDP over a Reverse SSH Tunnel

Now that we have some experience with the mechanics of getting our logged Windows security events from Raijin into Apache Superset where we can run some analytics, it’s time to look at a strategy for gradually creating charts (or tables) for specific attacks on Windows hosts. For this we have chosen a known exploit in which the Remote Desktop Protocol (RDP) is used by attackers to gain unauthorized access to another network-accessible Windows host.

Although this is not an obscure exploit, the literature on it is sparse and fragmented. However, MENASEC Applied Security Research posted an entire article, RDP over a Reverse SSH Tunnel, which looks at two different ways to detect this exploit. Because RDP has legitimate use cases, MENA point out in their post why detecting illegitimate RDP can be problematic:

Differentiating between a legit and suspicious RDP activity requires serious RDP baselining especially on large and interconnected networks where multiple parties (vendors, IT Help Desk, Network and System Team etc.) use the same protocol, for legit purposes and in a dynamic way.

First type of suspicious event

The first set of criteria indicating that there might be unauthorized RDP meets these conditions:

  • Event ID 4624 (An account was successfully logged on)

  • Logon Type = 10

  • Source IP address = loopback address (127.0.0.1)

Another possible indicator is when the value for Workstation Name matches that of Account Domain, which the author describes as "very abnormal." However, none of our sample events met this condition, so we will not include it in this query.

To create the chart in the Explore UI we need to choose Table as the Visualization Type, set the following Query options, expand Filters in order to choose WHERE, and use Custom SQL to manually enter the conditions:

Filter Event ID 4624

Once the table renders correctly, we can save this configuration as a new Chart named RDP tunnel likely (Event ID 4624), and assign it to the Windows Security dashboard:

rdp ssh tunnel likely eid 4624

Second type of suspicious event

The second set of criteria indicating that there might be unauthorized RDP meets these conditions:

  • Event ID 5156 (The Windows Filtering Platform has permitted a connection)

  • Source IP address = 127.0.0.1 or 127.0.0.2

  • Destination IP address = 127.0.0.1 or 127.0.0.2

  • Source Port or Destination Port = 3389

  • Application used: plink.exe

To create the chart in the Explore UI we need to choose Table as the Visualization Type, set the following Query options, expand Filters in order to choose WHERE, and use Custom SQL to manually enter the conditions:

Filter Event ID 4624

Once the table renders correctly, we can save this configuration as a new Chart named RDP tunnel likely (Event ID 5156), and assign it to the Windows Security dashboard:

rdp ssh tunnel likely eid 5156

Conclusion

It is a rare pleasure when a set of tools resonate with each other as well as these three do. Apache Superset is the perfect complement to Raijin Database Engine while NXLog Enterprise Edition integrates with practically everything! To be able to deploy a solution as complex as this so quickly and hit the ground running within the first hour with meaningful results speaks volumes for this end-to-end solution.

Given the minimum resources required for setting up and testing this integration, we hope you will give it a test drive. If any of the following goals are important to you, we are confident that your team will find that this solution fits in nicely with your current set of tools:

  • Centralized log collection and aggregation of Windows events with an agent that can read any log format, filter, parse, and enrich logs as needed

  • Owning a powerful, log-centric database engine optimized for aggregation queries that can be scaled to the growth of your infrastructure, is free to download, and does not require any costly subscriptions based on GBs of event data ingested

  • Responsive access to your security events via an intuitive UI that speaks SQL — thus leveraging the existing skill set of your team — provides powerful data exploration capabilities, and ad hoc query building, yet allows non-technical roles to create many charts without queries

  • Low or no-latency, on-prem, ingestion of security events in real time without having to share storage or network resources with thousands of other customers

  • A stable UI and backend that only gets updated when your team has fully signed off on the new release candidate that you have downloaded, customized, built, and tested

Disclaimer

While we endeavor to keep the information in this topic up to date and correct, NXLog makes no representations or warranties of any kind, express or implied about the completeness, accuracy, reliability, suitability, or availability of the content represented here.

The accurateness of the content was tested and proved to be working in our lab environment at the time of the last revision with the following software versions:

  • Apache Superset 1.2.0

  • NXLog Enterprise Edition v5.3.6735

  • Raijin Database Engine 0.9.3462

  • Apple macOS 11.4 (Big Sur) on M1 hardware

  • Windows 10

Last revision: 12 July 2021

NXLog Ltd. develops multi-platform log collection tools that support many different log sources, formats, transports, and integrations. The tools help administrators collect, parse, and forward logs so they can more easily respond to security issues, investigate operational problems, and analyze event data. NXLog distributes the free and open source NXLog Community Edition and offers additional features and support with the NXLog Enterprise Edition.

This document is provided for informational purposes only and is subject to change without notice. Trademarks are the properties of their respective owners.

Download a fully functional trial of the Enterprise Edition for free