RADIUS Accounting Views

Overview

RADIUS accounting views track session lifecycle (Start/Stop/Interim-Update) with duration and data transfer metrics. Essential for bandwidth monitoring, session analysis, and compliance reporting.

RADIUS_ACCOUNTING

Session accounting records - Start, Stop, and Interim-Update packets.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Accounting record time

CALLING_STATION_ID

VARCHAR2

MAC address (primary join key)

USERNAME

VARCHAR2

Authenticated identity

SESSION_ID

VARCHAR2

Unique session identifier (for auth correlation)

ACCT_STATUS_TYPE

VARCHAR2

'Start', 'Stop', or 'Interim-Update'

ACCT_SESSION_ID

VARCHAR2

RADIUS session ID

ACCT_SESSION_TIME

NUMBER

Session duration in seconds

ACCT_INPUT_OCTETS

NUMBER

Bytes received (download)

ACCT_OUTPUT_OCTETS

NUMBER

Bytes sent (upload)

ACCT_INPUT_PACKETS

NUMBER

Packets received

ACCT_OUTPUT_PACKETS

NUMBER

Packets sent

ACCT_TERMINATE_CAUSE

VARCHAR2

Session end reason (User-Request, Lost-Carrier, etc.)

FRAMED_IP_ADDRESS

VARCHAR2

Assigned IP address

NAS_IP_ADDRESS

VARCHAR2

Switch/WLC IP

NAS_PORT_ID

VARCHAR2

Port identifier

SERVICE_TYPE

VARCHAR2

Service type (Framed, Call-Check)

CALLED_STATION_ID

VARCHAR2

SSID or NAS port

NAS_IDENTIFIER

VARCHAR2

NAS hostname

Common Queries

Active Sessions (Start without Stop)

netapi ise dc query "
SELECT
    s.CALLING_STATION_ID AS mac,
    s.USERNAME,
    s.FRAMED_IP_ADDRESS AS ip,
    s.NAS_IP_ADDRESS AS nas,
    TO_CHAR(s.TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS start_time
FROM RADIUS_ACCOUNTING s
WHERE s.ACCT_STATUS_TYPE = 'Start'
    AND s.TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND NOT EXISTS (
        SELECT 1 FROM RADIUS_ACCOUNTING e
        WHERE e.SESSION_ID = s.SESSION_ID
        AND e.ACCT_STATUS_TYPE = 'Stop'
    )
ORDER BY s.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Session Duration Analysis

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    COUNT(*) AS sessions,
    ROUND(AVG(ACCT_SESSION_TIME) / 60, 1) AS avg_minutes,
    ROUND(MAX(ACCT_SESSION_TIME) / 3600, 1) AS max_hours,
    ROUND(SUM(ACCT_SESSION_TIME) / 3600, 1) AS total_hours
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID, USERNAME
ORDER BY total_hours DESC
FETCH FIRST 30 ROWS ONLY
"

Data Usage (Top Consumers)

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    ROUND(SUM(ACCT_INPUT_OCTETS) / 1073741824, 2) AS download_gb,
    ROUND(SUM(ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS upload_gb,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS total_gb
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID, USERNAME
ORDER BY total_gb DESC
FETCH FIRST 20 ROWS ONLY
"

Session Termination Reasons

netapi ise dc query "
SELECT
    ACCT_TERMINATE_CAUSE,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY ACCT_TERMINATE_CAUSE
ORDER BY count DESC
"

Specific MAC Session History

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ACCT_STATUS_TYPE AS type,
    ROUND(ACCT_SESSION_TIME / 60, 1) AS minutes,
    ROUND((ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1048576, 1) AS mb,
    FRAMED_IP_ADDRESS AS ip,
    ACCT_TERMINATE_CAUSE AS end_reason
FROM RADIUS_ACCOUNTING
WHERE CALLING_STATION_ID = '14:F6:D8:7B:31:80'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Daily Data Usage Report

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'YYYY-MM-DD') AS date,
    COUNT(DISTINCT CALLING_STATION_ID) AS unique_devices,
    COUNT(*) AS sessions,
    ROUND(SUM(ACCT_SESSION_TIME) / 3600, 1) AS total_hours,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS total_gb
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY TRUNC(TIMESTAMP_TIMEZONE)
ORDER BY date
"

Long Sessions (> 8 hours)

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS end_time,
    ROUND(ACCT_SESSION_TIME / 3600, 1) AS hours,
    ACCT_TERMINATE_CAUSE AS end_reason
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
    AND ACCT_SESSION_TIME > 28800
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY ACCT_SESSION_TIME DESC
FETCH FIRST 30 ROWS ONLY
"

RADIUS_ACCOUNTING_WEEK

Same schema, pre-filtered to last 7 days for faster weekly reports.

JOIN Examples

Accounting + Authentication Details

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

Accounting + Endpoint Profile

netapi ise dc query "
SELECT
    p.ENDPOINT_PROFILE AS device_type,
    COUNT(DISTINCT a.CALLING_STATION_ID) AS unique_devices,
    COUNT(*) AS sessions,
    ROUND(AVG(a.ACCT_SESSION_TIME) / 3600, 1) AS avg_hours,
    ROUND(SUM(a.ACCT_INPUT_OCTETS + a.ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS total_gb
FROM RADIUS_ACCOUNTING a
JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON a.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE a.ACCT_STATUS_TYPE = 'Stop'
    AND a.TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY p.ENDPOINT_PROFILE
ORDER BY total_gb DESC
FETCH FIRST 15 ROWS ONLY
"

Byte Conversion Reference

Conversion Formula

Bytes → KB

/ 1024

Bytes → MB

/ 1048576

Bytes → GB

/ 1073741824

Seconds → Minutes

/ 60

Seconds → Hours

/ 3600

See Also