ISE DataConnect

Direct SQL access to ISE’s Oracle database. For reports, analytics, and historical queries.

Overview

Connection

JDBC: jdbc:oracle:thin:@ise-01.inside.domusdigitalis.dev:2484/cpm10

Auth

DataConnect user (separate from ISE admin)

Format

SQL query results (tabular)

Enable

Administration > System > Settings > DataConnect

Key Views

View Contents

RADIUS_AUTHENTICATIONS

All RADIUS auth events (48h live, or aggregated)

ENDPOINTS

Endpoint database

NETWORK_DEVICES

NAD inventory

POLICY_SETS

Policy set configuration

ADMIN_USERS

Admin account audit

Examples

Basic Query

netapi ise dc query "SELECT * FROM RADIUS_AUTHENTICATIONS FETCH FIRST 10 ROWS ONLY"

Recent Authentications

netapi ise dc query "
SELECT
  username,
  mac_address,
  authentication_method,
  nas_ip_address,
  passed,
  timestamp
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
ORDER BY timestamp DESC
FETCH FIRST 20 ROWS ONLY
"

Failed Authentications

netapi ise dc query "
SELECT
  username,
  mac_address,
  failure_reason,
  nas_ip_address,
  timestamp
FROM radius_authentications
WHERE failed = 1
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC
FETCH FIRST 50 ROWS ONLY
"

EAP-TLS Authentications

netapi ise dc query "
SELECT
  username,
  mac_address,
  authentication_method,
  nas_ip_address,
  passed,
  timestamp
FROM radius_authentications
WHERE authentication_method LIKE '%tls%'
  AND timestamp > SYSDATE - 7
ORDER BY timestamp DESC
"

Session Counts by Policy

netapi ise dc query "
SELECT
  authentication_method,
  COUNT(*) AS auth_count,
  SUM(CASE WHEN passed = 1 THEN 1 ELSE 0 END) AS passed,
  SUM(CASE WHEN failed = 1 THEN 1 ELSE 0 END) AS failed
FROM radius_authentications
WHERE timestamp > SYSDATE - 1
GROUP BY authentication_method
ORDER BY auth_count DESC
"

jq Patterns

# Parse JSON output
netapi ise dc --format json query "SELECT username, passed FROM radius_authentications FETCH FIRST 10 ROWS ONLY" \
  | jq '.[] | "\(.username): \(if .passed == 1 then "PASS" else "FAIL" end)"'

# Count by auth method
netapi ise dc --format json query "SELECT authentication_method, COUNT(*) AS cnt FROM radius_authentications WHERE timestamp > SYSDATE - 1 GROUP BY authentication_method" \
  | jq -r '.[] | "\(.authentication_method)\t\(.cnt)"' | sort -t$'\t' -k2 -rn

netapi Commands

# Basic query
netapi ise dc query "SELECT USERNAME FROM RADIUS_AUTHENTICATIONS FETCH FIRST 10 ROWS ONLY"

# With format
netapi ise dc --format json query "SELECT * FROM ENDPOINTS FETCH FIRST 5 ROWS ONLY"

Environment Setup

# DataConnect uses separate credentials
export ISE_DC_HOST="ise-01.inside.domusdigitalis.dev"
export ISE_DC_PORT="2484"
export ISE_DC_USER="dataconnect"
export ISE_DC_PASS="<from gopass>"

Learnings

DataConnect Gotchas

  • Requires DataConnect feature to be enabled (license dependent)

  • Uses Oracle SQL syntax - FETCH FIRST N ROWS ONLY not LIMIT N

  • Date comparisons use SYSDATE - e.g., TIMESTAMP > SYSDATE - 1 for last 24h

  • Port 2484 (Oracle listener)

  • Credentials are separate from ISE admin - create DataConnect user in ISE

  • RADIUS_AUTHENTICATIONS only has ~48h of data; use aggregated views for longer