🔍 Real-Time Security Monitoring with Osquery on AlmaLinux: SQL Your Way to Security!

Ever wished you could ask your Linux server questions like “Show me all processes consuming more than 50% CPU” or “List all users who logged in yesterday” using simple SQL? 🤔 Meet Osquery - Facebook’s gift to the security world that turns your entire operating system into a relational database! Today, we’re transforming your AlmaLinux system into a queryable security powerhouse! Let’s make monitoring fun again! 🎉

🤔 Why is Osquery Important?

Imagine if you could Google search your server’s internals! That’s Osquery - it lets you investigate your system using familiar SQL queries instead of memorizing hundreds of Linux commands! 🔎

Here’s why Osquery is absolutely game-changing:

• 🗂️ SQL-based queries - Use SELECT statements to explore your system
• ⚡ Real-time monitoring - Get instant answers about system state
• 🔍 Threat hunting made easy - Find malware and anomalies quickly
• 📊 Cross-platform consistency - Same queries work on Linux, Mac, Windows
• 🎯 Performance monitoring - Track resource usage with simple queries
• 🛡️ Security investigations - Forensics and incident response simplified
• 📈 Scheduled queries - Automate monitoring with query packs
• 🔄 Integration ready - Works with SIEM, logging, and alerting tools

🎯 What You Need

Before we dive into SQL-powered monitoring, let’s check what you need! Super simple:

• ✅ AlmaLinux installed (any recent version)
• ✅ Root or sudo access (we need the power! 💪)
• ✅ Basic SQL knowledge (SELECT, WHERE, JOIN - that’s it!)
• ✅ 1GB free RAM (Osquery is lightweight)
• ✅ About 20 minutes of your time
• ✅ Curiosity about your system (the most important! 🧐)

📝 Step 1: Install Osquery

Let’s get Osquery installed on your AlmaLinux system! We’ll use the official repository.

# Add Osquery YUM repository
curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
# Imports Osquery GPG key

# Create repository file
sudo tee /etc/yum.repos.d/osquery.repo << EOF
[osquery]
name=Osquery
baseurl=https://pkg.osquery.io/rpm/\$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
EOF
# Creates Osquery repo configuration

# Update repository cache
sudo dnf makecache
# Refreshes package information

# Install Osquery
sudo dnf install -y osquery
# Installs Osquery package

Verify the installation worked! 🎉

# Check Osquery version
osqueryi --version
# Should show version info

# Test interactive shell
osqueryi
# Launches SQL shell - type .exit to quit

# Check installed files
rpm -ql osquery | head -10
# Shows Osquery file locations

🔧 Step 2: Configure Osquery

Time to configure Osquery for continuous monitoring! We’ll set up both interactive and daemon modes.

# Create main configuration file
sudo nano /etc/osquery/osquery.conf
# Opens configuration editor

Add this powerful configuration:

{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "disable_logging": "false",
    "schedule_splay_percent": "10",
    "pidfile": "/var/run/osquery/osquery.pidfile",
    "events_expiry": "3600",
    "database_path": "/var/osquery/osquery.db",
    "verbose": "false",
    "worker_threads": "2",
    "enable_monitor": "true",
    "disable_events": "false",
    "disable_audit": "false",
    "audit_allow_config": "true",
    "audit_allow_sockets": "true",
    "audit_allow_process_events": "true"
  },
  
  "schedule": {
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    },
    "users_snapshot": {
      "query": "SELECT * FROM users;",
      "interval": 300,
      "description": "Monitor user accounts"
    },
    "processes_snapshot": {
      "query": "SELECT pid, name, path, cmdline, uid FROM processes WHERE name NOT IN ('systemd', 'kernel');",
      "interval": 60,
      "description": "Track running processes"
    },
    "network_connections": {
      "query": "SELECT pid, family, protocol, local_address, local_port, remote_address, remote_port, state FROM process_open_sockets WHERE state='ESTABLISHED';",
      "interval": 30,
      "description": "Monitor network connections"
    },
    "suspicious_processes": {
      "query": "SELECT * FROM processes WHERE name IN ('nc', 'netcat', 'nmap', 'tcpdump', 'wireshark');",
      "interval": 10,
      "description": "Detect potentially suspicious tools"
    }
  },
  
  "decorators": {
    "load": [
      "SELECT uuid AS host_uuid FROM system_info;",
      "SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
    ]
  },
  
  "packs": {
    "osquery-monitoring": "/etc/osquery/packs/osquery-monitoring.conf",
    "incident-response": "/etc/osquery/packs/incident-response.conf"
  }
}

🌟 Step 3: Create Security Query Packs

Let’s create powerful query packs for security monitoring! 🛡️

# Create packs directory
sudo mkdir -p /etc/osquery/packs
# Makes directory for query packs

# Create incident response pack
sudo nano /etc/osquery/packs/incident-response.conf

Add this incident response pack:

{
  "platform": "linux",
  "version": "1.0.0",
  "queries": {
    "authorized_keys": {
      "query": "SELECT * FROM authorized_keys;",
      "interval": 300,
      "description": "Monitor SSH authorized keys"
    },
    "crontab": {
      "query": "SELECT * FROM crontab;",
      "interval": 300,
      "description": "Monitor scheduled tasks"
    },
    "listening_ports": {
      "query": "SELECT pid, port, protocol, path FROM listening_ports;",
      "interval": 60,
      "description": "Track listening services"
    },
    "suid_binaries": {
      "query": "SELECT * FROM suid_bin;",
      "interval": 3600,
      "description": "Monitor SUID binaries"
    },
    "kernel_modules": {
      "query": "SELECT * FROM kernel_modules;",
      "interval": 300,
      "description": "Track loaded kernel modules"
    },
    "login_history": {
      "query": "SELECT * FROM last;",
      "interval": 60,
      "description": "Monitor login events"
    },
    "iptables_rules": {
      "query": "SELECT * FROM iptables;",
      "interval": 300,
      "description": "Monitor firewall rules"
    },
    "shell_history": {
      "query": "SELECT * FROM shell_history;",
      "interval": 60,
      "description": "Track command history"
    }
  }
}

✅ Step 4: Start Osquery and Run Queries

Let’s fire up Osquery and start investigating! 🚀

# Start Osquery daemon
sudo systemctl start osqueryd
# Starts background monitoring

# Enable at boot
sudo systemctl enable osqueryd
# Auto-starts on reboot

# Check daemon status
sudo systemctl status osqueryd
# Should show "active (running)"

# Launch interactive mode
osqueryi
# Opens SQL shell

Now let’s run some powerful security queries! 🔍

-- In osqueryi shell:

-- Show system information
SELECT * FROM system_info;

-- List all users
SELECT uid, gid, username, description, shell FROM users;

-- Find recently modified files
SELECT path, filename, mtime, size FROM file 
WHERE path LIKE '/etc/%' 
AND mtime > (strftime('%s', 'now') - 3600);

-- Show running processes with network connections
SELECT DISTINCT p.pid, p.name, p.path, pos.remote_address, pos.remote_port 
FROM processes p 
JOIN process_open_sockets pos ON p.pid = pos.pid 
WHERE pos.state = 'ESTABLISHED';

-- Find suspicious hidden files
SELECT * FROM file 
WHERE path LIKE '/home/%/.%' 
AND type = 'regular' 
AND size > 0;

-- Exit osqueryi
.exit

🎮 Quick Examples

Let’s explore real-world security monitoring scenarios! 🔥

Example 1: Hunt for Backdoors

# Launch osqueryi
osqueryi

# Query for potential backdoors
SELECT * FROM listening_ports 
WHERE port NOT IN (22, 80, 443, 3306, 5432) 
AND protocol = 6;
-- Finds unusual listening TCP ports

# Check for reverse shells
SELECT p.pid, p.name, p.path, p.cmdline, pos.remote_address 
FROM processes p 
JOIN process_open_sockets pos ON p.pid = pos.pid 
WHERE p.name IN ('bash', 'sh', 'nc', 'netcat') 
AND pos.remote_port NOT IN (22, 80, 443);
-- Detects potential reverse shells

# Find hidden processes
SELECT * FROM processes 
WHERE path NOT LIKE '/usr/%' 
AND path NOT LIKE '/bin/%' 
AND path NOT LIKE '/sbin/%';
-- Shows processes from unusual locations

Example 2: Monitor User Activity

# Create user monitoring script
cat << 'EOF' > /usr/local/bin/osquery-user-monitor.sh
#!/bin/bash
echo "=== User Activity Report ==="
osqueryi --json "SELECT * FROM logged_in_users;" | jq '.'
echo ""
echo "=== Recent Commands ==="
osqueryi --json "SELECT command, time FROM shell_history ORDER BY time DESC LIMIT 10;" | jq '.'
echo ""
echo "=== Failed Logins ==="
osqueryi --json "SELECT username, time, host FROM failed_login_attempts;" | jq '.'
EOF

chmod +x /usr/local/bin/osquery-user-monitor.sh
# Run with: /usr/local/bin/osquery-user-monitor.sh

Example 3: Performance Monitoring

-- In osqueryi:

-- Top CPU consuming processes
SELECT pid, name, 
       ROUND((user_time + system_time) / 60.0, 2) AS cpu_minutes,
       resident_size / 1024 / 1024 AS memory_mb
FROM processes 
ORDER BY (user_time + system_time) DESC 
LIMIT 10;

-- Disk usage by directory
SELECT path, 
       ROUND(size / 1024.0 / 1024.0 / 1024.0, 2) AS size_gb,
       type
FROM file 
WHERE path LIKE '/var/%' 
AND type = 'directory'
ORDER BY size DESC 
LIMIT 20;

-- Network bandwidth usage
SELECT interface, 
       ROUND(ibytes / 1024.0 / 1024.0, 2) AS received_mb,
       ROUND(obytes / 1024.0 / 1024.0, 2) AS sent_mb
FROM interface_details;

🚨 Fix Common Problems

Don’t worry if you encounter issues! Here are quick fixes! 💪

Problem 1: “Osqueryd not starting”

# Solution: Check configuration
sudo osqueryd --config_check
# Validates configuration

# Check logs for errors
sudo tail -50 /var/log/osquery/osqueryd.results.log
# Shows recent logs

# Fix permissions
sudo chown -R root:root /etc/osquery
sudo chmod 600 /etc/osquery/osquery.conf
# Secures configuration

# Clear database and restart
sudo rm -rf /var/osquery/osquery.db
sudo systemctl restart osqueryd
# Fresh start

Problem 2: “Queries running slowly”

# Solution: Optimize queries
# Use LIMIT in queries
SELECT * FROM processes LIMIT 100;
-- Limits results

# Add indexes for frequently queried tables
# In osqueryi:
.timer on
-- Shows query execution time

# Adjust daemon settings
sudo nano /etc/osquery/osquery.conf
# Increase worker_threads value

# Monitor query performance
SELECT name, executions, wall_time, user_time 
FROM osquery_schedule 
ORDER BY wall_time DESC;
-- Shows slow queries

Problem 3: “Can’t see certain tables”

# Solution: Check table availability
# List all available tables
osqueryi ".tables"
-- Shows all tables

# Check table schema
osqueryi ".schema processes"
-- Shows table structure

# Some tables need specific flags
sudo nano /etc/osquery/osquery.flags
# Add flags like:
--enable_file_events
--disable_audit=false

# Restart to apply
sudo systemctl restart osqueryd

Problem 4: “Results not logging”

# Solution: Fix logging configuration
# Check log directory exists
sudo mkdir -p /var/log/osquery
sudo chown root:root /var/log/osquery

# Verify logging is enabled
grep "disable_logging" /etc/osquery/osquery.conf
# Should be "false"

# Test with manual query
echo "SELECT * FROM users;" | osqueryi --json
# Should output JSON

# Check daemon logs
sudo journalctl -u osqueryd -n 50
# Shows service logs

📋 Simple Commands Summary

Your Osquery SQL cheat sheet - bookmark this! 📌

💡 Tips for Success

Ready to become an Osquery ninja? Here are pro tips! 🥷

Query Optimization

• 🎯 Always use WHERE clauses to filter results
• 📊 Use LIMIT to prevent overwhelming output
• 🔄 JOIN carefully - it can be expensive
• ⏰ Schedule heavy queries during off-peak hours

Security Hunting Queries

-- Find world-writable files
SELECT * FROM file 
WHERE path LIKE '/etc/%' 
AND mode LIKE '%7';

-- Detect cryptocurrency miners
SELECT * FROM processes 
WHERE name LIKE '%miner%' 
OR cmdline LIKE '%pool%' 
OR cmdline LIKE '%xmr%';

-- Check for persistence mechanisms
SELECT * FROM startup_items;

Integration Ideas

• 📊 Send results to Elasticsearch
• 🔔 Alert on suspicious findings via webhook
• 📈 Create Grafana dashboards
• 🤖 Automate response with scripts

Monitoring Best Practices

# Create daily security report
cat << 'EOF' > /etc/cron.daily/osquery-report
#!/bin/bash
osqueryi --json "SELECT * FROM users WHERE uid >= 1000;" > /tmp/users.json
osqueryi --json "SELECT * FROM listening_ports;" > /tmp/ports.json
# Process and email results
EOF
chmod +x /etc/cron.daily/osquery-report

🏆 What You Learned

Outstanding work! Look at your achievements! 🎊 You’re now an Osquery expert:

• ✅ Installed and configured Osquery on AlmaLinux
• ✅ Created scheduled queries for continuous monitoring
• ✅ Built custom query packs for security
• ✅ Mastered SQL-based system investigation
• ✅ Set up real-time threat detection
• ✅ Learned performance monitoring queries
• ✅ Configured logging and alerting
• ✅ Troubleshot common issues
• ✅ Integrated incident response capabilities
• ✅ Built enterprise-grade monitoring

🎯 Why This Matters

You’ve just deployed the same technology Facebook uses to monitor thousands of servers! 🚀 Osquery transforms complex system investigation into simple SQL queries that anyone can understand and use.

No more memorizing obscure Linux commands or writing complex scripts. You can now ask your system questions in plain SQL and get instant answers. Whether you’re hunting for malware, investigating incidents, or just monitoring performance, Osquery makes it intuitive and fast.

Your AlmaLinux system is now transparent and queryable. You have visibility into every process, connection, file, and configuration. Security investigations that used to take hours now take seconds. You’re not just monitoring - you’re actively hunting threats with the power of SQL! 💪

Keep querying, keep investigating, and remember - with Osquery, your system has no secrets! You’ve got this! ⭐

Happy hunting, AlmaLinux security investigator! 🙌