• Products
    LOG COLLECTOR
    NXLog Enterprise Edition
    Full feature multi-platform log collection
    NXLog Community Edition
    Open-source free log collector
    ADD-ONS FOR NXLOG ENTERPRISE EDITION
    NXLog Add-Ons
    Integration with various software
    AGENT MANAGER FOR NXLOG ENTERPRISE EDITION
    NXLog Manager
    Manage and monitor NXLog instances
    NXLog Minder
    Hyper-scalable, API-first agent management
    DATABASE FOR NXLOG ENTERPRISE EDITION
    Raijin Database Engine
    The schemaless SQL database for storing events
    more from nxlog
    Professional Services
    Compare NXLog EE and CE
  • Downloads
    NXLog Enterprise Edition
    Full feature multi-platform log collection
    NXLog Manager
    Manage and monitor NXLog instances
    NXLog Community Edition
    Open-source free log collector
  • Solutions
    Integrations
    With SIEM, Devices, SaaS...
    Specfic OS support
    AIX, Linux, FreeBSD
    SCADA/ICS
    Energy, Oil & Gas, Transport...
    Windows Event log
    Collect locally or remotely, ..
    DNS Logging
    Enterprise-grade DNS log...
    Log Collection Modes
    Agent-based, Agentless or Cloud
    Agent Management
    Agents management and monitoring
    FIM
    File Integrity Monitoring
    macOS Logging
    ULS events, Apple System Logs ...

    By Industry

    Financial Services
    Government & Education
    Entertainment & Gambling
    Telecommunications
    Medical & Healthcare
    Military & Defense
    Law Firms & Legal Counsel
    Industrial & Manufacturing
  • Partners
    Find a Reseller
    Look for our resellers worldwide
    Technology Ecosystem
    See all our partners and integrations
    Partner Program
    Join our community of partners
    Partner Portal →
  • Resources
    Documentation
    Products guides and integrations
    Blog
    Tutorials, updates and releases
    White papers
    Datasheets, infographics and more
    Videos
    Trainings and tutorial on specific topics
    Webinars
    Community events and webinars
    Community Forum →
  • Support
  • Why Nxlog
    About Us
    Our journey, team and mission
    Customers
    Testimonials and case studies
    Careers
    We are hiring!
    Contact Us →
Log In Sign Up
Request Trial
LOG COLLECTOR
NXLog Enterprise Edition
Full feature multi-platform log collection
NXLog Community Edition
Open-source free log collector
ADD-ONS FOR NXLOG ENTERPRISE EDITION
NXLog Add-Ons
Integration with various software
AGENT MANAGER FOR NXLOG ENTERPRISE EDITION
NXLog Manager
Manage and monitor NXLog instances
NXLog Minder
Hyper-scalable, API-first agent management
DATABASE FOR NXLOG ENTERPRISE EDITION
Raijin Database Engine
The schemaless SQL database for storing events
more from nxlog
Professional Services
Compare NXLog EE and CE
NXLog Enterprise Edition
Full feature multi-platform log collection
NXLog Manager
Manage and monitor NXLog instances
NXLog Community Edition
Open-source free log collector
Integrations
With SIEM, Devices, SaaS...
Specfic OS support
AIX, Linux, FreeBSD
SCADA/ICS
Energy, Oil & Gas, Transport...
Windows Event log
Collect locally or remotely, ..
DNS Logging
Enterprise-grade DNS log...
Log Collection Modes
Agent-based, Agentless or Cloud
Agent Management
Agents management and monitoring
FIM
File Integrity Monitoring
macOS Logging
ULS events, Apple System Logs ...

By Industry

Financial Services
Government & Education
Entertainment & Gambling
Telecommunications
Medical & Healthcare
Military & Defense
Law Firms & Legal Counsel
Industrial & Manufacturing
Find a Reseller
Look for our resellers worldwide
Technology Ecosystem
See all our partners and integrations
Partner Program
Join our community of partners
Partner Portal →
Documentation
Products guides and integrations
Blog
Tutorials, updates and releases
White papers
Datasheets, infographics and more
Videos
Trainings and tutorial on specific topics
Webinars
Community events and webinars
Community Forum →
Support
About Us
Our journey, team and mission
Customers
Testimonials and case studies
Careers
We are hiring!
Contact Us →
  • Loading...
Request Trial
July 29, 2021 raijinwindowsstrategy

Using Raijin Database Engine to aggregate and analyze Windows security events

By John Kirch

Share
ALL SIEM STRATEGY SECURITY ANNOUNCEMENT DEPLOYMENT COMPLIANCE COMPARISON RSS

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

  • Install Docker Desktop on Mac

  • Install Docker Desktop on Windows

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

Useful links

  • Download Raijin Database Engine

  • NXLog Enterprise Edition v5 trial

  • Get the latest stable Apache Superset Docker image with built-in support for connecting to Raijin

  • Creating your first Apache Superset Dashboard

  • Superset 101: Getting Started - Time Series Charts

  • GitHub repository of Windows Event Log .evtx files containing Samples of Attacks on Windows

  • 6 windows event log IDs to monitor now

  • Which Windows Server Events Should You Monitor and Why?

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

GET STARTED TODAY:

| Learn more about NXLog Enterprise Edition | Learn more about NXLog Manager | Free Trial | Get Pricing |

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.

  • raijin
  • log aggregation
  • analytics
  • apache superset
  • database
Share

Facebook Twitter LinkedIn Reddit Mail
Related Posts

Making the most of Windows Event Forwarding for centralized log collection
7 minutes | December 17, 2018
DNS Log Collection - Part 2
9 minutes | May 28, 2020
Windows Event Log collection in a nutshell
4 minutes | June 14, 2021

Stay connected:

Sign up

Keep up to date with our weekly digest of articles.

By clicking singing up, I agree to the use of my personal data in accordance with NXLog Privacy Policy.

Featured posts

Announcing NXLog Enterprise Edition 5.7
January 20, 2023
NXLog - 2022 in review
December 22, 2022
Need to replace syslog-ng? Changing to NXLog is easier than you think
November 23, 2022
The EU's response to cyberwarfare
November 22, 2022
Looking beyond Cybersecurity Awareness Month
November 8, 2022
GDPR compliance and log data
September 23, 2022
NXLog in an industrial control security context
August 10, 2022
Raijin vs Elasticsearch
August 9, 2022
NXLog provides native support for Google Chronicle
May 11, 2022
Aggregating macOS logs for SIEM systems
February 17, 2022
How a centralized log collection tool can help your SIEM solutions
April 1, 2020

Categories

  • SIEM
  • STRATEGY
  • SECURITY
  • ANNOUNCEMENT
  • DEPLOYMENT
  • COMPLIANCE
  • COMPARISON

Subscribe to our newsletter to get the latest updates, news, and products releases.

© Copyright 2023 NXLog Ltd.

PRIVACY POLICY TERMS OF USE

  • PRODUCTS

  • NXLOG ENTERPRISE EDITION
  • NXLOG COMMUNITY EDITION
  • NXLOG ADD-ONS
  • NXLOG MANAGER
  • NXLOG MINDER
  • RAIJIN DATABASE
  • MORE NXLOG

  • COMPARE SOLUTIONS
  • INDUSTRIES
  • INTERGRATIONS
  • FIND A RESELLER
  • PARTNER PROGRAM
  • RESOURCES

  • DOCUMENTATION
  • WHITE PAPERS
  • WEBINARS
  • TUTORIALS
  • BLOG
  • COMMUNITY FORUM
  • ABOUT US

  • WHY NXLOG
  • CUSTOMERS
  • CAREERS
  • CONTACT US
  • DOWNLOADS

  • NXLOG ENTERPRISE EDITION
  • NXLOG COMMUNITY EDITION
  • NXLOG MINDER
  • NXLOG MANAGER
  • NXLOG ADD-ONS
  • RAIJIN DATABASE