dc query

Synopsis

netapi ise dc query "<SQL>"

Description

Execute raw SQL queries against ISE DataConnect views. Use with caution - this gives you direct access to ISE’s Oracle database. Powerful for custom analytics and edge cases not covered by other commands.

Arguments

Argument Description

SQL

SQL query to execute (required, quote the entire query)

Options

This command has no options.

Use the global --format flag at the dc level:

netapi ise dc --format json query "SELECT ..."

Usage

# Basic query
netapi ise dc query "SELECT COUNT(*) FROM ENDPOINTS_DATA"

# With filters
netapi ise dc query "SELECT MAC_ADDRESS, HOSTNAME FROM ENDPOINTS_DATA WHERE ROWNUM <= 10"

# JSON output for processing
netapi ise dc --format json query "SELECT MAC_ADDRESS, ENDPOINT_IP FROM ENDPOINTS_DATA"

Available Views

ISE DataConnect exposes these Oracle views:

View Description

ENDPOINTS_DATA

Endpoint database (MAC_ADDRESS, HOSTNAME, ENDPOINT_IP, ENDPOINT_POLICY)

RADIUS_AUTHENTICATIONS

Authentication attempts (USERNAME, CALLING_STATION_ID, PASSED, TIMESTAMP_TIMEZONE, FAILURE_REASON)

RADIUS_ACCOUNTING

Session accounting (SESSION_ID, ACCT_STATUS_TYPE, ACCT_SESSION_TIME, ACCT_INPUT_OCTETS, ACCT_OUTPUT_OCTETS)

PROFILED_ENDPOINTS_SUMMARY

Profiling data (ENDPOINT_ID, ENDPOINT_PROFILE, IDENTITY_GROUP)

NODE_LIST

ISE node information (hostname, roles, IP)

NETWORK_DEVICES

Network access devices (switches, WLCs)

Example Queries

Count All Endpoints

netapi ise dc query "SELECT COUNT(*) as total FROM ENDPOINTS_DATA"

Recent Authentications with Details

netapi ise dc query "
SELECT
  TIMESTAMP_TIMEZONE,
  CALLING_STATION_ID as MAC,
  USERNAME,
  PASSED
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1/24
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"

Endpoints with Specific Vendor OUI

# Find all Apple devices (OUI 3C:EC:EF)
netapi ise dc query "
SELECT MAC_ADDRESS, HOSTNAME, ENDPOINT_IP
FROM ENDPOINTS_DATA
WHERE MAC_ADDRESS LIKE '3C:EC:EF%'
"

Authentication Success Rate

netapi ise dc query "
SELECT
  COUNT(*) as total,
  SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as passed,
  SUM(CASE WHEN PASSED != 'Pass' THEN 1 ELSE 0 END) as failed
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
"

Join Endpoints with Profiler Data

netapi ise dc query "
SELECT
  e.MAC_ADDRESS,
  e.HOSTNAME,
  p.ENDPOINT_PROFILE,
  p.IDENTITY_GROUP
FROM ENDPOINTS_DATA e
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
WHERE e.HOSTNAME IS NOT NULL
FETCH FIRST 50 ROWS ONLY
"

Session Duration Analysis

netapi ise dc query "
SELECT
  CALLING_STATION_ID as MAC,
  AVG(ACCT_SESSION_TIME) as avg_session_seconds,
  COUNT(*) as session_count
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID
HAVING COUNT(*) > 5
ORDER BY avg_session_seconds DESC
FETCH FIRST 20 ROWS ONLY
"

Authentication by Method (Last 24h)

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

iPSK Authentication History

# Find all iPSK auths (IoT WIFI iPSK policy set)
netapi ise dc query "
SELECT
  TIMESTAMP_TIMEZONE,
  CALLING_STATION_ID as MAC,
  PASSED,
  AUTHORIZATION_PROFILES,
  FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE POLICY_SET_NAME = 'IoT WIFI iPSK'
AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"

iPSK Failures by SSID

# Find which SSID is getting failures
netapi ise dc query "
SELECT
  CALLED_STATION_ID as SSID,
  FAILURE_REASON,
  COUNT(*) as count
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED != 'Pass'
AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY CALLED_STATION_ID, FAILURE_REASON
ORDER BY count DESC
"

Data Usage (Bytes Transferred)

# Top data consumers
netapi ise dc query "
SELECT
  CALLING_STATION_ID as MAC,
  SUM(ACCT_INPUT_OCTETS) as bytes_in,
  SUM(ACCT_OUTPUT_OCTETS) as bytes_out,
  SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) as total_bytes
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID
ORDER BY total_bytes DESC
FETCH FIRST 20 ROWS ONLY
"

Session Duration by Endpoint

# Average session time per device
netapi ise dc query "
SELECT
  CALLING_STATION_ID as MAC,
  COUNT(*) as sessions,
  ROUND(AVG(ACCT_SESSION_TIME)/60, 1) as avg_minutes,
  ROUND(SUM(ACCT_SESSION_TIME)/3600, 1) as total_hours
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID
ORDER BY total_hours DESC
FETCH FIRST 20 ROWS ONLY
"

Find Specific MAC Full History

# Complete auth history for a MAC
netapi ise dc query "
SELECT
  TIMESTAMP_TIMEZONE,
  PASSED,
  AUTHENTICATION_METHOD,
  POLICY_SET_NAME,
  AUTHORIZATION_PROFILES,
  FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID = '9C:83:06:CE:89:46'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Compare Working vs Failed Device

# Side-by-side comparison
netapi ise dc query "
SELECT
  CALLING_STATION_ID as MAC,
  PASSED,
  POLICY_SET_NAME,
  AUTHORIZATION_PROFILES,
  FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID IN ('9C:83:06:CE:89:46', '70:15:FB:F8:47:EC')
AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
"

Oracle SQL Tips

Oracle Equivalent

SYSDATE - 1

Last 24 hours

SYSDATE - 1/24

Last 1 hour

SYSDATE - 7

Last 7 days

FETCH FIRST N ROWS ONLY

Limit results (Oracle 12c+)

ROWNUM ⇐ N

Limit results (older syntax)

||

String concatenation

Safety Notes

  • This is read-only access - you cannot modify ISE data

  • Avoid SELECT * on large tables - specify columns

  • Use FETCH FIRST N ROWS ONLY to limit results

  • Complex queries may impact ISE MnT performance

See Also