DataConnect SQL Reference

# Required
ISE_DC_USER="dataconnect"
ISE_DC_PASS="<password>"
ISE_DC_HOST="ise-01.inside.domusdigitalis.dev"
ISE_DC_PORT="2484"
ISE_DC_SERVICE="cpm10"

sqlplus "${ISE_DC_USER}/${ISE_DC_PASS}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=${ISE_DC_HOST})(PORT=${ISE_DC_PORT}))(CONNECT_DATA=(SERVICE_NAME=${ISE_DC_SERVICE})))"

import oracledb

dsn = f"{ISE_DC_HOST}:{ISE_DC_PORT}/{ISE_DC_SERVICE}"
conn = oracledb.connect(user=ISE_DC_USER, password=ISE_DC_PASS, dsn=dsn)
cursor = conn.cursor()

netapi ise dc test

SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    USERNAME,
    CALLING_STATION_ID AS mac,
    FRAMED_IP_ADDRESS AS ip,
    CASE PASSED WHEN 1 THEN 'PASS' ELSE 'FAIL' END AS status,
    AUTHENTICATION_PROTOCOL AS protocol,
    POLICY_SET_NAME AS policy_set,
    NAS_IP_ADDRESS AS nas_ip
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY;

SELECT
    USERNAME,
    CALLING_STATION_ID AS mac,
    FRAMED_IP_ADDRESS AS ip,
    POLICY_SET_NAME,
    AUTHORIZATION_RULE,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp
FROM RADIUS_AUTHENTICATION
WHERE AUTHENTICATION_PROTOCOL = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND PASSED = 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
    USERNAME,
    CALLING_STATION_ID AS mac,
    FAILURE_REASON,
    NAS_IP_ADDRESS AS nas_ip,
    NAS_PORT_ID AS port,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp
FROM RADIUS_AUTHENTICATION
WHERE PASSED = 0
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 100 ROWS ONLY;

SELECT
    FAILURE_REASON,
    COUNT(*) AS failure_count
FROM RADIUS_AUTHENTICATION
WHERE PASSED = 0
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY FAILURE_REASON
ORDER BY failure_count DESC
FETCH FIRST 10 ROWS ONLY;

-- Replace XX-XX-XX-XX-XX-XX with target MAC (DataConnect uses dashes)
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    USERNAME,
    FRAMED_IP_ADDRESS AS ip,
    CASE PASSED WHEN 1 THEN 'PASS' ELSE 'FAIL' END AS status,
    AUTHENTICATION_PROTOCOL AS protocol,
    FAILURE_REASON,
    POLICY_SET_NAME,
    AUTHORIZATION_PROFILES
FROM RADIUS_AUTHENTICATION
WHERE CALLING_STATION_ID = '28-92-00-89-EF-77'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    CALLING_STATION_ID AS mac,
    FRAMED_IP_ADDRESS AS ip,
    CASE PASSED WHEN 1 THEN 'PASS' ELSE 'FAIL' END AS status,
    NAS_IP_ADDRESS AS nas_ip,
    NAS_PORT_ID AS port,
    POLICY_SET_NAME
FROM RADIUS_AUTHENTICATION
WHERE UPPER(USERNAME) = UPPER('evanusmodestus')
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY;

SELECT COUNT(*) AS endpoint_count
FROM ENDPOINTS;

SELECT
    MAC_ADDRESS,
    ENDPOINT_POLICY AS profile,
    IDENTITY_GROUP_NAME AS identity_group,
    STATIC_ASSIGNMENT,
    TO_CHAR(CREATE_TIME, 'YYYY-MM-DD') AS first_seen,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') AS last_seen
FROM ENDPOINTS
ORDER BY UPDATE_TIME DESC
FETCH FIRST 100 ROWS ONLY;

SELECT
    ENDPOINT_POLICY AS profile,
    COUNT(*) AS count
FROM ENDPOINTS
GROUP BY ENDPOINT_POLICY
ORDER BY count DESC;

SELECT
    MAC_ADDRESS,
    ENDPOINT_POLICY AS profile,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') AS last_seen,
    ROUND(SYSDATE - UPDATE_TIME) AS days_inactive
FROM ENDPOINTS
WHERE UPDATE_TIME < SYSDATE - 90
ORDER BY UPDATE_TIME ASC
FETCH FIRST 100 ROWS ONLY;

SELECT
    ep.EDF_MACADDRESS AS mac,
    ep.EDF_HOSTNAME AS hostname,
    ep.EDF_ENDPOINTIP AS ip,
    ep.EDF_ENDPOINTPOLICY AS profile,
    ep.EDF_MATCHEDVALUE AS certainty_factor,
    ep.EDF_1_DA_MANUFACTURERNAME AS manufacturer,
    ep.EDF_1_DA_MODELNAME AS model,
    TO_CHAR(ep.EDF_UPDATE_TIME, 'YYYY-MM-DD HH24:MI') AS last_profiled
FROM CEPM.EDF_EP_MASTER ep
WHERE ep.EDF_UPDATE_TIME > SYSDATE - 7
ORDER BY ep.EDF_UPDATE_TIME DESC
FETCH FIRST 50 ROWS ONLY;

SELECT
    ep.EDF_MACADDRESS AS mac,
    ep.EDF_ENDPOINTPOLICY AS profile,
    COALESCE(ig.SEC_ROLENAME, 'None') AS identity_group,
    ep.EDF_1_DA_MANUFACTURERNAME AS manufacturer,
    TO_CHAR(ep.EDF_UPDATE_TIME, 'YYYY-MM-DD') AS last_seen
FROM CEPM.EDF_EP_MASTER ep
LEFT JOIN CEPM.EDF_EP_IDGROUP_MASTER igm ON ep.EDF_EPGUID = igm.EDF_EPGUID
LEFT JOIN CEPM.SEC_ROLE_MASTER ig ON igm.EDF_IDGROUPGUID = ig.SEC_ROLEGUID
WHERE ep.EDF_UPDATE_TIME > SYSDATE - 30
ORDER BY ep.EDF_UPDATE_TIME DESC
FETCH FIRST 100 ROWS ONLY;

SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    FRAMED_IP_ADDRESS AS ip,
    ACCT_SESSION_TIME AS session_seconds,
    ROUND(ACCT_SESSION_TIME / 3600, 1) AS session_hours,
    ACCT_INPUT_OCTETS AS bytes_in,
    ACCT_OUTPUT_OCTETS AS bytes_out,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS timestamp
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Stop'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY ACCT_SESSION_TIME DESC
FETCH FIRST 50 ROWS ONLY;

SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    FRAMED_IP_ADDRESS AS ip,
    NAS_IP_ADDRESS AS nas_ip,
    NAS_PORT_ID AS port,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS start_time
FROM RADIUS_ACCOUNTING
WHERE ACCT_STATUS_TYPE = 'Start'
  AND CALLING_STATION_ID NOT IN (
      SELECT CALLING_STATION_ID
      FROM RADIUS_ACCOUNTING
      WHERE ACCT_STATUS_TYPE = 'Stop'
        AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  )
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC;

SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24') AS hour,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 0 THEN 1 ELSE 0 END) AS failed
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24')
ORDER BY hour;

SELECT
    NAS_IP_ADDRESS AS nas_ip,
    COUNT(*) AS auth_count,
    SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 0 THEN 1 ELSE 0 END) AS failed
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY NAS_IP_ADDRESS
ORDER BY auth_count DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
    AUTHENTICATION_PROTOCOL AS protocol,
    COUNT(*) AS auth_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentage
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND PASSED = 1
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY auth_count DESC;

SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD') AS date,
    COUNT(DISTINCT USERNAME) AS unique_users,
    COUNT(DISTINCT CALLING_STATION_ID) AS unique_devices
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 30
  AND PASSED = 1
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD')
ORDER BY date DESC;

-- Step 1: Check if device ever authenticated
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS timestamp,
    USERNAME,
    CASE PASSED WHEN 1 THEN 'PASS' ELSE 'FAIL' END AS status,
    FAILURE_REASON,
    AUTHENTICATION_PROTOCOL,
    POLICY_SET_NAME,
    AUTHORIZATION_RULE
FROM RADIUS_AUTHENTICATION
WHERE CALLING_STATION_ID = '28-92-00-89-EF-77'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 10 ROWS ONLY;

SELECT
    POLICY_SET_NAME,
    AUTHORIZATION_RULE,
    COUNT(*) AS hit_count
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND PASSED = 1
GROUP BY POLICY_SET_NAME, AUTHORIZATION_RULE
ORDER BY hit_count DESC;

SELECT
    POLICY_SET_NAME,
    ROUND(AVG(RESPONSE_TIME), 0) AS avg_ms,
    ROUND(MAX(RESPONSE_TIME), 0) AS max_ms,
    COUNT(*) AS auth_count
FROM RADIUS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY POLICY_SET_NAME
ORDER BY avg_ms DESC;