ISE DataConnect Patterns

ISE DataConnect SQL patterns from production monitoring and analytics. Every entry has a date and context.

2026-04-02: Verify Endpoint Registration via DataConnect

Problem: After iPSK MAC registration via ERS, need to verify ISE processed it correctly.

Context: P16g deployment, endpoint verification after registration. ERS confirmed 201 Created, but need to verify ISE actually associated the MAC with the correct identity group and profiler policy.

The Fix:

# Built-in command — endpoint detail by MAC
netapi ise dc endpoint E0:D5:5D:6C:E1:66
# Authentication timeline — confirm iPSK PASSED
netapi ise dc auth-history E0:D5:5D:6C:E1:66 --hours 1
# Raw SQL — endpoint profiler data
netapi ise dc query "
SELECT mac_address, endpoint_profile, identity_group, create_time
FROM endpoints
WHERE mac_address = 'E0:D5:5D:6C:E1:66'
"
# Raw SQL — auth detail by MAC
netapi ise dc query "
SELECT username, mac_address, authentication_method,
       nas_ip_address, passed, failed, timestamp
FROM radius_authentications
WHERE mac_address = 'E0:D5:5D:6C:E1:66'
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC
"

Rule: ERS is for CRUD. DataConnect is for verification and analytics. Always verify ERS writes with a DataConnect query — the ERS 201 response only confirms the API accepted the request, not that ISE processed it correctly.

Worklog: WRKLOG-2026-04-02

2026-03-25: MSCHAPv2 Migration Audit Query

Problem: Need to identify endpoints still authenticating with MSCHAPv2 (target: migrate to EAP-TLS).

Context: CHLA MSCHAPv2-to-EAP-TLS migration project

The Fix:

# Built-in command — auth method distribution
netapi ise dc auth-methods
# SQL file — the full migration report
netapi ise dc query --file mschapv2_migration_enhanced
# Inline SQL — all MSCHAPv2 endpoints in last 7 days
netapi ise dc query "
SELECT mac_address, endpoint_profile, username,
       authentication_method, nas_ip_address,
       COUNT(*) as auth_count
FROM radius_authentications
WHERE authentication_method LIKE '%mschap%'
  AND timestamp > SYSDATE - 7
GROUP BY mac_address, endpoint_profile, username,
         authentication_method, nas_ip_address
ORDER BY auth_count DESC
"

Rule: DataConnect SQL against the radius_authentications table is the authoritative source for authentication method analysis. ISE GUI reports are limited; SQL is definitive. Use netapi ise dc query --list to see all 48 pre-built queries before writing custom SQL.

Worklog: WRKLOG-2026-03-25

Template for New Entries