DataConnect Queries

DataConnect Queries

Prerequisites

Load ISE credentials
# d000 (home lab — testing)
dsource d000 dev/network/ise

# d001 (CHLA — production)
# dsource d001 dev/network/ise
Define dc_query helper — standalone Python, no netapi
dc_query() {
  local sql="$1"
  ~/atelier/_projects/personal/netapi/.venv/bin/python -c "
import oracledb, os, json, ssl
ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
ssl_context.check_hostname = False
ssl_context.verify_mode = ssl.CERT_NONE
conn = oracledb.connect(
    user=os.environ['ISE_DATACONNECT_USER'],
    password=os.environ['ISE_DATACONNECT_PASS'],
    dsn=os.environ['ISE_DATACONNECT_DSN'],
    ssl_context=ssl_context,
    ssl_server_dn_match=False)
cursor = conn.cursor()
cursor.execute('''${sql}''')
columns = [col[0] for col in cursor.description]
rows = [dict(zip(columns, row)) for row in cursor.fetchall()]
print(json.dumps(rows, indent=2, default=str))
conn.close()
"
}
Verify connectivity
dc_query "SELECT COUNT(*) AS total FROM radius_authentications"
Verified output (d000, 2026-04-21)
[{ "TOTAL": 594 }]
ssl_server_cert_dn=None and wallet_location=None bypass Oracle wallet — uses system TLS in thin client mode. The .venv/bin/python is from the netapi venv (has oracledb) but does NOT import netapi.

Schema Notes

Domain Table Prefix Notes

d000 (home)

None — radius_authentications

Direct table names, no schema prefix

d001 (CHLA)

DATACONNECT. — DATACONNECT.RADIUS_AUTHENTICATIONS

Schema-qualified. Adjust queries accordingly.

Key Discovery (d000 Validation)

radius_authentications already contains DEVICE_NAME and NAS_PORT_TYPE — the NAS device join is NOT required for basic wireless detection. ISE populates these directly from the RADIUS attributes.

Available columns in radius_authentications (39 fields)
ACCESS_SERVICE, AUDIT_SESSION_ID, AUTHENTICATION_METHOD, AUTHENTICATION_PROTOCOL,
AUTHORIZATION_PROFILES, AUTHORIZATION_RULE, CALLED_STATION_ID, CALLING_STATION_ID,
CREDENTIAL_CHECK, DEVICE_NAME, DEVICE_TYPE, ENDPOINT_PROFILE, FAILED, FAILURE_REASON,
FRAMED_IP_ADDRESS, FRAMED_IPV6_ADDRESS, ID, IDENTITY_GROUP, IDENTITY_STORE, ISE_NODE,
LOCATION, MDM_SERVER_NAME, NAS_IP_ADDRESS, NAS_IPV6_ADDRESS, NAS_PORT_ID, NAS_PORT_TYPE,
ORIG_CALLING_STATION_ID, PASSED, POLICY_SET_NAME, POSTURE_STATUS, RESPONSE_TIME,
SECURITY_GROUP, SERVICE_TYPE, SYSLOG_MESSAGE_CODE, TIMESTAMP, TIMESTAMP_TIMEZONE,
USERNAME, USER_TYPE, CHECKSUM
NAS devices (d000)
Home-3560CX-01   Cisco   10.50.1.10   (wired — 102 auths)
Home-9800-WLC    Cisco   10.50.1.40   (wireless — 492 auths)
LAB-9300-01      Cisco   10.50.1.11   (wired)

Query 1 — Identify NAS Devices

List all NAS devices with auth counts — man jq → group_by
dc_query "
SELECT nas_ip_address, device_name, nas_port_type,
       COUNT(*) AS auth_count
FROM radius_authentications
GROUP BY nas_ip_address, device_name, nas_port_type
ORDER BY auth_count DESC
"
List network devices with profiles
dc_query "
SELECT name, profile_name,
       SUBSTR(ip_mask, 1, INSTR(ip_mask, '/') - 1) AS ip
FROM network_devices
ORDER BY name
"

Query 2 — Wireless vs Wired Detection

NAS_PORT_TYPE tells you the access method directly — no join needed
dc_query "
SELECT DISTINCT nas_port_type, COUNT(*) AS count
FROM radius_authentications
GROUP BY nas_port_type
ORDER BY count DESC
"

Expected results include values like Wireless - IEEE 802.11, Ethernet, etc.

All wireless authentications in last 30 days
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name AS nas_name,
       nas_ip_address,
       nas_port_type,
       authentication_protocol,
       endpoint_profile,
       identity_group,
       policy_set_name,
       authorization_rule,
       authorization_profiles,
       passed,
       failed,
       timestamp
FROM radius_authentications
WHERE LOWER(nas_port_type) LIKE '%wireless%'
  AND timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY timestamp DESC
FETCH FIRST 50 ROWS ONLY
"

Query 3 — Find Downtime Computers on Wireless

Option A: By MAC address — replace MAC1, MAC2 with Cerner 724 MACs
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name AS nas_name,
       nas_ip_address,
       nas_port_type,
       authentication_protocol,
       endpoint_profile,
       identity_group,
       policy_set_name,
       authorization_rule,
       passed,
       failed,
       timestamp
FROM radius_authentications
WHERE calling_station_id IN ('MAC1', 'MAC2')
  AND LOWER(nas_port_type) LIKE '%wireless%'
  AND timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY timestamp DESC
"
Option B: By hostname pattern — CALLED_STATION_ID or endpoint profile
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name AS nas_name,
       nas_port_type,
       endpoint_profile,
       identity_group,
       policy_set_name,
       authorization_rule,
       passed,
       timestamp
FROM radius_authentications
WHERE LOWER(nas_port_type) LIKE '%wireless%'
  AND (LOWER(username) LIKE '%cerner%'
       OR LOWER(username) LIKE '%724%'
       OR LOWER(username) LIKE '%downtime%'
       OR LOWER(endpoint_profile) LIKE '%cerner%'
       OR LOWER(identity_group) LIKE '%downtime%')
  AND timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY timestamp DESC
"
Option C: By identity group
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name AS nas_name,
       nas_port_type,
       identity_group,
       endpoint_profile,
       policy_set_name,
       authorization_rule,
       passed,
       timestamp
FROM radius_authentications
WHERE LOWER(identity_group) LIKE '%downtime%'
  AND LOWER(nas_port_type) LIKE '%wireless%'
  AND timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
ORDER BY timestamp DESC
"

Query 4 — Full Audit (Wired AND Wireless Comparison)

Shows all access types per endpoint — violations are WIRELESS rows
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name AS nas_name,
       nas_ip_address,
       CASE
         WHEN LOWER(nas_port_type) LIKE '%wireless%' THEN 'WIRELESS'
         ELSE 'WIRED'
       END AS access_type,
       nas_port_type,
       identity_group,
       endpoint_profile,
       authentication_protocol,
       policy_set_name,
       authorization_rule,
       authorization_profiles,
       COUNT(*) AS auth_count,
       MIN(timestamp) AS first_seen,
       MAX(timestamp) AS last_seen
FROM radius_authentications
WHERE (LOWER(username) LIKE '%cerner%'
       OR LOWER(username) LIKE '%724%'
       OR LOWER(username) LIKE '%downtime%'
       OR LOWER(identity_group) LIKE '%downtime%')
  AND timestamp > SYSTIMESTAMP - INTERVAL '30' DAY
GROUP BY calling_station_id, username, device_name, nas_ip_address,
         nas_port_type, identity_group, endpoint_profile,
         authentication_protocol, policy_set_name, authorization_rule,
         authorization_profiles
ORDER BY access_type, calling_station_id
" | tee /tmp/downtime-audit.json

Query 5 — Analyze with jq

Wireless violations only — man jq → select
jq '[.[] | select(.ACCESS_TYPE == "WIRELESS")]' /tmp/downtime-audit.json
Violation count per MAC — man jq → group_by, map, length
jq '
  group_by(.MAC) |
  map({
    mac: .[0].MAC,
    username: .[0].USERNAME,
    wireless: [.[] | select(.ACCESS_TYPE == "WIRELESS")] | length,
    wired: [.[] | select(.ACCESS_TYPE == "WIRED")] | length
  }) |
  sort_by(.wireless) | reverse
' /tmp/downtime-audit.json
Export violations as TSV — man jq → @tsv, pipe to column -t
jq -r '
  .[] | select(.ACCESS_TYPE == "WIRELESS") |
  [.MAC, .USERNAME, .NAS_NAME, .AUTH_COUNT, .LAST_SEEN] | @tsv
' /tmp/downtime-audit.json | column -t -s $'\t'
Summary stats
jq '{
  total_endpoints: (map(.MAC) | unique | length),
  wireless_violations: [.[] | select(.ACCESS_TYPE == "WIRELESS")] | length,
  wired_compliant: [.[] | select(.ACCESS_TYPE == "WIRED")] | length,
  violating_macs: [.[] | select(.ACCESS_TYPE == "WIRELESS") | .MAC] | unique
}' /tmp/downtime-audit.json

Query 6 — Test on d000 (Home Lab Validation)

Verify wireless detection works — your WLC is Home-9800-WLC at 10.50.1.40
dc_query "
SELECT calling_station_id AS mac,
       username,
       device_name,
       nas_ip_address,
       nas_port_type,
       CASE
         WHEN LOWER(nas_port_type) LIKE '%wireless%' THEN 'WIRELESS'
         ELSE 'WIRED'
       END AS access_type,
       COUNT(*) AS auths
FROM radius_authentications
WHERE timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY calling_station_id, username, device_name, nas_ip_address, nas_port_type
ORDER BY access_type, auths DESC
FETCH FIRST 20 ROWS ONLY
"
Verified NAS distribution (d000, 2026-04-21)
Home-9800-WLC    10.50.1.40   492 auths (wireless)
Home-3560CX-01   10.50.1.10   102 auths (wired)

Build Report

HTML with Catppuccin theme
~/atelier/_bibliotheca/domus-asciidoc-build/bin/build-adoc.sh \
  --variant catppuccin \
  docs/modules/ROOT/partials/projects/downtime-computers/dataconnect-queries.adoc
PDF for stakeholder distribution
~/atelier/_bibliotheca/domus-asciidoc-build/bin/build-adoc.sh \
  --format pdf --theme operations \
  docs/modules/ROOT/partials/projects/downtime-computers/dataconnect-queries.adoc

Applying to d001 (CHLA)

When running against d001, two changes:

  1. Load d001 creds: dsource d001 dev/network/ise

  2. If tables require schema prefix, add DATACONNECT. before table names:

    -- d000 (no prefix)
SELECT * FROM radius_authentications

-- d001 (schema prefix — test first, may not be needed)
SELECT * FROM DATACONNECT.RADIUS_AUTHENTICATIONS

Test without the prefix first. If it works, the schema is already in the search path.