MySQL HeatWave is the only fully managed MySQL database service that combines transactions, analytics, machine learning, and GenAI services, without ETL duplication. MySQL HeatWave also includes MySQL HeatWave Lakehouse, allowing users to query data stored in object storage, MySQL databases, or a combination of both. Users can deploy MySQL HeatWave–powered apps on a choice of public clouds: Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Microsoft Azure.

Logs contain a wealth of important data about the health of a system, application, or network. But due to the sheer volume of data involved, its almost impossible for a human to detect patterns and behaviours that could point to a potential system failure or security threat. This is where MySQL HeatWave AutoML comes to the rescue.

MySQL HeatWave AutoML offers log anomaly detection which enables organizations to effectively detect and summarize log anomalies, allowing for timely corrective actions.

This post discusses log anomaly detection; key use cases and the challenges faced in identifying anomalies within log data. Read on to learn more about this important topic.

Log anomaly detection

Log anomaly detection involves identifying unusual patterns or behaviors in log data from systems, applications, or networks. Such anomalies can signal issues like security breaches, system failures, or performance problems.

Log anomaly detection use cases

  • Operational Intelligence: Application downtime can cause significant financial losses. To prevent such events, operations teams look for early warning signs, which typically appear in logs.

  • System Monitoring: System logs are generated by operating systems, servers, and other infrastructure components. Anomalies in these logs can indicate security threats, system failures, performance issues, or operational inefficiencies.

  • Root Cause Analysis: Log data can be analyzed to quickly identify the root cause of issues, minimizing customer impact by enabling rapid resolution.

  • Regulatory Compliance and Auditing: Adhering to regulations such as GDPR, SOX, HIPAA, and other data protection laws is mandatory, with significant penalties for non-compliance. Logs can be monitored to detect potential violations, and compliance alerts can be generated as needed.

Challenges with log anomaly detection

  • Labeled data: Labeled data plays a critical role in establishing ground truth for training machine learning (ML) models. However, log data is typically abundant and highly dimensional, making it impractical to label entire datasets. Creating labeled data is often tedious, time-consuming, and costly.

  • Context loss: Context is important when it comes to detecting log anomalies. While a single log entry may seem normal in isolation, it can form part of an anomalous pattern when analyzed in sequence with surrounding logs.

  • False negatives and false positives: In log anomaly detection, a false negative occurs when the system marks anomalous events as normal, while a false positive occurs when the system incorrectly identifies normal events as anomalies. Both cases can be costly, leading to unnecessary alerts, wasted resources, and potential alert fatigue.

  • Cold start: A lack of sufficient historical log data can hinder the development of effective models to detect the anomalies.

  • Latency: Making anomalous events actionable to prevent system failures, where timely response is critical.

Log anomaly detection with MySQL HeatWave AutoML

MySQL HeatWave AutoML detects anomalies in unlabeled data in an automated fashion using a novel and patented technique called Generalized kth Nearest Neighbors (GkNN) which is based on an ensemble algorithm. It identifies various common types of anomalies which typically requires separate algorithms to detect. In addition, MySQL HeatWave AutoML also supports Principal Component Analysis (PCA), a technique to manage highly dimensional data such as log data. MySQL HeatWave AutoML also supports semi-supervised learning.

Key Features

Unsupervised and semi-supervised learning: MySQL HeatWave AutoML log anomaly detection supports both unsupervised and semi-supervised learning. Unsupervised learning detects new anomalies without requiring the model to be pretrained on them. Semi-supervised learning leverages known anomalies to improve detection accuracy. Combining unsupervised and semi-supervised learning results in a more effective log anomaly detection model.

Integrate Generative AI: During the prediction phase, anomalous log entries can be detected, but they may be difficult to interpret and may not clearly communicate the underlying issue. MySQL HeatWave GenAI can summarize these log entries, making it easier to understand and act on the root cause.

Mask log data using regular expressions: By default, raw logs are preprocessed to mask repetitive patterns. In addition to the built-in masking, users can provide custom regular expressions to further manipulate log data.

Multiple log source support: Logs from multiple sources can be used to train the log anomaly detection model. During training, all logs from the same source are processed together.

Support for context window: Log entries are grouped together to provide context for anomaly detection. User can specify the number of log entries in each group and the overlap between consecutive groups.

Automated log anomaly detection pipeline

A log anomaly detection automated pipeline comprises of the following steps.

  • Mask repetitive patterns: Raw logs are processed to mask repetitive patterns using drain3, a template mining algorithm that groups raw log messages into templates that can be effectively processed for log anomaly detection.
  • Apply user specified regular expression: In addition to default masking, user can specify regular expressions to process the log entries.
  • Group log entries: Group log entries together to provide context for anomaly detection.
  • Convert text to numerical vectors:  Convert each group of log entries into numerical vectors.
  • Build anomaly detection model: Train an anomaly detection model based on the numerical vectors.
  • Summarize log entries: Using generative AI, summarize log entries in the group.

  

Log anomaly detection pipeline:Parse, sequence, embed, detect

Build, Score, Predict – Log anomaly detection model using MySQL HeatWave AutoML

This section describes the steps the user needs to take to build a log anomaly detection model, score it to evaluate the model quality and predict log anomalies using the model.

Create and populate the train and test tables

The user should create a train and test table and populate them with log entries. The train table will be used to train the model and the test table will be used to evaluate the model.

Tables should have columns with following data – (1) Log entries (2) Source of log (3) Label for the log line.

At least one column must act as the primary key to establish the temporal order of logs. If the Source column is not present, it is assumed that all logs originate from the same source.

    
    mysql> CREATE TABLE training_data (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        log_message TEXT,
        timestamp DATETIME,
        target TINYINT
    );
    
    INSERT INTO training_data (log_message, timestamp, target) VALUES
        ("User login successful: admin", "2023-08-07 09:00:00", 0),
        ("Database connection established", "2023-08-07 09:05:23", 0),
        ("Failed login attempt from IP: 192.168.1.20", "2023-08-07 09:12:15", 1),
        ("Server load is high: 85%", "2023-08-07 09:20:30", 1),
        ("User logout: Jane", "2023-08-07 18:10:00", 0);
    
    mysql> CREATE TABLE testing_data (
        log_id INT AUTO_INCREMENT PRIMARY KEY,
        log_message TEXT,
        timestamp DATETIME,
        target TINYINT
    );
    
    INSERT INTO testing_data (log_message, timestamp, target) VALUES
        ("User login failed: Invalid credentials", "2023-08-08 10:30:00", 1),
        ("Unusual network traffic from IP: 10.0.0.5", "2023-08-08 12:45:30", 1);

 

Build the model

mysql>CALL sys.ML_TRAIN('anomaly_log_data.training_data', NULL, JSON_OBJECT('task', 'log_anomaly_detection', 'exclude_column_list', JSON_ARRAY('log_id', 'timestamp', 'target')), @unsupervised_log_model);

Score the model

mysql> CALL sys.ML_MODEL_LOAD(@unsupervised_log_model, NULL);
    mysql> CALL sys.ML_SCORE('anomaly_log_data.testing_data', 'target', @unsupervised_log_model, 'f1', @anomaly_log_score, NULL);
    mysql> SELECT @anomaly_log_score;
    +--------------------+
    | @anomaly_log_score |
    +--------------------+
    | 0.8571428656578064 |
    +--------------------+
    1 row in set (0.0452 sec)

Generate predictions

mysql> CALL sys.ML_PREDICT_TABLE('anomaly_log_data.testing_data', @unsupervised_log_model, 'anomaly_log_data.log_predictions_unsupervised',JSON_OBJECT('logad_options',JSON_OBJECT('summarize_logs', TRUE)));
    
    mysql> SELECT * FROM log_predictions_unsupervised\G
    *************************** 1. row ***************************
     id: 1
    parsed_log_segment: User login failed: Invalid credentials Server response time increased Normal database query Unusual network traffic from IP: 10.0.0.5 System update completed successfully. Error log: Stack trace included User activity: Admin accessed settings Unlabeled log: Further investigation needed Security alert: Potential malware detected System shutdown initiated
    
    ml_results: {"summary": "\nHere is a concise summary of the text:\n\nThe system encountered several issues, including an invalid login attempt, increased server response time, and unusual network traffic. A potential malware detection was also triggered, prompting a security alert. The system has been shut down for further investigation.", "index_map": [1, 2, 3, 4, 5, 6, 7, 8, 9, 10], "predictions": {"is_anomaly": 1}, "probabilities": {"normal": 0.0, "anomaly": 1.0}}
    *************************** 2. row ***************************
     id: 2
    parsed_log_segment: Unusual network traffic from IP: 10.0.0.5 System update completed successfully Error log: Stack trace included User activity: Admin accessed settings Unlabeled log: Further investigation needed Security alert: Potential malware detected System shutdown initiated
    
    ml_results: {"summary": "\nHere is a concise summary:\n\nA system update was completed successfully, but an error log indicates potential malware detection and requires further investigation.", "index_map": [4, 5, 6, 7, 8, 9, 10], "predictions": {"is_anomaly": 1}, "probabilities": {"normal": 0.0, "anomaly": 1.0}}

Note that ‘is_anomaly’ indicates if the row is an anomaly or normal, and ‘probabilities’ indicates the probability of being an anomaly.

Conclusion

Log anomaly detection is an effective technique for finding abnormalities in log data. It can be applied to various tasks such as operational intelligence, system monitoring, root cause analysis, regulatory compliance and auditing. MySQL HeatWave AutoML offers an automated machine learning pipeline for log anomaly detection that supports various anomaly detection algorithms as well as both unsupervised and supervised learning.

For more details, please refer to the MySQL HeatWave AutoML documentation and the MySQL HeatWave AutoML page.
You can also try MySQL HeatWave for free.
Using the free service, build a predictive ML model using the lab, Getting started with MySQL HeatWave AutoML to try MySQL HeatWave AutoML firsthand.
Also, checkout videos that demonstrate how to build anomaly detection model using unsupervised learning and how to use Principal Component Analysis (PCA) for anomaly detection.