Drill 04: API to Report

Extract from APIs, enrich data, generate reports.

Run This Drill

bash ~/atelier/_bibliotheca/domus-captures/docs/modules/ROOT/examples/etl-drills/04-api-to-report.sh

Drill Script

#!/bin/bash
# ETL DRILL 04: API TO REPORT
# Paste this entire script into your terminal
# Topics: curl + jq, data enrichment, report generation

echo "=================================================================="
echo "             ETL DRILL 04: API TO REPORT                         "
echo "=================================================================="
echo ""
echo "Pattern: curl API → jq transform → format report"
echo ""

# Simulate API responses (since we can't hit real APIs)
cat << 'EOF' > /tmp/api-sessions.json
{
  "total": 4,
  "sessions": [
    {"macAddress": "00:11:22:33:44:55", "ipAddress": "10.50.10.101", "userName": "alice@EXAMPLE.COM", "authMethod": "dot1x", "state": "AUTHENTICATED", "nasIp": "10.50.1.10"},
    {"macAddress": "AA:BB:CC:DD:EE:FF", "ipAddress": "10.50.10.102", "userName": "bob@EXAMPLE.COM", "authMethod": "dot1x", "state": "AUTHENTICATED", "nasIp": "10.50.1.11"},
    {"macAddress": "11:22:33:44:55:66", "ipAddress": "10.50.99.50", "userName": null, "authMethod": "mab", "state": "AUTHENTICATED", "nasIp": "10.50.1.10"},
    {"macAddress": "DE:AD:BE:EF:00:01", "ipAddress": null, "userName": null, "authMethod": "dot1x", "state": "FAILED", "nasIp": "10.50.1.12"}
  ]
}
EOF

cat << 'EOF' > /tmp/api-nodes.json
{
  "nodes": [
    {"hostname": "ise-01", "ip": "10.50.1.20", "roles": ["pan", "mnt", "psn"], "version": "3.3.0.430"},
    {"hostname": "ise-02", "ip": "10.50.1.21", "roles": ["psn"], "version": "3.3.0.430"}
  ]
}
EOF

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.1: BASIC API → JSON EXTRACTION"
echo "curl + jq pattern"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Simulated API call (using cat)"
echo "cat /tmp/api-sessions.json | jq -r '.sessions[].userName // \"GUEST\"'"
cat /tmp/api-sessions.json | jq -r '.sessions[].userName // "GUEST"'
echo ""

echo "Real pattern would be:"
echo "curl -s -H 'Authorization: Bearer TOKEN' 'https://api.example.com/sessions' | jq '.sessions[]'"
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.2: DATA FILTERING"
echo "Extract specific records"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Find failed authentications"
echo "cat /tmp/api-sessions.json | jq '.sessions[] | select(.state == \"FAILED\")'"
cat /tmp/api-sessions.json | jq '.sessions[] | select(.state == "FAILED")'
echo ""

echo "Command: Find dot1x sessions only"
echo "cat /tmp/api-sessions.json | jq '[.sessions[] | select(.authMethod == \"dot1x\")] | length'"
cat /tmp/api-sessions.json | jq '[.sessions[] | select(.authMethod == "dot1x")] | length'
echo " dot1x sessions"
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.3: DATA ENRICHMENT"
echo "Add calculated fields"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Add status category"
cat /tmp/api-sessions.json | jq '.sessions[] | {
  mac: .macAddress,
  user: (.userName // "GUEST"),
  status: (if .state == "AUTHENTICATED" then "OK" else "ALERT" end),
  auth: .authMethod
}'
echo ""

echo "Command: Clean username (remove domain)"
cat /tmp/api-sessions.json | jq -r '.sessions[] | select(.userName) | .userName | split("@")[0]'
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.4: AGGREGATION"
echo "Summary statistics"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Count by auth method"
cat /tmp/api-sessions.json | jq '.sessions | group_by(.authMethod) | map({method: .[0].authMethod, count: length})'
echo ""

echo "Command: Count by NAS"
cat /tmp/api-sessions.json | jq '.sessions | group_by(.nasIp) | map({nas: .[0].nasIp, sessions: length})'
echo ""

echo "Command: Summary object"
cat /tmp/api-sessions.json | jq '{
  total: .total,
  authenticated: [.sessions[] | select(.state == "AUTHENTICATED")] | length,
  failed: [.sessions[] | select(.state == "FAILED")] | length,
  by_method: (.sessions | group_by(.authMethod) | map({(.[0].authMethod): length}) | add)
}'
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.5: REPORT GENERATION"
echo "Format for human consumption"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Plain text table"
echo "echo '=== ISE Sessions ==='"
echo '=== ISE Sessions ==='
echo "(echo 'MAC               IP              USER'; cat /tmp/api-sessions.json | jq -r '.sessions[] | [.macAddress, (.ipAddress // \"N/A\"), (.userName // \"GUEST\")] | @tsv') | column -t"
(echo 'MAC               IP              USER'; cat /tmp/api-sessions.json | jq -r '.sessions[] | [.macAddress, (.ipAddress // "N/A"), (.userName // "GUEST")] | @tsv') | column -t
echo ""

echo "Command: Markdown table"
echo '| MAC | IP | User | Status |'
echo '|-----|----|----|--------|'
cat /tmp/api-sessions.json | jq -r '.sessions[] | "| \(.macAddress) | \(.ipAddress // "N/A") | \(.userName // "GUEST") | \(.state) |"'
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.6: MULTI-API JOIN"
echo "Combine data from multiple sources"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Enrich sessions with NAS hostname"
cat << 'SCRIPT'
# Create NAS lookup
NAS_LOOKUP=$(cat /tmp/api-nodes.json | jq -r '.nodes[] | "\(.ip)=\(.hostname)"' | tr '\n' ' ')

# Process sessions with lookup
cat /tmp/api-sessions.json | jq -r '.sessions[] | [.nasIp, .macAddress, .state] | @tsv' | while read nas mac state; do
    hostname=$(echo "$NAS_LOOKUP" | grep -oE "$nas=[^ ]+" | cut -d= -f2)
    echo "$mac $state ${hostname:-UNKNOWN}"
done
SCRIPT
echo ""
echo "Executing..."
cat /tmp/api-sessions.json | jq -r '.sessions[] | [.nasIp, .macAddress, .state] | @tsv' | while read nas mac state; do
    hostname=$(cat /tmp/api-nodes.json | jq -r --arg ip "$nas" '.nodes[] | select(.ip == $ip) | .hostname // "UNKNOWN"')
    echo "$mac $state ${hostname:-UNKNOWN}"
done
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 4.7: COMPLETE REPORT SCRIPT"
echo "Full ETL pipeline"
echo "------------------------------------------------------------------"
echo ""
cat << 'SCRIPT'
#!/bin/bash
# ISE Session Report Generator

API_SESSIONS="/tmp/api-sessions.json"  # Would be: curl -s "$ISE_URL/sessions"
OUTPUT="/tmp/session_report.md"

{
    echo "# ISE Session Report"
    echo "Generated: $(date)"
    echo ""

    # Summary
    echo "## Summary"
    cat "$API_SESSIONS" | jq -r '"- Total sessions: \(.total)"'
    cat "$API_SESSIONS" | jq -r '"- Authenticated: \([.sessions[] | select(.state == "AUTHENTICATED")] | length)"'
    cat "$API_SESSIONS" | jq -r '"- Failed: \([.sessions[] | select(.state == "FAILED")] | length)"'
    echo ""

    # By auth method
    echo "## By Authentication Method"
    cat "$API_SESSIONS" | jq -r '.sessions | group_by(.authMethod) | .[] | "- \(.[0].authMethod): \(length)"'
    echo ""

    # Failed sessions (alert section)
    FAILED=$(cat "$API_SESSIONS" | jq '[.sessions[] | select(.state == "FAILED")] | length')
    if [ "$FAILED" -gt 0 ]; then
        echo "## ⚠️ Failed Sessions"
        cat "$API_SESSIONS" | jq -r '.sessions[] | select(.state == "FAILED") | "- MAC: \(.macAddress), NAS: \(.nasIp)"'
        echo ""
    fi

    # Detail table
    echo "## All Sessions"
    echo "| MAC | IP | User | Auth | Status |"
    echo "|-----|----|----|------|--------|"
    cat "$API_SESSIONS" | jq -r '.sessions[] | "| \(.macAddress) | \(.ipAddress // "N/A") | \(.userName // "GUEST" | split("@")[0]) | \(.authMethod) | \(.state) |"'

} > "$OUTPUT"

echo "Report written to $OUTPUT"
cat "$OUTPUT"
SCRIPT
echo ""
echo "Executing..."
API_SESSIONS="/tmp/api-sessions.json"
{
    echo "# ISE Session Report"
    echo "Generated: $(date)"
    echo ""
    echo "## Summary"
    cat "$API_SESSIONS" | jq -r '"- Total sessions: \(.total)"'
    echo ""
    echo "## All Sessions"
    echo "| MAC | User | Status |"
    echo "|-----|------|--------|"
    cat "$API_SESSIONS" | jq -r '.sessions[] | "| \(.macAddress) | \(.userName // "GUEST" | split("@")[0]) | \(.state) |"'
}
echo ""

# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "YOUR TURN - TRY THESE:"
echo "------------------------------------------------------------------"
echo ""
echo "1. Generate /etc/hosts from API:"
echo "   cat /tmp/api-nodes.json | jq -r '.nodes[] | \"\\(.ip) \\(.hostname)\"'"
echo ""
echo "2. Find PSN nodes only:"
echo "   cat /tmp/api-nodes.json | jq '.nodes[] | select(.roles | contains([\"psn\"])) | .hostname'"
echo ""
echo "3. Alert if any failed sessions:"
echo "   cat /tmp/api-sessions.json | jq -e '[.sessions[] | select(.state == \"FAILED\")] | length > 0' && echo 'ALERT: Failed sessions!'"
echo ""
echo "------------------------------------------------------------------"
echo "KEY TAKEAWAYS:"
echo "1. curl -s for silent API calls"
echo "2. jq // for defaults (null handling)"
echo "3. group_by + map for aggregation"
echo "4. @tsv | column -t for aligned tables"
echo "5. Shell loops for multi-API joins"
echo "------------------------------------------------------------------"