Analytics Dashboard

# 7-Day authentication trend with visual bar chart
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'Dy DD-Mon') as day,
    COUNT(*) as total,
    SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END) as passed,
    ROUND(SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END)*100.0/COUNT(*), 1) as pct
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY TRUNC(TIMESTAMP_TIMEZONE), TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'Dy DD-Mon')
  ORDER BY TRUNC(TIMESTAMP_TIMEZONE)
" | jq -r '
  (. | map(.total) | max) as $max |
  .[] |
  .day + " │" +
  ((.total / $max * 40) | floor | . as $w |
    "\u001b[44m" + (" " * $w) + "\u001b[0m" + (" " * (40 - $w))) +
  "│ " + (.total | tostring) + " (" + (.pct | tostring) + "%)"
'

# Hour-of-day pattern heatmap
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'Dy') as dow,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') as hour,
    COUNT(*) as count
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'Dy'), TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24')
" | jq -r '
  (map(.count) | max) as $max |
  group_by(.dow) |
  "Hour: " + ([range(24) | tostring | ("0" + .)[-2:]] | join(" ")),
  (.[] |
    .[0].dow + ":  " +
    ([range(24) | . as $h |
      (.[0] | . as $day | [$day] | map(select(.hour == ($h | tostring | ("0" + .)[-2:]))) | .[0].count // 0) as $c |
      if $c == 0 then " · "
      elif $c < ($max * 0.25) then "\u001b[34m▪ \u001b[0m"
      elif $c < ($max * 0.50) then "\u001b[32m▪ \u001b[0m"
      elif $c < ($max * 0.75) then "\u001b[33m▪ \u001b[0m"
      else "\u001b[31m▪ \u001b[0m" end
    ] | join(""))
  )
'

# Detect endpoints with abnormal auth frequency
netapi ise dc --format json query "
  WITH daily_avg AS (
    SELECT
      CALLING_STATION_ID,
      AVG(cnt) as avg_daily
    FROM (
      SELECT CALLING_STATION_ID, TRUNC(TIMESTAMP_TIMEZONE) as dt, COUNT(*) as cnt
      FROM RADIUS_AUTHENTICATIONS
      WHERE TIMESTAMP_TIMEZONE BETWEEN SYSDATE - 8 AND SYSDATE - 1
      GROUP BY CALLING_STATION_ID, TRUNC(TIMESTAMP_TIMEZONE)
    )
    GROUP BY CALLING_STATION_ID
  ),
  today AS (
    SELECT CALLING_STATION_ID, COUNT(*) as today_count
    FROM RADIUS_AUTHENTICATIONS
    WHERE TIMESTAMP_TIMEZONE > TRUNC(SYSDATE)
    GROUP BY CALLING_STATION_ID
  )
  SELECT t.CALLING_STATION_ID as mac, t.today_count, ROUND(d.avg_daily, 1) as avg_daily
  FROM today t
  JOIN daily_avg d ON t.CALLING_STATION_ID = d.CALLING_STATION_ID
  WHERE t.today_count > d.avg_daily * 3
  ORDER BY t.today_count DESC
  FETCH FIRST 10 ROWS ONLY
" | jq -r '
  if length == 0 then
    "\u001b[32m✓ No anomalies detected\u001b[0m"
  else
    "\u001b[31m⚠ Anomalous endpoints:\u001b[0m",
    (.[] | "  \u001b[33m\(.mac)\u001b[0m: \(.today_count) today (avg: \(.avg_daily))")
  end
'

# Detect failure spikes (2x average)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE, 'HH24'), 'HH24:MI') as hour,
    SUM(CASE WHEN PASSED = 0 THEN 1 ELSE 0 END) as failures
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY TRUNC(TIMESTAMP_TIMEZONE, 'HH24')
  ORDER BY hour
" | jq -r '
  (map(.failures) | add / length) as $avg |
  (map(.failures) | add / length | . * 2) as $threshold |
  .[] |
  if .failures > $threshold then
    "\u001b[31m🚨 \(.hour): \(.failures) failures (threshold: \($threshold | floor))\u001b[0m"
  else empty end
'

# EAP-TLS adoption report with progress bars
echo -e "\n\033[1m📋 EAP-TLS Adoption Report\033[0m\n"

netapi ise dc --format json query "
  SELECT
    POLICY_SET_NAME,
    COUNT(*) as total,
    SUM(CASE WHEN AUTHENTICATION_METHOD = 'EAP-TLS' THEN 1 ELSE 0 END) as eaptls,
    ROUND(SUM(CASE WHEN AUTHENTICATION_METHOD = 'EAP-TLS' THEN 1 ELSE 0 END)*100.0/COUNT(*), 1) as eaptls_pct
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY POLICY_SET_NAME
  ORDER BY total DESC
" | jq -r '
  "┌─────────────────────────────────┬────────┬────────┬─────────┬──────────────────────┐",
  "│ Policy Set                      │  Total │ EAP-TLS│    %    │ Progress             │",
  "├─────────────────────────────────┼────────┼────────┼─────────┼──────────────────────┤",
  (.[] |
    "│ " + (.POLICY_SET_NAME | . + "                                "[0:31-length]) +
    " │" + (("      " + (.total | tostring))[-7:]) +
    " │" + (("      " + (.eaptls | tostring))[-7:]) +
    " │" + (("      " + (.eaptls_pct | tostring))[-6:]) + "% │ " +
    ((.eaptls_pct / 5 | floor) as $bars |
      (if .eaptls_pct >= 80 then "\u001b[32m" elif .eaptls_pct >= 50 then "\u001b[33m" else "\u001b[31m" end) +
      ("█" * $bars) + ("░" * (20 - $bars)) + "\u001b[0m") + " │"
  ),
  "└─────────────────────────────────┴────────┴────────┴─────────┴──────────────────────┘"
'

# Endpoint compliance status
netapi ise dc --format json query "
  SELECT
    CASE
      WHEN AUTHENTICATION_METHOD = 'EAP-TLS' THEN 'Compliant (EAP-TLS)'
      WHEN AUTHENTICATION_METHOD IN ('PEAP', 'EAP-FAST') THEN 'Partial (Password)'
      WHEN AUTHENTICATION_METHOD = 'MAB' THEN 'Non-Compliant (MAB)'
      ELSE 'Unknown'
    END as compliance_status,
    COUNT(DISTINCT CALLING_STATION_ID) as endpoints
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY CASE
      WHEN AUTHENTICATION_METHOD = 'EAP-TLS' THEN 'Compliant (EAP-TLS)'
      WHEN AUTHENTICATION_METHOD IN ('PEAP', 'EAP-FAST') THEN 'Partial (Password)'
      WHEN AUTHENTICATION_METHOD = 'MAB' THEN 'Non-Compliant (MAB)'
      ELSE 'Unknown'
    END
  ORDER BY endpoints DESC
" | jq -r '
  (map(.endpoints) | add) as $total |
  .[] |
  (if .compliance_status | contains("Compliant") then "\u001b[32m●\u001b[0m"
   elif .compliance_status | contains("Partial") then "\u001b[33m●\u001b[0m"
   else "\u001b[31m●\u001b[0m" end) +
  " " + .compliance_status + ": " +
  (.endpoints | tostring) + " endpoints (" +
  ((.endpoints / $total * 100) | . * 10 | floor / 10 | tostring) + "%)"
'

# Response time histogram with awk
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    CASE
      WHEN RESPONSE_TIME < 50 THEN '0-50ms'
      WHEN RESPONSE_TIME < 100 THEN '50-100ms'
      WHEN RESPONSE_TIME < 200 THEN '100-200ms'
      WHEN RESPONSE_TIME < 500 THEN '200-500ms'
      ELSE '500ms+'
    END as bucket,
    COUNT(*) as count
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND RESPONSE_TIME IS NOT NULL
  GROUP BY CASE
      WHEN RESPONSE_TIME < 50 THEN '0-50ms'
      WHEN RESPONSE_TIME < 100 THEN '50-100ms'
      WHEN RESPONSE_TIME < 200 THEN '100-200ms'
      WHEN RESPONSE_TIME < 500 THEN '200-500ms'
      ELSE '500ms+'
    END
  ORDER BY MIN(RESPONSE_TIME)
" | jq -r '.[] | "\(.bucket),\(.count)"' | awk -F',' '
{
  bucket = $1
  count = $2
  if (count > max) max = count
  buckets[NR] = bucket
  counts[NR] = count
  n = NR
}
END {
  for (i=1; i<=n; i++) {
    bar_len = int(counts[i] / max * 40)
    printf "%-12s │", buckets[i]
    for (j=0; j<bar_len; j++) printf "█"
    printf " %d\n", counts[i]
  }
}'

# Top talkers by session bandwidth
netapi ise dc --format json query "
  SELECT
    CALLING_STATION_ID as mac,
    COUNT(*) as sessions,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024 / 1024, 2) as total_gb,
    ROUND(AVG(ACCT_SESSION_TIME) / 3600, 2) as avg_hours
  FROM RADIUS_ACCOUNTING
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND ACCT_STATUS_TYPE = 'Stop'
  GROUP BY CALLING_STATION_ID
  ORDER BY total_gb DESC
  FETCH FIRST 10 ROWS ONLY
" | jq -r '
  "┌──────────────────────┬──────────┬───────────┬────────────┐",
  "│ MAC Address          │ Sessions │ Total GB  │ Avg Hours  │",
  "├──────────────────────┼──────────┼───────────┼────────────┤",
  (.[] |
    "│ \u001b[36m\(.mac)\u001b[0m │" +
    (("        " + (.sessions | tostring))[-8:]) + " │" +
    (("         " + (.total_gb | tostring))[-9:]) + " │" +
    (("          " + (.avg_hours | tostring))[-10:]) + " │"
  ),
  "└──────────────────────┴──────────┴───────────┴────────────┘"
'