Networking [Tab]

1\) network adapters (grid) ui columns adapter name • status • mac address • ip assignment (dhcp/static) • ipv4 address • ipv6 address • vlan id • connected to 1 1 data model (where to store) primary table device network adapter for adapters and ip config (arrays), dhcp metadata, link state, speed/duplex, type, vlan, etc to show “connected to” (switch/port), use network connection when a switch is the discovery vantage point, its physical network device port (local) can point to a remote endpoint nic (your adapter) via remote network adapter id ui can then display the switch name/port if you also manage switch ports, the local side lives in network device port joins flow endpoint nic → network connection remote network adapter id → network device port (local) → switch device 1 2 collection (how to gather) a) endpoint agent (windows / linux / macos) adapter inventory & link state windows get netadapter, get netadapteradvancedproperty, get netipaddress, get dnsclientserveraddress, get netipconfiguration linux ip link show, ip j addr, ethtool \<iface> (speed/duplex), /proc/net/wireless (wifi), networkmanager (nmcli g), or systemd networkd files macos networksetup listallhardwareports, ifconfig a, ipconfig getpacket \<iface> (dhcp), route n get default (gateway), scutil dns (dns) dhcp vs static (for “ip assignment”) windows win32 networkadapterconfiguration dhcpenabled or get netipinterface and dhcp state linux detect via nm/systemd config (e g , /etc/networkmanager/system connections/ nmconnection, /etc/systemd/network/ network) or presence of active dhclient lease in /var/lib/dhcp / macos networksetup getinfo \<service> shows “dhcp configuration” vs “manual” vlan id windows get netadapteradvancedproperty displayname "vlan id" or priorityvlantag (varies by nic driver) linux /proc/net/vlan/config, ip d link show (look for vlan id \<n>) macos vlans are configured as separate services; list via networksetup listvlans ipv6 ensure you collect both global and link local; store all in ip address v6\[] normalize into device network adapter name, description, mac address, status (up/down/disabled), interface type (ethernet/wi fi/loopback/tunnel), speed mbps, duplex mode, physical adapter flags; arrays for ip address v4, ip address v6, subnet mask v4, subnet mask v6, default gateway v4, default gateway v6, dns server\[], wins server\[]; plus dhcp fields and vlan id record source precedence in last seen source (agent vs intune) for merge logic b) network probe (lldp/cdp/mac tables) for “connected to” poll access switches/edges over snmp and collect lldp/cisco cdp neighbor tables (remote sysname, portid, platform, management ip, vlan) mac address tables (bridge fdb) to map endpoint mac → switch port when lldp isn’t present build/refresh network device port for each physical port discovered then write network connection rows linking local network device port id to remote endpoint (remote device id and/or remote network adapter id) by mac/ip correlation 1 3 delta / retention for adapters upsert by (device id, mac address, name) heuristic; mark unchanged ones as active; hide stale when not observed for n days; keep updated at current on change for connections update last seen at per discovery; if not seen after x cycles, set status='inactive' but retain first seen at for audit 1 4 rendering (example query plan) base select from device network adapter where device id = \ id order by name; connected to for each adapter, select nd device id as switch id, d name as switch name, nd port name from network connection nc join network device port nd on nd id = nc local port id join device d on d id = nd device id where nc remote network adapter id = \ adapter id and nc status='active' order by nc last seen at desc limit 1; 2\) open ports (grid) ui columns port • protocol • local address • process name • status 2 1 data model use device open port for listeners and active connections (store local/remote, state, owning pid, service banner/version) index by device id and (optionally) process id link to running processes via device process (per snapshot process metadata) 2 2 collection a) endpoint agent windows get nettcpconnection state listen,established owningprocess, get netudpendpoint, fall back to netstat abno; map owningprocess → process snapshot linux ss tunlp (listeners) and ss tuno (connections); optionally lsof npi macos lsof npitcp stcp\ listen, lsof npiudp, or netstat vanp tcp/udp service banner (optional) short, safe banner grabs for common ports (e g , tcp/22, tcp/80/443) with low timeouts normalize into device open port local address, port, protocol, state (tcp only), optional remote address/remote port for established connections, process id, service name, service version, and timestamps first seen at, last seen at use active in scan as a working flag during each run; set active=false for ports not seen after grace b) process table to show “process name” take a process snapshot right before or after the port scan insert/update device process with (device id, pid, start time) uniqueness to avoid dupes 2 3 delta / retention if a port was previously seen, update last seen at; if missing in a new scan, do not immediately delete—flip active after n missed scans indexes already support fast lookups 2 4 rendering show only active rows sorted by (protocol, port) join (optional) to most recent process snapshot by device id+pid 3\) static routes (grid) ui columns destination • netmask (prefix) • gateway • interface • metric 3 1 data model use device static route with destination cidr, gateway ip, optional interface name/index, metric, persistent, and last seen at unique key defends against dupes per interface 3 2 collection windows get netroute policystore activestore | where object {$ routemetric ne $null}; filter routemetric, destinationprefix, interfacealias/index, nexthop linux ip route show + ip j route for json; capture metric, dev, proto static macos netstat nr (or route n get) and parse; persistent routes stored in /library/preferences/systemconfiguration/preferences plist or mdm profiles map device interface name/index to your adapter record when possible 3 3 delta / retention upsert by (device id, destination cidr, gateway ip, interface index); refresh last seen at; mark active=false when not observed index on device id supports tab load 4\) directory & cloud identity (right rail) ui fields ad domain • ad joined • ad last contact • entra id tenant • entra id joined • intune managed • intune last sync 4 1 data model & mapping ad domain / ad joined / ad last contact link the device to an ad computer row (dn, last logon timestamp, etc ) and its ad domain (domain name) “ad joined = yes” if an ad computer exists for the device; “ad last contact” = ad computer last logon timestamp (safest replicated timestamp) entra id tenant use your microsoft 365/entra integration instance tenant id and primary domain for display (e g , example onmicrosoft com) entra id joined (join type) if you already added a table/column for device join state (e g , azure ad device join type or device azure ad join type), populate from graph/intune (hybrid/azureadjoined/azureadregistered) if not present, add one (recommended) intune managed / intune last sync a device is “intune managed = yes” if device intune device id is set; “intune last sync” comes from device intune last checkin at 4 2 collection a) ad (on prem) windows agents can report ad join (domain membership) via win32 computersystem domain, dsregcmd /status (hybrid hints), plus computer dn backfill and keep authoritative directory attributes via ldap reads on your directory connector b) entra id / intune (cloud) use microsoft graph intune device properties (check in time, compliance, join type) directory tenant info (primary domain) store tenant metadata in integration instance (already modeled) 5\) internet connectivity (right rail) ui fields external ip address • last public ip check 5 1 data model use device external ip address for the current external ip add device external ip last check at timestamptz (if not already present) to drive the “last public ip check” timestamp (you already have device external ip address ) 5 2 collection from each agent, hit a tiny, redundant set of endpoints (e g , anycast stun or a simple https “what is my ip” behind your domain) record the detected ip and the timestamp as the check time; debounce updates (only write when value changes or after a long interval) to avoid churn for nat shared ips for multi nic servers, this is outbound nat ip —document that the value is site level in many environments optionally also compute a location/site level external ip rollup 6\) source of truth, merge rules & schedules per field precedence in general agent > intune > directory > probes for adapter details; probes are authoritative for “connected to” (switch port) and vlan on the switch side; ad is authoritative for ad presence/last logon; intune is authoritative for mdm/entra join persist most recent last seen source for adapters frequencies (defaults) adapters & routes every 4–8 hours on servers; 8–24 hours on workstations; on boot and nic change via event hooks open ports quick scan every 2–4 hours (servers) / 8–24 hours (workstations) banner grabs are opt in and low rate switch discovery via probes every 1–4 hours (or trap driven) cloud (graph/intune) every 2–6 hours for device & tenant metadata; ad ldap every 4 hours 7\) data validation & normalization mac canonicalization store lowercase, colon less or colon normalized consistently in device network adapter mac address; index exists for lookups ip lists always store all addresses in arrays; pick the “primary” for display as the first routable non link local entry vlan prefer switch reported access vlan; if endpoint reports trunk/vlans, show adapter’s configured vlan in the grid and (optionally) show the switch port vlan on hover (“connected to”) routes compute human “netmask” column from destination cidr prefix when rendering 8\) security & performance least privilege windows agent queries use standard wmi/powershell; no admin required for most reads (except some nic advanced props) linux/macos stick to read only commands; avoid sudo where possible snmp read only for probes; rate limit walks; use lldp/cdp tables directly to minimize load pii & secrets never store dhcp options with sensitive data; banner grabs redact tokens; don’t persist payloads beyond service name/service version resiliency exponential backoff on graph/apis; cache tenant metadata; concurrent scans are bounded 9\) api & ui contracts adapters endpoint get /devices/\ id/network/adapters returns an array of adapters plus a nested connectedto (if found) { switchdeviceid, switchname, portname, lastseenat } backed by device network adapter + network connection open ports endpoint get /devices/\ id/network/open ports supports ?onlyactive=true and returns process names by best effort join to the latest device process snapshot static routes endpoint get /devices/\ id/network/static routes lists active routes with destination, prefix, gateway, interface, metric identity panel endpoint get /devices/\ id/identity summary composes from ad (ad domain domain name, ad computer last logon timestamp), intune (device intune device id, device intune last checkin at), entra tenant (integration instance primary domain), and your entra join field/table 10\) ingestion/delta algorithms (pseudo) adapters upsert each observed adapter; after a scan, mark any rows for the device not seen as active=false (soft delete) keep history via updated at open ports during a run, set active in scan=true for each seen line; after the run, flip rows with active in scan=false to active=false update last seen at static routes upsert and bump last seen at; mark missing as inactive after grace connections if a probe sees the same remote nic on a different port, update local port id and last seen at; keep first seen at for audit 11\) edge cases multiple ips per nic display first usable; expose all in details (your arrays already model this ) no lldp/cdp fall back to mac table correlation; if multiple ports claim the mac (e g , port security err), mark “connected to” ambiguous teamed/bonded nics present team as a logical adapter with member macs in details; on linux, interpret bond0 and member slaves virtual nics set physical adapter=false; populate virtual switch name if known (e g , hyper v/esxi) udp no connection “state”; show “listening” if bound locally 12\) testing & acceptance criteria adapters unplug/plug cable or disable/enable nic and confirm status updates within one scan cycle assign static ip → agent reports dhcp enabled=false; switch to dhcp → true create vlan sub interface on linux/macos and confirm vlan id mapping open ports start/stop a listener (nc l 0 0 0 0 8888) and verify rows appear/disappear with correct state and process name ensure active in scan flip works when port disappears routes add/delete a static route and validate upsert + last seen at and inactive marking connected to move patch from port a to port b and confirm network connection local port id updates and ui reflects the new port with a fresh last seen at identity panel disable ad account and confirm last logon timestamp no longer advances; intune unenroll test shows intune device id cleared and managed flag flips internet connectivity rotate nat ip (test lab) and verify device external ip address changes and “last public ip check” updates when the value changes 13\) what you may still need to add (small deltas) entra join type field (if missing) add device azure ad join type varchar(20) or a small azure ad device table to track device ↔ directory object & join state (hybrid/aadjoined/aadregistered) last public ip check (if missing) add device external ip last check at timestamptz to support the timestamp displayed in the right rail if you want, i can follow up with concrete agent scripts/commands per os and a set of sql views for the tab to make the ui wiring trivial