RADIUS Authentication View

# Recent authentications with color-coded status
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'HH24:MI:SS') as time,
    calling_station_id as mac,
    authentication_method as method,
    passed as status,
    SUBSTR(policy_set_name, 1, 25) as policy
  FROM mnt.radius_auth_48_live
  WHERE acs_timestamp > SYSDATE - 1/24
  ORDER BY acs_timestamp DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -r '
  "\u001b[1m  Time     │ MAC Address       │ Method     │ Policy\u001b[0m",
  "───────────┼───────────────────┼────────────┼─────────────────────────",
  (.[] |
    (if .status == 1 then "\u001b[32m✓\u001b[0m" else "\u001b[31m✗\u001b[0m" end) +
    " " + (.time // "     ") + " │ " +
    "\u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    (if .method == "EAP-TLS" then "\u001b[32m" elif .method == "PEAP" then "\u001b[33m" else "\u001b[34m" end) +
    (((.method // "") + "          ") | .[0:10]) + "\u001b[0m │ " +
    (.policy // "")
  )
'

# Hourly statistics with visual indicators
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(acs_timestamp, 'HH24'), 'HH24:MI') as hour,
    COUNT(*) as total,
    SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) as passed,
    SUM(CASE WHEN passed = 0 THEN 1 ELSE 0 END) as failed,
    ROUND(SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as pct,
    ROUND(AVG(CASE WHEN passed = 1 THEN response_time ELSE NULL END), 0) as avg_ms
  FROM mnt.radius_auth_48_live
  WHERE acs_timestamp > TRUNC(SYSDATE)
  GROUP BY TRUNC(acs_timestamp, 'HH24')
  ORDER BY hour
" | jq -r '
  (map(.total) | max) as $max |
  .[] |
  .hour + " │" +
  ((.total / $max * 20) | floor | . as $w |
    "\u001b[44m" + (" " * $w) + "\u001b[0m" + (" " * (20 - $w))) +
  "│ " +
  (if .pct >= 98 then "\u001b[32m" elif .pct >= 90 then "\u001b[33m" else "\u001b[31m" end) +
  (.pct | tostring) + "%\u001b[0m │ " +
  "T:" + (.total | tostring) + " P:" + (.passed | tostring) + " F:" + (.failed | tostring) +
  " │ " + (.avg_ms | tostring) + "ms"
'

# Auth history for specific MAC
MAC="C8:5B:76:C6:59:62"
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    nas_ip_address as nas,
    authentication_method as method,
    passed as status,
    CASE WHEN passed = 0 THEN failure_reason ELSE NULL END as failure
  FROM mnt.radius_auth_48_live
  WHERE calling_station_id = '${MAC}'
  AND acs_timestamp > SYSDATE - 2
  ORDER BY acs_timestamp DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  ["TIMESTAMP", "NAS", "METHOD", "STATUS", "FAILURE"],
  (.[] | [.timestamp, .nas, .method, (if .status == 1 then "PASS" else "FAIL" end), (.failure // "-")]) |
  @tsv
' | column -t -s$'\t'

# Auth history for specific user (supports partial match)
USER="john.doe"
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    calling_station_id as mac,
    nas_ip_address as nas,
    passed as status,
    selected_azn_profiles as authz
  FROM mnt.radius_auth_48_live
  WHERE LOWER(user_name) LIKE LOWER('%${USER}%')
  AND acs_timestamp > SYSDATE - 2
  ORDER BY acs_timestamp DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -C '.'

# All authentications from a specific switch/WLC
NAS_IP="10.50.1.200"
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'HH24:MI') as time,
    calling_station_id as mac,
    nas_port_id as port,
    authentication_method as method,
    passed as status
  FROM mnt.radius_auth_48_live
  WHERE nas_ip_address = '${NAS_IP}'
  AND acs_timestamp > SYSDATE - 1
  ORDER BY acs_timestamp DESC
  FETCH FIRST 100 ROWS ONLY
" | jq -r '
  "\u001b[1mTime  │ MAC Address       │ Port                 │ Method     │ Status\u001b[0m",
  "──────┼───────────────────┼──────────────────────┼────────────┼───────",
  (.[] |
    (.time // "     ") + " │ " +
    "\u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    "\u001b[35m" + (((.port // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    (((.method // "") + "          ") | .[0:10]) + " │ " +
    (if .status == 1 then "\u001b[32mPASS\u001b[0m" else "\u001b[31mFAIL\u001b[0m" end)
  )
'

# Detailed failure analysis with grouping
netapi ise dc --format json query "
  SELECT
    failure_reason,
    COUNT(*) as count,
    COUNT(DISTINCT calling_station_id) as unique_macs,
    COUNT(DISTINCT nas_ip_address) as unique_nas,
    MIN(TO_CHAR(acs_timestamp, 'HH24:MI')) as first_seen,
    MAX(TO_CHAR(acs_timestamp, 'HH24:MI')) as last_seen
  FROM mnt.radius_auth_48_live
  WHERE passed = 0
  AND acs_timestamp > SYSDATE - 1
  GROUP BY failure_reason
  ORDER BY count DESC
  FETCH FIRST 15 ROWS ONLY
" | jq -r '
  "┌─────────────────────────────────────────────────────────┬───────┬──────┬─────┬───────────────┐",
  "│ Failure Reason                                          │ Count │ MACs │ NAS │ Time Range    │",
  "├─────────────────────────────────────────────────────────┼───────┼──────┼─────┼───────────────┤",
  (.[] |
    "│ " +
    (if .count > 100 then "\u001b[31;1m" elif .count > 50 then "\u001b[31m" elif .count > 10 then "\u001b[33m" else "\u001b[0m" end) +
    (.failure_reason | . + "                                                         "[0:55-length]) +
    "\u001b[0m │" +
    (("     " + (.count|tostring))[-5:]) + " │" +
    (("    " + (.unique_macs|tostring))[-4:]) + " │" +
    (("   " + (.unique_nas|tostring))[-3:]) + " │ " +
    .first_seen + "-" + .last_seen + " │"
  ),
  "└─────────────────────────────────────────────────────────┴───────┴──────┴─────┴───────────────┘"
'

# EAP-TLS authentications with certificate details
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'HH24:MI:SS') as time,
    calling_station_id as mac,
    user_name as identity,
    nas_ip_address as nas,
    passed as status,
    response_time as ms
  FROM mnt.radius_auth_48_live
  WHERE authentication_method = 'EAP-TLS'
  AND acs_timestamp > SYSDATE - 1
  ORDER BY acs_timestamp DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  "\u001b[1m  Time     │ MAC Address       │ Identity                       │ NAS          │ Response\u001b[0m",
  "───────────┼───────────────────┼────────────────────────────────┼──────────────┼──────────",
  (.[] |
    "\u001b[32m●\u001b[0m " + (.time // "        ") + " │ " +
    "\u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    "\u001b[33m" + (((.identity // "") + "                              ") | .[0:30]) + "\u001b[0m │ " +
    (((.nas // "") + "            ") | .[0:12]) + " │ " +
    (if .status == 1 then "\u001b[32m" + ((.ms // 0)|tostring) + "ms\u001b[0m" else "\u001b[31mFAIL\u001b[0m" end)
  )
'

# Authentication distribution across PSN nodes
netapi ise dc --format json query "
  SELECT
    network_device_name as psn,
    COUNT(*) as auths,
    SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) as passed,
    ROUND(AVG(response_time), 0) as avg_ms,
    MAX(response_time) as max_ms
  FROM mnt.radius_auth_48_live
  WHERE acs_timestamp > SYSDATE - 1
  GROUP BY network_device_name
  ORDER BY auths DESC
" | jq -r '
  (map(.auths) | add) as $total |
  "┌────────────────────────────┬─────────┬─────────┬────────┬────────┐",
  "│ Network Device             │   Auths │   Pass% │ Avg ms │ Max ms │",
  "├────────────────────────────┼─────────┼─────────┼────────┼────────┤",
  (.[] |
    "│ \u001b[36m" + (((.psn // "") + "                            ") | .[0:26]) + "\u001b[0m │" +
    (("       " + ((.auths // 0)|tostring))[-7:]) + " │" +
    (("       " + (((.passed // 0) / ((.auths // 1) | if . == 0 then 1 else . end) * 100) | floor | tostring))[-6:]) + "% │" +
    (("      " + ((.avg_ms // 0)|tostring))[-6:]) + " │" +
    (("      " + ((.max_ms // 0)|tostring))[-6:]) + " │"
  ),
  "├────────────────────────────┼─────────┼─────────┼────────┼────────┤",
  "│ \u001b[1mTOTAL\u001b[0m                      │" + (("       " + ($total|tostring))[-7:]) + " │         │        │        │",
  "└────────────────────────────┴─────────┴─────────┴────────┴────────┘"
'

# Policy set effectiveness report
netapi ise dc --format json query "
  SELECT
    policy_set_name as policy,
    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 success_pct,
    COUNT(DISTINCT authentication_method) as methods
  FROM mnt.radius_auth_48_live
  WHERE acs_timestamp > SYSDATE - 2
  GROUP BY policy_set_name
  ORDER BY total DESC
" | jq -r '
  "\u001b[1m  Policy                              │ Auths   │ Success │ Methods\u001b[0m",
  "──────────────────────────────────────┼─────────┼─────────┼─────────",
  (.[] |
    (if .success_pct >= 98 then "\u001b[32m●\u001b[0m"
     elif .success_pct >= 90 then "\u001b[33m●\u001b[0m"
     else "\u001b[31m●\u001b[0m" end) +
    " " + (((.policy // "") + "                                   ") | .[0:35]) +
    " │ " + ((.total // 0) | tostring) + " │ " +
    (if (.success_pct // 0) >= 98 then "\u001b[32m" elif (.success_pct // 0) >= 90 then "\u001b[33m" else "\u001b[31m" end) +
    ((.success_pct // 0) | tostring) + "%\u001b[0m │ " +
    ((.methods // 0) | tostring)
  )
'

# Export to CSV for external analysis
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    calling_station_id as mac,
    user_name as identity,
    nas_ip_address as nas,
    nas_port_id as port,
    authentication_method as method,
    authentication_protocol as protocol,
    policy_set_name as policy,
    selected_azn_profiles as authz_profile,
    passed as success,
    failure_reason as failure,
    response_time as response_ms,
    framed_ip_address as ip,
    network_device_name as nad
  FROM mnt.radius_auth_48_live
  WHERE acs_timestamp > SYSDATE - 1
  ORDER BY acs_timestamp DESC
" | jq -r '
  (.[0] | keys) as $k |
  ($k | @csv),
  (.[] | [.[$k[]]] | @csv)
' > /tmp/radius-auth-$(date +%Y%m%d).csv

echo "Exported $(wc -l < /tmp/radius-auth-$(date +%Y%m%d).csv) records"