DataConnect Analytics

Overview

This page provides comprehensive SQL queries for security analytics, CISO reporting, and operational intelligence using ISE DataConnect.

Schema Discovery

Before writing queries, discover available views and their columns.

List All DataConnect Views

netapi ise dc query "
SELECT view_name
FROM all_views
WHERE owner = 'DATACONNECT'
ORDER BY view_name
"

Get Columns for a Specific View

netapi ise dc query "
SELECT column_name, data_type
FROM all_tab_columns
WHERE table_name = 'RADIUS_AUTHENTICATIONS'
ORDER BY column_id
"

Available Views Reference

View Description Key Columns

RADIUS_AUTHENTICATIONS

All RADIUS auth attempts

USERNAME, CALLING_STATION_ID, PASSED, TIMESTAMP_TIMEZONE, AUTHENTICATION_PROTOCOL, IDENTITY_STORE, IDENTITY_GROUP, AUTHORIZATION_PROFILES, AUTHORIZATION_RULE, POLICY_SET_NAME, ENDPOINT_PROFILE, NAS_IP_ADDRESS, FRAMED_IP_ADDRESS, FAILURE_REASON, AUTHENTICATION_METHOD

RADIUS_ACCOUNTING

Session accounting data

CALLING_STATION_ID, ACCT_SESSION_TIME, ACCT_INPUT_OCTETS, ACCT_OUTPUT_OCTETS, ACCT_STATUS_TYPE, ACCT_SESSION_ID

ENDPOINTS_DATA

Endpoint database

MAC_ADDRESS, HOSTNAME, ENDPOINT_IP, ENDPOINT_POLICY

PROFILED_ENDPOINTS_SUMMARY

Profiler results

ENDPOINT_ID, ENDPOINT_PROFILE, IDENTITY_GROUP

EXT_ID_SRC_CERT_AUTH_PROFILE

Certificate Authentication Profile definitions

NAME, DESCRIPTION (metadata only, no cert details)

TACACS_AUTHENTICATION

Device admin logins

USERNAME, DEVICE_NAME, DEVICE_IP, PASSED_FLAG, FAILURE_REASON, LOGGED_AT

TACACS_COMMAND_VIEW

Commands on network devices

USERNAME, DEVICE_NAME, CMD, CMD_ARG, PASSED_FLAG, LOGGED_AT

MNT.ADMINISTRATOR_LOGIN

ISE admin console logins

ADMIN_NAME, ADMIN_IP_ADDRESS, INTERFACE, LOGGED_AT, OPERATION_MSG

MNT.CONFIG_CHANGE

ISE configuration changes

ADMIN_NAME, OBJECT_TYPE, OBJECT_NAME, ADMIN_ACTION, LOGGED_AT, ADMIN_IP_ADDRESS

MNT.IRF_THREAT_EVENTS_VIEW

Threat-Centric NAC events

MAC_ADDRESS, THREAT_TYPE, SEVERITY, LOGGED_AT

MNT.IRF_COA_VIEW

Change of Authorization events

MAC_ADDRESS, COA_TYPE, TIMESTAMP, RESULT

MNT.POSTURE_AUTH_VIEW

Posture assessment results

MAC_ADDRESS, USERNAME, POSTURE_STATUS, POLICY_NAME, LOGGED_AT

MNT.MISCONFIGURED_NAS_VIEW

NAS configuration issues

NAS_IP_ADDRESS, FAILURE_REASON, LOGGED_AT

MNT.MISCONFIGURED_SUPPLICANTS_VIEW

Supplicant issues

MAC_ADDRESS, FAILURE_REASON, LOGGED_AT

NETWORK_DEVICES

Network access devices

ID, NAME, IP_MASK, PROFILE_NAME, LOCATION, TYPE

NETWORK_DEVICE_GROUPS

Device group hierarchy

Query for columns

NODE_LIST

ISE nodes

HOSTNAME, ROLES, IP_ADDRESS

Discovering Column Names

When a query fails with ORA-00904: invalid identifier, discover the correct column names:

# List all columns for a table/view
netapi ise dc query "
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'RADIUS_AUTHENTICATIONS'
ORDER BY column_id
"

# Find columns matching a pattern
netapi ise dc query "
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'RADIUS_AUTHENTICATIONS'
  AND column_name LIKE '%POLICY%'
ORDER BY column_id
"

# Find columns related to identity/auth/profile
netapi ise dc query "
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'RADIUS_AUTHENTICATIONS'
  AND (column_name LIKE '%IDENTITY%'
       OR column_name LIKE '%AUTH%'
       OR column_name LIKE '%PROFILE%')
ORDER BY column_id
"
Table 1. RADIUS_AUTHENTICATIONS Key Columns (Validated)
Column Description

USERNAME

Identity (hostname.domain for certs)

CALLING_STATION_ID

MAC address

NAS_IP_ADDRESS

Switch/WLC IP

FRAMED_IP_ADDRESS

Assigned IP

AUTHENTICATION_PROTOCOL

EAP-TLS, PEAP, MAB, etc.

AUTHENTICATION_METHOD

Method used

IDENTITY_STORE

Identity source (empty for cert auth)

IDENTITY_GROUP

Endpoint identity group

AUTHORIZATION_PROFILES

Applied authz profile

AUTHORIZATION_RULE

Matched authz rule

POLICY_SET_NAME

Policy set that matched

ENDPOINT_PROFILE

Profiler classification

PASSED

Pass/Fail

FAILURE_REASON

Error message on failure

TIMESTAMP_TIMEZONE

Auth timestamp with timezone

Certificate Authentication

Queries specifically for EAP-TLS and certificate-based authentication.

Verify Certificate Migration

After migrating from one CA to another (e.g., AD CS to Vault PKI), verify the new certificate is being used:

netapi ise dc query "
SELECT
    USERNAME,
    AUTHENTICATION_PROTOCOL,
    IDENTITY_GROUP,
    AUTHORIZATION_PROFILES,
    AUTHORIZATION_RULE,
    POLICY_SET_NAME,
    CALLING_STATION_ID,
    NAS_IP_ADDRESS,
    PASSED,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE USERNAME LIKE '%modestus-razer%'
  AND AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 5 ROWS ONLY
"

Certificate Auth by Identity Group

Show which identity groups are using certificate authentication:

netapi ise dc query "
SELECT
    IDENTITY_GROUP,
    COUNT(*) as cert_auths,
    COUNT(DISTINCT USERNAME) as unique_certs,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_devices
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY IDENTITY_GROUP
ORDER BY cert_auths DESC
"

Certificate Auth Policy Matching

See which policies and rules are matching certificate authentications:

netapi ise dc query "
SELECT
    POLICY_SET_NAME,
    AUTHORIZATION_RULE,
    AUTHORIZATION_PROFILES,
    COUNT(*) as hits,
    COUNT(DISTINCT USERNAME) as unique_certs
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY POLICY_SET_NAME, AUTHORIZATION_RULE, AUTHORIZATION_PROFILES
ORDER BY hits DESC
"

Failed Certificate Authentications

Troubleshoot certificate failures:

netapi ise dc query "
SELECT
    USERNAME,
    CALLING_STATION_ID,
    NAS_IP_ADDRESS,
    FAILURE_REASON,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND PASSED = 'Fail'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"

Certificate Auth vs Password Auth Ratio

Compare certificate-based vs password-based authentication:

netapi ise dc query "
SELECT
    CASE
        WHEN AUTHENTICATION_PROTOCOL = 'EAP-TLS' THEN 'Certificate (EAP-TLS)'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%PEAP%' THEN 'Password (PEAP)'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%MAB%' THEN 'MAC Bypass'
        ELSE AUTHENTICATION_PROTOCOL
    END as auth_type,
    COUNT(*) as auth_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY
    CASE
        WHEN AUTHENTICATION_PROTOCOL = 'EAP-TLS' THEN 'Certificate (EAP-TLS)'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%PEAP%' THEN 'Password (PEAP)'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%MAB%' THEN 'MAC Bypass'
        ELSE AUTHENTICATION_PROTOCOL
    END
ORDER BY auth_count DESC
"

Certificate Authentication Profile View

Query certificate authentication profile definitions:

netapi ise dc query "
SELECT NAME, DESCRIPTION
FROM EXT_ID_SRC_CERT_AUTH_PROFILE
"

This view only contains profile metadata (NAME, DESCRIPTION). Certificate details like issuer/subject are NOT exposed via DataConnect. To verify which CA issued a certificate, check the certificate itself with openssl x509 -in cert.pem -issuer -noout.

RADIUS Authentication Analytics

Authentication Protocol Breakdown

netapi ise dc query "
SELECT
    AUTHENTICATION_PROTOCOL,
    COUNT(*) as count,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_macs,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY count DESC
"

EAP-TLS Certificate Authentication (Comprehensive)

Full details for certificate-based authentication including policy matching:

netapi ise dc query "
SELECT
    USERNAME,
    AUTHENTICATION_PROTOCOL,
    IDENTITY_STORE,
    IDENTITY_GROUP,
    AUTHORIZATION_PROFILES,
    AUTHORIZATION_RULE,
    POLICY_SET_NAME,
    ENDPOINT_PROFILE,
    CALLING_STATION_ID,
    NAS_IP_ADDRESS,
    PASSED,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 10 ROWS ONLY
"
Example Output
USERNAME               │ modestus-razer.inside.domusdigitalis.dev
AUTHENTICATION_PROTOCOL│ EAP-TLS
IDENTITY_STORE         │ (empty for cert auth)
IDENTITY_GROUP         │ MGMT_DEVICES
AUTHORIZATION_PROFILES │ Linux_EAPTLS_Admin
AUTHORIZATION_RULE     │ Linux_EAP-TLS_Permit
POLICY_SET_NAME        │ Domus-Wired 802.1X
ENDPOINT_PROFILE       │ Unknown
CALLING_STATION_ID     │ 98:BB:1E:1F:A7:13
NAS_IP_ADDRESS         │ 10.50.1.10
PASSED                 │ Pass
TIMESTAMP_TIMEZONE     │ 2026-02-08 14:45:35.382000

EAP-TLS Simple View

Quick check for EAP-TLS authentications:

netapi ise dc query "
SELECT
    USERNAME,
    AUTHENTICATION_PROTOCOL,
    PASSED,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 5 ROWS ONLY
"

EAP-TLS with Network Details

Include MAC, switch IP, and assigned IP:

netapi ise dc query "
SELECT
    USERNAME,
    AUTHENTICATION_PROTOCOL,
    PASSED,
    CALLING_STATION_ID,
    NAS_IP_ADDRESS,
    FRAMED_IP_ADDRESS,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 5 ROWS ONLY
"

Unique EAP-TLS Device Count

netapi ise dc query "
SELECT
    COUNT(DISTINCT CALLING_STATION_ID) as unique_devices,
    COUNT(DISTINCT USERNAME) as unique_identities,
    COUNT(*) as total_auths
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
"

Authorization Profile Distribution

netapi ise dc query "
SELECT
    AUTHORIZATION_PROFILES,
    COUNT(*) as auth_count,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_devices
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY AUTHORIZATION_PROFILES
ORDER BY auth_count DESC
"

Policy Set Usage

netapi ise dc query "
SELECT
    POLICY_SET_NAME,
    COUNT(*) as hits,
    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(*), 2) as success_rate
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY POLICY_SET_NAME
ORDER BY hits DESC
"

Identity Store Distribution

netapi ise dc query "
SELECT
    IDENTITY_STORE,
    COUNT(*) as auths,
    COUNT(DISTINCT USERNAME) as unique_users
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY IDENTITY_STORE
ORDER BY auths DESC
"

Authentication by NAS Type

netapi ise dc query "
SELECT
    NAS_PORT_TYPE,
    COUNT(*) as auths,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as passed,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) as failed
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY NAS_PORT_TYPE
ORDER BY auths DESC
"

Failure Analysis

Top Failure Reasons

netapi ise dc query "
SELECT
    FAILURE_REASON,
    COUNT(*) as occurrences,
    COUNT(DISTINCT CALLING_STATION_ID) as affected_devices,
    COUNT(DISTINCT NAS_IP_ADDRESS) as affected_nas
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY FAILURE_REASON
ORDER BY occurrences DESC
FETCH FIRST 20 ROWS ONLY
"

Failed Authentication Details

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    AUTHENTICATION_PROTOCOL,
    FAILURE_REASON,
    NAS_IP_ADDRESS,
    TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Repeat Offenders (Multiple Failures)

Devices with 3+ failures may indicate misconfiguration or attack:

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    COUNT(*) as failure_count,
    LISTAGG(DISTINCT FAILURE_REASON, '; ') WITHIN GROUP (ORDER BY FAILURE_REASON) as reasons,
    MAX(TIMESTAMP_TIMEZONE) as last_failure
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY CALLING_STATION_ID, USERNAME
HAVING COUNT(*) >= 3
ORDER BY failure_count DESC
FETCH FIRST 20 ROWS ONLY
"

Failures by Hour (Trend)

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE, 'HH24'), 'YYYY-MM-DD HH24:00') as hour,
    COUNT(*) as failures,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_macs
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TRUNC(TIMESTAMP_TIMEZONE, 'HH24')
ORDER BY hour DESC
"

Security Analytics

Authentication Velocity (Brute Force Detection)

Devices with >10 attempts per hour may indicate brute force:

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    COUNT(*) as attempts_per_hour,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) as failures,
    TRUNC(TIMESTAMP_TIMEZONE, 'HH24') as hour
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY CALLING_STATION_ID, USERNAME, TRUNC(TIMESTAMP_TIMEZONE, 'HH24')
HAVING COUNT(*) > 10
ORDER BY attempts_per_hour DESC
"

After-Hours Authentication

Authentications outside business hours (6 PM - 6 AM):

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    NAS_IP_ADDRESS,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') as time,
    AUTHENTICATION_PROTOCOL,
    AUTHORIZATION_PROFILES
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND (TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') < '06'
       OR TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') >= '18')
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Weekend Authentication Activity

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'Day') as day_of_week,
    COUNT(*) as auths,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_devices
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND TO_CHAR(TIMESTAMP_TIMEZONE, 'D') IN ('1', '7')
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'Day')
ORDER BY auths DESC
"

Unknown/Unprofiled Devices

Devices that bypassed profiling (potential rogue devices):

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    AUTHORIZATION_PROFILES,
    IDENTITY_GROUP,
    COUNT(*) as auth_count,
    MAX(TIMESTAMP_TIMEZONE) as last_seen
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND (IDENTITY_GROUP IS NULL
       OR IDENTITY_GROUP LIKE '%Unknown%'
       OR AUTHORIZATION_PROFILES LIKE '%Guest%')
GROUP BY CALLING_STATION_ID, USERNAME, AUTHORIZATION_PROFILES, IDENTITY_GROUP
ORDER BY auth_count DESC
"

New Devices (First Seen)

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    MIN(TIMESTAMP_TIMEZONE) as first_seen,
    MAX(TIMESTAMP_TIMEZONE) as last_seen,
    COUNT(*) as total_auths
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
GROUP BY CALLING_STATION_ID, USERNAME
HAVING MIN(TIMESTAMP_TIMEZONE) > SYSDATE - 1
ORDER BY first_seen DESC
"

Dormant Devices (Stale Endpoints)

Devices inactive for 30+ days:

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    AUTHORIZATION_PROFILES,
    MAX(TIMESTAMP_TIMEZONE) as last_seen,
    ROUND(SYSDATE - MAX(TIMESTAMP_TIMEZONE)) as days_inactive
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
GROUP BY CALLING_STATION_ID, USERNAME, AUTHORIZATION_PROFILES
HAVING MAX(TIMESTAMP_TIMEZONE) < SYSDATE - 30
ORDER BY last_seen ASC
FETCH FIRST 50 ROWS ONLY
"

NAS/Infrastructure Analytics

NAS Device Health

Network access devices with high failure rates:

netapi ise dc query "
SELECT
    NAS_IP_ADDRESS,
    NAS_IDENTIFIER,
    COUNT(*) as total_auths,
    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 = 'Fail' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as failure_rate
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY NAS_IP_ADDRESS, NAS_IDENTIFIER
HAVING COUNT(*) >= 10
ORDER BY failure_rate DESC
"

NAS Activity by Volume

netapi ise dc query "
SELECT
    NAS_IP_ADDRESS,
    NAS_IDENTIFIER,
    COUNT(*) as total_auths,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_clients,
    COUNT(DISTINCT USERNAME) as unique_users
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY NAS_IP_ADDRESS, NAS_IDENTIFIER
ORDER BY total_auths DESC
"

Misconfigured NAS Devices

netapi ise dc query "
SELECT
    NAS_IP_ADDRESS,
    FAILURE_REASON,
    COUNT(*) as occurrences,
    MAX(LOGGED_AT) as last_seen
FROM MNT.MISCONFIGURED_NAS_VIEW
WHERE LOGGED_AT > SYSDATE - 7
GROUP BY NAS_IP_ADDRESS, FAILURE_REASON
ORDER BY occurrences DESC
"

Misconfigured Supplicants

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    FAILURE_REASON,
    COUNT(*) as occurrences,
    MAX(LOGGED_AT) as last_seen
FROM MNT.MISCONFIGURED_SUPPLICANTS_VIEW
WHERE LOGGED_AT > SYSDATE - 7
GROUP BY MAC_ADDRESS, FAILURE_REASON
ORDER BY occurrences DESC
"

RADIUS Accounting Analytics

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 20 ROWS ONLY
"

Data Usage (Top Consumers)

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    COUNT(*) as sessions,
    ROUND(SUM(ACCT_INPUT_OCTETS)/1048576, 2) as mb_in,
    ROUND(SUM(ACCT_OUTPUT_OCTETS)/1048576, 2) as mb_out,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS)/1048576, 2) as total_mb
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID, USERNAME
ORDER BY total_mb DESC
FETCH FIRST 20 ROWS ONLY
"

Sessions Over 1GB

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    FRAMED_IP_ADDRESS as IP,
    ROUND((ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS)/1073741824, 2) as gb_total,
    ROUND(ACCT_SESSION_TIME/3600, 1) as hours,
    TIMESTAMP_TIMEZONE
FROM RADIUS_ACCOUNTING
WHERE (ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) > 1073741824
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY gb_total DESC
"

Active Sessions (Currently Connected)

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    FRAMED_IP_ADDRESS as IP,
    NAS_IP_ADDRESS,
    ACCT_SESSION_ID,
    TIMESTAMP_TIMEZONE as session_start
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Start'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND NOT EXISTS (
    SELECT 1 FROM RADIUS_ACCOUNTING a2
    WHERE a2.ACCT_SESSION_ID = RADIUS_ACCOUNTING.ACCT_SESSION_ID
    AND a2.ACCT_STATUS_TYPE = 'Stop'
  )
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

TACACS+ Analytics

Device Admin Logins

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    USERNAME,
    DEVICE_NAME,
    DEVICE_IP,
    PASSED_FLAG as status,
    FAILURE_REASON
FROM TACACS_AUTHENTICATION
WHERE LOGGED_AT > SYSDATE - 7
ORDER BY LOGGED_AT DESC
FETCH FIRST 50 ROWS ONLY
"

Failed Device Logins

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    USERNAME,
    DEVICE_NAME,
    DEVICE_IP,
    FAILURE_REASON
FROM TACACS_AUTHENTICATION
WHERE PASSED_FLAG = 'Fail'
  AND LOGGED_AT > SYSDATE - 7
ORDER BY LOGGED_AT DESC
FETCH FIRST 50 ROWS ONLY
"

Privileged Command Audit

Track config/write/reload commands:

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    USERNAME,
    DEVICE_NAME,
    DEVICE_IP,
    CMD,
    CMD_ARG,
    PASSED_FLAG as status
FROM TACACS_COMMAND_VIEW
WHERE LOGGED_AT > SYSDATE - 7
  AND (CMD LIKE '%config%'
       OR CMD LIKE '%write%'
       OR CMD LIKE '%copy%'
       OR CMD LIKE '%reload%'
       OR CMD LIKE '%crypto%'
       OR CMD LIKE '%enable%')
ORDER BY LOGGED_AT DESC
FETCH FIRST 100 ROWS ONLY
"

All Commands by User

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    USERNAME,
    DEVICE_NAME,
    CMD,
    CMD_ARG
FROM TACACS_COMMAND_VIEW
WHERE USERNAME = 'admin'
  AND LOGGED_AT > SYSDATE - 1
ORDER BY LOGGED_AT DESC
FETCH FIRST 100 ROWS ONLY
"

Command Frequency by Device

netapi ise dc query "
SELECT
    DEVICE_NAME,
    DEVICE_IP,
    COUNT(*) as command_count,
    COUNT(DISTINCT USERNAME) as unique_admins
FROM TACACS_COMMAND_VIEW
WHERE LOGGED_AT > SYSDATE - 7
GROUP BY DEVICE_NAME, DEVICE_IP
ORDER BY command_count DESC
"

Admin Audit Analytics

ISE Console Logins

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    ADMIN_NAME,
    ADMIN_IP_ADDRESS,
    INTERFACE,
    OPERATION_MSG
FROM MNT.ADMINISTRATOR_LOGIN
WHERE LOGGED_AT > SYSDATE - 30
ORDER BY LOGGED_AT DESC
FETCH FIRST 100 ROWS ONLY
"

Failed Admin Logins

netapi ise dc query "
SELECT
    LOGGED_AT as time,
    ADMIN_NAME,
    ADMIN_IP_ADDRESS,
    INTERFACE,
    OPERATION_MSG
FROM MNT.ADMINISTRATOR_LOGIN
WHERE LOGGED_AT > SYSDATE - 30
  AND OPERATION_MSG LIKE '%fail%'
ORDER BY LOGGED_AT DESC
"

Configuration Changes

netapi ise dc query "
SELECT
    ACS_TIMESTAMP as time,
    ADMIN_NAME,
    ADMIN_IPADDRESS,
    ADMIN_INTERFACE,
    OBJECT_TYPE,
    OBJECT_NAME,
    REQUESTED_OPERATION
FROM MNT.CONFIG_CHANGE
WHERE ACS_TIMESTAMP > SYSDATE - 30
ORDER BY ACS_TIMESTAMP DESC
FETCH FIRST 100 ROWS ONLY
"

Policy Changes Only

netapi ise dc query "
SELECT
    ACS_TIMESTAMP as time,
    ADMIN_NAME,
    OBJECT_TYPE,
    OBJECT_NAME,
    REQUESTED_OPERATION
FROM MNT.CONFIG_CHANGE
WHERE ACS_TIMESTAMP > SYSDATE - 30
  AND OBJECT_TYPE LIKE '%Policy%'
ORDER BY ACS_TIMESTAMP DESC
"

Changes by Admin

netapi ise dc query "
SELECT
    ADMIN_NAME,
    COUNT(*) as changes,
    COUNT(DISTINCT OBJECT_TYPE) as object_types,
    MAX(ACS_TIMESTAMP) as last_change
FROM MNT.CONFIG_CHANGE
WHERE ACS_TIMESTAMP > SYSDATE - 30
GROUP BY ADMIN_NAME
ORDER BY changes DESC
"

Threat & Posture Analytics

Threat Events

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    THREAT_TYPE,
    SEVERITY,
    LOGGED_AT
FROM MNT.IRF_THREAT_EVENTS_VIEW
WHERE LOGGED_AT > SYSDATE - 7
ORDER BY LOGGED_AT DESC
FETCH FIRST 50 ROWS ONLY
"

High Severity Threats

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    THREAT_TYPE,
    SEVERITY,
    COUNT(*) as occurrences,
    MAX(LOGGED_AT) as last_seen
FROM MNT.IRF_THREAT_EVENTS_VIEW
WHERE SEVERITY IN ('HIGH', 'CRITICAL')
  AND LOGGED_AT > SYSDATE - 30
GROUP BY MAC_ADDRESS, THREAT_TYPE, SEVERITY
ORDER BY occurrences DESC
"

Change of Authorization Events

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    COA_TYPE,
    TIMESTAMP,
    RESULT
FROM MNT.IRF_COA_VIEW
WHERE TIMESTAMP > SYSDATE - 7
ORDER BY TIMESTAMP DESC
FETCH FIRST 50 ROWS ONLY
"

Posture Assessment Results

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    USERNAME,
    POSTURE_STATUS,
    POLICY_NAME,
    LOGGED_AT
FROM MNT.POSTURE_AUTH_VIEW
WHERE LOGGED_AT > SYSDATE - 7
ORDER BY LOGGED_AT DESC
FETCH FIRST 50 ROWS ONLY
"

Non-Compliant Endpoints

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    USERNAME,
    POSTURE_STATUS,
    POLICY_NAME,
    LOGGED_AT
FROM MNT.POSTURE_AUTH_VIEW
WHERE POSTURE_STATUS = 'NonCompliant'
  AND LOGGED_AT > SYSDATE - 7
ORDER BY LOGGED_AT DESC
"

CISO Dashboard Queries

Weekly Security Posture Summary

netapi ise dc query "
SELECT 'Total Authentications' as metric, TO_CHAR(COUNT(*)) as value FROM RADIUS_AUTHENTICATIONS WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
UNION ALL
SELECT 'Unique Devices', TO_CHAR(COUNT(DISTINCT CALLING_STATION_ID)) FROM RADIUS_AUTHENTICATIONS WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
UNION ALL
SELECT 'Unique Users', TO_CHAR(COUNT(DISTINCT USERNAME)) FROM RADIUS_AUTHENTICATIONS WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
UNION ALL
SELECT 'EAP-TLS Auths', TO_CHAR(COUNT(*)) FROM RADIUS_AUTHENTICATIONS WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS' AND TIMESTAMP_TIMEZONE > SYSDATE - 7
UNION ALL
SELECT 'Failed Auths', TO_CHAR(COUNT(*)) FROM RADIUS_AUTHENTICATIONS WHERE PASSED = 'Fail' AND TIMESTAMP_TIMEZONE > SYSDATE - 7
UNION ALL
SELECT 'Success Rate %', TO_CHAR(ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2)) FROM RADIUS_AUTHENTICATIONS WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
"

Daily Trend Report

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'YYYY-MM-DD') as date,
    COUNT(*) as total_auths,
    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(*), 2) as success_rate,
    COUNT(DISTINCT CALLING_STATION_ID) as unique_devices,
    COUNT(DISTINCT USERNAME) as unique_users
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY TRUNC(TIMESTAMP_TIMEZONE)
ORDER BY date DESC
"

Hourly Trend (Last 24h)

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE, 'HH24'), 'HH24:00') as hour,
    COUNT(*) as auths,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) as failures
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TRUNC(TIMESTAMP_TIMEZONE, 'HH24')
ORDER BY TRUNC(TIMESTAMP_TIMEZONE, 'HH24') DESC
"

Security Protocol Adoption

netapi ise dc query "
SELECT
    AUTHENTICATION_PROTOCOL,
    COUNT(*) as auths,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage,
    CASE
        WHEN AUTHENTICATION_PROTOCOL LIKE '%TLS%' THEN 'Certificate'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%PEAP%' THEN 'Password'
        WHEN AUTHENTICATION_PROTOCOL LIKE '%MAB%' THEN 'MAC Bypass'
        ELSE 'Other'
    END as security_level
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY auths DESC
"

Risk Score by Device

High auth attempts with high failure rate = risky:

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    USERNAME,
    COUNT(*) as attempts,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) as failures,
    ROUND(SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as failure_rate,
    CASE
        WHEN SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) > 10 AND
             SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) > 50 THEN 'HIGH'
        WHEN SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) > 5 THEN 'MEDIUM'
        ELSE 'LOW'
    END as risk_level
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY CALLING_STATION_ID, USERNAME
HAVING SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) > 0
ORDER BY failures DESC
FETCH FIRST 20 ROWS ONLY
"

Oracle SQL Reference

Date/Time Functions

Expression Meaning

SYSDATE

Current date/time

SYSDATE - 1

24 hours ago

SYSDATE - 1/24

1 hour ago

SYSDATE - 1/1440

1 minute ago

SYSDATE - 7

7 days ago

SYSDATE - 30

30 days ago

TRUNC(date, 'HH24')

Truncate to hour

TRUNC(date, 'DD')

Truncate to day

TO_CHAR(date, 'YYYY-MM-DD')

Format date

TO_CHAR(date, 'HH24:MI:SS')

Format time

TO_CHAR(date, 'Day')

Day name (Monday, etc.)

TO_CHAR(date, 'D')

Day number (1=Sunday)

Aggregation Functions

Function Description

COUNT(*)

Count all rows

COUNT(DISTINCT col)

Count unique values

SUM(col)

Sum values

AVG(col)

Average value

MIN(col) / MAX(col)

Min/Max values

ROUND(num, decimals)

Round number

LISTAGG(col, ';') WITHIN GROUP (ORDER BY col)

Concatenate values

Limit Results

Syntax Usage

FETCH FIRST N ROWS ONLY

Limit to N rows (Oracle 12c+)

ROWNUM ⇐ N

Older limit syntax

OFFSET M ROWS FETCH NEXT N ROWS ONLY

Pagination

See Also