1337skills.com

Appearance

Squert Cheat Sheet ​

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 incident response. Unlike traditional command-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 threat hunting 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 supports advanced filtering, searching, and correlation features that allow analysts to drill down into specific events, investigate related activities, and generate comprehensive reports 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 incident response workflows.

Installation ​

Ubuntu/Debian Installation ​

Installing Squert on Ubuntu/Debian systems:

bash
# 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 Installation ​

bash
# 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 Installation ​

Running Squert in Docker containers:

bash
# 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

Manual Installation ​

bash
# 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

Basic Usage ​

Initial Configuration ​

Setting up Squert configuration:

bash
# 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 ​

Accessing and using the Squert web interface:

bash
# 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 ​

Understanding Squert interface components:

bash
# 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

Advanced Features ​

Custom Dashboards ​

Creating custom dashboards and views:

php
<?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();
?>

Creating advanced search and filtering capabilities:

javascript
// 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 ​

Creating automated reporting capabilities:

php
<?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 ​

bash
#!/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

Integration Examples ​

SIEM Integration ​

python
#!/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 ​

Web Interface Not Loading:

bash
# 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

Database Connection Issues:

bash
# 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

Performance Issues:

bash
# 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 ​

Optimizing Squert performance:

bash
# 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 ​

Web Interface Security:

  • Implement HTTPS for all Squert access
  • Use strong authentication mechanisms
  • Implement session 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 connection encryption
  • Regular database security updates
  • Monitor database access logs
  • Implement backup encryption

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
  • Incident response procedures for Squert compromise

References ​

  1. Squert GitHub Repository
  2. Sguil Official Documentation
  3. Apache HTTP Server Documentation
  4. PHP Security Best Practices
  5. MySQL Performance Tuning