DataConnect
Overview
DataConnect provides direct SQL access to ISE’s operational database for reporting and analytics.
Port: 2484 (TLS)
Authentication: Oracle credentials
Driver: oracledb (thin client)
Initialization
from netapi.vendors.cisco.ise import DataConnectClient
# From dsec secrets
client = DataConnectClient.from_dsec('home.lab.ise.dataconnect')
# Manual initialization
client = DataConnectClient(
host='ise-01.inside.domusdigitalis.dev',
port=2484,
service_name='cpm10',
username='dataconnect',
password='secret'
)
Query Operations
CLI Commands
All commands support --format json for scripting.
Connection & Stats
netapi ise dc test # Test connection
netapi ise dc stats # Auth statistics (24h)
netapi ise dc stats --hours 168 # Last week
RADIUS Authentication
# Recent authentications
netapi ise dc recent # Last hour
netapi ise dc recent --hours 4 --status failed
netapi ise dc recent --mac 3C:EC:EF:43:50:42
# Failed authentications
netapi ise dc failed --hours 24
# Top failure reasons
netapi ise dc top-failures
netapi ise dc top-failures --hours 1 # Incident response
# Auth trends (hourly breakdown)
netapi ise dc trends --hours 48
# Auth method distribution
netapi ise dc auth-methods
Endpoint Analysis
# List endpoints
netapi ise dc endpoints
netapi ise dc endpoints --policy "Linux"
netapi ise dc endpoints --search "modestus"
# Single endpoint details
netapi ise dc endpoint 3C:EC:EF:43:50:42
netapi ise dc endpoint 3C:EC:EF:43:50:42 --with-auth
# Comprehensive session view (crown jewel)
netapi ise dc session 3C:EC:EF:43:50:42
# Auth history timeline
netapi ise dc auth-history 3C:EC:EF:43:50:42 --hours 48
# Stale endpoints (compliance)
netapi ise dc stale --days 30
netapi ise dc stale --days 90 # Dormant devices
Profiler
netapi ise dc profiler # Endpoints by profiler policy
netapi ise dc device-types # Endpoints by device type
NAS Health
netapi ise dc nas # Switch/WLC health by success rate
netapi ise dc nas --hours 48 --min 50 # Devices with 50+ auths
User Analysis
netapi ise dc users # Top users by auth count
netapi ise dc users --hours 168 # Last week
RADIUS Accounting
# Session duration and data usage
netapi ise dc accounting
netapi ise dc accounting --mac 3C:EC:EF:43:50:42
netapi ise dc accounting --min-bytes 1000000 # Sessions >1MB
Accounting (last 24h) ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━┓ ┃ Time ┃ MAC ┃ User ┃ IP ┃ Duration ┃ In ┃ Out ┃ ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━┩ │ 2026-01-24 │ 14:F6:D8:... │ modestus │ 10.50.10.5 │ 5h 17m │ 68.1MB │ 2.9GB │ │ 2026-01-24 │ 70:15:FB:... │ 70-15-.. │ 10.50.10.8 │ 3h 59m │ 158.0MB │ 45.1MB │ └────────────┴──────────────┴──────────┴────────────┴──────────┴─────────┴────────┘
TACACS+ (Device Admin)
# Device login events
netapi ise dc tacacs-auth
netapi ise dc tacacs-auth --status failed
netapi ise dc tacacs-auth --user admin
# Command authorization (audit trail)
netapi ise dc tacacs-commands
netapi ise dc tacacs-commands --user admin
netapi ise dc tacacs-commands --device core-sw
netapi ise dc tacacs-commands --command "show run"
TACACS Commands (last 24h) ┏━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┓ ┃ Time ┃ User ┃ Status ┃ Device ┃ Command ┃ Args ┃ ┡━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━┩ │ 2026-01-24 │ admin │ Pass │ core-sw-01 │ show │ running-config │ │ 2026-01-24 │ admin │ Pass │ core-sw-01 │ configure │ terminal │ └────────────┴────────┴────────┴────────────┴─────────────┴──────────────────┘
Admin Audit
# ISE console logins
netapi ise dc admin-logins
netapi ise dc admin-logins --admin superadmin
netapi ise dc admin-logins --hours 168 # Last week
# Configuration changes
netapi ise dc config-changes
netapi ise dc config-changes --admin superadmin
netapi ise dc config-changes --type "Policy" # Policy changes only
Admin Logins (last 24h) ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓ ┃ Time ┃ Admin ┃ IP Address ┃ Interface ┃ Operation ┃ ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩ │ 2026-01-24 │ evanusmodestus │ 10.50.10.111 │ GUI │ User logged out │ │ 2026-01-24 │ domus_ers_adm │ 169.254.4.27 │ ERS │ Admin auth success │ └────────────┴────────────────┴──────────────┴───────────┴─────────────────────┘
Threat & Security
# Threat-Centric NAC events (AMP, Qualys, Rapid7)
netapi ise dc threats
netapi ise dc threats --severity HIGH
netapi ise dc threats --hours 168
# Change of Authorization events
netapi ise dc coa-events
netapi ise dc coa-events --mac 3C:EC:EF:43:50:42
Posture (Compliance)
netapi ise dc posture
netapi ise dc posture --status noncompliant
netapi ise dc posture --hours 168
Posture Events (last 24h) ┏━━━━━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━┓ ┃ Time ┃ MAC ┃ User ┃ Status ┃ Policy ┃ AV ┃ ┡━━━━━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━┩ │ 2026-01-24 │ 14:F6:D8:... │ modestus │ Compliant │ Windows_Corp │ Yes │ │ 2026-01-24 │ 3C:EC:EF:... │ guest │ NonComplia │ Guest_Policy │ No │ └────────────┴──────────────┴──────────┴─────────────┴───────────────┴────────┘ 3 compliant | 1 non-compliant | 4 total
Troubleshooting
# Misconfigured NAS devices
netapi ise dc misconfigured-nas
netapi ise dc misconfigured-nas --hours 168
# Misconfigured supplicants (802.1X issues)
netapi ise dc misconfigured-supplicants
Raw SQL Queries
For complex analysis not covered by built-in commands:
# Basic query
netapi ise dc query "SELECT * FROM RADIUS_AUTHENTICATIONS FETCH FIRST 5 ROWS ONLY"
# Aggregations
netapi ise dc query "SELECT authentication_method, COUNT(*) as cnt
FROM RADIUS_AUTHENTICATIONS
GROUP BY authentication_method
ORDER BY cnt DESC"
# JSON output for scripting
netapi ise dc -f json query "SELECT nas_ip_address, COUNT(*) as auths
FROM RADIUS_AUTHENTICATIONS
GROUP BY nas_ip_address"
Key tables:
| Table | Description |
|---|---|
|
Main auth log |
|
Session accounting |
|
Device admin auth |
|
Commands on devices |
|
ISE admin logins |
|
Config changes |
|
Threat events |
|
CoA events |
|
Posture results |
|
NAS issues |
|
Supplicant issues |
Schema Discovery
List all available views:
netapi ise dc query "SELECT view_name FROM all_views WHERE owner = 'DATACONNECT' ORDER BY view_name"
Get columns for a specific table:
netapi ise dc query "SELECT column_name FROM all_tab_columns WHERE table_name = 'RADIUS_AUTHENTICATIONS' ORDER BY column_id"
CISO Security Analytics
EAP-TLS Certificate Authentication Report
Validate EAP-TLS endpoints using Vault PKI certificates:
netapi ise dc query "
SELECT
CALLING_STATION_ID as MAC,
USERNAME,
AUTHENTICATION_PROTOCOL,
IDENTITY_STORE,
AUTHORIZATION_PROFILES,
PASSED,
TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL LIKE '%TLS%'
AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 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
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
AND PASSED = 'Pass'
AND TIMESTAMP_TIMEZONE > SYSDATE - 7
"
Authentication Protocol Breakdown
netapi ise dc query "
SELECT
AUTHENTICATION_PROTOCOL,
COUNT(*) as count,
COUNT(DISTINCT CALLING_STATION_ID) as unique_macs
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY count DESC
"
Failed Authentication Analysis
netapi ise dc query "
SELECT
CALLING_STATION_ID as MAC,
USERNAME,
AUTHENTICATION_PROTOCOL,
FAILURE_REASON,
TIMESTAMP_TIMEZONE
FROM RADIUS_AUTHENTICATIONS
WHERE FAILED = 'Fail'
AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"
EAP-TLS by Authorization Profile
netapi ise dc query "
SELECT
AUTHORIZATION_PROFILES,
COUNT(*) as auth_count,
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 AUTHORIZATION_PROFILES
ORDER BY auth_count DESC
"
Security Risk Dashboard (Executive Summary)
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
"
Repeat Offenders (Devices with Multiple Failures)
Devices with 3+ failed authentications 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
"
After-Hours Authentication (Anomaly Detection)
Authentications outside business hours (6 PM - 6 AM) may warrant investigation:
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
"
NAS Device Health (Infrastructure Issues)
Network access devices with high failure rates indicate configuration problems:
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
"
Top Failure Reasons (Root Cause Analysis)
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 15 ROWS ONLY
"
Authentication Velocity (Brute Force Detection)
Devices with >10 auth attempts in 1 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
"
Unknown/Unprofilered 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
"
TACACS+ Privileged Command Audit
Track privileged commands executed on network devices:
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 50 ROWS ONLY
"
Admin Configuration Changes (Change Audit)
Track ISE configuration changes for compliance:
netapi ise dc query "
SELECT
LOGGED_AT as time,
ADMIN_NAME,
ADMIN_IP_ADDRESS,
INTERFACE,
OBJECT_TYPE,
OBJECT_NAME,
ADMIN_ACTION
FROM MNT.CONFIG_CHANGE
WHERE LOGGED_AT > SYSDATE - 30
ORDER BY LOGGED_AT DESC
FETCH FIRST 100 ROWS ONLY
"
Dormant Devices (Compliance Gap)
Devices that haven’t authenticated in 30+ days (stale endpoint risk):
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
"
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
"
Oracle date shortcuts:
-
SYSDATE - 1= last 24 hours -
SYSDATE - 1/24= last hour -
SYSDATE - 7= last week
SSL Certificate Configuration
ISE DataConnect uses a self-signed Oracle certificate. You have two options:
Option 2: Certificate Verification (Production)
Extract and configure the ISE DataConnect certificate:
# Extract cert (creates dir if missing, skips if exists)
CERT=~/.secrets/certs/<domain>/ise/dataconnect.crt
[ -f "$CERT" ] || {
mkdir -p "$(dirname "$CERT")" && \
echo | openssl s_client -connect <mnt-host>:2484 2>&1 | \
sed -n '/BEGIN CERT/,/END CERT/p' > "$CERT"
}
# Verify the cert
openssl x509 -in "$CERT" -noout -issuer -subject -dates
Add to your secrets (network.env):
@ISE_DATACONNECT_HOST = {{ISE_MNT_IP}}
@ISE_DATACONNECT_USER = dataconnect
@ISE_DATACONNECT_PASS = <password>
@ISE_DATACONNECT_PORT = 2484
@ISE_DATACONNECT_SERVICE = cpm10
@ISE_DATACONNECT_DSN = tcps://{{ISE_DATACONNECT_HOST}}:{{ISE_DATACONNECT_PORT}}/{{ISE_DATACONNECT_SERVICE}}
@ISE_DATACONNECT_CA = ~/.secrets/certs/<domain>/ise/dataconnect.crt
Test:
dsource <domain> dev/network && netapi ise dc test
# Expected: ✓ Connected to ISE DataConnect
Troubleshooting
ORA-01017: Invalid username/password
The dataconnect account may be locked after failed attempts.
Fix: ISE Admin GUI → Administration → System → Settings → Data Connect → toggle Off/On, set new password.
SSL Certificate Verify Failed
If using ISE_DATACONNECT_CA and getting SSL errors:
-
Ensure the cert was extracted from the correct MnT node (not PAN)
-
DataConnect runs on MnT node in distributed deployments
-
Verify cert is valid:
openssl x509 -in $CERT -noout -dates
|
The client uses an empty |