ISE DataConnect Query Codex

-- Simple inventory
-- File: endpoints/endpoint-inventory-simple.sql
SELECT mac_address, endpoint_profile, identity_group
FROM endpoints
FETCH FIRST 100 ROWS ONLY

-- Endpoints by profiler policy
-- File: endpoints/endpoints-by-profile.sql
SELECT endpoint_profile, COUNT(*) as count
FROM endpoints
GROUP BY endpoint_profile
ORDER BY count DESC

-- Stale endpoints (not seen in N days)
-- File: endpoints/endpoints-no-sessions.sql
SELECT mac_address, endpoint_profile, last_seen
FROM endpoints
WHERE last_seen < SYSDATE - 30

-- Auth method breakdown
-- File: auth/ise-auth-methods.sql
SELECT authentication_method, COUNT(*) as total,
       SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) as passed,
       SUM(CASE WHEN failed = 1 THEN 1 ELSE 0 END) as failed
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
GROUP BY authentication_method
ORDER BY total DESC

-- Failed auths last 24h
-- File: auth/ise-failed-auths-24h.sql
SELECT username, mac_address, failure_reason, nas_ip_address, timestamp
FROM radius_authentications
WHERE failed = 1 AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC

-- Hourly activity pattern
-- File: auth/ise-hourly-activity.sql
SELECT TO_CHAR(timestamp, 'HH24') as hour,
       COUNT(*) as total,
       SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) as passed,
       SUM(CASE WHEN failed = 1 THEN 1 ELSE 0 END) as failed
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
GROUP BY TO_CHAR(timestamp, 'HH24')
ORDER BY hour

-- Active sessions with traffic data
-- File: sessions/active-sessions.sql
-- NOTE: Wireless shows 0 packets if accounting-interim not enabled on WLC
SELECT mac_address, username, nas_ip_address, ip_address,
       input_packets, output_packets, total_mb,
       session_start, update_count
FROM radius_accounting
WHERE stopped_flag = 0
ORDER BY session_start DESC

-- All MSCHAPv2 endpoints (THE report the team needs)
-- File: migration/mschapv2_migration_enhanced.sql
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

-- Unique MSCHAPv2 devices
-- File: migration/mschapv2_unique_devices.sql
SELECT DISTINCT mac_address, endpoint_profile
FROM radius_authentications
WHERE authentication_method LIKE '%mschap%'
  AND timestamp > SYSDATE - 30

-- Switch activity summary
-- File: network/ise-switch-activity.sql
SELECT nas_ip_address, COUNT(*) as total_auths,
       COUNT(DISTINCT mac_address) as unique_macs
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
GROUP BY nas_ip_address
ORDER BY total_auths DESC

-- Hub detection (multiple MACs on one port)
-- File: network/ise-hub-detection.sql
SELECT nas_ip_address, nas_port_id,
       COUNT(DISTINCT mac_address) as mac_count,
       LISTAGG(DISTINCT mac_address, ', ') as macs
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
GROUP BY nas_ip_address, nas_port_id
HAVING COUNT(DISTINCT mac_address) > 1
ORDER BY mac_count DESC

-- Profiler summary (device type distribution)
-- File: profiler/profiler-endpoints-master.sql
SELECT endpoint_profile, COUNT(*) as count
FROM profiler_endpoints
GROUP BY endpoint_profile
ORDER BY count DESC

-- Discover available tables/views
-- File: diagnostic/discover-schema.sql
SELECT table_name FROM all_tables
WHERE owner = 'ISENODE'
ORDER BY table_name

-- MAC format check (ISE uses uppercase colon-separated)
-- File: diagnostic/diagnostic-mac-formats.sql
SELECT DISTINCT mac_address,
       LENGTH(mac_address) as len,
       SUBSTR(mac_address, 3, 1) as separator
FROM endpoints
FETCH FIRST 20 ROWS ONLY