RADIUS Authentication Views

Overview

The RADIUS authentication views contain all 802.1X, MAB, and wireless authentication attempts with full detail including failure reasons, policy matches, and identity information.

RADIUS_AUTHENTICATIONS

Primary authentication log - every RADIUS auth attempt.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Authentication time (with timezone)

CALLING_STATION_ID

VARCHAR2

MAC address (primary join key)

USERNAME

VARCHAR2

Identity (username, hostname, or MAC for MAB)

PASSED

VARCHAR2

'Pass' or 'Fail' (string, not boolean!)

FAILED

VARCHAR2

'Fail' if failed, null if passed

FAILURE_REASON

VARCHAR2

Detailed failure reason (null if passed)

NAS_IP_ADDRESS

VARCHAR2

Switch/WLC IP that sent the request

NAS_PORT_ID

VARCHAR2

Port identifier (Gi1/0/5, etc.)

NAS_IDENTIFIER

VARCHAR2

NAS hostname

FRAMED_IP_ADDRESS

VARCHAR2

Assigned IP address

AUTHENTICATION_METHOD

VARCHAR2

EAP type (dot1x, mab)

AUTHENTICATION_PROTOCOL

VARCHAR2

Protocol (EAP-TLS, PEAP, MAB, etc.)

POLICY_SET_NAME

VARCHAR2

Matched policy set name

AUTHORIZATION_RULE

VARCHAR2

Matched authz rule name

AUTHORIZATION_PROFILES

VARCHAR2

Applied authz profile(s)

IDENTITY_STORE

VARCHAR2

Identity source used (AD, LDAP, Internal)

IDENTITY_GROUP

VARCHAR2

Endpoint identity group

SELECTED_AUTHENTICATION_IDENTITY_STORES

VARCHAR2

Identity store sequence

SESSION_ID

VARCHAR2

RADIUS session ID (for accounting correlation)

CALLED_STATION_ID

VARCHAR2

SSID (for wireless) or NAS port

ACCESS_SERVICE

VARCHAR2

Allowed protocols used

DEVICE_TYPE

VARCHAR2

Profiled device type

LOCATION

VARCHAR2

Network device group location

ISE_NODE

VARCHAR2

ISE node that processed request

Common Queries

Recent Authentications

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    CALLING_STATION_ID AS mac,
    USERNAME,
    PASSED,
    AUTHENTICATION_PROTOCOL,
    POLICY_SET_NAME
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Failed Authentications with Reasons

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') AS time,
    CALLING_STATION_ID AS mac,
    USERNAME,
    FAILURE_REASON,
    NAS_IP_ADDRESS,
    AUTHENTICATION_PROTOCOL
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Authentication by Protocol

netapi ise dc query "
SELECT
    AUTHENTICATION_PROTOCOL,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) AS failed,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY total DESC
"

EAP-TLS Certificate Authentications

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    IDENTITY_STORE,
    AUTHORIZATION_PROFILES,
    POLICY_SET_NAME,
    COUNT(*) AS auth_count
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
    AND PASSED = 'Pass'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID, USERNAME, IDENTITY_STORE,
         AUTHORIZATION_PROFILES, POLICY_SET_NAME
ORDER BY auth_count DESC
FETCH FIRST 50 ROWS ONLY
"

Specific MAC History

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    PASSED,
    AUTHENTICATION_PROTOCOL,
    POLICY_SET_NAME,
    AUTHORIZATION_RULE,
    AUTHORIZATION_PROFILES,
    FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID = '14:F6:D8:7B:31:80'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 100 ROWS ONLY
"

Top Failure Reasons

netapi ise dc query "
SELECT
    FAILURE_REASON,
    COUNT(*) AS occurrences,
    COUNT(DISTINCT CALLING_STATION_ID) AS unique_macs,
    COUNT(DISTINCT NAS_IP_ADDRESS) AS unique_nas
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY FAILURE_REASON
ORDER BY occurrences DESC
FETCH FIRST 15 ROWS ONLY
"

Hourly Trend Analysis

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24') AS hour,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) AS failed,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24')
ORDER BY hour
"

RADIUS_AUTHENTICATIONS_WEEK

Same schema as RADIUS_AUTHENTICATIONS but pre-filtered to last 7 days. Use for faster weekly reports.

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'YYYY-MM-DD') AS date,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed
FROM RADIUS_AUTHENTICATIONS_WEEK
GROUP BY TRUNC(TIMESTAMP_TIMEZONE)
ORDER BY date
"

RADIUS_ERRORS_VIEW

Failed authentications with enhanced failure detail.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Failure time

CALLING_STATION_ID

VARCHAR2

MAC address

USERNAME

VARCHAR2

Attempted identity

FAILURE_REASON

VARCHAR2

Failure reason text

FAILURE_CODE

NUMBER

Numeric failure code

RESOLUTION

VARCHAR2

Suggested fix (when available)

 
netapi ise dc query "
SELECT
    FAILURE_CODE,
    FAILURE_REASON,
    COUNT(*) AS count
FROM RADIUS_ERRORS_VIEW
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY FAILURE_CODE, FAILURE_REASON
ORDER BY count DESC
FETCH FIRST 20 ROWS ONLY
"

RADIUS_AUTHENTICATION_SUMMARY

Pre-aggregated statistics (updated periodically).

netapi ise dc query "SELECT * FROM RADIUS_AUTHENTICATION_SUMMARY"

JOIN Examples

Auth + Endpoint Profile

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    r.USERNAME,
    r.PASSED,
    r.AUTHENTICATION_PROTOCOL,
    p.ENDPOINT_PROFILE,
    p.IDENTITY_GROUP
FROM RADIUS_AUTHENTICATIONS r
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON r.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY r.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Auth + Session Accounting

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    r.USERNAME,
    r.PASSED,
    a.ACCT_SESSION_TIME AS session_secs,
    ROUND((a.ACCT_INPUT_OCTETS + a.ACCT_OUTPUT_OCTETS) / 1048576, 2) AS total_mb
FROM RADIUS_AUTHENTICATIONS r
JOIN RADIUS_ACCOUNTING a
    ON r.CALLING_STATION_ID = a.CALLING_STATION_ID
    AND r.SESSION_ID = a.SESSION_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND a.ACCT_STATUS_TYPE = 'Stop'
    AND r.PASSED = 'Pass'
ORDER BY a.ACCT_SESSION_TIME DESC
FETCH FIRST 20 ROWS ONLY
"

See Also