ETL Session 05: Infrastructure ETL

Production patterns. This session applies ETL skills to real infrastructure data from ISE, Kubernetes, network devices, and SIEM systems.

Pre-Session State

  • Can build API-to-report pipelines

  • Understand data enrichment patterns

  • Know jq join operations

Pattern 1: ISE Session Analysis

Sample Data

cat > /tmp/ise-sessions.json << 'EOF'
[
  {"calling_station_id": "AA:BB:CC:11:22:33", "framed_ip_address": "10.50.10.100",
   "user_name": "evanusmodestus", "nas_ip_address": "10.50.1.10",
   "network_device_name": "switch-3560", "auth_method": "EAP-TLS",
   "posture_status": "Compliant", "session_state": "Authenticated"},
  {"calling_station_id": "DD:EE:FF:44:55:66", "framed_ip_address": "10.50.10.101",
   "user_name": "guest", "nas_ip_address": "10.50.1.10",
   "network_device_name": "switch-3560", "auth_method": "MAB",
   "posture_status": "Unknown", "session_state": "Authenticated"},
  {"calling_station_id": "11:22:33:AA:BB:CC", "framed_ip_address": "",
   "user_name": "contractor", "nas_ip_address": "10.50.1.11",
   "network_device_name": "ap-9120", "auth_method": "PEAP",
   "posture_status": "NonCompliant", "session_state": "Failed"}
]
EOF

Exercise: Sessions by Auth Method

cat /tmp/ise-sessions.json | jq -r '
  group_by(.auth_method) |
  map({method: .[0].auth_method, count: length, users: [.[].user_name]}) |
  .[] | "\(.method): \(.count) sessions - \(.users | join(", "))"'

Exercise: Failed Authentication Report

echo "# Failed Authentication Report"
echo ""
cat /tmp/ise-sessions.json | jq -r '
  .[] | select(.session_state == "Failed") |
  "- **\(.user_name)** via \(.auth_method) on \(.network_device_name)"'

Exercise: Posture Compliance Summary

cat /tmp/ise-sessions.json | jq '
  group_by(.posture_status) |
  map({status: .[0].posture_status, count: length}) |
  sort_by(-.count)'

Pattern 2: Kubernetes Pod Analysis

Sample Data

cat > /tmp/k8s-pods.json << 'EOF'
{"items": [
  {"metadata": {"name": "nginx-abc123", "namespace": "web"},
   "status": {"phase": "Running", "containerStatuses": [{"ready": true, "restartCount": 0}]}},
  {"metadata": {"name": "api-def456", "namespace": "backend"},
   "status": {"phase": "Running", "containerStatuses": [{"ready": true, "restartCount": 12}]}},
  {"metadata": {"name": "db-ghi789", "namespace": "data"},
   "status": {"phase": "CrashLoopBackOff", "containerStatuses": [{"ready": false, "restartCount": 45}]}},
  {"metadata": {"name": "cache-jkl012", "namespace": "data"},
   "status": {"phase": "Pending", "containerStatuses": []}}
]}
EOF

Exercise: Unhealthy Pods

cat /tmp/k8s-pods.json | jq -r '
  .items[] |
  select(.status.phase != "Running" or
         (.status.containerStatuses[0].restartCount // 0) > 10) |
  "\(.metadata.namespace)/\(.metadata.name): \(.status.phase) (restarts: \(.status.containerStatuses[0].restartCount // 0))"'

Exercise: Pods by Namespace

cat /tmp/k8s-pods.json | jq '
  .items | group_by(.metadata.namespace) |
  map({
    namespace: .[0].metadata.namespace,
    total: length,
    healthy: [.[] | select(.status.phase == "Running")] | length
  })'

Exercise: Restart Alert Pipeline

cat /tmp/k8s-pods.json | jq -r '
  .items[] |
  select((.status.containerStatuses[0].restartCount // 0) > 10) |
  {
    pod: .metadata.name,
    namespace: .metadata.namespace,
    restarts: .status.containerStatuses[0].restartCount,
    severity: (if .status.containerStatuses[0].restartCount > 30 then "critical" else "warning" end)
  }' | jq -s '.'

Pattern 3: Network Device Cross-Join

Sample Data

cat > /tmp/switches.json << 'EOF'
[
  {"name": "switch-3560", "ip": "10.50.1.10", "model": "WS-C3560CX-8PC-S"},
  {"name": "switch-9300", "ip": "10.50.1.11", "model": "C9300-24P"}
]
EOF

Exercise: Enrich ISE Sessions with Switch Info

jq -s '
  .[0] as $sessions |
  .[1] as $switches |
  $sessions | map(. as $s |
    ($switches[] | select(.ip == $s.nas_ip_address)) as $sw |
    $s + {switch_name: ($sw.name // "unknown"), switch_model: ($sw.model // "unknown")}
  )
' /tmp/ise-sessions.json /tmp/switches.json | jq '.'

Exercise: Sessions per Switch Model

jq -s '
  .[0] as $sessions |
  .[1] as $switches |
  $sessions | map(. as $s |
    ($switches[] | select(.ip == $s.nas_ip_address)) as $sw |
    {model: ($sw.model // "unknown")}
  ) | group_by(.model) | map({model: .[0].model, count: length})
' /tmp/ise-sessions.json /tmp/switches.json

Pattern 4: SIEM Routing Logic

Exercise: Route Events by Severity

cat > /tmp/events.json << 'EOF'
[
  {"type": "auth_fail", "user": "admin", "source": "ise"},
  {"type": "config_change", "device": "switch-3560", "source": "syslog"},
  {"type": "traffic_allow", "src": "10.50.10.100", "source": "ftd"},
  {"type": "malware_detected", "host": "workstation-01", "source": "edr"}
]
EOF

# Route to Sentinel (high value) vs S3 (bulk storage)
cat /tmp/events.json | jq '
  .[] |
  {
    event: .,
    destination: (
      if .type | test("fail|malware|intrusion") then "sentinel"
      elif .type | test("config_change") then "sentinel"
      else "s3_archive"
      end
    )
  }'

Exercise: Transform for SIEM Ingestion

cat /tmp/ise-sessions.json | jq '
  .[] |
  {
    timestamp: now | strftime("%Y-%m-%dT%H:%M:%SZ"),
    source: "ise",
    event_type: "authentication",
    user: .user_name,
    endpoint_mac: .calling_station_id,
    endpoint_ip: .framed_ip_address,
    auth_method: .auth_method,
    result: .session_state,
    severity: (if .session_state == "Failed" then "high" else "info" end)
  }'

Summary: Infrastructure Patterns

Use Case Pattern

ISE sessions

netapi ise mnt sessions -f json | jq

k8s pods

kubectl get pods -o json | jq '.items[]'

Unhealthy pods

select(.status.phase != "Running")

High restarts

select(.containerStatuses[0].restartCount > N)

Cross-join

jq -s '.[0] as $a | .[1] as $b | …​'

SIEM routing

if .type | test("pattern") then "sentinel" else "archive"

Timestamp add

now | strftime("%Y-%m-%dT%H:%M:%SZ")

Exercises to Complete

  1. [ ] Build ISE compliance report (compliant vs non-compliant)

  2. [ ] Create k8s health dashboard (pods by status and namespace)

  3. [ ] Join ISE sessions with switch inventory

  4. [ ] Implement SIEM routing for your log sources

Related Resources

  • Drill 05 - Practice these patterns

  • netapi documentation for real API access

Session Log

Timestamp Notes

Start

<Record when you started>

End

<Record when you finished>