Data Collection

Data Collection

Data Scope

Item Detail

Data source

ISE DataConnect via netapi ise dc query — Oracle JDBC port 2484

Primary views

RADIUS_AUTHENTICATIONS_WEEK, MISCONFIGURED_SUPPLICANTS_VIEW

Misconfigured supplicants

70,294 records confirmed via COUNT(*)

Key identifier field

AUTHENTICATION_PROTOCOL — identifies MSCHAPv2 vs EAP-TLS vs TEAP endpoints

Timezone gotcha

TIMESTAMP(6) WITH TIME ZONE columns cause DPY-3022 in oracledb thin mode — avoid selecting them

ISE DataConnect Queries

Auth Protocol Breakdown — Wired

netapi ise dc query "
SELECT
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  AUTHORIZATION_RULE,
  FAILURE_REASON,
  SUM(PASSED) AS TOTAL_PASSED,
  SUM(FAILED) AS TOTAL_FAILED,
  COUNT(*)    AS RECORD_COUNT
FROM DATACONNECT.RADIUS_AUTHENTICATIONS_WEEK
GROUP BY
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  AUTHORIZATION_RULE,
  FAILURE_REASON
ORDER BY TOTAL_FAILED DESC, TOTAL_PASSED DESC
" > /tmp/ise-wired-auth.csv

Auth Protocol Breakdown — Wireless Staff

netapi ise dc query "
SELECT
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  AUTHORIZATION_RULE,
  NAS_PORT_TYPE,
  FAILURE_REASON,
  SUM(PASSED) AS TOTAL_PASSED,
  SUM(FAILED) AS TOTAL_FAILED,
  COUNT(*)    AS RECORD_COUNT
FROM DATACONNECT.RADIUS_AUTHENTICATIONS_WEEK
WHERE NAS_PORT_TYPE = 'Wireless - IEEE 802.11'
GROUP BY
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  AUTHORIZATION_RULE,
  NAS_PORT_TYPE,
  FAILURE_REASON
ORDER BY TOTAL_FAILED DESC, TOTAL_PASSED DESC
" > /tmp/ise-wireless-auth.csv

Misconfigured Supplicants

netapi ise dc query "
SELECT
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  NETWORK_DEVICE_NAME,
  DEVICE_TYPE,
  LOCATION,
  NAS_PORT_TYPE,
  FAILURE_REASON,
  SUM(PASSED) AS TOTAL_PASSED,
  SUM(FAILED) AS TOTAL_FAILED
FROM DATACONNECT.MISCONFIGURED_SUPPLICANTS_VIEW
GROUP BY
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  CREDENTIAL_CHECK,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  NETWORK_DEVICE_NAME,
  DEVICE_TYPE,
  LOCATION,
  NAS_PORT_TYPE,
  FAILURE_REASON
ORDER BY TOTAL_FAILED DESC
" > /tmp/ise-misconfigured.csv

MSCHAPv2 Specific — Unique Endpoints

netapi ise dc query "
SELECT
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  NAS_PORT_TYPE,
  COUNT(*)    AS RECORD_COUNT,
  SUM(PASSED) AS TOTAL_PASSED,
  SUM(FAILED) AS TOTAL_FAILED
FROM DATACONNECT.RADIUS_AUTHENTICATIONS_WEEK
WHERE AUTHENTICATION_PROTOCOL LIKE '%MSCHAPv2%'
   OR AUTHENTICATION_PROTOCOL LIKE '%MSCHAP%'
   OR AUTHENTICATION_PROTOCOL LIKE '%PEAP%'
GROUP BY
  AUTHENTICATION_PROTOCOL,
  AUTHENTICATION_METHOD,
  ENDPOINT_PROFILE,
  IDENTITY_GROUP,
  POLICY_SET_NAME,
  NAS_PORT_TYPE
ORDER BY RECORD_COUNT DESC
" > /tmp/ise-mschapv2.csv

Python Report Generation

Excel report with 11 sheets including Executive Summary, Protocol Distribution, MSCHAPv2 Detail, Migration Status, and 10 embedded charts (Catppuccin Mocha themed).

Output: mschapv2_migration_report.xlsx

Key computed metrics:

  • MigrationStatus — Target (EAP-TLS/TEAP), Migrate (MSCHAPv2/PEAP), MAB (N/A), Review

  • FailureRate — TotalFailed / TotalEvents x 100

  • Migration progress — % of events already on target protocol vs needing migration

Script: siem-migration.adoc ISE Python Report section (to be promoted to scripts/ when stabilized).

DataConnect Gotchas

Gotcha Detail

TIMESTAMP(6) WITH TIME ZONE

Causes DPY-3022 in oracledb thin mode. Avoid selecting these columns.

MISCONFIGURED_SUPPLICANTS_VIEW

Does not have RECORD_COUNT column — use TOTAL_FAILED for counting.

Oracle JDBC port

2484 (TLS), not 1521. Configured in netapi DataConnect settings.

NAS_PORT_TYPE filtering

Use exact string: 'Wireless - IEEE 802.11' for wireless staff.