FRONTMATTER_27_# Squert Cheat Sheet¶
__HTML_TAG_17_📋 Copia Tutti i comandi_HTML_TAG_18_
__HTML_TAG_19_📄 Generare PDF
Panoramica¶
Squert è un'applicazione web-based progettata per fornire un'interfaccia visiva per la querying e l'analisi di eventi memorizzati in un database Sguil. Sviluppato come strumento complementare all'interfaccia client tradizionale di Sguil, Squert offre agli analisti di sicurezza una piattaforma web-based intuitiva per indagare gli incidenti di sicurezza della rete e l'analisi della correlazione degli eventi. L'applicazione sfrutta le moderne tecnologie web per presentare i dati degli eventi di sicurezza in grafici interattivi, grafici e tabelle, rendendo più facile per gli analisti identificare modelli, tendenze e anomalie nel traffico di rete.
La forza fondamentale di Squert sta nella sua capacità di trasformare i dati degli eventi di sicurezza raw in visualizzazioni significative che facilitano l'analisi rapida delle minacce e la risposta agli incidenti. A differenza dei tradizionali strumenti di sicurezza basati su comandi o desktop, Squert fornisce un'interfaccia web reattiva che può essere accessibile da qualsiasi browser moderno, consentendo ai team di sicurezza distribuiti di collaborare efficacemente sulle attività di indagine incidente e di caccia alle minacce. L'applicazione si integra perfettamente con le implementazioni Sguil esistenti, utilizzando lo stesso backend di database MySQL, fornendo funzionalità di visualizzazione e analisi migliorate.
L'architettura di Squert è costruita intorno alle tecnologie PHP e JavaScript, con l'obiettivo di fornire l'accesso in tempo reale ai dati degli eventi di sicurezza attraverso interfacce web dinamiche. L'applicazione supporta le funzionalità di filtraggio, ricerca e correlazione avanzate che permettono agli analisti di eseguire l'esercitazione in eventi specifici, indagare attività correlate e generare report completi per scopi di gestione e conformità. Con la sua enfasi sull'usabilità e l'analisi visiva, Squert è diventato un componente essenziale di molti centri di sicurezza che cercano di modernizzare i flussi di lavoro di risposta agli incidenti.
Installazione¶
## Ubuntu/Debian Installazione¶
Installazione di Squert sui sistemi Ubuntu/Debian:
# Update system packages
sudo apt update && sudo apt upgrade -y
# Install required dependencies
sudo apt install -y apache2 php php-mysql php-gd php-curl php-json \
php-xml php-mbstring libapache2-mod-php mysql-client git wget curl
# Enable Apache modules
sudo a2enmod rewrite
sudo a2enmod ssl
# Download Squert
cd /var/www
sudo git clone https://github.com/int13h/squert.git
sudo chown -R www-data:www-data squert
# Create Squert configuration directory
sudo mkdir -p /etc/squert
sudo chown www-data:www-data /etc/squert
# Set proper permissions
sudo chmod 755 /var/www/squert
sudo chmod -R 644 /var/www/squert/*
sudo chmod 755 /var/www/squert/scripts
# Create Apache virtual host
sudo cat > /etc/apache2/sites-available/squert.conf << 'EOF'
<VirtualHost *:80>
ServerName squert.local
DocumentRoot /var/www/squert
<Directory /var/www/squert>
Options -Indexes
AllowOverride All
Require all granted
</Directory>
# Security headers
Header always set X-Content-Type-Options nosniff
Header always set X-Frame-Options DENY
Header always set X-XSS-Protection "1; mode=block"
ErrorLog $\\\\{APACHE_LOG_DIR\\\\}/squert_error.log
CustomLog $\\\\{APACHE_LOG_DIR\\\\}/squert_access.log combined
</VirtualHost>
EOF
# Enable site and restart Apache
sudo a2ensite squert.conf
sudo systemctl restart apache2
# Create database user for Squert
mysql -u root -p ``<< 'EOF'
CREATE USER 'squert'@'localhost' IDENTIFIED BY 'squertpassword';
GRANT SELECT ON sguildb.* TO 'squert'@'localhost';
FLUSH PRIVILEGES;
EOF
CentOS/RHEL Installazione¶
# Install EPEL repository
sudo yum install -y epel-release
# Install required packages
sudo yum install -y httpd php php-mysql php-gd php-curl php-json \
php-xml php-mbstring mysql git wget curl
# Start and enable Apache
sudo systemctl start httpd
sudo systemctl enable httpd
# Download Squert
cd /var/www/html
sudo git clone https://github.com/int13h/squert.git
sudo chown -R apache:apache squert
# Create configuration directory
sudo mkdir -p /etc/squert
sudo chown apache:apache /etc/squert
# Configure SELinux (if enabled)
sudo setsebool -P httpd_can_network_connect 1
sudo setsebool -P httpd_can_network_connect_db 1
# Create Apache configuration
sudo cat >`` /etc/httpd/conf.d/squert.conf << 'EOF'
Alias /squert /var/www/html/squert
<Directory "/var/www/html/squert">
Options -Indexes
AllowOverride All
Require all granted
</Directory>
EOF
# Restart Apache
sudo systemctl restart httpd
# Configure firewall
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload
Installazione Docker¶
Squert in esecuzione in contenitori Docker:
# Create Docker network
docker network create squert-network
# Create Squert container
cat > Dockerfile.squert << 'EOF'
FROM php:7.4-apache
# Install required PHP extensions
RUN docker-php-ext-install mysqli pdo pdo_mysql gd
# Install additional packages
RUN apt-get update && apt-get install -y \
git \
&& rm -rf /var/lib/apt/lists/*
# Clone Squert
RUN git clone https://github.com/int13h/squert.git /var/www/html/squert
# Set permissions
RUN chown -R www-data:www-data /var/www/html/squert
RUN chmod -R 755 /var/www/html/squert
# Create configuration directory
RUN mkdir -p /etc/squert && chown www-data:www-data /etc/squert
# Apache configuration
COPY squert.conf /etc/apache2/sites-available/
RUN a2ensite squert && a2enmod rewrite
EXPOSE 80
CMD ["apache2-foreground"]
EOF
# Create Apache configuration for container
cat > squert.conf << 'EOF'
<VirtualHost *:80>
DocumentRoot /var/www/html/squert
<Directory /var/www/html/squert>
Options -Indexes
AllowOverride All
Require all granted
</Directory>
ErrorLog $\\\\{APACHE_LOG_DIR\\\\}/squert_error.log
CustomLog $\\\\{APACHE_LOG_DIR\\\\}/squert_access.log combined
</VirtualHost>
EOF
# Build and run Squert container
docker build -f Dockerfile.squert -t squert .
docker run -d --name squert \
--network squert-network \
-p 8080:80 \
-v squert-config:/etc/squert \
squert
# Connect to existing Sguil MySQL container
docker run -d --name squert-app \
--network squert-network \
-p 8080:80 \
-e MYSQL_HOST=sguil-mysql \
-e MYSQL_USER=squert \
-e MYSQL_PASSWORD=squertpassword \
-e MYSQL_DATABASE=sguildb \
squert
Installazione manuale¶
# Download latest Squert release
cd /tmp
wget https://github.com/int13h/squert/archive/master.tar.gz
tar -xzf master.tar.gz
# Copy to web directory
sudo cp -r squert-master /var/www/squert
sudo chown -R www-data:www-data /var/www/squert
# Set proper permissions
sudo find /var/www/squert -type f -exec chmod 644 \\\\{\\\\} \;
sudo find /var/www/squert -type d -exec chmod 755 \\\\{\\\\} \;
sudo chmod +x /var/www/squert/scripts/*
# Create symbolic link
sudo ln -s /var/www/squert /var/www/html/squert
# Create configuration directory
sudo mkdir -p /etc/squert
sudo chown www-data:www-data /etc/squert
Uso di base¶
Configurazione iniziale¶
Impostazione configurazione Squert:
# Create Squert configuration file
sudo cat > /var/www/squert/.scripts/squert.inc << 'EOF'
<?php
// Squert Configuration
// Database configuration
$dbname = 'sguildb';
$dbhost = 'localhost';
$dbuser = 'squert';
$dbpass = 'squertpassword';
$dbport = 3306;
// Application settings
$squert_title = 'Squert - Network Security Monitoring';
$refresh_time = 300; // 5 minutes
$event_limit = 1000;
// Time zone
date_default_timezone_set('America/New_York');
// Security settings
$use_https = false;
$session_timeout = 3600; // 1 hour
// Chart settings
$chart_height = 400;
$chart_width = 800;
// IP geolocation (optional)
$use_geoip = false;
$geoip_db_path = '/usr/share/GeoIP/GeoLiteCity.dat';
// External links
$whois_provider = 'https://whois.net/ip/';
$reputation_provider = 'https://www.virustotal.com/gui/ip-address/';
// Custom CSS
$custom_css = '';
// Debug mode
$debug = false;
?>
EOF
# Set proper permissions
sudo chown www-data:www-data /var/www/squert/.scripts/squert.inc
sudo chmod 600 /var/www/squert/.scripts/squert.inc
# Create database tables for Squert
mysql -u squert -p sguildb << 'EOF'
-- Create Squert-specific tables
CREATE TABLE IF NOT EXISTS mappings (
id INT AUTO_INCREMENT PRIMARY KEY,
ip_src VARCHAR(15),
ip_dst VARCHAR(15),
hostname VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS filters (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
filter_text TEXT,
user VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS bookmarks (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
url TEXT,
user VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF
Accesso all'interfaccia web¶
Accesso e utilizzo dell'interfaccia web Squert:
# Start Apache if not running
sudo systemctl start apache2
# Check Apache status
sudo systemctl status apache2
# Access Squert web interface
# Open browser and navigate to:
# http://your-server-ip/squert
# or
# http://localhost/squert
# Check Apache logs for any errors
sudo tail -f /var/log/apache2/squert_error.log
sudo tail -f /var/log/apache2/squert_access.log
# Test database connectivity
php -r "
\$conn = new mysqli('localhost', 'squert', 'squertpassword', 'sguildb');
if (\$conn->connect_error) \\\\{
die('Connection failed: ' . \$conn->connect_error);
\\\\}
echo 'Database connection successful\n';
\$conn->close();
"
Basic Navigation¶
Comprensione dei componenti dell'interfaccia Squert:
# Main dashboard components:
# 1. Summary view - Overview of recent events
# 2. Events view - Detailed event listing
# 3. Charts view - Visual analytics
# 4. Search interface - Advanced filtering
# 5. Reports section - Generated reports
# Key interface elements:
# - Time range selector
# - Event filters
# - Source/destination IP analysis
# - Signature analysis
# - Protocol breakdown
# - Geographic mapping (if enabled)
# Common workflows:
# 1. Monitor real-time events
# 2. Investigate specific incidents
# 3. Analyze traffic patterns
# 4. Generate compliance reports
# 5. Correlate related events
Caratteristiche avanzate¶
Custom Dashboards¶
Creazione di dashboard e visualizzazioni personalizzate:
<?php
// Custom dashboard configuration
// File: /var/www/squert/custom_dashboard.php
require_once '.scripts/squert.inc';
class CustomDashboard \\\\{
private $db;
public function __construct() \\\\{
global $dbhost, $dbuser, $dbpass, $dbname;
$this->db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($this->db->connect_error) \\\\{
die("Connection failed: " . $this->db->connect_error);
\\\\}
\\\\}
public function getTopAttackers($limit = 10, $hours = 24) \\\\{
$query = "
SELECT
src_ip,
COUNT(*) as event_count,
COUNT(DISTINCT dst_ip) as target_count,
COUNT(DISTINCT signature_id) as signature_count,
MIN(timestamp) as first_seen,
MAX(timestamp) as last_seen
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
GROUP BY src_ip
ORDER BY event_count DESC
LIMIT ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("ii", $hours, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
public function getTopTargets($limit = 10, $hours = 24) \\\\{
$query = "
SELECT
dst_ip,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as attacker_count,
COUNT(DISTINCT signature_id) as signature_count,
MIN(timestamp) as first_seen,
MAX(timestamp) as last_seen
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
GROUP BY dst_ip
ORDER BY event_count DESC
LIMIT ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("ii", $hours, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
public function getSignatureStats($hours = 24) \\\\{
$query = "
SELECT
signature,
signature_id,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_targets
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
GROUP BY signature, signature_id
ORDER BY event_count DESC
LIMIT 20
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("i", $hours);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
public function getHourlyEventCounts($hours = 24) \\\\{
$query = "
SELECT
DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') as hour,
COUNT(*) as event_count
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00')
ORDER BY hour
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("i", $hours);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
public function generateDashboardHTML() \\\\{
$topAttackers = $this->getTopAttackers();
$topTargets = $this->getTopTargets();
$signatureStats = $this->getSignatureStats();
$hourlyStats = $this->getHourlyEventCounts();
ob_start();
?>
<!DOCTYPE html>
<html>
<head>
<title>Custom Security Dashboard</title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
body \\\\{ font-family: Arial, sans-serif; margin: 20px; \\\\}
.dashboard-grid \\\\{ display: grid; grid-template-columns: 1fr 1fr; gap: 20px; \\\\}
.widget \\\\{ border: 1px solid #ddd; padding: 15px; border-radius: 5px; \\\\}
.widget h3 \\\\{ margin-top: 0; color: #333; \\\\}
table \\\\{ width: 100%; border-collapse: collapse; \\\\}
th, td \\\\{ padding: 8px; text-align: left; border-bottom: 1px solid #ddd; \\\\}
th \\\\{ background-color: #f2f2f2; \\\\}
.chart-container \\\\{ width: 100%; height: 300px; \\\\}
</style>
</head>
<body>
<h1>Security Operations Dashboard</h1>
<p>Last updated: <?php echo date('Y-m-d H:i:s'); ?></p>
<div class="dashboard-grid">
<div class="widget">
<h3>Top Attackers (24h)</h3>
<table>
<tr><th>Source IP</th><th>Events</th><th>Targets</th><th>Signatures</th></tr>
<?php foreach ($topAttackers as $attacker): ?>
<tr>
<td><?php echo htmlspecialchars($attacker['src_ip']); ?></td>
<td><?php echo $attacker['event_count']; ?></td>
<td><?php echo $attacker['target_count']; ?></td>
<td><?php echo $attacker['signature_count']; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="widget">
<h3>Top Targets (24h)</h3>
<table>
<tr><th>Target IP</th><th>Events</th><th>Attackers</th><th>Signatures</th></tr>
<?php foreach ($topTargets as $target): ?>
<tr>
<td><?php echo htmlspecialchars($target['dst_ip']); ?></td>
<td><?php echo $target['event_count']; ?></td>
<td><?php echo $target['attacker_count']; ?></td>
<td><?php echo $target['signature_count']; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="widget">
<h3>Event Timeline (24h)</h3>
<div class="chart-container">
<canvas id="timelineChart"></canvas>
</div>
</div>
<div class="widget">
<h3>Top Signatures (24h)</h3>
<table>
<tr><th>Signature</th><th>Events</th><th>Sources</th></tr>
<?php foreach (array_slice($signatureStats, 0, 10) as $sig): ?>
<tr>
<td><?php echo htmlspecialchars(substr($sig['signature'], 0, 50)) . '...'; ?></td>
<td><?php echo $sig['event_count']; ?></td>
<td><?php echo $sig['unique_sources']; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
</div>
<script>
// Timeline chart
const ctx = document.getElementById('timelineChart').getContext('2d');
const timelineData = <?php echo json_encode($hourlyStats); ?>;
new Chart(ctx, \\\\{
type: 'line',
data: \\\\{
labels: timelineData.map(item => item.hour),
datasets: [\\\\{
label: 'Events per Hour',
data: timelineData.map(item => item.event_count),
borderColor: 'rgb(75, 192, 192)',
tension: 0.1
\\\\}]
\\\\},
options: \\\\{
responsive: true,
maintainAspectRatio: false,
scales: \\\\{
y: \\\\{
beginAtZero: true
\\\\}
\\\\}
\\\\}
\\\\});
</script>
</body>
</html>
<?php
return ob_get_clean();
\\\\}
\\\\}
// Generate and display dashboard
$dashboard = new CustomDashboard();
echo $dashboard->generateDashboardHTML();
?>
Filtro avanzato e ricerca¶
Creazione di funzionalità avanzate di ricerca e filtraggio:
// Advanced search interface
// File: /var/www/squert/js/advanced_search.js
class AdvancedSearch \\\\{
constructor() \\\\{
this.filters = [];
this.initializeInterface();
\\\\}
initializeInterface() \\\\{
this.createSearchForm();
this.bindEvents();
\\\\}
createSearchForm() \\\\{
const searchContainer = document.getElementById('advanced-search');
searchContainer.innerHTML = `
<div class="search-form">
<h3>Advanced Event Search</h3>
<div class="filter-group">
<label>Time Range:</label>
<select id="time-range">
<option value="1h">Last Hour</option>
<option value="6h">Last 6 Hours</option>
<option value="24h">Last 24 Hours</option>
<option value="7d">Last 7 Days</option>
<option value="30d">Last 30 Days</option>
<option value="custom">Custom Range</option>
</select>
</div>
<div class="filter-group" id="custom-time" style="display:none;">
<label>From:</label>
<input type="datetime-local" id="start-time">
<label>To:</label>
<input type="datetime-local" id="end-time">
</div>
<div class="filter-group">
<label>Source IP:</label>
<input type="text" id="src-ip" placeholder="192.168.1.1 or 192.168.1.0/24">
</div>
<div class="filter-group">
<label>Destination IP:</label>
<input type="text" id="dst-ip" placeholder="10.0.0.1 or 10.0.0.0/16">
</div>
<div class="filter-group">
<label>Port Range:</label>
<input type="text" id="port-range" placeholder="80,443,8080-8090">
</div>
<div class="filter-group">
<label>Signature:</label>
<input type="text" id="signature" placeholder="Search signature text">
</div>
<div class="filter-group">
<label>Signature ID:</label>
<input type="text" id="signature-id" placeholder="1,2,3 or 100-200">
</div>
<div class="filter-group">
<label>Protocol:</label>
<select id="protocol">
<option value="">All Protocols</option>
<option value="1">ICMP</option>
<option value="6">TCP</option>
<option value="17">UDP</option>
</select>
</div>
<div class="filter-group">
<label>Event Limit:</label>
<select id="event-limit">
<option value="100">100 events</option>
<option value="500">500 events</option>
<option value="1000">1000 events</option>
<option value="5000">5000 events</option>
</select>
</div>
<div class="filter-actions">
<button id="search-btn" class="btn-primary">Search Events</button>
<button id="save-filter-btn" class="btn-secondary">Save Filter</button>
<button id="clear-btn" class="btn-secondary">Clear All</button>
</div>
<div id="saved-filters">
<h4>Saved Filters</h4>
<div id="filter-list"></div>
</div>
</div>
<div id="search-results">
<div id="results-summary"></div>
<div id="results-table"></div>
</div>
`;
\\\\}
bindEvents() \\\\{
// Time range change
document.getElementById('time-range').addEventListener('change', (e) => \\\\{
const customTime = document.getElementById('custom-time');
customTime.style.display = e.target.value === 'custom' ? 'block' : 'none';
\\\\});
// Search button
document.getElementById('search-btn').addEventListener('click', () => \\\\{
this.performSearch();
\\\\});
// Save filter button
document.getElementById('save-filter-btn').addEventListener('click', () => \\\\{
this.saveCurrentFilter();
\\\\});
// Clear button
document.getElementById('clear-btn').addEventListener('click', () => \\\\{
this.clearAllFilters();
\\\\});
// Load saved filters
this.loadSavedFilters();
\\\\}
buildSearchQuery() \\\\{
const filters = [];
// Time range
const timeRange = document.getElementById('time-range').value;
if (timeRange !== 'custom') \\\\{
filters.push(`timestamp >= DATE_SUB(NOW(), INTERVAL $\\{timeRange.replace(/[hd]/, timeRange.includes('h') ? ' HOUR' : ' DAY')\\})`);
\\\\} else \\\\{
const startTime = document.getElementById('start-time').value;
const endTime = document.getElementById('end-time').value;
if (startTime) filters.push(`timestamp >= '$\\{startTime\\}'`);
if (endTime) filters.push(`timestamp <= '$\\{endTime\\}'`);
\\\\}
// IP addresses
const srcIp = document.getElementById('src-ip').value.trim();
if (srcIp) \\\\{
if (srcIp.includes('/')) \\\\{
// CIDR notation
filters.push(`INET_ATON(src_ip) & INET_ATON('$\\{srcIp.split('/')[1]\\}') = INET_ATON('$\\{srcIp.split('/')[0]\\}')`);
\\\\} else \\\\{
filters.push(`src_ip = '$\\{srcIp\\}'`);
\\\\}
\\\\}
const dstIp = document.getElementById('dst-ip').value.trim();
if (dstIp) \\\\{
if (dstIp.includes('/')) \\\\{
filters.push(`INET_ATON(dst_ip) & INET_ATON('$\\{dstIp.split('/')[1]\\}') = INET_ATON('$\\{dstIp.split('/')[0]\\}')`);
\\\\} else \\\\{
filters.push(`dst_ip = '$\\{dstIp\\}'`);
\\\\}
\\\\}
// Ports
const portRange = document.getElementById('port-range').value.trim();
if (portRange) \\\\{
const portConditions = [];
portRange.split(',').forEach(port => \\\\{
if (port.includes('-')) \\\\{
const [start, end] = port.split('-');
portConditions.push(`(src_port BETWEEN $\\{start\\} AND $\\{end\\}) OR (dst_port BETWEEN $\\{start\\} AND $\\{end\\})`);
\\\\} else \\\\{
portConditions.push(`src_port = $\\{port\\} OR dst_port = $\\{port\\}`);
\\\\}
\\\\});
filters.push(`($\\{portConditions.join(' OR ')\\})`);
\\\\}
// Signature
const signature = document.getElementById('signature').value.trim();
if (signature) \\\\{
filters.push(`signature LIKE '%$\\{signature\\}%'`);
\\\\}
// Signature ID
const signatureId = document.getElementById('signature-id').value.trim();
if (signatureId) \\\\{
if (signatureId.includes('-')) \\\\{
const [start, end] = signatureId.split('-');
filters.push(`signature_id BETWEEN $\\{start\\} AND $\\{end\\}`);
\\\\} else if (signatureId.includes(',')) \\\\{
filters.push(`signature_id IN ($\\{signatureId\\})`);
\\\\} else \\\\{
filters.push(`signature_id = $\\{signatureId\\}`);
\\\\}
\\\\}
// Protocol
const protocol = document.getElementById('protocol').value;
if (protocol) \\\\{
filters.push(`ip_proto = $\\{protocol\\}`);
\\\\}
return filters.join(' AND ');
\\\\}
async performSearch() \\\\{
const whereClause = this.buildSearchQuery();
const limit = document.getElementById('event-limit').value;
const query = `
SELECT
sid, cid, timestamp, src_ip, src_port, dst_ip, dst_port,
ip_proto, signature, signature_id
FROM event
$\\{whereClause ? 'WHERE ' + whereClause : ''\\}
ORDER BY timestamp DESC
LIMIT $\\{limit\\}
`;
try \\\\{
const response = await fetch('search_events.php', \\\\{
method: 'POST',
headers: \\\\{
'Content-Type': 'application/json',
\\\\},
body: JSON.stringify(\\\\{ query: query \\\\})
\\\\});
const results = await response.json();
this.displayResults(results);
\\\\} catch (error) \\\\{
console.error('Search failed:', error);
this.displayError('Search failed: ' + error.message);
\\\\}
\\\\}
displayResults(results) \\\\{
const summaryDiv = document.getElementById('results-summary');
const tableDiv = document.getElementById('results-table');
summaryDiv.innerHTML = `
<h3>Search Results</h3>
<p>Found $\\{results.length\\} events</p>
`;
if (results.length === 0) \\\\{
tableDiv.innerHTML = '<p>No events found matching the search criteria.</p>';
return;
\\\\}
let tableHTML = `
<table class="results-table">
<thead>
<tr>
<th>Timestamp</th>
<th>Source</th>
<th>Destination</th>
<th>Protocol</th>
<th>Signature</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
`;
results.forEach(event => \\\\{
tableHTML += `
<tr>
<td>$\\{event.timestamp\\}</td>
<td>$\\{event.src_ip\\}:$\\{event.src_port\\}</td>
<td>$\\{event.dst_ip\\}:$\\{event.dst_port\\}</td>
<td>$\\{this.getProtocolName(event.ip_proto)\\}</td>
<td title="$\\{event.signature\\}">$\\{event.signature.substring(0, 50)\\}...</td>
<td>
<button onclick="viewEventDetails($\\{event.sid\\}, $\\{event.cid\\})">Details</button>
<button onclick="investigateIP('$\\{event.src_ip\\}')">Investigate</button>
</td>
</tr>
`;
\\\\});
tableHTML += '</tbody></table>';
tableDiv.innerHTML = tableHTML;
\\\\}
getProtocolName(proto) \\\\{
const protocols = \\\\{ 1: 'ICMP', 6: 'TCP', 17: 'UDP' \\\\};
return protocols[proto]||proto;
\\\\}
saveCurrentFilter() \\\\{
const filterName = prompt('Enter a name for this filter:');
if (!filterName) return;
const filterData = \\\\{
name: filterName,
timeRange: document.getElementById('time-range').value,
srcIp: document.getElementById('src-ip').value,
dstIp: document.getElementById('dst-ip').value,
portRange: document.getElementById('port-range').value,
signature: document.getElementById('signature').value,
signatureId: document.getElementById('signature-id').value,
protocol: document.getElementById('protocol').value,
limit: document.getElementById('event-limit').value
\\\\};
// Save to localStorage
const savedFilters = JSON.parse(localStorage.getItem('squert_filters')||'[]');
savedFilters.push(filterData);
localStorage.setItem('squert_filters', JSON.stringify(savedFilters));
this.loadSavedFilters();
\\\\}
loadSavedFilters() \\\\{
const savedFilters = JSON.parse(localStorage.getItem('squert_filters')||'[]');
const filterList = document.getElementById('filter-list');
filterList.innerHTML = '';
savedFilters.forEach((filter, index) => \\\\{
const filterDiv = document.createElement('div');
filterDiv.className = 'saved-filter';
filterDiv.innerHTML = `
<span>$\\{filter.name\\}</span>
<button onclick="loadFilter($\\{index\\})">Load</button>
<button onclick="deleteFilter($\\{index\\})">Delete</button>
`;
filterList.appendChild(filterDiv);
\\\\});
\\\\}
clearAllFilters() \\\\{
document.getElementById('time-range').value = '24h';
document.getElementById('src-ip').value = '';
document.getElementById('dst-ip').value = '';
document.getElementById('port-range').value = '';
document.getElementById('signature').value = '';
document.getElementById('signature-id').value = '';
document.getElementById('protocol').value = '';
document.getElementById('event-limit').value = '1000';
document.getElementById('custom-time').style.display = 'none';
\\\\}
\\\\}
// Initialize advanced search when page loads
document.addEventListener('DOMContentLoaded', () => \\\\{
new AdvancedSearch();
\\\\});
// Global functions for event handling
function viewEventDetails(sid, cid) \\\\{
window.open(`event_details.php?sid=$\\{sid\\}&cid=$\\{cid\\}`, '_blank');
\\\\}
function investigateIP(ip) \\\\{
window.open(`ip_investigation.php?ip=$\\{ip\\}`, '_blank');
\\\\}
function loadFilter(index) \\\\{
const savedFilters = JSON.parse(localStorage.getItem('squert_filters')||'[]');
const filter = savedFilters[index];
if (filter) \\\\{
document.getElementById('time-range').value = filter.timeRange;
document.getElementById('src-ip').value = filter.srcIp;
document.getElementById('dst-ip').value = filter.dstIp;
document.getElementById('port-range').value = filter.portRange;
document.getElementById('signature').value = filter.signature;
document.getElementById('signature-id').value = filter.signatureId;
document.getElementById('protocol').value = filter.protocol;
document.getElementById('event-limit').value = filter.limit;
\\\\}
\\\\}
function deleteFilter(index) \\\\{
if (confirm('Are you sure you want to delete this filter?')) \\\\{
const savedFilters = JSON.parse(localStorage.getItem('squert_filters')||'[]');
savedFilters.splice(index, 1);
localStorage.setItem('squert_filters', JSON.stringify(savedFilters));
// Reload the filter list
const search = new AdvancedSearch();
search.loadSavedFilters();
\\\\}
\\\\}
Reporting automatizzato¶
Creazione di funzionalità di reporting automatizzate:
<?php
// Automated reporting system
// File: /var/www/squert/reports/automated_reports.php
require_once '../.scripts/squert.inc';
class AutomatedReports \\\\{
private $db;
private $config;
public function __construct() \\\\{
global $dbhost, $dbuser, $dbpass, $dbname;
$this->db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($this->db->connect_error) \\\\{
die("Connection failed: " . $this->db->connect_error);
\\\\}
$this->config = [
'report_dir' => '/var/www/squert/reports/generated',
'email_reports' => true,
'email_to' => 'security@company.com',
'email_from' => 'squert@company.com'
];
// Create reports directory if it doesn't exist
if (!is_dir($this->config['report_dir'])) \\\\{
mkdir($this->config['report_dir'], 0755, true);
\\\\}
\\\\}
public function generateDailyReport($date = null) \\\\{
if (!$date) \\\\{
$date = date('Y-m-d', strtotime('-1 day'));
\\\\}
$report_data = [
'date' => $date,
'summary' => $this->getDailySummary($date),
'top_attackers' => $this->getTopAttackers($date),
'top_targets' => $this->getTopTargets($date),
'top_signatures' => $this->getTopSignatures($date),
'hourly_stats' => $this->getHourlyStats($date),
'protocol_breakdown' => $this->getProtocolBreakdown($date),
'geographic_analysis' => $this->getGeographicAnalysis($date)
];
$html_report = $this->generateHTMLReport($report_data, 'daily');
$pdf_report = $this->generatePDFReport($report_data, 'daily');
// Save reports
$html_file = $this->config['report_dir'] . "/daily_report_\\\\{$date\\\\}.html";
$pdf_file = $this->config['report_dir'] . "/daily_report_\\\\{$date\\\\}.pdf";
file_put_contents($html_file, $html_report);
file_put_contents($pdf_file, $pdf_report);
// Email report if configured
if ($this->config['email_reports']) \\\\{
$this->emailReport($html_file, $pdf_file, "Daily Security Report - \\\\{$date\\\\}");
\\\\}
return [
'html_file' => $html_file,
'pdf_file' => $pdf_file,
'data' => $report_data
];
\\\\}
public function generateWeeklyReport($week_start = null) \\\\{
if (!$week_start) \\\\{
$week_start = date('Y-m-d', strtotime('last monday'));
\\\\}
$week_end = date('Y-m-d', strtotime($week_start . ' +6 days'));
$report_data = [
'week_start' => $week_start,
'week_end' => $week_end,
'summary' => $this->getWeeklySummary($week_start, $week_end),
'trends' => $this->getWeeklyTrends($week_start, $week_end),
'top_incidents' => $this->getTopIncidents($week_start, $week_end),
'security_metrics' => $this->getSecurityMetrics($week_start, $week_end)
];
$html_report = $this->generateHTMLReport($report_data, 'weekly');
$html_file = $this->config['report_dir'] . "/weekly_report_\\\\{$week_start\\\\}_to_\\\\{$week_end\\\\}.html";
file_put_contents($html_file, $html_report);
return ['html_file' => $html_file, 'data' => $report_data];
\\\\}
private function getDailySummary($date) \\\\{
$query = "
SELECT
COUNT(*) as total_events,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_targets,
COUNT(DISTINCT signature_id) as unique_signatures,
MIN(timestamp) as first_event,
MAX(timestamp) as last_event
FROM event
WHERE DATE(timestamp) = ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("s", $date);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
\\\\}
private function getTopAttackers($date, $limit = 10) \\\\{
$query = "
SELECT
src_ip,
COUNT(*) as event_count,
COUNT(DISTINCT dst_ip) as target_count,
COUNT(DISTINCT signature_id) as signature_count,
GROUP_CONCAT(DISTINCT signature ORDER BY signature LIMIT 3) as top_signatures
FROM event
WHERE DATE(timestamp) = ?
GROUP BY src_ip
ORDER BY event_count DESC
LIMIT ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("si", $date, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
private function getTopTargets($date, $limit = 10) \\\\{
$query = "
SELECT
dst_ip,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as attacker_count,
COUNT(DISTINCT signature_id) as signature_count
FROM event
WHERE DATE(timestamp) = ?
GROUP BY dst_ip
ORDER BY event_count DESC
LIMIT ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("si", $date, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
private function getTopSignatures($date, $limit = 15) \\\\{
$query = "
SELECT
signature,
signature_id,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_targets
FROM event
WHERE DATE(timestamp) = ?
GROUP BY signature, signature_id
ORDER BY event_count DESC
LIMIT ?
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("si", $date, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
private function getHourlyStats($date) \\\\{
$query = "
SELECT
HOUR(timestamp) as hour,
COUNT(*) as event_count
FROM event
WHERE DATE(timestamp) = ?
GROUP BY HOUR(timestamp)
ORDER BY hour
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("s", $date);
$stmt->execute();
$results = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
// Fill in missing hours with 0 events
$hourly_stats = array_fill(0, 24, 0);
foreach ($results as $row) \\\\{
$hourly_stats[$row['hour']] = $row['event_count'];
\\\\}
return $hourly_stats;
\\\\}
private function getProtocolBreakdown($date) \\\\{
$query = "
SELECT
ip_proto,
COUNT(*) as event_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM event WHERE DATE(timestamp) = ?), 2) as percentage
FROM event
WHERE DATE(timestamp) = ?
GROUP BY ip_proto
ORDER BY event_count DESC
";
$stmt = $this->db->prepare($query);
$stmt->bind_param("ss", $date, $date);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
\\\\}
private function generateHTMLReport($data, $type) \\\\{
ob_start();
?>
<!DOCTYPE html>
<html>
<head>
<title><?php echo ucfirst($type); ?> Security Report</title>
<style>
body \\\\{ font-family: Arial, sans-serif; margin: 20px; \\\\}
.header \\\\{ background: #f4f4f4; padding: 20px; border-radius: 5px; \\\\}
.summary \\\\{ display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin: 20px 0; \\\\}
.metric \\\\{ background: #e9e9e9; padding: 15px; border-radius: 5px; text-align: center; \\\\}
.metric h3 \\\\{ margin: 0; color: #333; \\\\}
.metric .value \\\\{ font-size: 24px; font-weight: bold; color: #007cba; \\\\}
table \\\\{ width: 100%; border-collapse: collapse; margin: 20px 0; \\\\}
th, td \\\\{ padding: 10px; text-align: left; border-bottom: 1px solid #ddd; \\\\}
th \\\\{ background-color: #f2f2f2; \\\\}
.chart \\\\{ margin: 20px 0; \\\\}
.section \\\\{ margin: 30px 0; \\\\}
.section h2 \\\\{ color: #333; border-bottom: 2px solid #007cba; padding-bottom: 5px; \\\\}
</style>
</head>
<body>
<div class="header">
<h1><?php echo ucfirst($type); ?> Security Report</h1>
<?php if ($type == 'daily'): ?>
<p>Report Date: <?php echo $data['date']; ?></p>
<?php else: ?>
<p>Report Period: <?php echo $data['week_start']; ?> to <?php echo $data['week_end']; ?></p>
<?php endif; ?>
<p>Generated: <?php echo date('Y-m-d H:i:s'); ?></p>
</div>
<?php if ($type == 'daily'): ?>
<div class="summary">
<div class="metric">
<h3>Total Events</h3>
<div class="value"><?php echo number_format($data['summary']['total_events']); ?></div>
</div>
<div class="metric">
<h3>Unique Sources</h3>
<div class="value"><?php echo number_format($data['summary']['unique_sources']); ?></div>
</div>
<div class="metric">
<h3>Unique Targets</h3>
<div class="value"><?php echo number_format($data['summary']['unique_targets']); ?></div>
</div>
<div class="metric">
<h3>Unique Signatures</h3>
<div class="value"><?php echo number_format($data['summary']['unique_signatures']); ?></div>
</div>
</div>
<div class="section">
<h2>Top Attackers</h2>
<table>
<tr><th>Source IP</th><th>Events</th><th>Targets</th><th>Signatures</th></tr>
<?php foreach ($data['top_attackers'] as $attacker): ?>
<tr>
<td><?php echo htmlspecialchars($attacker['src_ip']); ?></td>
<td><?php echo number_format($attacker['event_count']); ?></td>
<td><?php echo number_format($attacker['target_count']); ?></td>
<td><?php echo number_format($attacker['signature_count']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="section">
<h2>Top Targets</h2>
<table>
<tr><th>Target IP</th><th>Events</th><th>Attackers</th><th>Signatures</th></tr>
<?php foreach ($data['top_targets'] as $target): ?>
<tr>
<td><?php echo htmlspecialchars($target['dst_ip']); ?></td>
<td><?php echo number_format($target['event_count']); ?></td>
<td><?php echo number_format($target['attacker_count']); ?></td>
<td><?php echo number_format($target['signature_count']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="section">
<h2>Top Signatures</h2>
<table>
<tr><th>Signature</th><th>Events</th><th>Sources</th><th>Targets</th></tr>
<?php foreach ($data['top_signatures'] as $sig): ?>
<tr>
<td><?php echo htmlspecialchars($sig['signature']); ?></td>
<td><?php echo number_format($sig['event_count']); ?></td>
<td><?php echo number_format($sig['unique_sources']); ?></td>
<td><?php echo number_format($sig['unique_targets']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<?php endif; ?>
</body>
</html>
<?php
return ob_get_clean();
\\\\}
private function emailReport($html_file, $pdf_file, $subject) \\\\{
// Implementation depends on your email system
// This is a basic example using PHP mail()
$to = $this->config['email_to'];
$from = $this->config['email_from'];
$headers = "From: \\\\{$from\\\\}\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=UTF-8\r\n";
$body = file_get_contents($html_file);
mail($to, $subject, $body, $headers);
\\\\}
\\\\}
// Usage example
if (php_sapi_name() === 'cli') \\\\{
// Command line usage
$reports = new AutomatedReports();
$action = $argv[1] ?? 'daily';
$date = $argv[2] ?? null;
switch ($action) \\\\{
case 'daily':
$result = $reports->generateDailyReport($date);
echo "Daily report generated: \\\\{$result['html_file']\\\\}\n";
break;
case 'weekly':
$result = $reports->generateWeeklyReport($date);
echo "Weekly report generated: \\\\{$result['html_file']\\\\}\n";
break;
default:
echo "Usage: php automated_reports.php [daily|weekly] [date]\n";
\\\\}
\\\\}
?>
Automation Scripts¶
Script di monitoraggio completo¶
#!/bin/bash
# Comprehensive Squert monitoring and maintenance
# Configuration
SQUERT_DIR="/var/www/squert"
LOG_DIR="/var/log/squert"
BACKUP_DIR="/var/backups/squert"
CONFIG_FILE="/var/www/squert/.scripts/squert.inc"
APACHE_LOG="/var/log/apache2/squert_error.log"
# Database configuration
DB_HOST="localhost"
DB_USER="squert"
DB_PASS="squertpassword"
DB_NAME="sguildb"
# Monitoring thresholds
MAX_LOG_SIZE="100M"
MAX_DISK_USAGE="90"
MAX_RESPONSE_TIME="5"
# Create necessary directories
mkdir -p "$LOG_DIR" "$BACKUP_DIR"
# Logging function
log_message() \\\\{
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1"|tee -a "$LOG_DIR/monitor.log"
\\\\}
# Check Squert web interface availability
check_web_interface() \\\\{
log_message "Checking Squert web interface..."
local response_time
response_time=$(curl -o /dev/null -s -w '%\\\\{time_total\\\\}' http://localhost/squert/ 2>/dev/null)
if [ $? -eq 0 ]; then
log_message "Web interface is accessible (response time: $\\\\{response_time\\\\}s)"
# Check if response time is acceptable
if (( $(echo "$response_time > $MAX_RESPONSE_TIME"|bc -l) )); then
log_message "WARNING: Slow response time: $\\\\{response_time\\\\}s (threshold: $\\\\{MAX_RESPONSE_TIME\\\\}s)"
return 1
fi
return 0
else
log_message "ERROR: Web interface is not accessible"
return 1
fi
\\\\}
# Check database connectivity
check_database() \\\\{
log_message "Checking database connectivity..."
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SELECT 1;" >/dev/null 2>&1
if [ $? -eq 0 ]; then
log_message "Database connection successful"
# Check recent events
local recent_events
recent_events=$(mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -N -e "
SELECT COUNT(*) FROM event WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR);
" 2>/dev/null)
log_message "Recent events (last hour): $recent_events"
return 0
else
log_message "ERROR: Database connection failed"
return 1
fi
\\\\}
# Check Apache service
check_apache() \\\\{
log_message "Checking Apache service..."
if systemctl is-active --quiet apache2; then
log_message "Apache service is running"
# Check for recent errors
local error_count
error_count=$(tail -n 100 "$APACHE_LOG" 2>/dev/null|grep -c "$(date '+%Y-%m-%d')"||echo "0")
if [ "$error_count" -gt 10 ]; then
log_message "WARNING: High number of Apache errors today: $error_count"
fi
return 0
else
log_message "ERROR: Apache service is not running"
return 1
fi
\\\\}
# Check disk space
check_disk_space() \\\\{
log_message "Checking disk space..."
local usage
usage=$(df -h "$SQUERT_DIR"|awk 'NR==2 \\\\{print $5\\\\}'|sed 's/%//')
log_message "Disk usage: $\\\\{usage\\\\}%"
if [ "$usage" -gt "$MAX_DISK_USAGE" ]; then
log_message "WARNING: High disk usage: $\\\\{usage\\\\}% (threshold: $\\\\{MAX_DISK_USAGE\\\\}%)"
return 1
fi
return 0
\\\\}
# Check log file sizes
check_log_sizes() \\\\{
log_message "Checking log file sizes..."
find "$LOG_DIR" -name "*.log" -size +"$MAX_LOG_SIZE"|while read -r logfile; do
log_message "WARNING: Large log file: $logfile"
# Rotate large log files
if [ -f "$logfile" ]; then
mv "$logfile" "$\\\\{logfile\\\\}.$(date +%Y%m%d-%H%M%S)"
touch "$logfile"
chown www-data:www-data "$logfile"
log_message "Rotated log file: $logfile"
fi
done
\\\\}
# Performance optimization
optimize_performance() \\\\{
log_message "Running performance optimization..."
# Clear PHP opcache if available
if command -v php >/dev/null 2>&1; then
php -r "if (function_exists('opcache_reset')) \\\\{ opcache_reset(); echo 'OPcache cleared\n'; \\\\}"
fi
# Optimize database tables
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
OPTIMIZE TABLE event;
ANALYZE TABLE event;
" >/dev/null 2>&1
log_message "Performance optimization completed"
\\\\}
# Backup configuration
backup_configuration() \\\\{
log_message "Backing up Squert configuration..."
local backup_file="$BACKUP_DIR/squert-config-$(date +%Y%m%d-%H%M%S).tar.gz"
tar -czf "$backup_file" \
"$CONFIG_FILE" \
"$SQUERT_DIR/.scripts/" \
"/etc/apache2/sites-available/squert.conf" \
2>/dev/null
if [ $? -eq 0 ]; then
log_message "Configuration backup created: $backup_file"
# Keep only last 7 days of backups
find "$BACKUP_DIR" -name "squert-config-*.tar.gz" -mtime +7 -delete
return 0
else
log_message "ERROR: Configuration backup failed"
return 1
fi
\\\\}
# Generate health report
generate_health_report() \\\\{
log_message "Generating health report..."
local report_file="$LOG_DIR/health-report-$(date +%Y%m%d-%H%M%S).html"
cat > "$report_file" << EOF
<!DOCTYPE html>
<html>
<head>
<title>Squert Health Report</title>
<style>
body \\\\{ font-family: Arial, sans-serif; margin: 20px; \\\\}
.status-ok \\\\{ color: green; \\\\}
.status-warning \\\\{ color: orange; \\\\}
.status-error \\\\{ color: red; \\\\}
table \\\\{ border-collapse: collapse; width: 100%; \\\\}
th, td \\\\{ border: 1px solid #ddd; padding: 8px; text-align: left; \\\\}
th \\\\{ background-color: #f2f2f2; \\\\}
</style>
</head>
<body>
<h1>Squert Health Report</h1>
<p>Generated: $(date)</p>
<h2>System Status</h2>
<table>
<tr><th>Component</th><th>Status</th><th>Details</th></tr>
EOF
# Check each component and add to report
if check_web_interface >/dev/null 2>&1; then
echo " <tr><td>Web Interface</td><td class=\"status-ok\">OK</td><td>Accessible</td></tr>" >> "$report_file"
else
echo " <tr><td>Web Interface</td><td class=\"status-error\">ERROR</td><td>Not accessible</td></tr>" >> "$report_file"
fi
if check_database >/dev/null 2>&1; then
echo " <tr><td>Database</td><td class=\"status-ok\">OK</td><td>Connected</td></tr>" >> "$report_file"
else
echo " <tr><td>Database</td><td class=\"status-error\">ERROR</td><td>Connection failed</td></tr>" >> "$report_file"
fi
if check_apache >/dev/null 2>&1; then
echo " <tr><td>Apache</td><td class=\"status-ok\">OK</td><td>Running</td></tr>" >> "$report_file"
else
echo " <tr><td>Apache</td><td class=\"status-error\">ERROR</td><td>Not running</td></tr>" >> "$report_file"
fi
local disk_usage
disk_usage=$(df -h "$SQUERT_DIR"|awk 'NR==2 \\\\{print $5\\\\}')
echo " <tr><td>Disk Space</td><td class=\"status-ok\">OK</td><td>Usage: $disk_usage</td></tr>" >> "$report_file"
cat >> "$report_file" << EOF
</table>
<h2>Recent Activity</h2>
<pre>$(tail -n 20 "$LOG_DIR/monitor.log" 2>/dev/null||echo "No recent activity logged")</pre>
</body>
</html>
EOF
log_message "Health report generated: $report_file"
\\\\}
# Send alert notification
send_alert() \\\\{
local subject="$1"
local message="$2"
# Send email if mail is configured
if command -v mail >/dev/null 2>&1; then
echo "$message"|mail -s "Squert Alert: $subject" security@company.com
fi
# Log to syslog
logger -t squert-monitor "$subject: $message"
log_message "Alert sent: $subject"
\\\\}
# Main monitoring function
run_monitoring() \\\\{
log_message "Starting Squert monitoring cycle"
local issues=0
# Run all checks
check_web_interface||((issues++))
check_database||((issues++))
check_apache||((issues++))
check_disk_space||((issues++))
check_log_sizes
# Performance optimization (weekly)
if [ "$(date +%u)" -eq 1 ] && [ "$(date +%H)" -eq 2 ]; then
optimize_performance
backup_configuration
fi
# Generate health report (daily)
if [ "$(date +%H)" -eq 6 ]; then
generate_health_report
fi
# Send alerts if issues found
if [ "$issues" -gt 0 ]; then
send_alert "System Issues Detected" "Found $issues issues during monitoring. Check logs for details."
fi
log_message "Monitoring cycle completed with $issues issues"
return $issues
\\\\}
# Maintenance functions
cleanup_old_files() \\\\{
log_message "Cleaning up old files..."
# Remove old log files (older than 30 days)
find "$LOG_DIR" -name "*.log.*" -mtime +30 -delete
# Remove old health reports (older than 7 days)
find "$LOG_DIR" -name "health-report-*.html" -mtime +7 -delete
# Remove old backups (older than 30 days)
find "$BACKUP_DIR" -name "*.tar.gz" -mtime +30 -delete
log_message "Cleanup completed"
\\\\}
# Update Squert
update_squert() \\\\{
log_message "Checking for Squert updates..."
cd "$SQUERT_DIR"||exit 1
# Backup current version
backup_configuration
# Check for updates
git fetch origin
local current_commit
local latest_commit
current_commit=$(git rev-parse HEAD)
latest_commit=$(git rev-parse origin/master)
if [ "$current_commit" != "$latest_commit" ]; then
log_message "Updates available, updating Squert..."
# Pull updates
git pull origin master
# Set proper permissions
chown -R www-data:www-data "$SQUERT_DIR"
find "$SQUERT_DIR" -type f -exec chmod 644 \\\\{\\\\} \;
find "$SQUERT_DIR" -type d -exec chmod 755 \\\\{\\\\} \;
# Restart Apache
systemctl restart apache2
log_message "Squert updated successfully"
else
log_message "Squert is up to date"
fi
\\\\}
# Command line interface
case "$\\\\{1:-monitor\\\\}" in
"monitor")
run_monitoring
;;
"cleanup")
cleanup_old_files
;;
"update")
update_squert
;;
"backup")
backup_configuration
;;
"report")
generate_health_report
;;
"optimize")
optimize_performance
;;
*)
echo "Usage: $0 \\\\{monitor|cleanup|update|backup|report|optimize\\\\}"
echo ""
echo "Commands:"
echo " monitor - Run complete monitoring cycle (default)"
echo " cleanup - Clean up old files and logs"
echo " update - Check for and apply Squert updates"
echo " backup - Backup Squert configuration"
echo " report - Generate health report"
echo " optimize - Run performance optimization"
exit 1
;;
esac
Esempi di integrazione¶
SIEM Integrazione¶
#!/usr/bin/env python3
# Squert SIEM integration script
import mysql.connector
import json
import requests
import time
from datetime import datetime, timedelta
class SquertSIEMIntegration:
def __init__(self, config):
self.config = config
self.db_config = config['database']
self.siem_config = config['siem']
def connect_database(self):
"""Connect to Squert/Sguil database"""
return mysql.connector.connect(**self.db_config)
def get_recent_events(self, minutes=5):
"""Get events from the last N minutes"""
connection = self.connect_database()
cursor = connection.cursor(dictionary=True)
query = """
SELECT
sid, cid, timestamp, src_ip, src_port, dst_ip, dst_port,
ip_proto, signature, signature_gen, signature_id, signature_rev
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL %s MINUTE)
ORDER BY timestamp DESC
"""
cursor.execute(query, (minutes,))
events = cursor.fetchall()
cursor.close()
connection.close()
return events
def transform_for_splunk(self, events):
"""Transform events for Splunk HEC"""
splunk_events = []
for event in events:
splunk_event = \\\\{
"time": int(event['timestamp'].timestamp()),
"source": "squert",
"sourcetype": "sguil:event",
"index": self.siem_config.get('splunk_index', 'security'),
"event": \\\\{
"sid": event['sid'],
"cid": event['cid'],
"timestamp": event['timestamp'].isoformat(),
"src_ip": event['src_ip'],
"src_port": event['src_port'],
"dst_ip": event['dst_ip'],
"dst_port": event['dst_port'],
"protocol": event['ip_proto'],
"signature": event['signature'],
"signature_id": event['signature_id'],
"signature_gen": event['signature_gen'],
"signature_rev": event['signature_rev']
\\\\}
\\\\}
splunk_events.append(splunk_event)
return splunk_events
def send_to_splunk(self, events):
"""Send events to Splunk via HEC"""
if not events:
return True
splunk_events = self.transform_for_splunk(events)
headers = \\\\{
'Authorization': f"Splunk \\\\{self.siem_config['splunk_token']\\\\}",
'Content-Type': 'application/json'
\\\\}
for event in splunk_events:
try:
response = requests.post(
self.siem_config['splunk_hec_url'],
headers=headers,
json=event,
verify=False,
timeout=10
)
if response.status_code != 200:
print(f"Failed to send event to Splunk: \\\\{response.status_code\\\\}")
return False
except Exception as e:
print(f"Error sending to Splunk: \\\\{e\\\\}")
return False
print(f"Sent \\\\{len(splunk_events)\\\\} events to Splunk")
return True
def transform_for_elasticsearch(self, events):
"""Transform events for Elasticsearch"""
es_events = []
for event in events:
es_event = \\\\{
"@timestamp": event['timestamp'].isoformat(),
"source": \\\\{
"ip": event['src_ip'],
"port": event['src_port']
\\\\},
"destination": \\\\{
"ip": event['dst_ip'],
"port": event['dst_port']
\\\\},
"network": \\\\{
"protocol": self.get_protocol_name(event['ip_proto'])
\\\\},
"event": \\\\{
"id": f"\\\\{event['sid']\\\\}-\\\\{event['cid']\\\\}",
"category": "network",
"type": "alert",
"severity": self.get_severity(event['signature_id'])
\\\\},
"rule": \\\\{
"id": event['signature_id'],
"name": event['signature'],
"version": event['signature_rev']
\\\\},
"sguil": \\\\{
"sid": event['sid'],
"cid": event['cid'],
"signature_gen": event['signature_gen']
\\\\}
\\\\}
es_events.append(es_event)
return es_events
def send_to_elasticsearch(self, events):
"""Send events to Elasticsearch"""
if not events:
return True
es_events = self.transform_for_elasticsearch(events)
for event in es_events:
try:
index_name = f"squert-\\\\{datetime.now().strftime('%Y.%m.%d')\\\\}"
doc_id = event['event']['id']
response = requests.post(
f"\\\\{self.siem_config['elasticsearch_url']\\\\}/\\\\{index_name\\\\}/_doc/\\\\{doc_id\\\\}",
json=event,
timeout=10
)
if response.status_code not in [200, 201]:
print(f"Failed to send event to Elasticsearch: \\\\{response.status_code\\\\}")
return False
except Exception as e:
print(f"Error sending to Elasticsearch: \\\\{e\\\\}")
return False
print(f"Sent \\\\{len(es_events)\\\\} events to Elasticsearch")
return True
def get_protocol_name(self, proto_num):
"""Convert protocol number to name"""
protocols = \\\\{1: 'icmp', 6: 'tcp', 17: 'udp'\\\\}
return protocols.get(proto_num, str(proto_num))
def get_severity(self, signature_id):
"""Determine event severity based on signature ID"""
if signature_id in [1, 2, 3]:
return 'high'
elif signature_id in [4, 5, 6]:
return 'medium'
else:
return 'low'
def run_integration(self):
"""Run the SIEM integration"""
print(f"Starting SIEM integration at \\\\{datetime.now()\\\\}")
# Get recent events
events = self.get_recent_events(5) # Last 5 minutes
if not events:
print("No new events to process")
return
print(f"Processing \\\\{len(events)\\\\} events")
# Send to configured SIEM systems
if self.siem_config.get('splunk_enabled', False):
self.send_to_splunk(events)
if self.siem_config.get('elasticsearch_enabled', False):
self.send_to_elasticsearch(events)
print("SIEM integration completed")
# Configuration
config = \\\\{
'database': \\\\{
'host': 'localhost',
'user': 'squert',
'password': 'squertpassword',
'database': 'sguildb'
\\\\},
'siem': \\\\{
'splunk_enabled': True,
'splunk_hec_url': 'https://splunk.company.com:8088/services/collector/event',
'splunk_token': 'your-hec-token',
'splunk_index': 'security',
'elasticsearch_enabled': True,
'elasticsearch_url': 'http://elasticsearch.company.com:9200'
\\\\}
\\\\}
# Run integration
if __name__ == "__main__":
integration = SquertSIEMIntegration(config)
integration.run_integration()
Risoluzione dei problemi¶
Questioni comuni¶
Interfaccia Web Non Caricamento:
¶
Database Problemi di connessione: Traduzione:
** Problemi di conformità: ** Traduzione:
Ottimizzazione delle prestazioni¶
Ottimizzazione delle prestazioni Squert:
# Apache optimization
cat >> /etc/apache2/conf-available/squert-performance.conf << 'EOF'
# Squert performance optimizations
# Enable compression
LoadModule deflate_module modules/mod_deflate.so
<Location "/squert">
SetOutputFilter DEFLATE
SetEnvIfNoCase Request_URI \
\.(?:gif|jpe?g|png)$ no-gzip dont-vary
SetEnvIfNoCase Request_URI \
\.(?:exe|t?gz|zip|bz2|sit|rar)$ no-gzip dont-vary
</Location>
# Enable caching
LoadModule expires_module modules/mod_expires.so
<Location "/squert">
ExpiresActive On
ExpiresByType text/css "access plus 1 month"
ExpiresByType application/javascript "access plus 1 month"
ExpiresByType image/png "access plus 1 month"
ExpiresByType image/jpg "access plus 1 month"
ExpiresByType image/jpeg "access plus 1 month"
ExpiresByType image/gif "access plus 1 month"
</Location>
EOF
sudo a2enconf squert-performance
sudo systemctl restart apache2
# PHP optimization
cat >> /etc/php/7.4/apache2/conf.d/99-squert.ini << 'EOF'
; Squert PHP optimizations
memory_limit = 256M
max_execution_time = 60
max_input_vars = 3000
; OPcache settings
opcache.enable = 1
opcache.memory_consumption = 128
opcache.interned_strings_buffer = 8
opcache.max_accelerated_files = 4000
opcache.revalidate_freq = 2
opcache.fast_shutdown = 1
EOF
sudo systemctl restart apache2
# Database optimization
mysql -u root -p << 'EOF'
-- Optimize Sguil database for Squert
USE sguildb;
-- Add indexes for common Squert queries
CREATE INDEX idx_event_timestamp_src ON event (timestamp, src_ip);
CREATE INDEX idx_event_timestamp_dst ON event (timestamp, dst_ip);
CREATE INDEX idx_event_signature_timestamp ON event (signature_id, timestamp);
CREATE INDEX idx_event_src_dst ON event (src_ip, dst_ip);
-- Optimize table
OPTIMIZE TABLE event;
ANALYZE TABLE event;
EOF
Considerazioni di sicurezza¶
Access Control¶
Web Interface Security: - Implement HTTPS per tutti gli accessi Squert - Utilizzare meccanismi di autenticazione forti - Esercitazione e gestione della sessione - Regolari aggiornamenti di sicurezza per tutti i componenti - Monitorare i registri di accesso per attività sospette
Database Security¶
- Utilizzare un utente database dedicato con privilegi minimi
- Implementare la crittografia della connessione del database
- Regolari aggiornamenti di sicurezza del database
- Monitorare i log di accesso al database
- Implementare la crittografia di backup
Protezione dei dati¶
Event Data Security: - Crittografare i dati sensibili degli eventi a riposo - Attuazione delle politiche di conservazione dei dati - Accesso sicuro ai dati di cattura dei pacchetti - Pulizia regolare dei file temporanei - Implement access logging per i dati degli eventi
** Sicurezza operativa: ** - Valutazioni di sicurezza regolari dell'infrastruttura Squert - Monitor per tentativi di accesso non autorizzati - Implementare procedure di backup e ripristino corrette - Aggiornamenti regolari di Squert e dipendenze - Procedure di risposta per il compromesso Squert
Referenze¶
- [Squert GitHub Repository]
- Documentazione ufficiale della gilda
- Apache HTTP Server Documentazione_
- PHP Security Best Practices_
- [MySQL Performance Tuning](URL_26___