RADIUS Accounting View

# Currently active sessions (no Stop record yet)
netapi ise dc --format json query "
  SELECT
    s.CALLING_STATION_ID as mac,
    s.USERNAME as identity,
    s.NAS_IP_ADDRESS as nas,
    s.NAS_PORT_ID as port,
    s.FRAMED_IP_ADDRESS as ip,
    TO_CHAR(s.TIMESTAMP_TIMEZONE, 'HH24:MI') as started,
    ROUND((SYSDATE - s.TIMESTAMP_TIMEZONE) * 24 * 60, 0) as mins
  FROM RADIUS_ACCOUNTING s
  WHERE s.ACCT_STATUS_TYPE = 'Start'
  AND s.TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND NOT EXISTS (
    SELECT 1 FROM RADIUS_ACCOUNTING e
    WHERE e.ACCT_SESSION_ID = s.ACCT_SESSION_ID
    AND e.ACCT_STATUS_TYPE = 'Stop'
  )
  ORDER BY s.TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  "\u001b[1m  MAC Address       │ Identity                  │ NAS          │ Port             │ IP           │ Started\u001b[0m",
  "────────────────────┼───────────────────────────┼──────────────┼──────────────────┼──────────────┼─────────────",
  (.[] |
    "\u001b[32m●\u001b[0m " +
    "\u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    (((.identity // "") + "                         ") | .[0:25]) + " │ " +
    (((.nas // "") + "            ") | .[0:12]) + " │ " +
    "\u001b[35m" + (((.port // "") + "                ") | .[0:16]) + "\u001b[0m │ " +
    (((.ip // "N/A") + "            ") | .[0:12]) + " │ " +
    (.started // "") + " (" + ((.mins // 0) | tostring) + "m)"
  )
'

# Session history for specific MAC
MAC="C8:5B:76:C6:59:62"
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') as timestamp,
    ACCT_STATUS_TYPE as type,
    ROUND(ACCT_SESSION_TIME / 60, 1) as mins,
    ROUND((ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024, 2) as mb,
    ACCT_TERMINATE_CAUSE as term_cause
  FROM RADIUS_ACCOUNTING
  WHERE CALLING_STATION_ID = '${MAC}'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  .[] |
  (if .type == "Start" then "\u001b[32m▶\u001b[0m"
   elif .type == "Stop" then "\u001b[31m■\u001b[0m"
   else "\u001b[33m◆\u001b[0m" end) +
  " " + .timestamp + " │ " +
  (if .mins then (.mins | tostring) + " min" else "---" end) + " │ " +
  (if .mb then (.mb | tostring) + " MB" else "---" end) + " │ " +
  (.term_cause // "---")
'

# Session duration statistics with histogram
netapi ise dc --format json query "
  SELECT
    CASE
      WHEN ACCT_SESSION_TIME < 60 THEN '< 1 min'
      WHEN ACCT_SESSION_TIME < 300 THEN '1-5 min'
      WHEN ACCT_SESSION_TIME < 900 THEN '5-15 min'
      WHEN ACCT_SESSION_TIME < 3600 THEN '15-60 min'
      WHEN ACCT_SESSION_TIME < 14400 THEN '1-4 hrs'
      WHEN ACCT_SESSION_TIME < 28800 THEN '4-8 hrs'
      ELSE '8+ hrs'
    END as duration_bucket,
    COUNT(*) as sessions,
    ROUND(AVG(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024, 2) as avg_mb
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Stop'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY CASE
      WHEN ACCT_SESSION_TIME < 60 THEN '< 1 min'
      WHEN ACCT_SESSION_TIME < 300 THEN '1-5 min'
      WHEN ACCT_SESSION_TIME < 900 THEN '5-15 min'
      WHEN ACCT_SESSION_TIME < 3600 THEN '15-60 min'
      WHEN ACCT_SESSION_TIME < 14400 THEN '1-4 hrs'
      WHEN ACCT_SESSION_TIME < 28800 THEN '4-8 hrs'
      ELSE '8+ hrs'
    END
  ORDER BY MIN(ACCT_SESSION_TIME)
" | jq -r '
  "\u001b[1mDuration   │                              │ Sessions   │ Avg MB\u001b[0m",
  "───────────┼──────────────────────────────┼────────────┼───────",
  ((map(.sessions) | max) as $max |
    .[] |
    (((.duration_bucket // "") + "          ") | .[0:10]) + " │" +
    (((.sessions // 0) / ($max | if . == 0 then 1 else . end) * 30) | floor | . as $w |
      "\u001b[42m" + (" " * $w) + "\u001b[0m" + (" " * (30 - $w))) +
    "│ " + ((.sessions // 0) | tostring) + " │ " +
    ((.avg_mb // 0) | tostring)
  )
'

# Session termination cause analysis
netapi ise dc --format json query "
  SELECT
    ACCT_TERMINATE_CAUSE as reason,
    COUNT(*) as count,
    ROUND(AVG(ACCT_SESSION_TIME) / 60, 1) as avg_mins,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024, 0) as total_mb
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Stop'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND ACCT_TERMINATE_CAUSE IS NOT NULL
  GROUP BY ACCT_TERMINATE_CAUSE
  ORDER BY count DESC
" | jq -r '
  "\u001b[1m  Termination Cause         │ Sessions   │ Avg Duration │ Total MB\u001b[0m",
  "────────────────────────────┼────────────┼──────────────┼──────────",
  (.[] |
    (if .reason == "User-Request" then "\u001b[32m●\u001b[0m"
     elif .reason == "Session-Timeout" then "\u001b[33m●\u001b[0m"
     elif .reason == "Admin-Reset" then "\u001b[34m●\u001b[0m"
     elif .reason == "Lost-Carrier" then "\u001b[31m●\u001b[0m"
     else "\u001b[37m●\u001b[0m" end) +
    " " + (((.reason // "") + "                         ") | .[0:25]) +
    " │ " + ((.count // 0) | tostring) + " │ " +
    ((.avg_mins // 0) | tostring) + " min │ " +
    ((.total_mb // 0) | tostring)
  )
'

# Top bandwidth consumers (last 7 days)
netapi ise dc --format json query "
  SELECT
    USERNAME as identity,
    COUNT(*) as sessions,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024 / 1024, 2) as total_gb,
    ROUND(SUM(ACCT_SESSION_TIME) / 3600, 1) as total_hours,
    COUNT(DISTINCT CALLING_STATION_ID) as devices
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Stop'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND USERNAME IS NOT NULL
  GROUP BY USERNAME
  ORDER BY total_gb DESC
  FETCH FIRST 15 ROWS ONLY
" | jq -r '
  "┌────────────────────────────────┬──────────┬───────────┬────────────┬─────────┐",
  "│ Identity                       │ Sessions │  Total GB │ Total Hrs  │ Devices │",
  "├────────────────────────────────┼──────────┼───────────┼────────────┼─────────┤",
  (.[] |
    "│ \u001b[33m" + (((.identity // "") + "                              ") | .[0:30]) + "\u001b[0m │" +
    (("        " + ((.sessions // 0)|tostring))[-8:]) + " │" +
    (("         " + ((.total_gb // 0)|tostring))[-9:]) + " │" +
    (("          " + ((.total_hours // 0)|tostring))[-10:]) + " │" +
    (("       " + ((.devices // 0)|tostring))[-7:]) + " │"
  ),
  "└────────────────────────────────┴──────────┴───────────┴────────────┴─────────┘"
'

# Traffic per network device
netapi ise dc --format json query "
  SELECT
    NAS_IP_ADDRESS as nas,
    COUNT(*) as sessions,
    ROUND(SUM(ACCT_INPUT_OCTETS) / 1024 / 1024 / 1024, 2) as rx_gb,
    ROUND(SUM(ACCT_OUTPUT_OCTETS) / 1024 / 1024 / 1024, 2) as tx_gb,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_macs
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Stop'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY NAS_IP_ADDRESS
  ORDER BY (SUM(ACCT_INPUT_OCTETS) + SUM(ACCT_OUTPUT_OCTETS)) DESC
  FETCH FIRST 10 ROWS ONLY
" | jq -r '
  .[] |
  "\u001b[36m" + .nas + "\u001b[0m │ " +
  (.sessions | tostring) + " sessions │ " +
  "\u001b[32m↓\u001b[0m " + (.rx_gb | tostring) + " GB │ " +
  "\u001b[31m↑\u001b[0m " + (.tx_gb | tostring) + " GB │ " +
  (.unique_macs | tostring) + " MACs"
'

# Daily traffic trend with bar chart
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'Dy DD-Mon') as day,
    COUNT(*) as sessions,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024 / 1024, 2) as total_gb
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Stop'
  AND 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_gb) | max) as $max |
  .[] |
  .day + " │" +
  ((.total_gb / $max * 30) | floor | . as $w |
    "\u001b[46m" + (" " * $w) + "\u001b[0m" + (" " * (30 - $w))) +
  "│ " + (.total_gb | tostring) + " GB │ " + (.sessions | tostring) + " sessions"
'

# Interim update frequency analysis (CoA monitoring)
netapi ise dc --format json query "
  SELECT
    ACCT_SESSION_ID,
    CALLING_STATION_ID as mac,
    COUNT(*) as update_count,
    MIN(TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI')) as first_update,
    MAX(TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI')) as last_update,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024, 2) as total_mb
  FROM RADIUS_ACCOUNTING
  WHERE ACCT_STATUS_TYPE = 'Interim-Update'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY ACCT_SESSION_ID, CALLING_STATION_ID
  HAVING COUNT(*) > 5
  ORDER BY update_count DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -r '
  .[] |
  "\u001b[36m" + .mac + "\u001b[0m │ " +
  "\u001b[33m" + (.update_count | tostring) + " updates\u001b[0m │ " +
  .first_update + "-" + .last_update + " │ " +
  (.total_mb | tostring) + " MB"
'

# Export accounting data to CSV
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    CALLING_STATION_ID as mac,
    USERNAME as identity,
    NAS_IP_ADDRESS as nas,
    NAS_PORT_ID as port,
    ACCT_STATUS_TYPE as status_type,
    ACCT_SESSION_ID as session_id,
    ACCT_SESSION_TIME as duration_sec,
    ACCT_INPUT_OCTETS as rx_bytes,
    ACCT_OUTPUT_OCTETS as tx_bytes,
    ACCT_TERMINATE_CAUSE as term_cause,
    FRAMED_IP_ADDRESS as ip
  FROM RADIUS_ACCOUNTING
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  ORDER BY TIMESTAMP_TIMEZONE DESC
" | jq -r '
  (.[0] | keys) as $k |
  ($k | @csv),
  (.[] | [.[$k[]]] | @csv)
' > /tmp/radius-acct-$(date +%Y%m%d).csv

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