Daily Worklog

# Get authentication logs for a MAC address
netapi ise mnt auth-logs 34:F6:8D:0E:76:E7

# Get active session details by MAC
netapi ise mnt session 34:F6:8D:0E:76:E7

# Get authentication status
netapi ise mnt auth-status 34:F6:8D:0E:76:E7

# List all active RADIUS sessions
netapi ise mnt sessions

# Get failed authentication attempts
netapi ise mnt failed

# List all failure reason codes
netapi ise mnt failure-reasons

# Get recent authentications (live RADIUS log)
netapi ise dc recent

# Get comprehensive session view for a MAC
netapi ise dc session 34:F6:8D:0E:76:E7

# Get authentication history timeline for a MAC
netapi ise dc auth-history 34:F6:8D:0E:76:E7

# Get failed authentications
netapi ise dc failed

# Get endpoint details
netapi ise dc endpoint 34:F6:8D:0E:76:E7

# List endpoints
netapi ise dc endpoints

# Get top failure reasons with counts
netapi ise dc top-failures

# Get authentication statistics
netapi ise dc stats

# Get authentication method distribution
netapi ise dc auth-methods

# Get hourly authentication trends
netapi ise dc trends

# Get NAS (switch/WLC) health by auth success rate
netapi ise dc nas

# Find stale endpoints not seen in N days
netapi ise dc stale --days 30

-- Get endpoint by MAC
SELECT edf_macaddress AS mac_address,
       edf_endpointpolicy AS endpoint_policy,
       edf_endpointip AS ip_address,
       edf_identitygroupid AS identity_group,
       TO_CHAR(edf_create_time, 'YYYY-MM-DD HH24:MI:SS') AS created,
       TO_CHAR(edf_update_time, 'YYYY-MM-DD HH24:MI:SS') AS updated
FROM cepm.edf_ep_master
WHERE UPPER(edf_macaddress) = '3C:E0:64:E8:90:6E'

-- Get auth history for a MAC (last 24 hours)
SELECT TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
       calling_station_id AS mac_address,
       user_name AS username,
       nas_ip_address,
       authentication_method AS auth_method,
       selected_azn_profiles AS authz_profile,
       policy_set_name AS policy_set,
       CASE WHEN passed = 1 THEN 'PASSED' ELSE 'FAILED' END AS status,
       failure_reason
FROM mnt.radius_auth_48_live
WHERE UPPER(calling_station_id) = '3C:E0:64:E8:90:6E'
  AND acs_timestamp >= SYSDATE - 1
ORDER BY acs_timestamp DESC
FETCH FIRST 50 ROWS ONLY

-- Get failed auths in last 24 hours
SELECT TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
       calling_station_id as mac_address,
       user_name as username,
       nas_ip_address,
       failure_reason
FROM mnt.radius_auth_48_live
WHERE failed = 1
  AND acs_timestamp >= SYSDATE - 1
ORDER BY acs_timestamp DESC
FETCH FIRST 100 ROWS ONLY

-- Get top failure reasons with counts
SELECT failure_reason,
       COUNT(*) AS count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM mnt.radius_auth_48_live
WHERE failed = 1
  AND acs_timestamp >= SYSDATE - 1
  AND failure_reason IS NOT NULL
GROUP BY failure_reason
ORDER BY count DESC
FETCH FIRST 20 ROWS ONLY

-- Get auth statistics for last 24 hours
SELECT 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 mnt.radius_auth_48_live
WHERE acs_timestamp >= SYSDATE - 1

 netapi ise dc query "
-- Get auth history for a MAC (last 24 hours)
SELECT TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
       calling_station_id AS mac_address,
       user_name AS username,
       nas_ip_address,
       authentication_method AS auth_method,
       selected_azn_profiles AS authz_profile,
       policy_set_name AS policy_set,
       CASE WHEN passed = 1 THEN 'PASSED' ELSE 'FAILED' END AS status,
       failure_reason
FROM mnt.radius_auth_48_live
WHERE UPPER(calling_station_id) = 'b4:e9:b8:f6:c8:17'
  AND acs_timestamp >= SYSDATE - 1
ORDER BY acs_timestamp DESC
FETCH FIRST 50 ROWS ONLY"