DataConnect Power Queries

netapi ise dc query "
SELECT
  TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24') as hour,
  COUNT(*) as total,
  SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as passed,
  ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY TO_CHAR(TIMESTAMP_TIMEZONE, 'YYYY-MM-DD HH24')
ORDER BY hour
"

netapi ise dc query "
SELECT
  FAILURE_REASON,
  COUNT(*) as count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED != 'Pass' AND TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY FAILURE_REASON
ORDER BY count DESC
FETCH FIRST 10 ROWS ONLY
"

netapi ise dc query "
SELECT
  TIMESTAMP_TIMEZONE,
  CALLING_STATION_ID as MAC,
  USERNAME,
  FAILURE_REASON,
  DEVICE_NAME as Switch,
  NAS_PORT_ID as Port
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED != 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 4/24
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
  AUTHENTICATION_METHOD,
  AUTHENTICATION_PROTOCOL,
  COUNT(*) as count,
  SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as passed,
  ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as success_pct
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY AUTHENTICATION_METHOD, AUTHENTICATION_PROTOCOL
ORDER BY count DESC
"

netapi ise dc query "
SELECT
  TIMESTAMP_TIMEZONE,
  PASSED,
  AUTHENTICATION_METHOD,
  AUTHENTICATION_PROTOCOL,
  POLICY_SET_NAME,
  AUTHORIZATION_PROFILES,
  FAILURE_REASON,
  DEVICE_NAME,
  NAS_PORT_ID
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID = 'AA:BB:CC:DD:EE:FF'
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
    DEVICE_NAME as Switch,
    NAS_PORT_ID as Port,
    NAS_PORT_TYPE as Port_Type,
    COUNT(DISTINCT CALLING_STATION_ID) as MAC_Count,
    COUNT(*) as Auth_Events,
    LISTAGG(DISTINCT CALLING_STATION_ID, '; ') WITHIN GROUP (ORDER BY CALLING_STATION_ID) as MACs,
    CASE
        WHEN NAS_PORT_TYPE LIKE '%Ethernet%' AND COUNT(DISTINCT CALLING_STATION_ID) > 2 THEN 'SUSPECTED HUB'
        WHEN NAS_PORT_TYPE LIKE '%Ethernet%' AND COUNT(DISTINCT CALLING_STATION_ID) > 5 THEN 'LIKELY HYPERVISOR'
        WHEN COUNT(DISTINCT CALLING_STATION_ID) > 10 THEN 'INVESTIGATE IMMEDIATELY'
        ELSE 'Normal'
    END as Risk_Level
FROM radius_authentications
WHERE TIMESTAMP >= (SYSDATE - 3)
  AND NAS_PORT_ID IS NOT NULL
GROUP BY DEVICE_NAME, NAS_PORT_ID, NAS_PORT_TYPE
HAVING COUNT(DISTINCT CALLING_STATION_ID) > 1
ORDER BY MAC_Count DESC
"

netapi ise dc query "
SELECT
    DEVICE_NAME as Switch,
    NAS_PORT_ID as Port,
    COUNT(DISTINCT CALLING_STATION_ID) as VM_Count,
    COUNT(*) as Total_Auths,
    LISTAGG(DISTINCT CALLING_STATION_ID, '; ') WITHIN GROUP (ORDER BY CALLING_STATION_ID) as MAC_Addresses,
    LISTAGG(DISTINCT USERNAME, '; ') WITHIN GROUP (ORDER BY USERNAME) as Usernames,
    LISTAGG(DISTINCT ENDPOINT_PROFILE, '; ') WITHIN GROUP (ORDER BY ENDPOINT_PROFILE) as Device_Types,
    ROUND(EXTRACT(DAY FROM (MAX(TIMESTAMP) - MIN(TIMESTAMP))), 1) as Days_Active
FROM radius_authentications
WHERE TIMESTAMP >= (SYSDATE - 7)
  AND NAS_PORT_ID IS NOT NULL
GROUP BY DEVICE_NAME, NAS_PORT_ID
HAVING COUNT(DISTINCT CALLING_STATION_ID) >= 3
ORDER BY VM_Count DESC
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    COUNT(*) as Failure_Count,
    MAX(USERNAME) as Last_Username,
    MAX(FAILURE_REASON) as Last_Reason,
    MAX(DEVICE_NAME) as Last_Switch,
    MIN(TIMESTAMP_TIMEZONE) as First_Attempt,
    MAX(TIMESTAMP_TIMEZONE) as Last_Attempt
FROM RADIUS_AUTHENTICATIONS
WHERE PASSED != 'Pass'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 1/24
GROUP BY CALLING_STATION_ID
HAVING COUNT(*) > 10
ORDER BY Failure_Count DESC
"

netapi ise dc query "
SELECT
    MAC_ADDRESS,
    HOSTNAME,
    ENDPOINT_IP,
    ENDPOINT_POLICY
FROM ENDPOINTS_DATA
WHERE ENDPOINT_POLICY = 'Unknown'
   OR ENDPOINT_POLICY IS NULL
ORDER BY MAC_ADDRESS
FETCH FIRST 100 ROWS ONLY
"

netapi ise dc query "
SELECT
    DEVICE_NAME as Switch,
    NAS_IP_ADDRESS as IP,
    COUNT(DISTINCT CALLING_STATION_ID) as Unique_Devices,
    COUNT(*) as Total_Auths,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as Success,
    SUM(CASE WHEN PASSED != 'Pass' THEN 1 ELSE 0 END) as Failed,
    ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as Success_Pct,
    MAX(TIMESTAMP) as Last_Auth
FROM radius_authentications
WHERE TIMESTAMP >= (SYSDATE - 1)
  AND DEVICE_NAME IS NOT NULL
GROUP BY DEVICE_NAME, NAS_IP_ADDRESS
ORDER BY Total_Auths DESC
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    MAX(USERNAME) as Username,
    MAX(ENDPOINT_PROFILE) as Device_Type,
    COUNT(DISTINCT DEVICE_NAME) as Switch_Count,
    COUNT(*) as Auth_Count,
    LISTAGG(DISTINCT DEVICE_NAME, '; ') WITHIN GROUP (ORDER BY DEVICE_NAME) as Switches,
    MIN(TIMESTAMP) as First_Auth,
    MAX(TIMESTAMP) as Last_Auth
FROM radius_authentications
WHERE TIMESTAMP >= (SYSDATE - 4/24)
GROUP BY CALLING_STATION_ID
HAVING COUNT(DISTINCT DEVICE_NAME) > 1
ORDER BY Switch_Count DESC
"

netapi ise dc query "
SELECT
    DEVICE_NAME as Switch,
    NAS_PORT_ID as Port,
    CALLING_STATION_ID as MAC,
    COUNT(*) as Auth_Events,
    SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) as Passed,
    SUM(CASE WHEN PASSED != 'Pass' THEN 1 ELSE 0 END) as Failed,
    MIN(TIMESTAMP_TIMEZONE) as First_Event,
    MAX(TIMESTAMP_TIMEZONE) as Last_Event
FROM RADIUS_AUTHENTICATIONS
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1
GROUP BY DEVICE_NAME, NAS_PORT_ID, CALLING_STATION_ID
HAVING COUNT(*) > 20
ORDER BY Auth_Events DESC
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC_Address,
    USERNAME,
    AUTHENTICATION_METHOD,
    AUTHENTICATION_PROTOCOL,
    DEVICE_NAME,
    ENDPOINT_PROFILE,
    COUNT(*) as Auth_Count,
    MAX(TIMESTAMP) as Last_Auth
FROM radius_authentications
WHERE AUTHENTICATION_PROTOCOL LIKE '%MSCHAP%'
  AND TIMESTAMP >= (SYSDATE - 30)
GROUP BY CALLING_STATION_ID, USERNAME, AUTHENTICATION_METHOD,
         AUTHENTICATION_PROTOCOL, DEVICE_NAME, ENDPOINT_PROFILE
ORDER BY Last_Auth DESC
"

netapi ise dc query "
SELECT
    USERNAME,
    COUNT(DISTINCT CALLING_STATION_ID) as Device_Count,
    LISTAGG(DISTINCT ENDPOINT_PROFILE, '; ') WITHIN GROUP (ORDER BY ENDPOINT_PROFILE) as Device_Types,
    COUNT(*) as Total_Auths,
    MAX(TIMESTAMP_TIMEZONE) as Last_Auth
FROM RADIUS_AUTHENTICATIONS
WHERE AUTHENTICATION_PROTOCOL LIKE '%PEAP%'
  AND TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY USERNAME
ORDER BY Device_Count DESC
FETCH FIRST 50 ROWS ONLY
"

netapi ise dc query "
SELECT
  CALLING_STATION_ID as MAC,
  PASSED,
  AUTHENTICATION_METHOD,
  AUTHENTICATION_PROTOCOL,
  POLICY_SET_NAME,
  AUTHORIZATION_PROFILES,
  FAILURE_REASON
FROM RADIUS_AUTHENTICATIONS
WHERE CALLING_STATION_ID IN ('AA:BB:CC:DD:EE:FF', '11:22:33:44:55:66')
AND TIMESTAMP_TIMEZONE > SYSDATE - 1
ORDER BY TIMESTAMP_TIMEZONE DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    TRUNC(TIMESTAMP) as Date,
    COUNT(DISTINCT CALLING_STATION_ID) as Unique_MACs,
    COUNT(*) as Total_Auths,
    COUNT(DISTINCT DEVICE_NAME) as Active_Switches,
    COUNT(DISTINCT USERNAME) as Unique_Users
FROM radius_authentications
WHERE TIMESTAMP >= (SYSDATE - 30)
GROUP BY TRUNC(TIMESTAMP)
ORDER BY TRUNC(TIMESTAMP) DESC
"

netapi ise dc query "
SELECT
    ENDPOINT_PROFILE as Policy,
    COUNT(*) as Endpoint_Count
FROM PROFILED_ENDPOINTS_SUMMARY
GROUP BY ENDPOINT_PROFILE
ORDER BY Endpoint_Count DESC
"

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    e.ENDPOINT_IP,
    e.ENDPOINT_POLICY,
    (SELECT MAX(TIMESTAMP_TIMEZONE) FROM RADIUS_AUTHENTICATIONS
     WHERE CALLING_STATION_ID = e.MAC_ADDRESS) as Last_Auth
FROM ENDPOINTS_DATA e
WHERE NOT EXISTS (
    SELECT 1 FROM RADIUS_AUTHENTICATIONS r
    WHERE r.CALLING_STATION_ID = e.MAC_ADDRESS
      AND r.TIMESTAMP_TIMEZONE > SYSDATE - 30
)
FETCH FIRST 100 ROWS ONLY
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    COUNT(*) as Sessions,
    ROUND(AVG(ACCT_SESSION_TIME)/60, 1) as Avg_Minutes,
    ROUND(SUM(ACCT_SESSION_TIME)/3600, 1) as Total_Hours,
    ROUND(SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS)/1024/1024, 1) as Total_MB
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
  AND ACCT_STATUS_TYPE = 'Stop'
GROUP BY CALLING_STATION_ID
ORDER BY Total_Hours DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
    CALLING_STATION_ID as MAC,
    SUM(ACCT_INPUT_OCTETS)/1024/1024 as Download_MB,
    SUM(ACCT_OUTPUT_OCTETS)/1024/1024 as Upload_MB,
    SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS)/1024/1024 as Total_MB,
    COUNT(*) as Session_Count
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID
ORDER BY Total_MB DESC
FETCH FIRST 20 ROWS ONLY
"

netapi ise dc query "
SELECT
  p.ENDPOINT_PROFILE as Device_Type,
  COUNT(DISTINCT a.CALLING_STATION_ID) as Unique_Devices,
  ROUND(AVG(a.ACCT_SESSION_TIME)/3600, 1) as Avg_Session_Hours,
  ROUND(MAX(a.ACCT_SESSION_TIME)/3600, 1) as Max_Session_Hours
FROM RADIUS_ACCOUNTING a
JOIN PROFILED_ENDPOINTS_SUMMARY p ON a.CALLING_STATION_ID = p.ENDPOINT_ID
WHERE a.ACCT_STATUS_TYPE = 'Stop' AND a.TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY p.ENDPOINT_PROFILE
ORDER BY Unique_Devices DESC
FETCH FIRST 15 ROWS ONLY
"

netapi ise dc query "
SELECT
    ep.edf_macaddress as MAC,
    ep.edf_endpointip as IP,
    ep.edf_hostname as Hostname,
    ep.edf_endpointpolicy as Policy,
    CASE ep.edf_deviceregistrationstatus
        WHEN 0 THEN 'NotRegistered'
        WHEN 1 THEN 'Registered'
        ELSE TO_CHAR(ep.edf_deviceregistrationstatus)
    END as Registration_Status,
    ep.edf_1_da_manufacturername as Manufacturer,
    ep.edf_1_da_modelname as Model,
    ep.edf_1_da_osversion as OS_Version,
    TO_CHAR(ep.edf_update_time, 'YYYY-MM-DD HH24:MI') as Last_Updated
FROM cepm.edf_ep_master ep
WHERE ep.edf_update_time > SYSDATE - 7
ORDER BY ep.edf_update_time DESC
FETCH FIRST 100 ROWS ONLY
"

netapi ise dc query "
SELECT
    e.MAC_ADDRESS,
    e.HOSTNAME,
    p.ENDPOINT_PROFILE,
    auth.AUTHENTICATION_METHOD,
    auth.AUTHENTICATION_PROTOCOL,
    auth.last_auth
FROM ENDPOINTS_DATA e
LEFT JOIN PROFILED_ENDPOINTS_SUMMARY p ON e.MAC_ADDRESS = p.ENDPOINT_ID
LEFT JOIN (
    SELECT
        CALLING_STATION_ID,
        MAX(AUTHENTICATION_METHOD) as AUTHENTICATION_METHOD,
        MAX(AUTHENTICATION_PROTOCOL) as AUTHENTICATION_PROTOCOL,
        MAX(TIMESTAMP_TIMEZONE) as last_auth
    FROM RADIUS_AUTHENTICATIONS
    WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
    GROUP BY CALLING_STATION_ID
) auth ON e.MAC_ADDRESS = auth.CALLING_STATION_ID
WHERE auth.last_auth IS NOT NULL
ORDER BY auth.last_auth DESC
FETCH FIRST 50 ROWS ONLY
"

ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct

ROUND(SUM(CASE WHEN PASSED = 'Pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)

WHERE TIMESTAMP_TIMEZONE > SYSDATE - 1  -- Last 24 hours