如何在企业系统查询审计日志中识别自动化查询Bot？

Hey Allan, let's tackle this problem step by step—your initial approach to analyzing time intervals is a solid starting point, but we can refine it and add more robust techniques to reliably spot automated query bots in your audit logs.

The issue with just counting single intervals under 60 seconds is that humans might occasionally have quick back-to-back queries, but bots tend to have sequences of short intervals. Here's how to adjust your analysis to capture that:

First, calculate time differences between consecutive queries per user, then flag short intervals, and finally look for consecutive clusters of these short gaps. Using Python's pandas makes this straightforward:

import pandas as pd

# Load your log data (replace with your actual data source)
logs = pd.DataFrame({
    'query_id': [1,2,3,4,5,6],
    'id': [1,1,1,1,1,1],
    'query_time': pd.to_datetime([
        '2018-02-01 00:09:02', 
        '2018-02-01 00:24:55', 
        '2018-02-01 00:58:55', 
        '2018-02-01 01:01:49', 
        '2018-02-01 01:05:42', 
        '2018-02-01 01:18:56'
    ])
})

# Calculate time differences between consecutive queries for each user
logs['time_diff_sec'] = logs.groupby('id')['query_time'].diff().dt.total_seconds()

# Flag intervals shorter than 60 seconds
logs['is_short_interval'] = logs['time_diff_sec'] < 60

# Detect sequences of 2+ consecutive short intervals (strong bot indicator)
logs['consecutive_shorts'] = logs.groupby('id')['is_short_interval'] \
    .rolling(window=3, min_periods=2).sum().reset_index(0, drop=True)

# Get unique user IDs that show bot-like consecutive short intervals
bot_candidates = logs[logs['consecutive_shorts'] >= 2]['id'].unique()

This way, you're filtering out one-off human quick queries and focusing on the repetitive pattern bots often exhibit.

Bots frequently run on fixed schedules—so time series techniques can help you spot these repeating patterns. Two effective methods here are:

a. Autocorrelation Function (ACF) #

ACF measures how correlated a time series is with itself at different time lags. A sharp peak at a specific lag (e.g., every 5 minutes) means the queries are happening on a consistent schedule.

from statsmodels.graphics.tsaplots import plot_acf
import matplotlib.pyplot as plt

# For a specific user, create a time series of query counts per minute
user_queries = logs[logs['id'] == 1].set_index('query_time')
user_query_counts = user_queries.resample('1min').count()['query_id']

# Plot ACF to detect periodicity
plot_acf(user_query_counts, lags=60)  # Check lags up to 60 minutes
plt.show()

If you see a clear peak at lag 5, that means the user is querying every 5 minutes—almost certainly a bot.

b. Fourier Transform #

You can use a Fast Fourier Transform (FFT) to convert the time series into the frequency domain, which will highlight dominant periodic patterns. This is especially useful if the bot's schedule isn't a simple fixed interval.

To make your detection even more reliable, combine time-based features with other behavioral signals:

• Query Frequency: Bots typically have far higher query rates than humans. Calculate queries per hour/day for each user—any user with an abnormally high rate is a candidate.
• Consistent Query Patterns: If your logs include query parameters, check if the user is running identical queries (or minor variations) repeatedly. Humans rarely do this.
• 24/7 Activity: Bots often operate non-stop, while humans have downtime (nights, weekends). Compare query distribution across days/hours for each user.
• Session Behavior: Look for sudden bursts of queries followed by long gaps, or perfectly regular intervals that don't match natural human behavior.

Machine Learning for Scalable Detection #

If you have a large dataset, train a classification model to distinguish bots from humans using combined features. For example, with a Random Forest:

from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split

# Create aggregated features per user
user_features = logs.groupby('id').agg({
    'time_diff_sec': ['mean', 'min', 'max', 'std'],
    'is_short_interval': 'sum',
    'query_id': 'count'
}).reset_index()

# Flatten column names for easier handling
user_features.columns = [
    'user_id', 'avg_interval', 'min_interval', 
    'max_interval', 'interval_std', 'short_interval_count', 'total_queries'
]

# Add a label (you'll need known bot/human data to train this)
# Replace known_bots with your actual list of confirmed bot IDs
known_bots = []
user_features['is_bot'] = [1 if uid in known_bots else 0 for uid in user_features['user_id']]

# Train-test split
X = user_features.drop(['user_id', 'is_bot'], axis=1)
y = user_features['is_bot']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)

# Train the classifier
clf = RandomForestClassifier(n_estimators=100)
clf.fit(X_train, y_train)

# Predict bot candidates
user_features['predicted_bot'] = clf.predict(X)

Start with the refined interval analysis, layer in time series periodicity checks, and then add multi-feature ML if you need to scale to a large number of users. This should give you far better results than your initial approach!

内容的提问来源于stack exchange，提问作者Allan