Views Reference

dsource d000 dev/network

netapi ise dc query "
  SELECT view_name, num_rows
  FROM all_views
  WHERE owner = 'ISEAPPDATA'
  ORDER BY view_name
" --format json | jq -r '.[] | .view_name'

VIEW_NAME="RADIUS_AUTHENTICATIONS"
netapi ise dc query "
  SELECT column_name, data_type
  FROM all_tab_columns
  WHERE table_name = '${VIEW_NAME}'
  AND owner = 'ISEAPPDATA'
  ORDER BY column_id
" --format json | jq -r '.[] | "\(.column_name): \(.data_type)"'

netapi ise dc query "
  SELECT
    TO_CHAR(TIMESTAMP_TIMEZONE, 'HH24:MI') as time,
    CALLING_STATION_ID as mac,
    AUTHENTICATION_METHOD as method,
    PASSED as status
  FROM RADIUS_AUTHENTICATIONS
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1/24
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 10 ROWS ONLY
" --format json | jq -C '.'

netapi ise dc query "
  SELECT
    CALLING_STATION_ID as mac,
    ACCT_STATUS_TYPE as type,
    ROUND(ACCT_SESSION_TIME / 60, 1) as mins,
    ROUND((ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) / 1024 / 1024, 2) as mb
  FROM RADIUS_ACCOUNTING
  WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
  AND ACCT_STATUS_TYPE = 'Stop'
  ORDER BY TIMESTAMP_TIMEZONE DESC
  FETCH FIRST 10 ROWS ONLY
" --format json | jq -C '.'

netapi ise dc query "
  SELECT
    MAC_ADDRESS as mac,
    ENDPOINT_POLICY as profile,
    TO_CHAR(UPDATE_TIME, 'YYYY-MM-DD') as last_seen
  FROM ENDPOINTS
  ORDER BY UPDATE_TIME DESC
  FETCH FIRST 10 ROWS ONLY
" --format json | jq -C '.'