DataConnect SQL Cookbook

# Auth success rate (last hour)
netapi ise dc query "
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1/24
"

netapi ise dc query "
SELECT
    FAILURE_REASON,
    COUNT(*) AS count
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
GROUP BY FAILURE_REASON
ORDER BY count DESC
FETCH FIRST 10 ROWS ONLY
"

netapi ise dc query "
SELECT
    AUTHENTICATION_PROTOCOL,
    COUNT(*) AS total,
    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(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY AUTHENTICATION_PROTOCOL
ORDER BY total DESC
"

netapi ise dc query "
SELECT
    POLICY_SET_NAME,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY POLICY_SET_NAME
ORDER BY total DESC
"

netapi ise dc query "
SELECT
    AUTHORIZATION_PROFILES,
    COUNT(*) AS count,
    COUNT(DISTINCT CALLING_STATION_ID) AS unique_macs
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY AUTHORIZATION_PROFILES
ORDER BY count DESC
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') AS hour,
    COUNT(*) AS total,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) AS failed
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24')
ORDER BY hour
"

netapi ise dc query "
SELECT
    TO_CHAR(TRUNC(TIMESTAMP_TIMEZONE), 'YYYY-MM-DD') AS date,
    COUNT(*) AS total,
    COUNT(DISTINCT CALLING_STATION_ID) AS unique_macs,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY TRUNC(TIMESTAMP_TIMEZONE)
ORDER BY date
"

netapi ise dc query "
SELECT
    FAILURE_REASON,
    COUNT(*) AS occurrences,
    COUNT(DISTINCT CALLING_STATION_ID) AS affected_macs,
    COUNT(DISTINCT NAS_IP_ADDRESS) AS affected_nas
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY FAILURE_REASON
ORDER BY occurrences DESC
FETCH FIRST 15 ROWS ONLY
"

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
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY CALLING_STATION_ID, USERNAME
HAVING COUNT(*) >= 5
ORDER BY failure_count DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    NAS_IP_ADDRESS,
    NAS_IDENTIFIER,
    COUNT(*) AS failures,
    LISTAGG(DISTINCT FAILURE_REASON, '; ') WITHIN GROUP (ORDER BY FAILURE_REASON) AS reasons
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY NAS_IP_ADDRESS, NAS_IDENTIFIER
ORDER BY failures DESC
FETCH FIRST 15 ROWS ONLY
"

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    r.USERNAME,
    r.PASSED,
    r.AUTHENTICATION_PROTOCOL,
    p.ENDPOINT_PROFILE,
    p.IDENTITY_GROUP,
    p.MATCHED_CERTAINTY_FACTOR AS certainty
FROM RADIUS_AUTHENTICATIONS r
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON r.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY r.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    r.PASSED,
    r.POLICY_SET_NAME,
    r.AUTHORIZATION_PROFILES
FROM RADIUS_AUTHENTICATIONS r
LEFT JOIN ENDPOINTS_DATA e
    ON r.CALLING_STATION_ID = e.MAC_ADDRESS
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY r.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    r.USERNAME,
    r.PASSED,
    r.AUTHENTICATION_PROTOCOL,
    a.ACCT_SESSION_TIME AS session_secs,
    ROUND((a.ACCT_INPUT_OCTETS + a.ACCT_OUTPUT_OCTETS) / 1048576, 1) AS total_mb,
    a.ACCT_TERMINATE_CAUSE AS end_reason
FROM RADIUS_AUTHENTICATIONS r
JOIN RADIUS_ACCOUNTING a
    ON r.CALLING_STATION_ID = a.CALLING_STATION_ID
    AND r.SESSION_ID = a.SESSION_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND a.ACCT_STATUS_TYPE = 'Stop'
ORDER BY a.ACCT_SESSION_TIME DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    r.CALLING_STATION_ID AS mac,
    e.HOSTNAME,
    p.ENDPOINT_PROFILE AS profile,
    r.AUTHENTICATION_PROTOCOL,
    r.POLICY_SET_NAME,
    ROUND(a.ACCT_SESSION_TIME / 3600, 1) AS hours,
    ROUND((a.ACCT_INPUT_OCTETS + a.ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS gb
FROM RADIUS_AUTHENTICATIONS r
JOIN RADIUS_ACCOUNTING a
    ON r.CALLING_STATION_ID = a.CALLING_STATION_ID
    AND r.SESSION_ID = a.SESSION_ID
LEFT JOIN ENDPOINTS_DATA e
    ON r.CALLING_STATION_ID = e.MAC_ADDRESS
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON r.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND a.ACCT_STATUS_TYPE = 'Stop'
    AND r.PASSED = 'Pass'
ORDER BY a.ACCT_SESSION_TIME DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    p.ENDPOINT_PROFILE,
    COUNT(DISTINCT r.CALLING_STATION_ID) AS unique_devices,
    COUNT(*) AS total_auths,
    SUM(CASE WHEN r.PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    ROUND(SUM(CASE WHEN r.PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS r
JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON r.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE r.TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY p.ENDPOINT_PROFILE
ORDER BY unique_devices DESC
FETCH FIRST 15 ROWS ONLY
"

netapi ise dc query "
SELECT
    p.ENDPOINT_PROFILE,
    COUNT(DISTINCT a.CALLING_STATION_ID) AS devices,
    ROUND(SUM(a.ACCT_SESSION_TIME) / 3600, 1) AS total_hours,
    ROUND(SUM(a.ACCT_INPUT_OCTETS + a.ACCT_OUTPUT_OCTETS) / 1073741824, 2) AS total_gb
FROM RADIUS_ACCOUNTING a
JOIN PROFILED_ENDPOINTS_SUMMARY p
    ON a.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE a.ACCT_STATUS_TYPE = 'Stop'
    AND a.TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY p.ENDPOINT_PROFILE
ORDER BY total_gb DESC
FETCH FIRST 15 ROWS ONLY
"

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 = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY NAS_IP_ADDRESS, NAS_IDENTIFIER
HAVING COUNT(*) >= 10
ORDER BY failed DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    PASSED,
    AUTHENTICATION_PROTOCOL,
    POLICY_SET_NAME,
    AUTHORIZATION_RULE,
    AUTHORIZATION_PROFILES,
    NAS_IP_ADDRESS,
    FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID = '14:F6:D8:7B:31:80'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 100 ROWS ONLY
"

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    e.HARDWARE_MANUFACTURER,
    e.DEVICE_TYPE,
    p.ENDPOINT_PROFILE,
    p.IDENTITY_GROUP,
    p.MATCHED_CERTAINTY_FACTOR,
    TO_CHAR(e.CREATE_TIME, 'YYYY-MM-DD') AS first_seen,
    TO_CHAR(e.UPDATE_TIME, 'YYYY-MM-DD') AS last_seen
FROM ENDPOINTS_DATA e
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
WHERE e.MAC_ADDRESS = '14:F6:D8:7B:31:80'
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    PASSED,
    AUTHENTICATION_PROTOCOL,
    POLICY_SET_NAME,
    AUTHORIZATION_PROFILES,
    FAILURE_REASON,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') AS time
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID IN ('14:F6:D8:7B:31:80', '98:BB:1E:1F:A7:13')
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID AS mac,
    USERNAME,
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    POLICY_SET_NAME,
    NAS_IP_ADDRESS
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED = 'Pass'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
    AND (TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') < '06'
         OR TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') >= '22')
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    DEVICE_TYPE,
    TO_CHAR(CREATE_TIME, 'HH24:MI:SS') AS first_seen
FROM ENDPOINTS_DATA
WHERE CREATE_TIME > TRUNC(SYSDATE)
ORDER BY CREATE_TIME DESC
"

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    e.HARDWARE_MANUFACTURER,
    p.ENDPOINT_PROFILE
FROM ENDPOINTS_DATA e
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
WHERE p.ENDPOINT_PROFILE IS NULL
   OR UPPER(p.ENDPOINT_PROFILE) LIKE '%UNKNOWN%'
ORDER BY e.UPDATE_TIME DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    'Total Auths' 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
"

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') AS last_seen,
    ROUND(SYSDATE - UPDATE_TIME) AS days_stale
FROM ENDPOINTS_DATA
WHERE UPDATE_TIME < SYSDATE - 30
ORDER BY UPDATE_TIME ASC
FETCH FIRST 50 ROWS ONLY
"