Phase 4: Post-Renewal Monitoring

dsource d001 dev/network/ise

dc_query "
SELECT
    failure_reason,
    COUNT(*) AS count,
    MIN(timestamp_timezone) AS first_seen,
    MAX(timestamp_timezone) AS last_seen
FROM radius_authentications
WHERE failed > 0
  AND timestamp > SYSTIMESTAMP - INTERVAL '2' HOUR
GROUP BY failure_reason
ORDER BY count DESC
FETCH FIRST 20 ROWS ONLY
"

dc_query "
SELECT
    calling_station_id AS mac,
    username,
    failure_reason,
    nas_ip_address,
    timestamp_timezone
FROM radius_authentications
WHERE failed > 0
  AND timestamp > SYSTIMESTAMP - INTERVAL '2' HOUR
  AND (
      LOWER(failure_reason) LIKE '%certificate%'
      OR LOWER(failure_reason) LIKE '%trust%'
      OR LOWER(failure_reason) LIKE '%expired%'
      OR LOWER(failure_reason) LIKE '%chain%'
      OR LOWER(failure_reason) LIKE '%tls%'
      OR LOWER(failure_reason) LIKE '%ssl%'
  )
ORDER BY timestamp_timezone DESC
FETCH FIRST 50 ROWS ONLY
"

dc_query "
SELECT
    TRUNC(timestamp_timezone, 'HH24') AS hour,
    COUNT(*) AS total_auths,
    SUM(CASE WHEN passed = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN failed > 0 THEN 1 ELSE 0 END) AS failed,
    ROUND(SUM(CASE WHEN failed > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS fail_pct
FROM radius_authentications
WHERE timestamp > SYSTIMESTAMP - INTERVAL '48' HOUR
GROUP BY TRUNC(timestamp_timezone, 'HH24')
ORDER BY hour DESC
"

! ISE Admin GUI
Operations > RADIUS > Live Logs
  - Filter: Status = Failed
  - Look for: "12511 Unexpectedly received TLS alert message"
  - Look for: certificate chain / trust errors

! Enable CAPI2 log on a test Windows endpoint:
Event Viewer > Applications and Services Logs > Microsoft > Windows > CAPI2
  > Right-click Operational > Enable Log

! Look for certificate validation failures after connecting to 802.1X