netapi Integration

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
    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
    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
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
    TRUNC(TIMESTAMP) as Date,
    COUNT(DISTINCT CALLING_STATION_ID) as Unique_MACs_Per_Day,
    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
    CALLING_STATION_ID as MAC_Address,
    USERNAME,
    AUTHENTICATION_METHOD,
    AUTHENTICATION_PROTOCOL,
    DEVICE_NAME,
    ENDPOINT_PROFILE,
    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
    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 FAILED = 1 THEN 1 ELSE 0 END) as Failed,
    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,
    COUNT(*) as sessions,
    ROUND(AVG(ACCT_SESSION_TIME)/60, 1) as avg_minutes,
    ROUND(SUM(ACCT_SESSION_TIME)/3600, 1) as total_hours,
    SUM(ACCT_INPUT_OCTETS + ACCT_OUTPUT_OCTETS) as total_bytes
FROM RADIUS_ACCOUNTING
WHERE TIMESTAMP_TIMEZONE > SYSDATE - 7
GROUP BY CALLING_STATION_ID
ORDER BY total_hours DESC
FETCH FIRST 20 ROWS ONLY
"