Audit & Admin Views

Overview

The ADMIN_AUDIT_LOG view contains all ISE configuration changes including who made them, when, and what was modified.

Schema

Column Type Description

ACS_TIMESTAMP

TIMESTAMP

Event timestamp

ADMIN_NAME

VARCHAR2

Administrator username

ADMIN_IPADDRESS

VARCHAR2

Admin workstation IP

OBJECT_TYPE

VARCHAR2

Modified object type

OBJECT_NAME

VARCHAR2

Modified object name

REQUESTED_OPERATION

VARCHAR2

Create, Update, Delete

FAILURE_FLAG

VARCHAR2

'true' if failed

MESSAGETEXT

VARCHAR2

Audit message description

Setup

dsource d000 dev/network

Change Tracking

Recent Configuration Changes

# Recent configuration changes
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_TYPE as type,
    OBJECT_NAME as name
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 1
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  "\u001b[1m  Timestamp        │ Admin                │ Object Type               │ Name\u001b[0m",
  "───────────────────┼──────────────────────┼───────────────────────────┼──────────────────────────",
  (.[] |
    (if .op == "Create" then "\u001b[32m+\u001b[0m"
     elif .op == "Delete" then "\u001b[31m-\u001b[0m"
     elif .op == "Update" then "\u001b[33m~\u001b[0m"
     else "\u001b[34m●\u001b[0m" end) +
    " " + (.timestamp // "                ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    "\u001b[35m" + (((.type // "") + "                         ") | .[0:25]) + "\u001b[0m │ " +
    (.name // "")
  )
'

Changes by Admin

# Configuration changes grouped by admin
netapi ise dc --format json query "
  SELECT
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_TYPE as type,
    COUNT(*) as changes
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 7
  GROUP BY ADMIN_NAME, REQUESTED_OPERATION, OBJECT_TYPE
  ORDER BY ADMIN_NAME, changes DESC
" | jq -r '
  group_by(.admin) | .[] |
  "\u001b[1m\u001b[33m=== " + (.[0].admin // "(system)") + " ===\u001b[0m",
  (sort_by(-.changes) | .[] |
    "  " +
    (if .op == "Create" then "\u001b[32m+" elif .op == "Delete" then "\u001b[31m-" else "\u001b[33m~" end) +
    "\u001b[0m " + (((.type // "") + "                         ") | .[0:25]) +
    " │ " + (.changes | tostring) + " changes"
  ),
  ""
'

Daily Change Summary

# Daily change summary by type
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TRUNC(ACS_TIMESTAMP), 'Dy DD-Mon') as day,
    COUNT(*) as total,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Create' THEN 1 ELSE 0 END) as creates,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Update' THEN 1 ELSE 0 END) as updates,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Delete' THEN 1 ELSE 0 END) as deletes,
    COUNT(DISTINCT ADMIN_NAME) as admins
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 7
  GROUP BY TRUNC(ACS_TIMESTAMP), TO_CHAR(TRUNC(ACS_TIMESTAMP), 'Dy DD-Mon')
  ORDER BY TRUNC(ACS_TIMESTAMP)
" | jq -r '
  (map(.total) | max) as $max |
  .[] |
  .day + " │" +
  ((.total / $max * 20) | floor | . as $w |
    "\u001b[44m" + (" " * $w) + "\u001b[0m" + (" " * (20 - $w))) +
  "│ " +
  "\u001b[32m+" + (.creates | tostring) + "\u001b[0m " +
  "\u001b[33m~" + (.updates | tostring) + "\u001b[0m " +
  "\u001b[31m-" + (.deletes | tostring) + "\u001b[0m │ " +
  (.admins | tostring) + " admins"
'

Object-Specific Audit

Policy Changes

# Policy set and rule changes (critical audit)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_NAME as policy_name
  FROM MNT.CONFIG_CHANGE
  WHERE OBJECT_TYPE IN (
    'Policy Set',
    'Authorization Policy',
    'Authentication Policy',
    'Authorization Rule',
    'Authentication Rule',
    'Condition'
  )
  AND ACS_TIMESTAMP > SYSDATE - 30
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 100 ROWS ONLY
" | jq -r '
  .[] |
  (if .op == "Create" then "\u001b[32m+ CREATED\u001b[0m"
   elif .op == "Delete" then "\u001b[31m- DELETED\u001b[0m"
   else "\u001b[33m~ UPDATED\u001b[0m" end) +
  " │ " + .timestamp + " │ " +
  "\u001b[33m" + .admin + "\u001b[0m │ " +
  "\u001b[36m" + .policy_name + "\u001b[0m"
'

Endpoint/Group Changes

# Endpoint/identity group changes
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_TYPE as type,
    OBJECT_NAME as name
  FROM MNT.CONFIG_CHANGE
  WHERE OBJECT_TYPE IN (
    'Endpoint',
    'Endpoint Group',
    'Identity Group',
    'User Identity Group',
    'Endpoint Identity Group'
  )
  AND ACS_TIMESTAMP > SYSDATE - 7
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 100 ROWS ONLY
" | jq -r '
  "\u001b[1m  Timestamp        │ Admin                │ Type                      │ Name\u001b[0m",
  "───────────────────┼──────────────────────┼───────────────────────────┼──────────────────────────",
  (.[] |
    (if .op == "Create" then "\u001b[32m+\u001b[0m"
     elif .op == "Delete" then "\u001b[31m-\u001b[0m"
     else "\u001b[33m~\u001b[0m" end) +
    " " + (.timestamp // "                ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    "\u001b[35m" + (((.type // "") + "                         ") | .[0:25]) + "\u001b[0m │ " +
    (.name // "")
  )
'

Network Device Changes

# Network device changes (NAD modifications)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_NAME as device_name
  FROM MNT.CONFIG_CHANGE
  WHERE OBJECT_TYPE IN (
    'Network Device',
    'Network Device Group',
    'RADIUS Server Sequence',
    'TACACS Server Sequence'
  )
  AND ACS_TIMESTAMP > SYSDATE - 30
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  .[] |
  (if .op == "Create" then "\u001b[32m+ Added\u001b[0m"
   elif .op == "Delete" then "\u001b[31m- Removed\u001b[0m"
   else "\u001b[33m~ Modified\u001b[0m" end) +
  " │ " + .timestamp + " │ " +
  "\u001b[33m" + .admin + "\u001b[0m │ " +
  "\u001b[36m" + .device_name + "\u001b[0m"
'

Certificate Operations

# Certificate operations audit
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_NAME as cert_name
  FROM MNT.CONFIG_CHANGE
  WHERE OBJECT_TYPE IN (
    'Certificate',
    'Trusted Certificate',
    'System Certificate',
    'CA Certificate',
    'Certificate Template'
  )
  AND ACS_TIMESTAMP > SYSDATE - 90
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  .[] |
  "\u001b[35m🔐\u001b[0m " + .timestamp + " │ " +
  "\u001b[33m" + .admin + "\u001b[0m │ " +
  "\u001b[34m" + .op + "\u001b[0m │ " +
  .cert_name
'

Security Analysis

Admin Login Audit

# ISE admin GUI login audit (uses MNT.ADMINISTRATOR_LOGIN)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIME, 'HH24:MI:SS') as time,
    ADMIN_NAME as admin,
    ADMIN_IPADDRESS as source_ip,
    ADMIN_INTERFACE as interface,
    OPERATION_MESSAGE_TEXT as message
  FROM MNT.ADMINISTRATOR_LOGIN
  WHERE ACS_TIME > SYSDATE - 1
  ORDER BY ACS_TIME DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  "\u001b[1m  Time     │ Admin                │ Source\u001b[0m",
  "───────────┼──────────────────────┼──────────────────",
  (.[] |
    (if (.message // "") | test("success"; "i") then "\u001b[32m✓\u001b[0m"
     elif (.message // "") | test("fail"; "i") then "\u001b[31m✗\u001b[0m"
     else "\u001b[34m●\u001b[0m" end) +
    " " + (.time // "        ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    (.source_ip // "unknown")
  )
'

High-Risk Operations

# High-risk configuration changes
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    REQUESTED_OPERATION as op,
    OBJECT_TYPE as type,
    OBJECT_NAME as name
  FROM MNT.CONFIG_CHANGE
  WHERE (
    OBJECT_TYPE IN (
      'System Settings',
      'Admin User',
      'Admin Group',
      'External RADIUS Server',
      'Certificate',
      'Trusted Certificate',
      'Deployment',
      'Backup',
      'Restore'
    )
    OR REQUESTED_OPERATION = 'Delete'
  )
  AND ACS_TIMESTAMP > SYSDATE - 7
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 100 ROWS ONLY
" | jq -r '
  "\u001b[1mRisk Level    │ Timestamp        │ Admin                │ Op     │ Type                      │ Name\u001b[0m",
  "──────────────┼──────────────────┼──────────────────────┼────────┼───────────────────────────┼──────────────────────────",
  (.[] |
    (if .op == "Delete" then "\u001b[31;1m⚠ DELETE   \u001b[0m"
     elif ((.type // "") | test("Admin|Certificate|System"; "i")) then "\u001b[33m⚠ HIGH-RISK\u001b[0m"
     else "\u001b[34m●          \u001b[0m" end) +
    " │ " + (.timestamp // "                ") + " │ " +
    "\u001b[33m" + (((.admin // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    "\u001b[35m" + (((.op // "") + "      ") | .[0:6]) + "\u001b[0m │ " +
    "\u001b[36m" + (((.type // "") + "                         ") | .[0:25]) + "\u001b[0m │ " + (.name // "")
  )
'

Statistics

Object Type Statistics

# Object type modification statistics
netapi ise dc --format json query "
  SELECT
    OBJECT_TYPE as type,
    COUNT(*) as changes,
    COUNT(DISTINCT ADMIN_NAME) as admins
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 7
  GROUP BY OBJECT_TYPE
  ORDER BY changes DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -r '
  (map(.changes) | max) as $max |
  .[] |
  (((.type // "") + "                              ") | .[0:30]) + " │" +
  ((.changes / $max * 25) | floor | . as $w |
    "\u001b[45m" + (" " * $w) + "\u001b[0m" + (" " * (25 - $w))) +
  "│ " + (.changes | tostring) + " │ " +
  (.admins | tostring) + " admins"
'

Compliance

Compliance Audit Report

# Compliance audit report (who changed what, when)
echo -e "\n\033[1;36m═══════════════════════════════════════════════════════════\033[0m"
echo -e "\033[1;36m         ISE Configuration Audit Report - $(date '+%Y-%m-%d')         \033[0m"
echo -e "\033[1;36m═══════════════════════════════════════════════════════════\033[0m\n"

echo -e "\033[1m📊 Change Summary (Last 7 Days)\033[0m"
netapi ise dc --format json query "
  SELECT
    COUNT(*) as total_changes,
    COUNT(DISTINCT ADMIN_NAME) as unique_admins,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Create' THEN 1 ELSE 0 END) as creates,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Update' THEN 1 ELSE 0 END) as updates,
    SUM(CASE WHEN REQUESTED_OPERATION = 'Delete' THEN 1 ELSE 0 END) as deletes
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 7
" | jq -r '
  .[0] |
  "Total Changes: " + (.total_changes | tostring),
  "Unique Admins: " + (.unique_admins | tostring),
  "\u001b[32mCreates: " + (.creates | tostring) + "\u001b[0m",
  "\u001b[33mUpdates: " + (.updates | tostring) + "\u001b[0m",
  "\u001b[31mDeletes: " + (.deletes | tostring) + "\u001b[0m"
'

echo -e "\n\033[1m👥 Admin Activity\033[0m"
netapi ise dc --format json query "
  SELECT
    ADMIN_NAME as admin,
    COUNT(*) as changes
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 7
  GROUP BY ADMIN_NAME
  ORDER BY changes DESC
" | jq -r '
  .[] | "\u001b[33m" + .admin + "\u001b[0m: " + (.changes | tostring) + " changes"
'

echo -e "\n\033[1;36m═══════════════════════════════════════════════════════════\033[0m\n"

Export

CSV Export

# Export audit trail to CSV
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    ADMIN_NAME as admin,
    ADMIN_IPADDRESS as admin_ip,
    REQUESTED_OPERATION as operation,
    OBJECT_TYPE as object_type,
    OBJECT_NAME as object_name,
    FAILURE_FLAG as failed,
    MESSAGETEXT as message
  FROM MNT.CONFIG_CHANGE
  WHERE ACS_TIMESTAMP > SYSDATE - 30
  ORDER BY ACS_TIMESTAMP DESC
" | jq -r '
  (.[0] | keys) as $k |
  ($k | @csv),
  (.[] | [.[$k[]]] | @csv)
' > /tmp/ise-audit-$(date +%Y%m%d).csv

echo "Exported $(wc -l < /tmp/ise-audit-$(date +%Y%m%d).csv) audit records"

See Also