TACACS+ Views

Overview

TACACS+ views provide device administration audit data including network device logins, command execution, and authorization decisions. Essential for compliance, troubleshooting, and security monitoring.

TACACS_AUTHENTICATION

Device admin login attempts - all TACACS+ authentication requests.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Authentication time

USERNAME

VARCHAR2

Admin username

PASSED

VARCHAR2

'Pass' or 'Fail' (string)

FAILED

VARCHAR2

'Fail' if failed, null if passed

FAILURE_REASON

VARCHAR2

Reason for failure (if applicable)

DEVICE_IP

VARCHAR2

Network device IP address

DEVICE_NAME

VARCHAR2

Network device hostname

REMOTE_ADDRESS

VARCHAR2

Source IP of admin connection

AUTHENTICATION_SERVICE

VARCHAR2

Service type (Login, Enable)

AUTHENTICATION_PRIVILEGE_LEVEL

NUMBER

Privilege level requested

ISE_NODE

VARCHAR2

ISE node that processed request

IDENTITY_STORE

VARCHAR2

Identity source used (AD, Internal, etc.)

AUTHENTICATION_METHOD

VARCHAR2

Auth method (PAP, CHAP, etc.)

DEVICE_TYPE

VARCHAR2

Device type from network device groups

LOCATION

VARCHAR2

Device location from network device groups

DEVICE_PORT

VARCHAR2

Port/line used for access

Common Queries

Recent Device Logins

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    USERNAME,
    DEVICE_NAME,
    DEVICE_IP,
    PASSED,
    AUTHENTICATION_SERVICE
FROM TACACS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Failed Device Logins

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') AS time,
    USERNAME,
    DEVICE_NAME,
    DEVICE_IP,
    REMOTE_ADDRESS AS source_ip,
    FAILURE_REASON
FROM TACACS_AUTHENTICATION
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Login Summary by Device

netapi ise dc query "
SELECT
    DEVICE_NAME,
    DEVICE_IP,
    COUNT(*) AS total_logins,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN PASSED = 'Fail' THEN 1 ELSE 0 END) AS failed,
    COUNT(DISTINCT USERNAME) AS unique_users
FROM TACACS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY DEVICE_NAME, DEVICE_IP
ORDER BY total_logins DESC
FETCH FIRST 20 ROWS ONLY
"

Admin Activity by User

netapi ise dc query "
SELECT
    USERNAME,
    COUNT(*) AS login_count,
    COUNT(DISTINCT DEVICE_IP) AS unique_devices,
    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 TACACS_AUTHENTICATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY USERNAME
ORDER BY login_count DESC
"

TACACS_AUTHORIZATION

Privilege and service authorization - what admins are allowed to do.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Authorization time

USERNAME

VARCHAR2

Admin username

PASSED

VARCHAR2

'Pass' or 'Fail'

DEVICE_IP

VARCHAR2

Network device IP

DEVICE_NAME

VARCHAR2

Network device hostname

AUTHORIZATION_POLICY

VARCHAR2

Matched TACACS policy name

SHELL_PROFILE

VARCHAR2

Assigned shell profile

COMMAND_SET

VARCHAR2

Assigned command set

PRIVILEGE_LEVEL

NUMBER

Granted privilege level

AUTHORIZATION_SERVICE

VARCHAR2

Service (shell, exec)

SELECTED_COMMAND_SET

VARCHAR2

Command set applied

Common Queries

Authorization Decisions

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    USERNAME,
    DEVICE_NAME,
    AUTHORIZATION_POLICY,
    SHELL_PROFILE,
    COMMAND_SET,
    PASSED
FROM TACACS_AUTHORIZATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Privilege Level Distribution

netapi ise dc query "
SELECT
    PRIVILEGE_LEVEL,
    COUNT(*) AS count,
    COUNT(DISTINCT USERNAME) AS unique_users
FROM TACACS_AUTHORIZATION
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
    AND PRIVILEGE_LEVEL IS NOT NULL
GROUP BY PRIVILEGE_LEVEL
ORDER BY PRIVILEGE_LEVEL DESC
"

TACACS_COMMAND_ACCOUNTING

Command execution audit - every command run on network devices.

Key Columns

Column Type Description

TIMESTAMP_TIMEZONE

TIMESTAMP

Command execution time

USERNAME

VARCHAR2

Admin who ran command

DEVICE_IP

VARCHAR2

Device IP address

DEVICE_NAME

VARCHAR2

Device hostname

COMMAND

VARCHAR2

Full command executed

ARGUMENTS

VARCHAR2

Command arguments

PRIVILEGE_LEVEL

NUMBER

Privilege level when command ran

REMOTE_ADDRESS

VARCHAR2

Source IP of admin session

SESSION_ID

VARCHAR2

Session identifier

COMMAND_STATUS

VARCHAR2

Result status

DEVICE_TYPE

VARCHAR2

Device type

LOCATION

VARCHAR2

Device location

Common Queries

Recent Commands

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') AS time,
    USERNAME,
    DEVICE_NAME,
    PRIVILEGE_LEVEL AS priv,
    COMMAND || ' ' || NVL(ARGUMENTS, '') AS full_command
FROM TACACS_COMMAND_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 100 ROWS ONLY
"

Configuration Commands (High-Risk)

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    USERNAME,
    DEVICE_NAME,
    COMMAND || ' ' || NVL(ARGUMENTS, '') AS full_command
FROM TACACS_COMMAND_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND (
        UPPER(COMMAND) LIKE '%CONFIGURE%'
        OR UPPER(COMMAND) LIKE '%WRITE%'
        OR UPPER(COMMAND) LIKE '%RELOAD%'
        OR UPPER(COMMAND) LIKE '%COPY%'
        OR UPPER(COMMAND) LIKE '%DELETE%'
        OR UPPER(COMMAND) LIKE '%ERASE%'
        OR UPPER(COMMAND) LIKE '%NO %'
    )
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

Commands by User

netapi ise dc query "
SELECT
    USERNAME,
    COUNT(*) AS command_count,
    COUNT(DISTINCT DEVICE_IP) AS devices_accessed,
    MAX(TIMESTAMP_TIMEZONE) AS last_activity
FROM TACACS_COMMAND_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY USERNAME
ORDER BY command_count DESC
"

Specific User Audit Trail

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') AS time,
    DEVICE_NAME,
    PRIVILEGE_LEVEL AS priv,
    COMMAND || ' ' || NVL(ARGUMENTS, '') AS full_command
FROM TACACS_COMMAND_ACCOUNTING
WHERE USERNAME = 'admin'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 100 ROWS ONLY
"

Device Access Pattern

netapi ise dc query "
SELECT
    DEVICE_NAME,
    DEVICE_IP,
    COUNT(*) AS total_commands,
    COUNT(DISTINCT USERNAME) AS unique_admins,
    MIN(TIMESTAMP_TIMEZONE) AS first_access,
    MAX(TIMESTAMP_TIMEZONE) AS last_access
FROM TACACS_COMMAND_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY DEVICE_NAME, DEVICE_IP
ORDER BY total_commands DESC
FETCH FIRST 20 ROWS ONLY
"

TACACS_ACCOUNTING

Session accounting - login/logout events with duration.

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    USERNAME,
    DEVICE_NAME,
    REMOTE_ADDRESS AS source,
    ACCT_STATUS_TYPE AS event
FROM TACACS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

LAST_TWO_DAYS Views

Pre-filtered views for faster queries on recent data:

  • TACACS_AUTHENTICATION_LAST_TWO_DAYS

  • TACACS_AUTHORIZATION_LAST_TWO_DAYS

  • TACACS_ACCOUNTING_LAST_TWO_DAYS

netapi ise dc query "
SELECT * FROM TACACS_AUTHENTICATION_LAST_TWO_DAYS
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"

JOIN Examples

Full Admin Session (Auth + Commands)

netapi ise dc query "
SELECT
    a.USERNAME,
    a.DEVICE_NAME,
    TO_CHAR(a.TIMESTAMP_TIMEZONE, 'HH24:MI') AS login_time,
    a.PASSED AS auth_result,
    (SELECT COUNT(*) FROM TACACS_COMMAND_ACCOUNTING c
     WHERE c.USERNAME = a.USERNAME
     AND c.DEVICE_IP = a.DEVICE_IP
     AND c.TIMESTAMP_TIMEZONE BETWEEN a.TIMESTAMP_TIMEZONE AND a.TIMESTAMP_TIMEZONE + 1) AS commands
FROM TACACS_AUTHENTICATION a
WHERE a.TIMESTAMP_TIMEZONE > SYSDATE - 1
    AND a.PASSED = 'Pass'
ORDER BY a.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Auth + Authorization (What User Can Do)

netapi ise dc query "
SELECT
    a.USERNAME,
    a.DEVICE_NAME,
    a.PASSED AS auth_passed,
    z.AUTHORIZATION_POLICY,
    z.SHELL_PROFILE,
    z.PRIVILEGE_LEVEL
FROM TACACS_AUTHENTICATION a
LEFT JOIN TACACS_AUTHORIZATION z
    ON a.USERNAME = z.USERNAME
    AND a.DEVICE_IP = z.DEVICE_IP
    AND z.TIMESTAMP_TIMEZONE BETWEEN a.TIMESTAMP_TIMEZONE AND a.TIMESTAMP_TIMEZONE + INTERVAL '5' MINUTE
WHERE a.TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY a.TIMESTAMP_TIMEZONE DESC
FETCH FIRST 30 ROWS ONLY
"

Security Patterns

Unusual After-Hours Access

netapi ise dc query "
SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI') AS time,
    USERNAME,
    DEVICE_NAME,
    REMOTE_ADDRESS
FROM TACACS_AUTHENTICATION
WHERE PASSED = 'Pass'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 7
    AND (
        TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') < '06'
        OR TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24') > '22'
        OR TO_CHAR(TIMESTAMP_TIMEZONE, 'D') IN ('1', '7')
    )
ORDER BY TIMESTAMP_TIMEZONE DESC
"

Brute Force Detection (Multiple Failures)

netapi ise dc query "
SELECT
    USERNAME,
    DEVICE_IP,
    REMOTE_ADDRESS,
    COUNT(*) AS failures,
    MIN(TIMESTAMP_TIMEZONE) AS first_attempt,
    MAX(TIMESTAMP_TIMEZONE) AS last_attempt
FROM TACACS_AUTHENTICATION
WHERE PASSED = 'Fail'
    AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY USERNAME, DEVICE_IP, REMOTE_ADDRESS
HAVING COUNT(*) >= 5
ORDER BY failures DESC
"

See Also