CMDB: Steampipe + Powerpipe

CMDB.sh

#!/bin/bash
# CMDB.sh - Service manager for Steampipe + Powerpipe
# Usage: CMDB.sh {start|stop|status|restart}

# Detect script directory
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

# Control files in the same directory as the script
PIDFILE="${SCRIPT_DIR}/powerpipe.pid"
LOGFILE="${SCRIPT_DIR}/powerpipe.log"

# Help function
usage() {
    echo "Usage: $0 {start|stop|status|restart} [--daemon]"
    echo ""
    echo "Commands:"
    echo "  start      Start services (interactive mode by default)"
    echo "  stop       Stop services"
    echo "  status     Show service status"
    echo "  restart    Restart services"
    echo ""
    echo "Options:"
    echo "  --daemon   Start in daemon mode (background) - use with systemd"
    exit 1
}

# Function to start the service
do_start() {
    local DAEMON_MODE="${1:-false}"

    echo "========================================"
    echo "  Steampipe + Powerpipe Warm-up Script  "
    echo "========================================"

    # Check if already running
    if [ -f "$PIDFILE" ]; then
        PID=$(cat "$PIDFILE")
        if ps -p "$PID" > /dev/null 2>&1; then
            echo "Powerpipe is already running (PID: $PID)"
            exit 1
        else
            rm -f "$PIDFILE"
        fi
    fi

    # Start Steampipe
    echo -e "\n[1/3] Starting Steampipe service..."
    steampipe service start 2>/dev/null || true
    sleep 3

    # Tables to warm up
    TABLES=(
        # Core
        "aws_account"
        "aws_region"

        # Lambda
        "aws_lambda_function"
        "aws_lambda_alias"
        "aws_lambda_event_source_mapping"
        "aws_lambda_function_url"
        "aws_lambda_layer"
        "aws_lambda_layer_version"
        "aws_lambda_permission"
        "aws_lambda_version"

        # S3 - Complete
        "aws_s3_bucket"
        "aws_s3_access_point"
        "aws_s3_bucket_intelligent_tiering_configuration"
        "aws_s3_bucket_lifecycle_configuration"
        "aws_s3_bucket_metric"
        "aws_s3_bucket_notification"
        "aws_s3_bucket_object"
        "aws_s3_bucket_policy"
        "aws_s3_bucket_replication_rule"
        "aws_s3_bucket_versioning"
        "aws_s3_bucket_website"
        "aws_s3_multi_region_access_point"
        "aws_s3_object_version"

        # SQS - Complete
        "aws_sqs_queue"
        "aws_sqs_queue_policy"

        # SNS
        "aws_sns_topic"
        "aws_sns_subscription"
        "aws_sns_topic_policy"

        # Secrets Manager
        "aws_secretsmanager_secret"
        "aws_secretsmanager_secret_version"

        # SSM
        "aws_ssm_parameter"

        # ECS
        "aws_ecs_cluster"
        "aws_ecs_service"
        "aws_ecs_task"
        "aws_ecs_task_definition"
        "aws_ecs_container_instance"
        "aws_ecs_task_set"

        # ECR
        "aws_ecr_repository"
        "aws_ecr_image"
        "aws_ecr_lifecycle_policy"
        "aws_ecr_repository_policy"

        # RDS
        "aws_rds_db_instance"
        "aws_rds_db_cluster"
        "aws_rds_reserved_db_instance"
        "aws_rds_db_snapshot"
        "aws_rds_db_cluster_snapshot"
        "aws_rds_db_parameter_group"
        "aws_rds_db_cluster_parameter_group"
        "aws_rds_db_subnet_group"
        "aws_rds_db_option_group"
        "aws_rds_db_event_subscription"

        # IAM
        "aws_iam_user"
        "aws_iam_role"
        "aws_iam_group"
        "aws_iam_policy"
        "aws_iam_access_key"

        # EC2
        "aws_ec2_instance"
        "aws_ebs_volume"
        "aws_ebs_snapshot"
        "aws_ec2_ami"

        # VPC
        "aws_vpc"
        "aws_vpc_subnet"
        "aws_vpc_security_group"

        # CloudWatch
        "aws_cloudwatch_log_group"
        "aws_cloudwatch_alarm"

        # KMS
        "aws_kms_key"
        "aws_kms_alias"

        # ACM
        "aws_acm_certificate"
    )

    echo -e "\n[2/3] Warming up cache (${#TABLES[@]} tables)..."
    echo "     Timeout: 45s per table"
    echo ""

    SUCCESS=0
    FAILED=0

    for table in "${TABLES[@]}"; do
        printf "  %-50s " "$table"

        # 45 seconds timeout per table
        timeout 45 steampipe query "select 1 from $table limit 1" > /dev/null 2>&1
        RESULT=$?

        if [ $RESULT -eq 0 ]; then
            echo "✓"
            SUCCESS=$((SUCCESS + 1))
        else
            echo "✗"
            FAILED=$((FAILED + 1))
        fi
    done

    echo ""
    echo "  Result: $SUCCESS ok / $FAILED failed"

    # Start Powerpipe in background
    echo -e "\n[3/3] Starting Powerpipe server..."
    echo "     Dashboard: http://localhost:9033"
    echo "     Log: $LOGFILE"
    echo ""

    # Clear previous log
    > "$LOGFILE"

    # Start Powerpipe in background and save PID
    powerpipe server > "$LOGFILE" 2>&1 &
    echo $! > "$PIDFILE"

    echo "Waiting for server to become available..."
    echo ""

    # Monitor log and wait for server to be ready
    TIMEOUT=120
    ELAPSED=0
    READY=false

    while [ $ELAPSED -lt $TIMEOUT ]; do
        # Check if process is still running
        if [ -f "$PIDFILE" ]; then
            PID=$(cat "$PIDFILE" 2>/dev/null)
            if [ -n "$PID" ] && ! ps -p "$PID" > /dev/null 2>&1; then
                echo ""
                echo "ERROR: Powerpipe terminated unexpectedly!"
                echo "Last log lines:"
                tail -20 "$LOGFILE"
                rm -f "$PIDFILE"
                exit 1
            fi
        fi

        # Show new log lines
        if [ -f "$LOGFILE" ]; then
            tail -n 1 "$LOGFILE" 2>/dev/null | grep -v "^$" || true
        fi

        # Check if server is ready (look for startup messages)
        if grep -q "Listening on" "$LOGFILE" 2>/dev/null || \
           grep -q "listening on" "$LOGFILE" 2>/dev/null || \
           grep -q "Server started" "$LOGFILE" 2>/dev/null || \
           curl -s http://localhost:9033 > /dev/null 2>&1; then
            READY=true
            break
        fi

        sleep 1
        ((ELAPSED++))
    done

    echo ""

    if [ "$READY" = true ]; then
        echo "=========================================="
        echo "  ✓ Powerpipe is ready!"
        echo "=========================================="
        echo ""
        echo "  PID: $(cat $PIDFILE)"
        echo "  Dashboard: http://localhost:9033"
        echo "  Log: $LOGFILE"
        echo ""

        if [ "$DAEMON_MODE" = "true" ]; then
            # Daemon mode: return after confirming it's running
            echo "Service started in daemon mode."
            echo "Use '$0 status' to check status."
            echo "Use '$0 stop' to stop the service."
        else
            # Interactive mode: keep log visible
            echo "Press Ctrl+C to stop the service or close the terminal."
            echo ""

            # Trap for cleanup when receiving Ctrl+C
            trap 'echo ""; echo "Interrupted. Stopping services..."; do_stop; exit 0' INT TERM

            # Keep process in foreground showing the log
            tail -f "$LOGFILE"
        fi
    else
        echo "ERROR: Timeout waiting for server to start!"
        echo "Last log lines:"
        tail -20 "$LOGFILE"

        # Clean up process
        if [ -f "$PIDFILE" ]; then
            kill $(cat "$PIDFILE") 2>/dev/null || true
            rm -f "$PIDFILE"
        fi
        exit 1
    fi
}

# Function to stop the service
do_stop() {
    echo "Stopping Powerpipe..."

    if [ ! -f "$PIDFILE" ]; then
        echo "Powerpipe is not running (PID file not found)"
        steampipe service stop 2>/dev/null || true
        return 1
    fi

    PID=$(cat "$PIDFILE")

    if ps -p "$PID" > /dev/null 2>&1; then
        kill "$PID"
        sleep 2

        # Force kill if still running
        if ps -p "$PID" > /dev/null 2>&1; then
            kill -9 "$PID" 2>/dev/null || true
        fi

        rm -f "$PIDFILE"
        echo "Powerpipe stopped (PID: $PID)"
    else
        echo "Process not found (PID: $PID)"
        rm -f "$PIDFILE"
    fi

    # Stop Steampipe
    echo "Stopping Steampipe service..."
    steampipe service stop 2>/dev/null || true

    echo "Services stopped!"
}

# Function to check status
do_status() {
    echo "Service status:"
    echo ""

    # Check Powerpipe
    if [ -f "$PIDFILE" ]; then
        PID=$(cat "$PIDFILE")
        if ps -p "$PID" > /dev/null 2>&1; then
            echo "  Powerpipe: RUNNING (PID: $PID)"
            echo "  Dashboard: http://localhost:9033"
        else
            echo "  Powerpipe: STOPPED (stale PID: $PID)"
        fi
    else
        echo "  Powerpipe: STOPPED"
    fi

    # Check Steampipe
    STEAMPIPE_STATUS=$(steampipe service status 2>&1)
    if echo "$STEAMPIPE_STATUS" | grep -q "not running"; then
        echo "  Steampipe: STOPPED"
    elif echo "$STEAMPIPE_STATUS" | grep -q "running"; then
        echo "  Steampipe: RUNNING"
    else
        echo "  Steampipe: UNKNOWN STATUS"
    fi
}

# Function to restart
do_restart() {
    local DAEMON_MODE="${1:-false}"
    echo "Restarting services..."
    do_stop
    sleep 2
    do_start "$DAEMON_MODE"
}

# Process arguments
DAEMON_MODE="false"

# Check if --daemon was passed
for arg in "$@"; do
    if [ "$arg" = "--daemon" ]; then
        DAEMON_MODE="true"
        break
    fi
done

case "${1:-}" in
    start)
        do_start "$DAEMON_MODE"
        ;;
    stop)
        do_stop
        ;;
    status)
        do_status
        ;;
    restart)
        do_restart "$DAEMON_MODE"
        ;;
    *)
        usage
        ;;
esac

exit 0

powerpipe.service

[Unit]
Description=Powerpipe Dashboard Server with Steampipe Cache Warm-up
Documentation=https://powerpipe.io/
After=network-online.target
Wants=network-online.target

[Service]
Type=forking
User=YOUR_USER
Group=YOUR_USER
WorkingDirectory=/path/to/your/project

# PID file managed by the script (in project directory)
PIDFile=/path/to/your/project/powerpipe.pid

# Commands for start, stop and reload
ExecStart=/path/to/your/project/CMDB.sh start --daemon
ExecStop=/path/to/your/project/CMDB.sh stop
ExecReload=/path/to/your/project/CMDB.sh restart --daemon

# Automatic restart on failure
Restart=on-failure
RestartSec=10

# Operation timeouts
TimeoutStartSec=600
TimeoutStopSec=60

# Logs
StandardOutput=journal
StandardError=journal
SyslogIdentifier=powerpipe

# Resource limits
LimitNOFILE=65536

# Security
PrivateTmp=true
NoNewPrivileges=true

[Install]
WantedBy=multi-user.target

SYSTEMD_INSTALL.md

Powerpipe Systemd Service Installation

Prerequisites

• Steampipe installed
• Powerpipe installed
• sudo permissions

Environment Variables

Define the following variables before proceeding:

# Directory where the project is located
PROJECT_DIR="/path/to/your/project"

# Your system user
YOUR_USER="$(whoami)"

Installation

1. Make the script executable

chmod +x ${PROJECT_DIR}/CMDB.sh

2. Configure the service file

Edit the powerpipe.service file and replace the placeholders:

# Replace YOUR_USER with your username
sed -i "s|YOUR_USER|${YOUR_USER}|g" ${PROJECT_DIR}/powerpipe.service

# Replace the project path (appears 3 times in the file)
sed -i "s|/path/to/your/project|${PROJECT_DIR}|g" ${PROJECT_DIR}/powerpipe.service

3. Install the systemd service

# Copy the service file to systemd
sudo cp ${PROJECT_DIR}/powerpipe.service /etc/systemd/system/

# Reload systemd
sudo systemctl daemon-reload

# Enable the service to start on boot
sudo systemctl enable powerpipe.service

Usage

Start the service

sudo systemctl start powerpipe

Stop the service

sudo systemctl stop powerpipe

Check status

sudo systemctl status powerpipe

Restart the service

sudo systemctl restart powerpipe

View logs in real-time

# Systemd logs
sudo journalctl -u powerpipe -f

# Powerpipe log
tail -f ${PROJECT_DIR}/powerpipe.log

Disable automatic startup

sudo systemctl disable powerpipe

Manual Script Usage (without systemd)

You can also use the script directly:

# Navigate to the project directory
cd ${PROJECT_DIR}

# Start in interactive mode (default)
# Shows progress and keeps log visible until Ctrl+C
./CMDB.sh start

# Start in daemon mode (background)
# Starts, waits until ready, and returns to terminal
./CMDB.sh start --daemon

# Stop
./CMDB.sh stop

# Status
./CMDB.sh status

# Restart in interactive mode
./CMDB.sh restart

# Restart in daemon mode
./CMDB.sh restart --daemon

Difference between interactive and daemon modes

Interactive Mode (default):

• Warms up Steampipe cache
• Starts Powerpipe and waits until available
• Shows output in real-time
• Keeps terminal open with visible log
• Press Ctrl+C to stop services
• Ideal for development and testing

Daemon Mode (--daemon):

• Warms up Steampipe cache
• Starts Powerpipe and waits until available
• Confirms it's running
• Returns to terminal (service in background)
• Use ./CMDB.sh stop to stop
• Ideal for systemd or production use

Accessing the Dashboard

After starting the service, the dashboard will be available at:

http://localhost:9033

Important Files

• Script: ${PROJECT_DIR}/CMDB.sh
• Service: /etc/systemd/system/powerpipe.service
• PID File: ${PROJECT_DIR}/powerpipe.pid
• Log File: ${PROJECT_DIR}/powerpipe.log

Note: PID and Log files are automatically created in the project directory, requiring no special root permissions.

Troubleshooting

Check if the service is running

sudo systemctl status powerpipe
ps aux | grep powerpipe
cat ${PROJECT_DIR}/powerpipe.pid

Check error logs

# Systemd logs
sudo journalctl -u powerpipe -n 50 --no-pager

# Powerpipe log
tail -n 50 ${PROJECT_DIR}/powerpipe.log

# Follow log in real-time
tail -f ${PROJECT_DIR}/powerpipe.log

Remove stale PID

If the service won't start due to an old PID:

rm -f ${PROJECT_DIR}/powerpipe.pid
sudo systemctl start powerpipe

Check if ports are in use

sudo netstat -tulpn | grep 9033
sudo lsof -i :9033