Endpoint & Profiling Views

Overview

Endpoint views provide device inventory, profiler classifications, and identity group assignments. These are the "source of truth" for what ISE knows about network devices.

ENDPOINTS_DATA

Master endpoint inventory - all known endpoints with IPs, hostnames, and profiles.

Key Columns

Column Type Description

MAC_ADDRESS

VARCHAR2

Primary key (canonical MAC format)

HOSTNAME

VARCHAR2

DNS hostname (if resolved)

ENDPOINT_IP

VARCHAR2

Last known IP address

ENDPOINT_POLICY

VARCHAR2

Applied endpoint policy

IDENTITY_GROUP_ID

VARCHAR2

Endpoint identity group UUID

STATIC_GROUP_ASSIGNMENT

VARCHAR2

'true' if manually assigned

STATIC_PROFILE_ASSIGNMENT

VARCHAR2

'true' if profile manually set

PORTAL_USER

VARCHAR2

Guest portal username (if applicable)

CREATE_TIME

TIMESTAMP

First seen timestamp

UPDATE_TIME

TIMESTAMP

Last activity timestamp

PROFILE_UPDATE_TIME

TIMESTAMP

Last profiler update

DEVICE_TYPE

VARCHAR2

Device type from profiler

HARDWARE_MANUFACTURER

VARCHAR2

OUI-based manufacturer

HARDWARE_MODEL

VARCHAR2

Hardware model (if profiled)

SOFTWARE_VERSION

VARCHAR2

OS version (if profiled)

REGISTERED

VARCHAR2

Registration status

MDM_SERVER

VARCHAR2

MDM server (if integrated)

MDM_COMPLIANCE_STATUS

VARCHAR2

MDM compliance status

Common Queries

All Endpoints with Key Info

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    ENDPOINT_POLICY,
    DEVICE_TYPE,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') AS last_seen
FROM ENDPOINTS_DATA
ORDER BY UPDATE_TIME DESC
FETCH FIRST 100 ROWS ONLY
"

Search by Hostname

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    ENDPOINT_POLICY
FROM ENDPOINTS_DATA
WHERE UPPER(HOSTNAME) LIKE UPPER('%modestus%')
ORDER BY HOSTNAME
"

Search by IP Range

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    ENDPOINT_POLICY
FROM ENDPOINTS_DATA
WHERE ENDPOINT_IP LIKE '10.50.10.%'
ORDER BY ENDPOINT_IP
"

Endpoints by Vendor (OUI)

netapi ise dc query "
SELECT
    HARDWARE_MANUFACTURER,
    COUNT(*) AS count
FROM ENDPOINTS_DATA
WHERE HARDWARE_MANUFACTURER IS NOT NULL
GROUP BY HARDWARE_MANUFACTURER
ORDER BY count DESC
FETCH FIRST 20 ROWS ONLY
"

Stale Endpoints (No Activity in 30 Days)

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') AS last_seen,
    ROUND(SYSDATE - UPDATE_TIME) AS days_stale
FROM ENDPOINTS_DATA
WHERE UPDATE_TIME < SYSDATE - 30
ORDER BY UPDATE_TIME ASC
FETCH FIRST 100 ROWS ONLY
"

Static vs Dynamic Assignments

netapi ise dc query "
SELECT
    CASE
        WHEN STATIC_GROUP_ASSIGNMENT = 'true' THEN 'Static Group'
        WHEN STATIC_PROFILE_ASSIGNMENT = 'true' THEN 'Static Profile'
        ELSE 'Dynamic'
    END AS assignment_type,
    COUNT(*) AS count
FROM ENDPOINTS_DATA
GROUP BY
    CASE
        WHEN STATIC_GROUP_ASSIGNMENT = 'true' THEN 'Static Group'
        WHEN STATIC_PROFILE_ASSIGNMENT = 'true' THEN 'Static Profile'
        ELSE 'Dynamic'
    END
"

PROFILED_ENDPOINTS_SUMMARY

Profiler classification data - profile assignments with certainty factors.

Key Columns

Column Type Description

ENDPOINT_ID

VARCHAR2

MAC address (join to ENDPOINTS_DATA.MAC_ADDRESS)

ENDPOINT_PROFILE

VARCHAR2

Matched profiler policy name

IDENTITY_GROUP

VARCHAR2

Assigned identity group name

MATCHED_POLICY

VARCHAR2

Profiler policy that matched

MATCHED_CERTAINTY_FACTOR

NUMBER

Certainty factor (higher = more confident)

TOTAL_CERTAINTY_FACTOR

NUMBER

Cumulative certainty

ENDPOINT_SOURCE

VARCHAR2

How endpoint was discovered

Common Queries

Profile Distribution

netapi ise dc query "
SELECT
    ENDPOINT_PROFILE,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM PROFILED_ENDPOINTS_SUMMARY
GROUP BY ENDPOINT_PROFILE
ORDER BY count DESC
FETCH FIRST 20 ROWS ONLY
"

Unknown/Unclassified Devices

netapi ise dc query "
SELECT
    p.ENDPOINT_ID AS mac,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    p.ENDPOINT_PROFILE,
    p.MATCHED_CERTAINTY_FACTOR AS certainty
FROM PROFILED_ENDPOINTS_SUMMARY p
JOIN ENDPOINTS_DATA e ON p.ENDPOINT_ID = e.MAC_ADDRESS
WHERE UPPER(p.ENDPOINT_PROFILE) LIKE '%UNKNOWN%'
ORDER BY e.UPDATE_TIME DESC
FETCH FIRST 50 ROWS ONLY
"

Low Certainty Profiles (Might Be Wrong)

netapi ise dc query "
SELECT
    ENDPOINT_ID AS mac,
    ENDPOINT_PROFILE,
    MATCHED_CERTAINTY_FACTOR AS certainty
FROM PROFILED_ENDPOINTS_SUMMARY
WHERE MATCHED_CERTAINTY_FACTOR < 20
ORDER BY MATCHED_CERTAINTY_FACTOR ASC
FETCH FIRST 30 ROWS ONLY
"

Endpoints by Identity Group

netapi ise dc query "
SELECT
    IDENTITY_GROUP,
    COUNT(*) AS count
FROM PROFILED_ENDPOINTS_SUMMARY
WHERE IDENTITY_GROUP IS NOT NULL
GROUP BY IDENTITY_GROUP
ORDER BY count DESC
"

ENDPOINT_IDENTITY_GROUPS

Identity group definitions - the groups endpoints can be assigned to.

netapi ise dc query "SELECT * FROM ENDPOINT_IDENTITY_GROUPS"

JOIN Examples

Full Endpoint View (Data + Profile)

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    e.HARDWARE_MANUFACTURER AS vendor,
    p.ENDPOINT_PROFILE,
    p.IDENTITY_GROUP,
    p.MATCHED_CERTAINTY_FACTOR AS certainty,
    TO_CHAR(e.UPDATE_TIME, 'YYYY-MM-DD') AS last_seen
FROM ENDPOINTS_DATA e
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
ORDER BY e.UPDATE_TIME DESC
FETCH FIRST 50 ROWS ONLY
"

Endpoints with Recent Authentication

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    p.ENDPOINT_PROFILE,
    r.PASSED,
    r.AUTHENTICATION_PROTOCOL,
    TO_CHAR(r.TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS last_auth
FROM ENDPOINTS_DATA e
JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
JOIN (
    SELECT CALLING_STATION_ID, PASSED, AUTHENTICATION_PROTOCOL, TIMESTAMP_TIMEZONE,
           ROW_NUMBER() OVER (PARTITION BY CALLING_STATION_ID ORDER BY TIMESTAMP_TIMEZONE DESC) AS rn
    FROM RADIUS_AUTHENTICATIONS
    WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
) r ON e.MAC_ADDRESS = r.CALLING_STATION_ID AND r.rn = 1
ORDER BY r.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Profile Summary by Vendor

netapi ise dc query "
SELECT
    e.HARDWARE_MANUFACTURER AS vendor,
    p.ENDPOINT_PROFILE AS profile,
    COUNT(*) AS count
FROM ENDPOINTS_DATA e
JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
WHERE e.HARDWARE_MANUFACTURER IS NOT NULL
GROUP BY e.HARDWARE_MANUFACTURER, p.ENDPOINT_PROFILE
ORDER BY vendor, count DESC
FETCH FIRST 50 ROWS ONLY
"

See Also