DataConnect

# Configure DataConnect credentials
netapi ise dc configure

# Test connection
netapi ise dc query "SELECT 1 FROM DUAL"

# Connection string format
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=ise-mnt)(PORT=2484))(CONNECT_DATA=(SID=cpm)))

# With Oracle SQLcl
sql dataconnect/password@//ise-mnt:2484/cpm

netapi ise dc 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)
"

netapi ise dc 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
"

netapi ise dc query "
  SELECT
    AUTHENTICATION_METHOD,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  GROUP BY AUTHENTICATION_METHOD
  ORDER BY count DESC
"

netapi ise dc query "
  SELECT
    CALLING_STATION_ID as mac,
    ROUND(MAX(ACCT_SESSION_TIME)/3600, 2) as hours
  FROM RADIUS_ACCOUNTING
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  GROUP BY CALLING_STATION_ID
  ORDER BY hours DESC
  FETCH FIRST 10 ROWS ONLY
"