Endpoints & Profiling Views

# Endpoint profile distribution with percentages
netapi ise dc --format json query "
  SELECT
    edf_endpointpolicy as profile,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct
  FROM cepm.edf_ep_master
  WHERE edf_endpointpolicy IS NOT NULL
  GROUP BY edf_endpointpolicy
  ORDER BY count DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -r '
  .[] |
  ((.pct / 2) | floor | . as $bars |
    (if .pct > 20 then "\u001b[32m" elif .pct > 5 then "\u001b[33m" else "\u001b[34m" end) +
    ("█" * $bars) + ("░" * (50 - $bars)) + "\u001b[0m") +
  " " + (.pct | tostring) + "% " +
  "\u001b[36m" + .profile + "\u001b[0m (" + (.count | tostring) + ")"
'

# Unknown/unclassified endpoints
netapi ise dc --format json query "
  SELECT
    edf_macaddress as mac,
    TO_CHAR(edf_create_time, 'YYYY-MM-DD HH24:MI') as first_seen,
    TO_CHAR(edf_update_time, 'YYYY-MM-DD HH24:MI') as last_seen
  FROM cepm.edf_ep_master
  WHERE edf_endpointpolicy IN ('Unknown', 'Cisco-Device', 'Workstation')
  ORDER BY edf_update_time DESC NULLS LAST
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  .[] |
  "\u001b[33m?\u001b[0m \u001b[36m" + .mac + "\u001b[0m │ First: " + .first_seen + " │ Last: " + .last_seen
'

# Static vs dynamic group assignment
netapi ise dc --format json query "
  SELECT
    CASE WHEN edf_staticassignment = 1 THEN 'Static' ELSE 'Dynamic' END as assignment,
    edf_endpointpolicy as profile,
    COUNT(*) as count
  FROM cepm.edf_ep_master
  WHERE edf_endpointpolicy IS NOT NULL
  GROUP BY CASE WHEN edf_staticassignment = 1 THEN 'Static' ELSE 'Dynamic' END, edf_endpointpolicy
  ORDER BY count DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  group_by(.assignment) | .[] |
  "\u001b[1m=== " + (.[0].assignment // "Unknown") + " Assignment ===\u001b[0m",
  "  Profile                        │ Count",
  "─────────────────────────────────┼──────",
  (sort_by(-.count) | .[:10][] |
    (if .assignment == "Static" then "\u001b[34m▪\u001b[0m" else "\u001b[32m▪\u001b[0m" end) +
    " " + (((.profile // "") + "                              ") | .[0:30]) +
    " │ " + (.count | tostring)
  ),
  ""
'

# OUI/Vendor breakdown with awk lookup
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    SUBSTR(edf_macaddress, 1, 8) as oui,
    COUNT(*) as count
  FROM cepm.edf_ep_master
  WHERE edf_macaddress IS NOT NULL
  GROUP BY SUBSTR(edf_macaddress, 1, 8)
  ORDER BY count DESC
  FETCH FIRST 25 ROWS ONLY
" | jq -r '.[] | "\(.oui),\(.count)"' | awk -F',' '
BEGIN {
  # Common OUI mappings
  v["C8:5B:76"]="Apple"
  v["98:BB:1E"]="Intel"
  v["14:F6:D8"]="Intel"
  v["DC:A6:32"]="Raspberry Pi"
  v["00:50:56"]="VMware"
  v["52:54:00"]="QEMU/KVM"
  v["B8:27:EB"]="Raspberry Pi"
  v["E4:5F:01"]="Raspberry Pi"
  v["00:1A:79"]="Dell"
  v["00:25:B5"]="Dell"
  v["3C:7C:3F"]="Apple"
  v["A4:83:E7"]="Apple"
  v["F0:18:98"]="Apple"
  v["00:0C:29"]="VMware"
  v["08:00:27"]="VirtualBox"
  v["00:15:5D"]="Hyper-V"
  v["AC:DE:48"]="Dell"
  v["00:0A:F7"]="Broadcom"
  v["E4:54:E8"]="Xiaomi"
  v["1C:69:7A"]="EliteGroup"
}
{
  oui = $1
  count = $2
  vendor = (oui in v) ? v[oui] : "Unknown"
  printf "\033[36m%-12s\033[0m %-20s %6d ", oui, vendor, count
  # Bar chart
  bar_len = int(count / 10)
  if (bar_len > 40) bar_len = 40
  for (i=0; i<bar_len; i++) printf "█"
  print ""
}'

# Endpoint profile count (profiler matching uses edf_endpointpolicy)
netapi ise dc --format json query "
  SELECT
    edf_endpointpolicy as profile,
    COUNT(*) as endpoints
  FROM cepm.edf_ep_master
  WHERE edf_endpointpolicy IS NOT NULL
  GROUP BY edf_endpointpolicy
  ORDER BY endpoints DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -r '
  "\u001b[1m  Profile                        │ Endpoints\u001b[0m",
  "─────────────────────────────────┼──────────",
  (.[] |
    "\u001b[32m●\u001b[0m " +
    (((.profile // "") + "                              ") | .[0:30]) +
    " │ " + (.endpoints | tostring)
  )
'

# DHCP hostname patterns for profiling analysis
netapi ise dc --format json query "
  SELECT
    edf_hostname as hostname,
    edf_endpointpolicy as profile,
    COUNT(*) as count
  FROM cepm.edf_ep_master
  WHERE edf_hostname IS NOT NULL
  GROUP BY edf_hostname, edf_endpointpolicy
  ORDER BY count DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  "\u001b[1mHostname                  │ Profile              │ Count\u001b[0m",
  "──────────────────────────┼──────────────────────┼──────",
  (.[] |
    "\u001b[33m" + (((.hostname // "") + "                         ") | .[0:25]) + "\u001b[0m │ " +
    "\u001b[36m" + (((.profile // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    (.count | tostring)
  )
'

# Endpoints with recent profile changes (check MNT.CONFIG_CHANGE)
netapi ise dc --format json query "
  SELECT
    TO_CHAR(ACS_TIMESTAMP, 'YYYY-MM-DD HH24:MI') as timestamp,
    ADMIN_NAME as admin,
    OBJECT_NAME as endpoint_mac,
    REQUESTED_OPERATION as operation
  FROM MNT.CONFIG_CHANGE
  WHERE OBJECT_TYPE = 'Endpoint'
  AND ACS_TIMESTAMP > SYSDATE - 7
  ORDER BY ACS_TIMESTAMP DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  .[] |
  "\u001b[33m⚠\u001b[0m " + .timestamp + " │ " +
  "\u001b[36m" + .endpoint_mac + "\u001b[0m │ " +
  "\u001b[32m" + .operation + "\u001b[0m by " + .admin
'

# Recently discovered endpoints (last 24h)
netapi ise dc --format json query "
  SELECT
    edf_macaddress as mac,
    edf_endpointpolicy as profile,
    TO_CHAR(edf_create_time, 'YYYY-MM-DD HH24:MI') as discovered
  FROM cepm.edf_ep_master
  WHERE edf_create_time > SYSDATE - 1
  ORDER BY edf_create_time DESC
  FETCH FIRST 30 ROWS ONLY
" | jq -r '
  "\u001b[1m  MAC Address       │ Profile              │ Discovered\u001b[0m",
  "────────────────────┼──────────────────────┼─────────────────",
  (.[] |
    "\u001b[32m+\u001b[0m \u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    "\u001b[33m" + (((.profile // "") + "                    ") | .[0:20]) + "\u001b[0m │ " +
    (.discovered // "")
  )
'

# Stale endpoints (no activity in 30+ days)
netapi ise dc --format json query "
  SELECT
    edf_macaddress as mac,
    edf_endpointpolicy as profile,
    TO_CHAR(edf_update_time, 'YYYY-MM-DD') as last_seen,
    ROUND(SYSDATE - edf_update_time, 0) as days_stale
  FROM cepm.edf_ep_master
  WHERE edf_update_time < SYSDATE - 30
  ORDER BY edf_update_time ASC
  FETCH FIRST 50 ROWS ONLY
" | jq -r '
  "\u001b[1m  MAC Address       │ Profile              │ Last Seen  │ Days\u001b[0m",
  "────────────────────┼──────────────────────┼────────────┼──────",
  (.[] |
    (if .days_stale > 90 then "\u001b[31m✗\u001b[0m"
     elif .days_stale > 60 then "\u001b[33m⚠\u001b[0m"
     else "\u001b[34m●\u001b[0m" end) +
    " \u001b[36m" + (.mac // "N/A") + "\u001b[0m │ " +
    (((.profile // "") + "                    ") | .[0:20]) + " │ " +
    (.last_seen // "N/A") + " │ " + ((.days_stale // 0) | tostring)
  )
'

# Endpoint identity group summary
netapi ise dc --format json query "
  SELECT
    edf_identitygroupid as group_id,
    COUNT(*) as endpoint_count,
    COUNT(DISTINCT edf_endpointpolicy) as profile_count
  FROM cepm.edf_ep_master
  WHERE edf_identitygroupid IS NOT NULL
  GROUP BY edf_identitygroupid
  ORDER BY endpoint_count DESC
  FETCH FIRST 20 ROWS ONLY
" | jq -C '.'

# Export endpoints to CSV
# Note: netapi dc doesn't have --format csv, use JSON + jq conversion
netapi ise dc --format json query "
  SELECT
    edf_macaddress as mac,
    edf_endpointpolicy as profile,
    edf_endpointip as ip,
    edf_hostname as hostname,
    edf_staticassignment as static,
    edf_identitygroupid as group_id,
    TO_CHAR(edf_create_time, 'YYYY-MM-DD HH24:MI:SS') as created,
    TO_CHAR(edf_update_time, 'YYYY-MM-DD HH24:MI:SS') as updated
  FROM cepm.edf_ep_master
  ORDER BY edf_update_time DESC NULLS LAST
" | jq -r '
  (.[0] | keys) as $k |
  ($k | @csv),
  (.[] | [.[$k[]]] | @csv)
' > /tmp/endpoints-$(date +%Y%m%d).csv

echo "Exported $(wc -l < /tmp/endpoints-$(date +%Y%m%d).csv) endpoints"