Squert
Squert Cheat Sheet
__HTML_TAG_17_ Todos los comandos
Overview¶
Squert es una aplicación web diseñada para proporcionar una interfaz visual para los eventos de consulta y análisis almacenados en una base de datos Sguil. Desarrollado como una herramienta complementaria de la interfaz de cliente tradicional de Sguil, Squert ofrece a los analistas de seguridad una plataforma intuitiva basada en la web para investigar incidentes de seguridad de la red y realizar análisis de correlación de eventos. La aplicación aprovecha las tecnologías web modernas para presentar datos de eventos de seguridad en gráficos interactivos, gráficos y tablas, facilitando a los analistas identificar patrones, tendencias y anomalías en el tráfico de red.
La fuerza básica de Squert radica en su capacidad de transformar los datos de eventos de seguridad cruda en visualizaciones significativas que faciliten el análisis rápido de amenazas y la respuesta a incidentes. A diferencia de las herramientas tradicionales de seguridad basadas en comandos o escritorio, Squert proporciona una interfaz web receptiva que se puede acceder desde cualquier navegador moderno, permitiendo a los equipos de seguridad distribuidos colaborar eficazmente en actividades de investigación de incidentes y caza de amenazas. La aplicación se integra perfectamente con las implementaciones Sguil existentes, utilizando el mismo backend de la base de datos MySQL al tiempo que proporciona una mayor capacidad de visualización y análisis.
La arquitectura de Squert se construye alrededor de las tecnologías PHP y JavaScript, con el objetivo de proporcionar acceso en tiempo real a los datos de eventos de seguridad a través de interfaces web dinámicas. La aplicación soporta características avanzadas de filtrado, búsqueda y correlación que permiten a los analistas profundizar en eventos específicos, investigar actividades relacionadas y generar informes completos para fines de gestión y cumplimiento. Con su énfasis en usabilidad y análisis visual, Squert se ha convertido en un componente esencial de muchos centros de operaciones de seguridad que buscan modernizar sus flujos de trabajo de respuesta a incidentes.
Instalación¶
Ubuntu/Debian Instalación¶
Instalación de Squert en sistemas 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 Instalación¶
# 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
Docker Instalación¶
Running Squert en contenedores 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
Instalación manual¶
# 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 básico¶
Configuración inicial¶
Configuración de configuración 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
Web Interface Access¶
Acceso y uso de la interfaz 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¶
Comprender componentes de interfaz 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
Características avanzadas¶
Custom Dashboards¶
Creación de paneles y vistas personalizadas:
<?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 avanzado y búsqueda¶
Creación de capacidades avanzadas de búsqueda y filtrado:
// 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();
\\\\}
\\\\}
Automated Reporting¶
Creación de capacidades automatizadas de presentación de informes:
<?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¶
Comprehensive Monitoring Script¶
#!/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
Integración Ejemplos¶
SIEM Integration¶
#!/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()
Troubleshooting¶
Common Issues¶
Interfaz web No Carga:
# Check Apache status
sudo systemctl status apache2
# Check Apache error logs
sudo tail -f /var/log/apache2/squert_error.log
# Check PHP errors
sudo tail -f /var/log/apache2/error.log|grep -i php
# Verify file permissions
sudo chown -R www-data:www-data /var/www/squert
sudo chmod -R 755 /var/www/squert
# Test PHP configuration
php -m|grep -i mysql
php -r "phpinfo();"|grep -i mysql
** Problemas de conexión de datos:**
# Test database connection
mysql -h localhost -u squert -p sguildb -e "SELECT COUNT(*) FROM event;"
# Check database permissions
mysql -u root -p -e "SHOW GRANTS FOR 'squert'@'localhost';"
# Verify database configuration
cat /var/www/squert/.scripts/squert.inc|grep -E "(dbhost|dbuser|dbpass|dbname)"
# Check MySQL service
sudo systemctl status mysql
sudo systemctl start mysql
** Cuestiones de desempeño**
# Check system resources
top -p $(pgrep apache2)
free -h
df -h
# Optimize database
mysql -u squert -p sguildb -e "OPTIMIZE TABLE event;"
mysql -u squert -p sguildb -e "ANALYZE TABLE event;"
# Check slow queries
mysql -u root -p -e "SHOW PROCESSLIST;"
# Enable PHP OPcache
echo "opcache.enable=1" >> /etc/php/7.4/apache2/php.ini
sudo systemctl restart apache2
Performance Optimization¶
Optimización del rendimiento de 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
Security Considerations¶
Access Control¶
** Seguridad de la Interfaz en Internet** - Implementar HTTPS para todo acceso Squert - Use mecanismos de autenticación fuertes - Ejecutar el tiempo y la gestión del período de sesiones - Actualizaciones periódicas de seguridad para todos los componentes - Supervisar los registros de acceso para actividades sospechosas
Seguridad de la base de datos** - Usar un usuario de base de datos dedicado con privilegios mínimos - Implementar el cifrado de conexión de bases de datos - Actualizaciones periódicas de seguridad de bases de datos - Monitorear registros de acceso a bases de datos - Implementar el cifrado de respaldo
Protección de datos¶
Event Data Security: - Cifrar datos de eventos sensibles en reposo - Implementar políticas de retención de datos - Acceso seguro a los datos de captura de paquetes - Limpieza regular de archivos temporales - Implementar registros de acceso para datos de eventos
** Seguridad Operacional** - Evaluaciones periódicas de seguridad de la infraestructura de Squert - Vigilancia de los intentos de acceso no autorizado - Implementar procedimientos adecuados de respaldo y recuperación - Actualizaciones periódicas de Squert y dependencias - Procedimientos de respuesta a incidentes de compromiso Squert
Referencias¶
- [Squert GitHub Repository](URL_22__
- [Sguil Official Documentation](URL_23_
- Apache HTTP Server Documentation_
- [PHP Security Best Practices](URL_25_
- MySQL Performance Tuning_