Audit & Admin Views

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    ADMIN_NAME,
    ADMIN_IP_ADDRESS,
    ADMIN_INTERFACE,
    ADMIN_PASSED,
    ISE_NODE
FROM ADMINISTRATOR_LOGINS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ADMIN_NAME,
    ADMIN_IP_ADDRESS,
    ADMIN_INTERFACE,
    FAILURE_REASON
FROM ADMINISTRATOR_LOGINS
WHERE ADMIN_PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

netapi ise dc query "
SELECT
    ADMIN_NAME,
    COUNT(*) AS total_logins,
    SUM(CASE WHEN ADMIN_PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN ADMIN_PASSED = 'Fail' THEN 1 ELSE 0 END) AS failed,
    COUNT(DISTINCT ADMIN_IP_ADDRESS) AS unique_ips,
    MAX(TIMESTAMP_TIMEZONE) AS last_login
FROM ADMINISTRATOR_LOGINS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 30
GROUP BY ADMIN_NAME
ORDER BY total_logins DESC
"

netapi ise dc query "
SELECT
    ADMIN_INTERFACE,
    COUNT(*) AS logins,
    SUM(CASE WHEN ADMIN_PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    COUNT(DISTINCT ADMIN_NAME) AS unique_admins
FROM ADMINISTRATOR_LOGINS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY ADMIN_INTERFACE
ORDER BY logins DESC
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ADMIN_NAME,
    OBJECT_TYPE,
    OBJECT_NAME,
    OPERATION_TYPE,
    OPERATION_STATUS
FROM CHANGE_CONFIGURATION_AUDIT
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    OBJECT_TYPE,
    COUNT(*) AS changes,
    SUM(CASE WHEN OPERATION_TYPE = 'Create' THEN 1 ELSE 0 END) AS creates,
    SUM(CASE WHEN OPERATION_TYPE = 'Update' THEN 1 ELSE 0 END) AS updates,
    SUM(CASE WHEN OPERATION_TYPE = 'Delete' THEN 1 ELSE 0 END) AS deletes
FROM CHANGE_CONFIGURATION_AUDIT
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY OBJECT_TYPE
ORDER BY changes DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ADMIN_NAME,
    OBJECT_NAME,
    OPERATION_TYPE,
    ADMIN_INTERFACE
FROM CHANGE_CONFIGURATION_AUDIT
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
    AND (
        UPPER(OBJECT_TYPE) LIKE '%POLICY%'
        OR UPPER(OBJECT_TYPE) LIKE '%AUTHORIZATION%'
        OR UPPER(OBJECT_TYPE) LIKE '%AUTHENTICATION%'
    )
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

netapi ise dc query "
SELECT
    ADMIN_NAME,
    COUNT(*) AS total_changes,
    COUNT(DISTINCT OBJECT_TYPE) AS object_types,
    SUM(CASE WHEN OPERATION_TYPE = 'Delete' THEN 1 ELSE 0 END) AS deletions,
    MAX(TIMESTAMP_TIMEZONE) AS last_change
FROM CHANGE_CONFIGURATION_AUDIT
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 30
GROUP BY ADMIN_NAME
ORDER BY total_changes DESC
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ADMIN_NAME,
    OBJECT_TYPE,
    OBJECT_NAME,
    ADMIN_IP_ADDRESS
FROM CHANGE_CONFIGURATION_AUDIT
WHERE OPERATION_TYPE = 'Delete'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') AS time,
    ADMIN_NAME,
    HTTP_METHOD,
    REQUEST_URI,
    RESPONSE_CODE
FROM OPENAPI_OPERATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    ADMIN_NAME,
    HTTP_METHOD,
    REQUEST_URI,
    RESPONSE_CODE,
    RESPONSE_MESSAGE
FROM OPENAPI_OPERATIONS
WHERE RESPONSE_CODE >= 400
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

netapi ise dc query "
SELECT
    REQUEST_URI,
    COUNT(*) AS calls,
    COUNT(DISTINCT ADMIN_NAME) AS unique_users,
    SUM(CASE WHEN RESPONSE_CODE < 400 THEN 1 ELSE 0 END) AS success,
    SUM(CASE WHEN RESPONSE_CODE >= 400 THEN 1 ELSE 0 END) AS errors
FROM OPENAPI_OPERATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY REQUEST_URI
ORDER BY calls DESC
FETCH FIRST 20 ROWS ONLY
"