Squert hoja de trucos
Overview
Squert is a web-based application designed to provide a visual interface for querying and analyzing events stored in a Sguil database. Developed as a complementary tool to Sguil's traditional client interface, Squert offers security analysts an intuitive web-based platform for investigating network security incidents and performing event correlation analysis. The application leverages modern web technologies to present security event data in interactive charts, graphs, and tables, making it easier for analysts to identify patterns, trends, and anomalies in network traffic.
The core strength of Squert lies in its ability to transform raw security event data into meaningful visualizations that facilitate rapid threat analysis and respuesta a incidentes. Unlike traditional comando-line or desktop-based security tools, Squert provides a responsive web interface that can be accessed from any modern browser, enabling distributed security teams to collaborate effectively on incident investigation and caza de amenazas activities. The application integrates seamlessly with existing Sguil deployments, utilizing the same MySQL database backend while providing enhanced visualization and analysis capabilities.
Squert's architecture is built around PHP and JavaScript technologies, with a focus on providing real-time access to security event data through dynamic web interfaces. The application suppuertos advanced filtering, searching, and correlation features that allow analysts to drill down into specific events, investigate related activities, and generate comprehensive repuertos for management and compliance purposes. With its emphasis on usability and visual analytics, Squert has become an essential component of many security operations centers seeking to modernize their respuesta a incidentes workflows.
instalación
Ubuntu/Debian instalación
Installing Squert on Ubuntu/Debian systems:
# 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 configuración 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>
opcións -Indexes
AllowOverride All
Require all granted
</Directory>
# Security headers
Header always set X-Content-Type-opcións nosniff
Header always set X-Frame-opcións 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 'squertcontraseña';
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 configuración 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 configuración
sudo cat >`` /etc/httpd/conf.d/squert.conf << 'EOF'
Alias /squert /var/www/html/squert
<Directory "/var/www/html/squert">
opcións -Indexes
AllowOverride All
Require all granted
</Directory>
EOF
# Restart Apache
sudo systemctl restart httpd
# Configure firewall
sudo firewall-cmd --permanent --add-servicio=http
sudo firewall-cmd --permanent --add-servicio=https
sudo firewall-cmd --reload
Docker instalación
Running Squert in Docker containers:
# 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 configuración directory
RUN mkdir -p /etc/squert && chown www-data:www-data /etc/squert
# Apache configuración
COPY squert.conf /etc/apache2/sites-available/
RUN a2ensite squert && a2enmod rewrite
EXPOSE 80
CMD ["apache2-foreground"]
EOF
# Create Apache configuración for container
cat > squert.conf << 'EOF'
<Virtualhost *:80>
DocumentRoot /var/www/html/squert
<Directory /var/www/html/squert>
opcións -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_contraseña=squertcontraseña \
-e MYSQL_DATABASE=sguildb \
squert
Manual instalación
# 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 configuración directory
sudo mkdir -p /etc/squert
sudo chown www-data:www-data /etc/squert
Basic uso
Initial configuración
Setting up Squert configuración:
# Create Squert configuración file
sudo cat > /var/www/squert/.scripts/squert.inc << 'EOF'
<?php
// Squert configuración
// Database configuración
$dbname = 'sguildb';
$dbhost = 'localhost';
$dbuser = 'squert';
$dbpass = 'squertcontraseña';
$dbpuerto = 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;
$sesión_timeout = 3600; // 1 hour
// Chart settings
$chart_height = 400;
$chart_width = 800;
// IP geolocation (opciónal)
$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 clave,
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 clave,
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 clave,
title VARCHAR(200),
url TEXT,
user VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF
Web Interface Access
Accessing and using the Squert web interface:
# 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', 'squertcontraseña', 'sguildb');
if (\$conn->connect_error) \\\\{
die('conexión failed: ' . \$conn->connect_error);
\\\\}
echo 'Database conexión successful\n';
\$conn->close();
"
Basic Navigation
Understanding Squert interface components:
# 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. Repuertos section - Generated repuertos
# clave interface elements:
# - Time range selector
# - Event filters
# - Source/destination IP analysis
# - firma analysis
# - protocolo breakdown
# - Geographic mapping (if enabled)
# Common workflows:
# 1. Monitor real-time events
# 2. Investigate specific incidents
# 3. Analyze traffic patterns
# 4. Generate compliance repuertos
# 5. Correlate related events
Advanced Features
Custom Dashboards
Creating custom dashboards and views:
<?php
// Custom dashboard configuración
// 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("conexión 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 objetivo_count,
COUNT(DISTINCT firma_id) as firma_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 getTopobjetivos($limit = 10, $hours = 24) \\\\{
$query = "
SELECT
dst_ip,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as attacker_count,
COUNT(DISTINCT firma_id) as firma_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 getfirmaStats($hours = 24) \\\\{
$query = "
SELECT
firma,
firma_id,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_objetivos
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
GROUP BY firma, firma_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();
$topobjetivos = $this->getTopobjetivos();
$firmaStats = $this->getfirmaStats();
$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>objetivos</th><th>firmas</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['objetivo_count']; ?></td>
<td><?php echo $attacker['firma_count']; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="widget">
<h3>Top objetivos (24h)</h3>
<table>
<tr><th>objetivo IP</th><th>Events</th><th>Attackers</th><th>firmas</th></tr>
<?php foreach ($topobjetivos as $objetivo): ?>
<tr>
<td><?php echo htmlspecialchars($objetivo['dst_ip']); ?></td>
<td><?php echo $objetivo['event_count']; ?></td>
<td><?php echo $objetivo['attacker_count']; ?></td>
<td><?php echo $objetivo['firma_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 firmas (24h)</h3>
<table>
<tr><th>firma</th><th>Events</th><th>Sources</th></tr>
<?php foreach (array_slice($firmaStats, 0, 10) as $sig): ?>
<tr>
<td><?php echo htmlspecialchars(substr($sig['firma'], 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
\\\\}]
\\\\},
opcións: \\\\{
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();
?>
Advanced Filtering and Search
Creating advanced search and filtering capabilities:
// 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">
<opción value="1h">Last Hour</opción>
<opción value="6h">Last 6 Hours</opción>
<opción value="24h">Last 24 Hours</opción>
<opción value="7d">Last 7 Days</opción>
<opción value="30d">Last 30 Days</opción>
<opción value="custom">Custom Range</opción>
</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>puerto Range:</label>
<input type="text" id="puerto-range" placeholder="80,443,8080-8090">
</div>
<div class="filter-group">
<label>firma:</label>
<input type="text" id="firma" placeholder="Search firma text">
</div>
<div class="filter-group">
<label>firma ID:</label>
<input type="text" id="firma-id" placeholder="1,2,3 or 100-200">
</div>
<div class="filter-group">
<label>protocolo:</label>
<select id="protocolo">
<opción value="">All protocolos</opción>
<opción value="1">ICMP</opción>
<opción value="6">TCP</opción>
<opción value="17">UDP</opción>
</select>
</div>
<div class="filter-group">
<label>Event Limit:</label>
<select id="event-limit">
<opción value="100">100 events</opción>
<opción value="500">500 events</opción>
<opción value="1000">1000 events</opción>
<opción value="5000">5000 events</opción>
</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.objetivo.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\\}'`);
\\\\}
\\\\}
// puertos
const puertoRange = document.getElementById('puerto-range').value.trim();
if (puertoRange) \\\\{
const puertoConditions = [];
puertoRange.split(',').forEach(puerto => \\\\{
if (puerto.includes('-')) \\\\{
const [start, end] = puerto.split('-');
puertoConditions.push(`(src_puerto BETWEEN $\\{start\\} AND $\\{end\\}) OR (dst_puerto BETWEEN $\\{start\\} AND $\\{end\\})`);
\\\\} else \\\\{
puertoConditions.push(`src_puerto = $\\{puerto\\} OR dst_puerto = $\\{puerto\\}`);
\\\\}
\\\\});
filters.push(`($\\{puertoConditions.join(' OR ')\\})`);
\\\\}
// firma
const firma = document.getElementById('firma').value.trim();
if (firma) \\\\{
filters.push(`firma LIKE '%$\\{firma\\}%'`);
\\\\}
// firma ID
const firmaId = document.getElementById('firma-id').value.trim();
if (firmaId) \\\\{
if (firmaId.includes('-')) \\\\{
const [start, end] = firmaId.split('-');
filters.push(`firma_id BETWEEN $\\{start\\} AND $\\{end\\}`);
\\\\} else if (firmaId.includes(',')) \\\\{
filters.push(`firma_id IN ($\\{firmaId\\})`);
\\\\} else \\\\{
filters.push(`firma_id = $\\{firmaId\\}`);
\\\\}
\\\\}
// protocolo
const protocolo = document.getElementById('protocolo').value;
if (protocolo) \\\\{
filters.push(`ip_proto = $\\{protocolo\\}`);
\\\\}
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_puerto, dst_ip, dst_puerto,
ip_proto, firma, firma_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>protocolo</th>
<th>firma</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
`;
results.forEach(event => \\\\{
tableHTML += `
<tr>
<td>$\\{event.timestamp\\}</td>
<td>$\\{event.src_ip\\}:$\\{event.src_puerto\\}</td>
<td>$\\{event.dst_ip\\}:$\\{event.dst_puerto\\}</td>
<td>$\\{this.getprotocoloName(event.ip_proto)\\}</td>
<td title="$\\{event.firma\\}">$\\{event.firma.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;
\\\\}
getprotocoloName(proto) \\\\{
const protocolos = \\\\{ 1: 'ICMP', 6: 'TCP', 17: 'UDP' \\\\};
| return protocolos[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,
puertoRange: document.getElementById('puerto-range').value,
firma: document.getElementById('firma').value,
firmaId: document.getElementById('firma-id').value,
protocolo: document.getElementById('protocolo').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('puerto-range').value = '';
document.getElementById('firma').value = '';
document.getElementById('firma-id').value = '';
document.getElementById('protocolo').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('puerto-range').value = filter.puertoRange;
document.getElementById('firma').value = filter.firma;
document.getElementById('firma-id').value = filter.firmaId;
document.getElementById('protocolo').value = filter.protocolo;
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 Repuertoing
Creating automated repuertoing capabilities:
<?php
// Automated repuertoing system
// File: /var/www/squert/repuertos/automated_repuertos.php
require_once '../.scripts/squert.inc';
class AutomatedRepuertos \\\\{
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("conexión failed: " . $this->db->connect_error);
\\\\}
$this->config = [
'repuerto_dir' => '/var/www/squert/repuertos/generated',
'email_repuertos' => true,
'email_to' => 'security@company.com',
'email_from' => 'squert@company.com'
];
// Create repuertos directory if it doesn't exist
if (!is_dir($this->config['repuerto_dir'])) \\\\{
mkdir($this->config['repuerto_dir'], 0755, true);
\\\\}
\\\\}
public function generateDailyRepuerto($date = null) \\\\{
if (!$date) \\\\{
$date = date('Y-m-d', strtotime('-1 day'));
\\\\}
$repuerto_data = [
'date' => $date,
'summary' => $this->getDailySummary($date),
'top_attackers' => $this->getTopAttackers($date),
'top_objetivos' => $this->getTopobjetivos($date),
'top_firmas' => $this->getTopfirmas($date),
'hourly_stats' => $this->getHourlyStats($date),
'protocolo_breakdown' => $this->getprotocoloBreakdown($date),
'geographic_analysis' => $this->getGeographicAnalysis($date)
];
$html_repuerto = $this->generateHTMLRepuerto($repuerto_data, 'daily');
$pdf_repuerto = $this->generatePDFRepuerto($repuerto_data, 'daily');
// Save repuertos
$html_file = $this->config['repuerto_dir'] . "/daily_repuerto_\\\\{$date\\\\}.html";
$pdf_file = $this->config['repuerto_dir'] . "/daily_repuerto_\\\\{$date\\\\}.pdf";
file_put_contents($html_file, $html_repuerto);
file_put_contents($pdf_file, $pdf_repuerto);
// Email repuerto if configured
if ($this->config['email_repuertos']) \\\\{
$this->emailRepuerto($html_file, $pdf_file, "Daily Security Repuerto - \\\\{$date\\\\}");
\\\\}
return [
'html_file' => $html_file,
'pdf_file' => $pdf_file,
'data' => $repuerto_data
];
\\\\}
public function generateWeeklyRepuerto($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'));
$repuerto_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_repuerto = $this->generateHTMLRepuerto($repuerto_data, 'weekly');
$html_file = $this->config['repuerto_dir'] . "/weekly_repuerto_\\\\{$week_start\\\\}_to_\\\\{$week_end\\\\}.html";
file_put_contents($html_file, $html_repuerto);
return ['html_file' => $html_file, 'data' => $repuerto_data];
\\\\}
private function getDailySummary($date) \\\\{
$query = "
SELECT
COUNT(*) as total_events,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_objetivos,
COUNT(DISTINCT firma_id) as unique_firmas,
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 objetivo_count,
COUNT(DISTINCT firma_id) as firma_count,
GROUP_CONCAT(DISTINCT firma ORDER BY firma LIMIT 3) as top_firmas
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 getTopobjetivos($date, $limit = 10) \\\\{
$query = "
SELECT
dst_ip,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as attacker_count,
COUNT(DISTINCT firma_id) as firma_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 getTopfirmas($date, $limit = 15) \\\\{
$query = "
SELECT
firma,
firma_id,
COUNT(*) as event_count,
COUNT(DISTINCT src_ip) as unique_sources,
COUNT(DISTINCT dst_ip) as unique_objetivos
FROM event
WHERE DATE(timestamp) = ?
GROUP BY firma, firma_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 getprotocoloBreakdown($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 generateHTMLRepuerto($data, $type) \\\\{
ob_start();
?>
<!DOCTYPE html>
<html>
<head>
<title><?php echo ucfirst($type); ?> Security Repuerto</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 Repuerto</h1>
<?php if ($type == 'daily'): ?>
<p>Repuerto Date: <?php echo $data['date']; ?></p>
<?php else: ?>
<p>Repuerto 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 objetivos</h3>
<div class="value"><?php echo number_format($data['summary']['unique_objetivos']); ?></div>
</div>
<div class="metric">
<h3>Unique firmas</h3>
<div class="value"><?php echo number_format($data['summary']['unique_firmas']); ?></div>
</div>
</div>
<div class="section">
<h2>Top Attackers</h2>
<table>
<tr><th>Source IP</th><th>Events</th><th>objetivos</th><th>firmas</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['objetivo_count']); ?></td>
<td><?php echo number_format($attacker['firma_count']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="section">
<h2>Top objetivos</h2>
<table>
<tr><th>objetivo IP</th><th>Events</th><th>Attackers</th><th>firmas</th></tr>
<?php foreach ($data['top_objetivos'] as $objetivo): ?>
<tr>
<td><?php echo htmlspecialchars($objetivo['dst_ip']); ?></td>
<td><?php echo number_format($objetivo['event_count']); ?></td>
<td><?php echo number_format($objetivo['attacker_count']); ?></td>
<td><?php echo number_format($objetivo['firma_count']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<div class="section">
<h2>Top firmas</h2>
<table>
<tr><th>firma</th><th>Events</th><th>Sources</th><th>objetivos</th></tr>
<?php foreach ($data['top_firmas'] as $sig): ?>
<tr>
<td><?php echo htmlspecialchars($sig['firma']); ?></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_objetivos']); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<?php endif; ?>
</body>
</html>
<?php
return ob_get_clean();
\\\\}
private function emailRepuerto($html_file, $pdf_file, $subject) \\\\{
// Implementation depends on your email system
// This is a basic ejemplo 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);
\\\\}
\\\\}
// uso ejemplo
if (php_sapi_name() === 'cli') \\\\{
// comando line uso
$repuertos = new AutomatedRepuertos();
$action = $argv[1] ?? 'daily';
$date = $argv[2] ?? null;
switch ($action) \\\\{
case 'daily':
$result = $repuertos->generateDailyRepuerto($date);
echo "Daily repuerto generated: \\\\{$result['html_file']\\\\}\n";
break;
case 'weekly':
$result = $repuertos->generateWeeklyRepuerto($date);
echo "Weekly repuerto generated: \\\\{$result['html_file']\\\\}\n";
break;
default:
echo "uso: php automated_repuertos.php [daily|weekly] [date]\n";
\\\\}
\\\\}
?>
Automation Scripts
Comprehensive Monitoring Script
#!/bin/bash
# Comprehensive Squert monitoring and maintenance
# configuración
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 configuración
DB_host="localhost"
DB_USER="squert"
DB_PASS="squertcontraseña"
DB_NAME="sguildb"
# Monitoring thresholds
MAX_LOG_SIZE="100M"
MAX_DISK_uso="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 conexión 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 conexión failed"
return 1
fi
\\\\}
# Check Apache servicio
check_apache() \\\\{
log_message "Checking Apache servicio..."
if systemctl is-active --quiet apache2; then
log_message "Apache servicio 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 servicio is not running"
return 1
fi
\\\\}
# Check disk space
check_disk_space() \\\\{
log_message "Checking disk space..."
local uso
| uso=$(df -h "$SQUERT_DIR" | awk 'NR==2 \\\\{print $5\\\\}' | sed 's/%//') |
log_message "Disk uso: $\\\\{uso\\\\}%"
if [ "$uso" -gt "$MAX_DISK_uso" ]; then
log_message "WARNING: High disk uso: $\\\\{uso\\\\}% (threshold: $\\\\{MAX_DISK_uso\\\\}%)"
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 comando -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 configuración
backup_configuración() \\\\{
log_message "Backing up Squert configuración..."
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 "configuración 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: configuración backup failed"
return 1
fi
\\\\}
# Generate health repuerto
generate_health_repuerto() \\\\{
log_message "Generating health repuerto..."
local repuerto_file="$LOG_DIR/health-repuerto-$(date +%Y%m%d-%H%M%S).html"
cat > "$repuerto_file" << EOF
<!DOCTYPE html>
<html>
<head>
<title>Squert Health Repuerto</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 Repuerto</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 repuerto
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>" >> "$repuerto_file"
else
echo " <tr><td>Web Interface</td><td class=\"status-error\">ERROR</td><td>Not accessible</td></tr>" >> "$repuerto_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>" >> "$repuerto_file"
else
echo " <tr><td>Database</td><td class=\"status-error\">ERROR</td><td>conexión failed</td></tr>" >> "$repuerto_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>" >> "$repuerto_file"
else
echo " <tr><td>Apache</td><td class=\"status-error\">ERROR</td><td>Not running</td></tr>" >> "$repuerto_file"
fi
local disk_uso
disk_uso=$(df -h "$SQUERT_DIR"|awk 'NR==2 \\\\{print $5\\\\}')
echo " <tr><td>Disk Space</td><td class=\"status-ok\">OK</td><td>uso: $disk_uso</td></tr>" >> "$repuerto_file"
cat >> "$repuerto_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 repuerto generated: $repuerto_file"
\\\\}
# Send alert notification
send_alert() \\\\{
local subject="$1"
local message="$2"
# Send email if mail is configured
if comando -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_configuración
fi
# Generate health repuerto (daily)
if [ "$(date +%H)" -eq 6 ]; then
generate_health_repuerto
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 repuertos (older than 7 days)
find "$LOG_DIR" -name "health-repuerto-*.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_configuración
# 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
\\\\}
# comando line interface
case "$\\\\{1:-monitor\\\\}" in
"monitor")
run_monitoring
;;
"cleanup")
cleanup_old_files
;;
"update")
update_squert
;;
"backup")
backup_configuración
;;
"repuerto")
generate_health_repuerto
;;
"optimize")
optimize_performance
;;
*)
| echo "uso: $0 \\\\{monitor | cleanup | update | backup | repuerto | optimize\\\\}" |
echo ""
echo "comandos:"
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 configuración"
echo " repuerto - Generate health repuerto"
echo " optimize - Run performance optimization"
exit 1
;;
esac
Integration ejemplos
SIEM Integration
#!/usr/bin/env python3
# Squert SIEM integration script
impuerto mysql.connector
impuerto json
impuerto requests
impuerto time
from datetime impuerto 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"""
conexión = self.connect_database()
cursor = conexión.cursor(dictionary=True)
query = """
SELECT
sid, cid, timestamp, src_ip, src_puerto, dst_ip, dst_puerto,
ip_proto, firma, firma_gen, firma_id, firma_rev
FROM event
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL %s MINUTE)
ORDER BY timestamp DESC
"""
cursor.execute(query, (minutes,))
events = cursor.fetchall()
cursor.close()
conexión.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_puerto": event['src_puerto'],
"dst_ip": event['dst_ip'],
"dst_puerto": event['dst_puerto'],
"protocolo": event['ip_proto'],
"firma": event['firma'],
"firma_id": event['firma_id'],
"firma_gen": event['firma_gen'],
"firma_rev": event['firma_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 = \\\\{
'autorización': 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'],
"puerto": event['src_puerto']
\\\\},
"destination": \\\\{
"ip": event['dst_ip'],
"puerto": event['dst_puerto']
\\\\},
"network": \\\\{
"protocolo": self.get_protocolo_name(event['ip_proto'])
\\\\},
"event": \\\\{
"id": f"\\\\{event['sid']\\\\}-\\\\{event['cid']\\\\}",
"category": "network",
"type": "alert",
"severity": self.get_severity(event['firma_id'])
\\\\},
"rule": \\\\{
"id": event['firma_id'],
"name": event['firma'],
"version": event['firma_rev']
\\\\},
"sguil": \\\\{
"sid": event['sid'],
"cid": event['cid'],
"firma_gen": event['firma_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_protocolo_name(self, proto_num):
"""Convert protocolo number to name"""
protocolos = \\\\{1: 'icmp', 6: 'tcp', 17: 'udp'\\\\}
return protocolos.get(proto_num, str(proto_num))
def get_severity(self, firma_id):
"""Determine event severity based on firma ID"""
if firma_id in [1, 2, 3]:
return 'high'
elif firma_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 proceso")
return
print(f"procesoing \\\\{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")
# configuración
config = \\\\{
'database': \\\\{
'host': 'localhost',
'user': 'squert',
'contraseña': 'squertcontraseña',
'database': 'sguildb'
\\\\},
'siem': \\\\{
'splunk_enabled': True,
'splunk_hec_url': 'https://splunk.company.com:8088/servicios/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()
solución de problemas
Common Issues
Web Interface Not Loading:
# 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 configuración
php -m|grep -i mysql
php -r "phpinfo();"|grep -i mysql
Database conexión Issues:
# Test database conexión
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 configuración
| cat /var/www/squert/.scripts/squert.inc | grep -E "(dbhost | dbuser | dbpass | dbname)" |
# Check MySQL servicio
sudo systemctl status mysql
sudo systemctl start mysql
Performance Issues:
# 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 procesoLIST;"
# Enable PHP OPcache
echo "opcache.enable=1" >> /etc/php/7.4/apache2/php.ini
sudo systemctl restart apache2
Performance Optimization
Optimizing Squert performance:
# 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_firma_timestamp ON event (firma_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
Control de Acceso
Web Interface Security: - Implement HTTPS for all Squert access - Use strong autenticación mechanisms - Implement sesión timeout and management - Regular security updates for all components - Monitor access logs for suspicious activity
Database Security: - Use dedicated database user with minimal privileges - Implement database conexión cifrado - Regular database security updates - Monitor database access logs - Implement backup cifrado
Data Protection
Event Data Security: - Encrypt sensitive event data at rest - Implement data retention policies - Secure access to packet capture data - Regular cleanup of temporary files - Implement access logging for event data
Operational Security: - Regular security assessments of Squert infrastructure - Monitor for unauthorized access attempts - Implement proper backup and recovery procedures - Regular updates of Squert and dependencies - respuesta a incidentes procedures for Squert compromise