Real-Time Website Security Monitoring with WarpStream, RisingWave, and Grafana
Tags: risingwave, dataengineering, apachekafka, grafana
Published: 2024-06-11 17:45:51

---
title: Real-Time Website Security Monitoring with WarpStream, RisingWave, and Grafana
published: true
date: 2024-04-23 15:23:49 UTC
tags: risingwave,dataengineering,apachekafka,grafana
canonical_url: 
---

by Fahad Shah, Developer Advocate — RisingWave

This blog is guest-authored by Fahad Shah from RisingWave and cross-posted from RisingWave’s blog.

In this blog, we have presented the development of a real-time security threat monitoring system that integrates RisingWave, WarpStream, and Grafana. The setup process for the entire system is quite straightforward. To monitor each metric, you only need to create a single materialized view in RisingWave and visualize it in Grafana.

[Sign up for a free WarpStream trial](https://console.warpstream.com/signup/?utm_source=medium&utm_medium=blog&utm_campaign=smg).

### Overview

In this blog, we’ll explore the design of a real-time security monitoring system that combines RisingWave, WarpStream, and Grafana. This architectural design ensures scalability and responsiveness, enabling quick identification and mitigation of security risks in web applications. By leveraging the synergies of RisingWave, WarpStream, and Grafana, the system offers comprehensive security monitoring capabilities for improved threat detection and response.

[WarpStream](https://www.warpstream.com/), a data streaming platform compatible with Apache Kafka, serves as the central messaging backbone for collecting and distributing website audit logs in real-time. It is built directly on top of object storage. These logs are seamlessly ingested into [RisingWave](https://risingwave.com/), a streaming database, enabling continuous analysis and filtering of the incoming data stream. By leveraging RisingWave’s powerful processing capabilities, the system effectively detects potential security threats.

[Grafana](https://grafana.com/) creates a unified real-time dashboard that presents detailed insights into user activities, website referrer analytics by user interaction, user status code analysis, and security profiling of users. This holistic view enables efficient monitoring and analysis of security-related metrics. Additionally, the system can generate alerts and support automated actions in response to detected threats, thereby adopting a proactive approach to security monitoring.

### Step 1: Set up WarpStream

First, we generate random website logs and then send all this data to a topic in WarpStream, which is then ingested into RisingWave. For more information, refer to [data ingestion from WarpStream into RisingWave](https://docs.risingwave.com/docs/current/ingest-from-warpstream/) in the documentation.

The sample data sent to the WarpStream topic contains information about user activities, requests, and responses on a website.

{ "request\_timestamp": "2024-01-25T12:30:45.678Z", "ip\_address": "192.168.1.100", "user\_agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.4567.89 Safari/537.36", "url": "", "http\_method": "GET", "status\_code": 200, "response\_time": 120, "referrer": "google", "user\_id": "12345", "username": "john\_doe", "user\_action": "view\_page", "security\_level": "low" }

### Step 2: Ingest data streams into RisingWave

Now that we have sent the data stream to WarpStream (in JSON), we can ingest the stream with the following SQL statement. For more information on setting up RisingWave and getting started, refer to the [Quick start](https://docs.risingwave.com/docs/current/get-started/) in RisingWave documentation.

Create source

This query configures a source named website\_logs\_source to ingest data from the WarpStream topic named website\_logs. The query also defines the data schema using JSON format, including fields such as request timestamp, IP address, user agent, URL, HTTP method, status code, response time, and more.

CREATE SOURCE website\_logs\_source ( request\_timestamp TIMESTAMP, ip\_address VARCHAR, user\_agent TEXT, url TEXT, http\_method VARCHAR, status\_code INTEGER, response\_time INTEGER, referrer TEXT, user\_id VARCHAR, username VARCHAR, user\_action VARCHAR, security\_level VARCHAR ) WITH ( connector='kafka', topic = 'website\_logs', properties.bootstrap.server = 'message\_queue:29092', scan.startup.mode = 'earliest' ) FORMAT PLAIN ENCODE JSON;

Create materialized views for data analysis in RisingWave

‍  
Analyzing website user metrics

This query establishes a materialized view named website\_user\_metrics to generate aggregated statistics on user activity using website logs. The materialized view calculates various metrics, including total requests, response times, error counts, login/logout counts, and unique IP addresses, within one-minute intervals. These aggregated statistics are then presented for each user, along with the corresponding time window.

CREATE MATERIALIZED VIEW website\_user\_metrics AS WITH UserActivityStats AS ( SELECT username, COUNT(username) AS total\_requests, window\_start, window\_end, MIN(response\_time) AS min\_response\_time, MAX(response\_time) AS max\_response\_time, AVG(response\_time) AS avg\_response\_time, PERCENTILE\_CONT(0.5) WITHIN GROUP (ORDER BY response\_time) AS median\_response\_time, SUM(CASE WHEN status\_code >= 400 THEN 1 ELSE 0 END) AS total\_errors, SUM(CASE WHEN user\_action = 'login' THEN 1 ELSE 0 END) AS login\_count, SUM(CASE WHEN user\_action = 'logout' THEN 1 ELSE 0 END) AS logout\_count, COUNT(DISTINCT ip\_address) AS unique\_ips FROM TUMBLE (website\_logs\_source, request\_timestamp, INTERVAL '1 MINUTES') GROUP BY username, window\_start, window\_end) SELECT username, total\_requests, min\_response\_time, max\_response\_time, avg\_response\_time, median\_response\_time, total\_errors, login\_count, logout\_count, unique\_ips, window\_start, window\_end FROM UserActivityStats;

Ranking top user actions in a time window

This query generates a materialized view called top\_user\_actions to identify and rank the three most frequent user actions within one-minute intervals of website logs. It utilizes window functions to assign rankings based on the occurrence of actions. The materialized view presents the action name, the count of occurrences, and the corresponding time window for each ranked action.

CREATE MATERIALIZED VIEW top\_user\_actions AS WITH ranked\_user\_actions AS ( SELECT user\_action, COUNT(user\_action) AS count\_user\_activity, window\_start, window\_end, ROW\_NUMBER() OVER (PARTITION BY window\_start, window\_end ORDER BY COUNT(user\_action) DESC) AS action\_rank FROM TUMBLE (website\_logs\_source, request\_timestamp, INTERVAL '1 MINUTES') GROUP BY user\_action, window\_start, window\_end ) SELECT user\_action, count\_user\_activity, window\_start, window\_end FROM ranked\_user\_actions WHERE action\_rank <= 5 ORDER BY window\_start, action\_rank;

Analyzing referrer activity of website by user interaction

This query establishes a materialized view called referrer\_activity\_summary to provide a summary of website activity based on referrers within one-minute intervals. The materialized view aggregates multiple metrics, including referrer visit count, page visits, interactions, content interactions, and window interactions. These metrics are presented alongside the corresponding time window for analysis and evaluation.

CREATE MATERIALIZED VIEW referrer\_activity\_summary AS SELECT referrer, COUNT(referrer) AS referrer\_visit\_count, SUM(CASE WHEN user\_action IN ('view\_page', 'navigate\_page') THEN 1 ELSE 0 END) AS page\_visits, SUM(CASE WHEN user\_action IN ('submit\_form', 'login', 'logout') THEN 1 ELSE 0 END) AS interactions, SUM(CASE WHEN user\_action IN ('scroll\_page', 'download\_file', 'upload\_file') THEN 1 ELSE 0 END) AS content\_interactions, SUM(CASE WHEN user\_action IN ('close\_window', 'open\_new\_tab') THEN 1 ELSE 0 END) AS window\_interactions, window\_start, window\_end FROM TUMBLE(website\_logs\_source, request\_timestamp, INTERVAL '1 MINUTES') GROUP BY referrer, window\_start, window\_end;

User HTTP status code analysis

This query establishes a materialized view called status\_code\_analysis\_summary to analyze and summarize the distribution of HTTP status codes within one-minute intervals of website logs. The materialized view calculates various metrics, including the count of each status code, the average response time for each status code, and the cumulative count and percentage of status codes within each time window. These metrics are presented alongside the corresponding time window, ordered in descending order by both the time window and status code.

CREATE MATERIALIZED VIEW status\_code\_analysis\_summary WITH Status\_Code\_Analysis AS ( SELECT status\_code, COUNT(status\_code) AS count\_status\_code, AVG(response\_time) AS avg\_response\_time, SUM(COUNT(status\_code)) OVER (PARTITION BY window\_start, window\_end ORDER BY status\_code) AS cumulative\_count, 100.0 \* SUM(COUNT(status\_code)) OVER (PARTITION BY window\_start, window\_end ORDER BY status\_code) / SUM(COUNT(status\_code)) OVER (PARTITION BY window\_start, window\_end) AS cumulative\_percentage, window\_start, window\_end FROM TUMBLE (website\_logs\_source, request\_timestamp, INTERVAL '1 MINUTES') GROUP BY status\_code, window\_start, window\_end ) SELECT status\_code, count\_status\_code, avg\_response\_time, cumulative\_count, cumulative\_percentage, window\_start, window\_end FROM Status\_Code\_Analysis ORDER BY window\_start DESC, status\_code;

User security profiling and security level analysis

This query establishes a materialized view named security\_level\_analysis\_summary to analyze and summarize security levels within one-minute intervals of website logs. The materialized view calculates various metrics, including the count of each security level, the average response time for each security level, and the median count of security levels within each time window. These metrics are presented alongside the corresponding time window, ordered in descending order by both the time window and the median count of security levels.

CREATE MATERIALIZED VIEW security\_level\_analysis\_summary AS WITH Security\_Profiling AS ( SELECT security\_level, COUNT(security\_level) AS count\_security\_level, AVG(response\_time) AS avg\_response\_time, PERCENTILE\_DISC (0.5) WITHIN GROUP (ORDER BY security\_level) AS median\_count\_security\_level, window\_start, window\_end FROM TUMBLE (website\_logs\_source, request\_timestamp, INTERVAL '1 MINUTES') GROUP BY security\_level, window\_start, window\_end ) SELECT security\_level, count\_security\_level, avg\_response\_time, median\_count\_security\_level, window\_start, window\_end FROM Security\_Profiling ORDER BY window\_start DESC, median\_count\_security\_level;

### Step 3: Send the data from RisingWave to Apache Grafana for visualization

We’ll configure Grafana to read data from RisingWave and build visualizations.

Connect RisingWave to Grafana

To utilize RisingWave as a data source in Grafana and create visualizations and dashboards, follow the instructions provided in [Configure Grafana to read data from RisingWave](https://docs.risingwave.com/docs/current/grafana-integration/). Once the connection between RisingWave and Grafana is established, you can incorporate materialized views from RisingWave as tables to design charts and build a comprehensive dashboard.

Visualizing data with Grafana: table, charts, and dashboards

This table is generated from the website\_logs\_source source that was create earlier.

![](https://cdn-images-1.medium.com/max/1024/0*LMTeH4jdUa3Q1Msx.png)

This chart is generated from referrer\_activity\_summary materialized view to summarize website activity based on referrers.

![](https://cdn-images-1.medium.com/max/1024/0*tp2WpIrUMNQ7z8fP.png)

This chart is generated from a materialized view named website\_user\_metrics to provide aggregated statistics on user activity based on website logs.

![](https://cdn-images-1.medium.com/max/1024/0*Zmk0UVW0USVTzRMO.png)

This chart is generated from the security\_level\_analysis\_summary materialized view to analyze and summarize security levels within one-minute intervals in website logs.

![](https://cdn-images-1.medium.com/max/1024/0*aO1rXZ2jNYxnRjFg.png)

This chart is created on a materialized view top\_user\_actions to identify and rank the top five user actions based on their frequency within one-minute intervals of website logs.

![](https://cdn-images-1.medium.com/max/1024/0*O_7uedFDBF9xcDvI.png)

This chart is generated from the status\_code\_analysis\_summary materialized view to analyze and summarize the distribution of HTTP status codes over one-minute intervals in website logs.

![](https://cdn-images-1.medium.com/max/1024/0*9q5t1iXi4DSWn7iH.png)

This unified dashboard offers a comprehensive collection of charts for real-time monitoring of a website or online platform. It provides valuable insights into user and website activity, including metrics based on referrers, distribution of HTTP status codes, top five user actions, and summaries of security levels. By combining these charts, the dashboard provides a holistic view and enables enhanced threat detection and response through comprehensive security monitoring capabilities.

![](https://cdn-images-1.medium.com/max/1024/0*N4DZWc0I23uJoiJZ.png)

### Conclusion

In this blog, we have presented the development of a real-time security threat monitoring system that integrates RisingWave, WarpStream, and Grafana. The setup process for the entire system is quite straightforward. To monitor each metric, you only need to create a single materialized view in RisingWave and visualize it in Grafana. The analysis showcased in the blog serves as an example and inspiration. If your data points are readily available and provide the necessary data, you can express sophisticated analytical and transformational logic. Feel free to explore further and reach out to us if you have any questions or need assistance.