TACACS+ Views

# Recent device admin logins
netapi ise dc --format json query "
  SELECT
    TO_CHAR(GENERATED_TIME, 'HH24:MI:SS') as time,
    USERNAME as admin,
    DEVICE_NAME as device,
    DEVICE_IP as device_ip,
    REMOTE_ADDRESS as source_ip,
    STATUS as status
  FROM TACACS_AUTHENTICATION
  WHERE GENERATED_TIME > SYSDATE - 1
  ORDER BY GENERATED_TIME DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  "\u001b[1m  Time     │ Admin           │ Device               │ Device IP    │ Source\u001b[0m",
  "───────────┼─────────────────┼──────────────────────┼──────────────┼─────────────",
  (.[] |
    (if .status == "Pass" then "\u001b[32m✓\u001b[0m" else "\u001b[31m✗\u001b[0m" end) +
    " " + (.time // "        ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "               ") | .[0:15]) + "\u001b[0m │ " +
    "\u001b[36m" + (((.device // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    (((.device_ip // "") + "            ") | .[0:12]) + " │ " + (.source_ip // "")
  )
'

# Failed TACACS authentications (potential brute force)
netapi ise dc --format json query "
  SELECT
    USERNAME as admin,
    REMOTE_ADDRESS as source_ip,
    COUNT(*) as failures,
    COUNT(DISTINCT DEVICE_IP) as target_devices,
    MIN(TO_CHAR(GENERATED_TIME, 'HH24:MI')) as first_attempt,
    MAX(TO_CHAR(GENERATED_TIME, 'HH24:MI')) as last_attempt
  FROM TACACS_AUTHENTICATION
  WHERE STATUS = 'Fail'
  AND GENERATED_TIME > SYSDATE - 1
  GROUP BY USERNAME, REMOTE_ADDRESS
  HAVING COUNT(*) >= 3
  ORDER BY failures DESC
" | jq -r '
  if length == 0 then
    "\u001b[32m✓ No suspicious login patterns detected\u001b[0m"
  else
    "\u001b[31m⚠ Suspicious login activity:\u001b[0m",
    (.[] |
      "\u001b[31m●\u001b[0m " +
      "\u001b[33m" + .admin + "\u001b[0m from " + .source_ip +
      " │ " + (.failures | tostring) + " failures │ " +
      (.target_devices | tostring) + " devices │ " +
      .first_attempt + "-" + .last_attempt
    )
  end
'

# Command authorization audit trail
netapi ise dc --format json query "
  SELECT
    TO_CHAR(GENERATED_TIME, 'HH24:MI:SS') as time,
    USERNAME as admin,
    DEVICE_NAME as device,
    CMD as command,
    CMD_ARG as args,
    STATUS as status
  FROM TACACS_COMMAND_VIEW
  WHERE GENERATED_TIME > SYSDATE - 1
  AND CMD IS NOT NULL
  ORDER BY GENERATED_TIME DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  "\u001b[1m  Time     │ Admin      │ Device          │ Command\u001b[0m",
  "───────────┼────────────┼─────────────────┼─────────────────────────",
  (.[] |
    (if .status == "Pass" then "\u001b[32m✓\u001b[0m" else "\u001b[31m✗\u001b[0m" end) +
    " " + (.time // "        ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "          ") | .[0:10]) + "\u001b[0m │ " +
    "\u001b[36m" + (((.device // "") + "               ") | .[0:15]) + "\u001b[0m │ " +
    (.command // "") + " " + (.args // "")
  )
'

# Sensitive/dangerous commands executed
netapi ise dc --format json query "
  SELECT
    TO_CHAR(GENERATED_TIME, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    USERNAME as admin,
    DEVICE_NAME as device,
    DEVICE_IP as device_ip,
    CMD as command,
    CMD_ARG as args,
    STATUS as status
  FROM TACACS_COMMAND_VIEW
  WHERE (
    LOWER(CMD) LIKE '%write%'
    OR LOWER(CMD) LIKE '%copy%'
    OR LOWER(CMD) LIKE '%reload%'
    OR LOWER(CMD) LIKE '%delete%'
    OR LOWER(CMD) LIKE '%erase%'
    OR LOWER(CMD) LIKE '%debug%'
    OR LOWER(CMD) LIKE '%crypto%'
    OR LOWER(CMD) LIKE '%enable%'
    OR LOWER(CMD) LIKE '%username%'
    OR LOWER(CMD) LIKE '%aaa%'
  )
  AND GENERATED_TIME > SYSDATE - 7
  ORDER BY GENERATED_TIME DESC
  FETCH FIRST 100 ROWS ONLY
" | jq -r '
  .[] |
  (if .status == "Pass" then "\u001b[33m⚠\u001b[0m" else "\u001b[31m✗\u001b[0m" end) +
  " " + .timestamp + " │ " +
  "\u001b[33m" + .admin + "\u001b[0m │ " +
  "\u001b[36m" + .device + "\u001b[0m │ " +
  "\u001b[35m" + .command + " " + (.args // "") + "\u001b[0m"
'

# Admin activity summary (last 24h)
netapi ise dc --format json query "
  SELECT
    a.USERNAME as admin,
    COUNT(DISTINCT a.DEVICE_IP) as devices_accessed,
    COUNT(*) as login_count,
    SUM(CASE WHEN a.STATUS = 'Fail' THEN 1 ELSE 0 END) as failed_logins
  FROM TACACS_AUTHENTICATION a
  WHERE a.GENERATED_TIME > SYSDATE - 1
  GROUP BY a.USERNAME
  ORDER BY login_count DESC
" | jq -r '
  "┌──────────────────┬──────────┬─────────┬─────────┐",
  "│ Admin            │ Devices  │  Logins │  Failed │",
  "├──────────────────┼──────────┼─────────┼─────────┤",
  (.[] |
    "│ \u001b[33m" + (((.admin // "") + "                  ") | .[0:16]) + "\u001b[0m │" +
    (("        " + ((.devices_accessed // 0)|tostring))[-8:]) + " │" +
    (("       " + ((.login_count // 0)|tostring))[-7:]) + " │" +
    (if (.failed_logins // 0) > 0 then "\u001b[31m" else "" end) +
    (("       " + ((.failed_logins // 0)|tostring))[-7:]) +
    (if (.failed_logins // 0) > 0 then "\u001b[0m" else "" end) + " │"
  ),
  "└──────────────────┴──────────┴─────────┴─────────┘"
'

# Device access matrix (who accessed what)
netapi ise dc --format json query "
  SELECT
    DEVICE_NAME as device,
    USERNAME as admin,
    COUNT(*) as sessions,
    MAX(TO_CHAR(GENERATED_TIME, 'HH24:MI')) as last_access
  FROM TACACS_AUTHENTICATION
  WHERE STATUS = 'Pass'
  AND GENERATED_TIME > SYSDATE - 1
  GROUP BY DEVICE_NAME, USERNAME
  ORDER BY device, sessions DESC
" | jq -r '
  group_by(.device) | .[] |
  "\u001b[1m\u001b[36m" + .[0].device + "\u001b[0m",
  (sort_by(-.sessions) | .[] |
    "  \u001b[33m" + .admin + "\u001b[0m │ " +
    (.sessions | tostring) + " sessions │ last: " + .last_access
  ),
  ""
'

# Off-hours device access (potential unauthorized)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(GENERATED_TIME, 'YYYY-MM-DD HH24:MI') as timestamp,
    USERNAME as admin,
    DEVICE_NAME as device,
    REMOTE_ADDRESS as source_ip,
    STATUS as status
  FROM TACACS_AUTHENTICATION
  WHERE (
    TO_CHAR(GENERATED_TIME, 'HH24') < '06'
    OR TO_CHAR(GENERATED_TIME, 'HH24') >= '22'
    OR TO_CHAR(GENERATED_TIME, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')
  )
  AND GENERATED_TIME > SYSDATE - 7
  ORDER BY GENERATED_TIME DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  if length == 0 then
    "\u001b[32m✓ No off-hours access detected\u001b[0m"
  else
    "\u001b[33m⚠ Off-hours device access:\u001b[0m",
    (.[] |
      (if .status == "Pass" then "\u001b[33m●\u001b[0m" else "\u001b[31m●\u001b[0m" end) +
      " " + .timestamp + " │ " +
      "\u001b[33m" + .admin + "\u001b[0m │ " +
      "\u001b[36m" + .device + "\u001b[0m │ from " + .source_ip
    )
  end
'

# Export TACACS audit trail to CSV
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    'Authentication' as record_type,
    TO_CHAR(a.GENERATED_TIME, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    a.USERNAME as admin,
    a.DEVICE_NAME as device,
    a.DEVICE_IP as device_ip,
    a.REMOTE_ADDRESS as source_ip,
    NULL as command,
    a.STATUS as status,
    a.FAILURE_REASON as details
  FROM TACACS_AUTHENTICATION a
  WHERE a.GENERATED_TIME > SYSDATE - 7
  UNION ALL
  SELECT
    'Command' as record_type,
    TO_CHAR(c.GENERATED_TIME, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    c.USERNAME as admin,
    c.DEVICE_NAME as device,
    c.DEVICE_IP as device_ip,
    NULL as source_ip,
    c.CMD || ' ' || NVL(c.CMD_ARG, '') as command,
    c.STATUS as status,
    NULL as details
  FROM TACACS_COMMAND_VIEW c
  WHERE c.GENERATED_TIME > SYSDATE - 7
  ORDER BY timestamp DESC
" | jq -r '
  (.[0] | keys) as $k |
  ($k | @csv),
  (.[] | [.[$k[]]] | @csv)
' > /tmp/tacacs-audit-$(date +%Y%m%d).csv

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