Drill 05: Infrastructure ETL
Real infrastructure patterns: ISE sessions, k8s pods, network devices, SIEM routing.
Run This Drill
bash ~/atelier/_bibliotheca/domus-captures/docs/modules/ROOT/examples/etl-drills/05-infrastructure-etl.shDrill Script
#!/bin/bash
# ETL DRILL 05: INFRASTRUCTURE ETL
# Paste this entire script into your terminal
# Topics: Real patterns from ISE, k8s, netapi workflows
echo "=================================================================="
echo " ETL DRILL 05: INFRASTRUCTURE ETL "
echo "=================================================================="
echo ""
echo "Real-world patterns for infrastructure data pipelines"
echo ""
# Create realistic test data
cat << 'EOF' > /tmp/ise-sessions.json
{"sessions":[
{"macAddress":"00:11:22:33:44:55","ipAddress":"10.50.10.101","userName":"alice@INSIDE.DOMUSDIGITALIS.DEV","nasIpAddress":"10.50.1.10","authMethod":"dot1x","state":"AUTHENTICATED","sessionTime":3600,"failureReason":null},
{"macAddress":"AA:BB:CC:DD:EE:FF","ipAddress":"10.50.10.102","userName":"bob@INSIDE.DOMUSDIGITALIS.DEV","nasIpAddress":"10.50.1.11","authMethod":"dot1x","state":"AUTHENTICATED","sessionTime":1800,"failureReason":null},
{"macAddress":"11:22:33:44:55:66","ipAddress":"10.50.99.50","userName":null,"nasIpAddress":"10.50.1.10","authMethod":"mab","state":"AUTHENTICATED","sessionTime":600,"failureReason":null},
{"macAddress":"DE:AD:BE:EF:00:01","ipAddress":null,"userName":"mallory@INSIDE.DOMUSDIGITALIS.DEV","nasIpAddress":"10.50.1.12","authMethod":"dot1x","state":"FAILED","sessionTime":0,"failureReason":"Authentication failed: wrong password"}
]}
EOF
cat << 'EOF' > /tmp/k8s-pods.json
{"items":[
{"metadata":{"name":"wazuh-indexer-0","namespace":"wazuh","labels":{"app":"wazuh-indexer"}},"status":{"phase":"Running","containerStatuses":[{"ready":true,"restartCount":0}]}},
{"metadata":{"name":"wazuh-manager-0","namespace":"wazuh","labels":{"app":"wazuh-manager"}},"status":{"phase":"Running","containerStatuses":[{"ready":true,"restartCount":2}]}},
{"metadata":{"name":"prometheus-0","namespace":"monitoring","labels":{"app":"prometheus"}},"status":{"phase":"Running","containerStatuses":[{"ready":true,"restartCount":0}]}},
{"metadata":{"name":"grafana-abc123","namespace":"monitoring","labels":{"app":"grafana"}},"status":{"phase":"Pending","containerStatuses":[{"ready":false,"restartCount":5}]}}
]}
EOF
cat << 'EOF' > /tmp/switches.json
{"devices":[
{"hostname":"sw-access-01","ip":"10.50.1.10","model":"C9300-48P","ios":"17.6.4","uptime":864000,"interfaces":48,"vlans":[10,20,50,99]},
{"hostname":"sw-access-02","ip":"10.50.1.11","model":"C9300-48P","ios":"17.6.4","uptime":864000,"interfaces":48,"vlans":[10,20,50,99]},
{"hostname":"sw-core-01","ip":"10.50.1.1","model":"C9500-24Y4C","ios":"17.9.1","uptime":2592000,"interfaces":24,"vlans":[10,20,30,50,99]}
]}
EOF
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.1: ISE SESSION ANALYSIS"
echo "Common netapi ise mnt patterns"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Failed auth report (would be: netapi ise -f json mnt sessions)"
cat /tmp/ise-sessions.json | jq '.sessions[] | select(.state == "FAILED") | {
mac: .macAddress,
user: (.userName | split("@")[0]),
nas: .nasIpAddress,
reason: .failureReason
}'
echo ""
echo "Command: Session count by NAS (top talkers)"
cat /tmp/ise-sessions.json | jq '[.sessions[] | .nasIpAddress] | group_by(.) | map({nas: .[0], count: length}) | sort_by(.count) | reverse'
echo ""
echo "Command: Active users extract (clean domain)"
cat /tmp/ise-sessions.json | jq -r '[.sessions[] | select(.userName) | .userName | split("@")[0]] | unique | .[]'
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.2: KUBERNETES HEALTH CHECK"
echo "kubectl get pods -o json patterns"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Unhealthy pods"
cat /tmp/k8s-pods.json | jq '.items[] | select(.status.phase != "Running" or .status.containerStatuses[0].ready == false) | {
name: .metadata.name,
namespace: .metadata.namespace,
phase: .status.phase,
ready: .status.containerStatuses[0].ready,
restarts: .status.containerStatuses[0].restartCount
}'
echo ""
echo "Command: Pods with restarts > 0"
cat /tmp/k8s-pods.json | jq -r '.items[] | select(.status.containerStatuses[0].restartCount > 0) | "\(.metadata.namespace)/\(.metadata.name): \(.status.containerStatuses[0].restartCount) restarts"'
echo ""
echo "Command: Summary by namespace"
cat /tmp/k8s-pods.json | jq '.items | group_by(.metadata.namespace) | map({
namespace: .[0].metadata.namespace,
total: length,
running: [.[] | select(.status.phase == "Running")] | length,
unhealthy: [.[] | select(.status.phase != "Running" or .status.containerStatuses[0].ready == false)] | length
})'
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.3: NETWORK INVENTORY"
echo "Switch and infrastructure data"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Device summary (like netapi inventory)"
cat /tmp/switches.json | jq -r '.devices[] | "[\(.model)] \(.hostname) (\(.ip)) - IOS \(.ios), up \(.uptime/86400 | floor) days"'
echo ""
echo "Command: VLAN usage across switches"
cat /tmp/switches.json | jq '[.devices[].vlans] | flatten | unique | sort'
echo ""
echo "Command: Devices needing IOS upgrade (< 17.9)"
cat /tmp/switches.json | jq '.devices[] | select(.ios < "17.9") | {hostname: .hostname, current: .ios, target: "17.9.x"}'
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.4: CROSS-SYSTEM CORRELATION"
echo "Join data from multiple sources"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Map sessions to switch hostnames"
cat << 'JQSCRIPT' > /tmp/join.jq
# Load switches as lookup
(input | .devices | map({(.ip): .hostname}) | add) as $switches |
# Process sessions
input | .sessions[] | {
mac: .macAddress,
user: (.userName // "GUEST" | split("@")[0]),
switch: ($switches[.nasIpAddress] // "UNKNOWN"),
nas_ip: .nasIpAddress
}
JQSCRIPT
jq -n -f /tmp/join.jq /tmp/switches.json /tmp/ise-sessions.json
echo ""
echo "Command: Alerting summary (multi-source)"
cat << 'SCRIPT'
#!/bin/bash
echo "=== Infrastructure Health Check ==="
# ISE: Failed auths
FAILED=$(cat /tmp/ise-sessions.json | jq '[.sessions[] | select(.state == "FAILED")] | length')
if [ "$FAILED" -gt 0 ]; then
echo "⚠️ ISE: $FAILED failed authentications"
else
echo "✓ ISE: All authentications successful"
fi
# K8s: Unhealthy pods
UNHEALTHY=$(cat /tmp/k8s-pods.json | jq '[.items[] | select(.status.phase != "Running")] | length')
if [ "$UNHEALTHY" -gt 0 ]; then
echo "⚠️ K8s: $UNHEALTHY unhealthy pods"
else
echo "✓ K8s: All pods running"
fi
# Network: Old IOS
OLD_IOS=$(cat /tmp/switches.json | jq '[.devices[] | select(.ios < "17.9")] | length')
if [ "$OLD_IOS" -gt 0 ]; then
echo "⚠️ Network: $OLD_IOS devices need IOS upgrade"
else
echo "✓ Network: All devices on current IOS"
fi
SCRIPT
echo ""
echo "Executing..."
FAILED=$(cat /tmp/ise-sessions.json | jq '[.sessions[] | select(.state == "FAILED")] | length')
UNHEALTHY=$(cat /tmp/k8s-pods.json | jq '[.items[] | select(.status.phase != "Running")] | length')
OLD_IOS=$(cat /tmp/switches.json | jq '[.devices[] | select(.ios < "17.9")] | length')
echo "=== Infrastructure Health Check ==="
[ "$FAILED" -gt 0 ] && echo "⚠️ ISE: $FAILED failed authentications" || echo "✓ ISE: All authentications successful"
[ "$UNHEALTHY" -gt 0 ] && echo "⚠️ K8s: $UNHEALTHY unhealthy pods" || echo "✓ K8s: All pods running"
[ "$OLD_IOS" -gt 0 ] && echo "⚠️ Network: $OLD_IOS devices need IOS upgrade" || echo "✓ Network: All devices on current IOS"
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.5: MONAD-STYLE ROUTING"
echo "Transform + route pattern"
echo "------------------------------------------------------------------"
echo ""
echo "Command: Route based on content (Sentinel vs S3)"
cat /tmp/ise-sessions.json | jq '.sessions[] | {
data: .,
destination: (
if .state == "FAILED" then "SENTINEL"
elif .authMethod == "dot1x" and .state == "AUTHENTICATED" then "S3_ARCHIVE"
else "S3_ARCHIVE"
end
)
} | "[\(.destination)] \(.data.macAddress) - \(.data.state)"'
echo ""
echo "Command: Transform for SIEM (normalize field names)"
cat /tmp/ise-sessions.json | jq '.sessions[] | {
timestamp: now | todate,
event_type: "authentication",
source_mac: .macAddress,
source_ip: .ipAddress,
username: .userName,
auth_result: .state,
nas_device: .nasIpAddress,
failure_reason: .failureReason
}'
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "DRILL 5.6: DAILY REPORT SCRIPT"
echo "Complete infrastructure ETL"
echo "------------------------------------------------------------------"
echo ""
cat << 'REPORT'
#!/bin/bash
# Daily Infrastructure Report
# Run: ./daily-report.sh > /tmp/daily-$(date +%Y%m%d).md
cat << EOF
# Infrastructure Daily Report
Generated: $(date)
## ISE Authentication Summary
$(cat /tmp/ise-sessions.json | jq -r '"- Total sessions: \(.sessions | length)"')
$(cat /tmp/ise-sessions.json | jq -r '"- Authenticated: \([.sessions[] | select(.state == "AUTHENTICATED")] | length)"')
$(cat /tmp/ise-sessions.json | jq -r '"- Failed: \([.sessions[] | select(.state == "FAILED")] | length)"')
## Kubernetes Status
$(cat /tmp/k8s-pods.json | jq -r '.items | group_by(.metadata.namespace) | .[] | "- \(.[0].metadata.namespace): \(length) pods"')
## Network Devices
$(cat /tmp/switches.json | jq -r '"- Total devices: \(.devices | length)"')
$(cat /tmp/switches.json | jq -r '"- Needing upgrade: \([.devices[] | select(.ios < "17.9")] | length)"')
## Action Items
$(cat /tmp/ise-sessions.json | jq -r 'if ([.sessions[] | select(.state == "FAILED")] | length) > 0 then "- [ ] Investigate failed authentications" else empty end')
$(cat /tmp/k8s-pods.json | jq -r 'if ([.items[] | select(.status.phase != "Running")] | length) > 0 then "- [ ] Check unhealthy pods" else empty end')
$(cat /tmp/switches.json | jq -r 'if ([.devices[] | select(.ios < "17.9")] | length) > 0 then "- [ ] Schedule IOS upgrades" else empty end')
EOF
REPORT
echo "(Report template shown - demonstrates the pattern)"
echo ""
# ---------------------------------------------------------------------------
echo "------------------------------------------------------------------"
echo "YOUR TURN - TRY THESE:"
echo "------------------------------------------------------------------"
echo ""
echo "1. Find MACs on specific switch:"
echo " cat /tmp/ise-sessions.json | jq -r '.sessions[] | select(.nasIpAddress == \"10.50.1.10\") | .macAddress'"
echo ""
echo "2. k8s restart summary by app:"
echo " cat /tmp/k8s-pods.json | jq '.items | group_by(.metadata.labels.app) | map({app: .[0].metadata.labels.app, restarts: [.[].status.containerStatuses[0].restartCount] | add})'"
echo ""
echo "3. Generate ansible inventory from switches:"
echo " cat /tmp/switches.json | jq -r '.devices[] | \"\\(.hostname) ansible_host=\\(.ip) ios_version=\\(.ios)\"'"
echo ""
echo "------------------------------------------------------------------"
echo "KEY TAKEAWAYS:"
echo "1. netapi -f json | jq for all infrastructure CLIs"
echo "2. select() for filtering, group_by() for aggregation"
echo "3. Cross-system joins with jq -n -f script.jq"
echo "4. Transform + route for SIEM pipelines"
echo "5. Shell scripts orchestrate multi-source reports"
echo "------------------------------------------------------------------"