Query Patterns

# Authentication statistics for today
netapi ise dc --format json query "
  SELECT
    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_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > TRUNC(SYSDATE)
"

# Quick auth stats (netapi shortcut)
netapi ise dc stats

# Recent authentications (last 10)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') as time,
    CALLING_STATION_ID as mac,
    USERNAME,
    PASSED,
    AUTHENTICATION_METHOD
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1/24
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 10 ROWS ONLY
"

# Recent authentications (netapi shortcut)
netapi ise dc recent --limit 10

# Failed authentications (last hour)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') as time,
    CALLING_STATION_ID as mac,
    USERNAME,
    FAILURE_REASON
  FROM RADIUS_AUTHENTICATIONS
  WHERE PASSED = 0
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
  ORDER BY TIMESTAMP_TIMEZONE DESC
"

# Failed authentications (netapi shortcut)
netapi ise dc failed --hours 1

# Top failure reasons (last 24h)
netapi ise dc --format json query "
  SELECT
    FAILURE_REASON,
    COUNT(*) as count
  FROM RADIUS_AUTHENTICATIONS
  WHERE PASSED = 0
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY FAILURE_REASON
  ORDER BY count DESC
  FETCH FIRST 10 ROWS ONLY
"

# Top failure reasons (netapi shortcut)
netapi ise dc top-failures

# Hourly authentication trend (last 24h)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, '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_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24')
  ORDER BY hour
"

# Auth trends (netapi shortcut)
netapi ise dc trends --hours 24

# Authentication methods distribution
netapi ise dc --format json query "
  SELECT
    AUTHENTICATION_METHOD,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY AUTHENTICATION_METHOD
  ORDER BY count DESC
"

# Auth methods breakdown (netapi shortcut)
netapi ise dc auth-methods

# Authentication history for specific endpoint
MAC="C8:5B:76:C6:59:62"
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24:MI:SS') as timestamp,
    POLICY_SET_NAME,
    AUTHENTICATION_METHOD,
    PASSED,
    FAILURE_REASON
  FROM RADIUS_AUTHENTICATIONS
  WHERE CALLING_STATION_ID = '${MAC}'
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 20 ROWS ONLY
"

# Endpoint auth history (netapi shortcut)
netapi ise dc auth-history "C8:5B:76:C6:59:62"

# Endpoint profiling data
netapi ise dc --format json query "
  SELECT
    MAC_ADDRESS,
    ENDPOINT_POLICY,
    STATIC_ASSIGNMENT,
    CREATE_TIME
  FROM ENDPOINTS
  WHERE ENDPOINT_POLICY LIKE '%Linux%'
  FETCH FIRST 20 ROWS ONLY
"

# Endpoint profiling (netapi shortcut)
netapi ise dc profiler --policy "Linux"

# Stale endpoints (no auth in 30 days)
netapi ise dc --format json query "
  SELECT
    e.MAC_ADDRESS,
    e.ENDPOINT_POLICY,
    MAX(r.TIMESTAMP_TIMEZONE) as last_auth
  FROM ENDPOINTS e
  LEFT JOIN RADIUS_AUTHENTICATIONS r ON e.MAC_ADDRESS = r.CALLING_STATION_ID
  GROUP BY e.MAC_ADDRESS, e.ENDPOINT_POLICY
  HAVING MAX(r.TIMESTAMP_TIMEZONE) < SYSDATE - 30 OR MAX(r.TIMESTAMP_TIMEZONE) IS NULL
  ORDER BY last_auth NULLS FIRST
  FETCH FIRST 50 ROWS ONLY
"

# Stale endpoints (netapi shortcut)
netapi ise dc stale --days 30

# Authentications by NAS (network device)
netapi ise dc --format json query "
  SELECT
    NAS_IP_ADDRESS,
    NAS_PORT_TYPE,
    COUNT(*) as auths
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY NAS_IP_ADDRESS, NAS_PORT_TYPE
  ORDER BY auths DESC
  FETCH FIRST 20 ROWS ONLY
"

# NAS usage (netapi shortcut)
netapi ise dc nas

# Policy set usage (last 7 days)
netapi ise dc --format json query "
  SELECT
    POLICY_SET_NAME,
    COUNT(*) as hits,
    SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END) as passed,
    SUM(CASE WHEN PASSED = 0 THEN 1 ELSE 0 END) as failed,
    ROUND(SUM(CASE WHEN PASSED = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as success_pct
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY POLICY_SET_NAME
  ORDER BY hits DESC
"

# Authentications by user (last 24h)
netapi ise dc --format json query "
  SELECT
    USERNAME,
    COUNT(*) as auths,
    COUNT(DISTINCT CALLING_STATION_ID) as devices
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND USERNAME IS NOT NULL
  GROUP BY USERNAME
  ORDER BY auths DESC
  FETCH FIRST 20 ROWS ONLY
"

# User authentication summary (netapi shortcut)
netapi ise dc users

# EAP-TLS authentications only
netapi ise dc --format json query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI:SS') as time,
    CALLING_STATION_ID as mac,
    USERNAME,
    PASSED
  FROM RADIUS_AUTHENTICATIONS
  WHERE AUTHENTICATION_METHOD = 'EAP-TLS'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 20 ROWS ONLY
"

# List available DataConnect views
netapi ise dc --format json query "
  SELECT view_name
  FROM all_views
  WHERE owner = 'ISEDATA'
  ORDER BY view_name
"

# Get columns for a specific view
netapi ise dc --format json query "
  SELECT column_name, data_type
  FROM all_tab_columns
  WHERE table_name = 'RADIUS_AUTHENTICATIONS'
  AND owner = 'ISEDATA'
  ORDER BY column_id
"