Network Monitor

🛰️ Home Network Monitoring & Analytics Dashboard

A real-time network monitoring and analytics platform built using Node-RED, PostgreSQL, OpenWRT, and AppSmith.
Track traffic, devices, and DNS activity across your home or small-office network using a clean, extensible architecture.

✨ Features

🔍 Device Discovery & Tracking
- Real-time DHCP leases, hostnames, MAC/IP mapping
- Scan logs, RTT (latency), and status monitoring
📡 Traffic Monitoring
- Per-device download/upload stats using nlbwmon
- Layer-7 protocol tracking (QUIC, HTTPS, etc.)
- Traffic deltas and rate (bytes/sec) calculations
📊 DNS Analytics
- Summary of DNS queries per device
- Total vs blocked queries, top domains
- Latency averages
📈 Dashboards
- AppSmith-powered UI with tables, charts, and trends
- Top protocols, hourly trends, device-specific views
🛢️ PostgreSQL Backend
- Schemas for network_devices, network_usage, dns_query_summary, device_scan_log, and more
- Efficient queries for rollups and trends

🏗️ Architecture

[ OpenWRT + nlbwmon + Adguard Home]
        ↓
   [ Node-RED ]
   - Parses NLBW + DHCP
   - Calculates deltas & rate
   - Saves to PostgreSQL
        ↓
  [ PostgreSQL DB ]
        ↓
    [ AppSmith ]
  - Interactive Dashboards
  - Editable Device Table
![](https://raw.githubusercontent.com/appsmithorg/appsmith/release/static/appsmith_logo_primary.png)

This app is built using Appsmith. Turn any datasource into an internal app in minutes. Appsmith lets you drag-and-drop components to build dashboards, write logic with JavaScript objects and connect to any API, database or GraphQL source.

![](https://raw.githubusercontent.com/appsmithorg/appsmith/release/static/images/integrations.png)

### [Github](https://github.com/appsmithorg/appsmith) • [Docs](https://docs.appsmith.com/?utm_source=github&utm_medium=social&utm_content=appsmith_docs&utm_campaign=null&utm_term=appsmith_docs) • [Community](https://community.appsmith.com/) • [Tutorials](https://github.com/appsmithorg/appsmith/tree/update/readme#tutorials) • [Youtube](https://www.youtube.com/appsmith) • [Discord](https://discord.gg/rBTTVJp)

##### You can visit the application using the below link

###### [![](https://assets.appsmith.com/git-sync/Buttons.svg) ](http://192.168.0.7:8093/applications/685fa65935da3262ccd9bcfb/pages/685fa65935da3262ccd9bcfd) [![](https://assets.appsmith.com/git-sync/Buttons2.svg)](http://192.168.0.7:8093/applications/685fa65935da3262ccd9bcfb/pages/685fa65935da3262ccd9bcfd/edit)

Note: some third-party nodes may appear with blank styling, and not as they appear in the Node-RED Editor.

[{"id":"5143c870a6bb7dc2","type":"tab","label":"Network Monitor","disabled":false,"info":"","env":[]},{"id":"782ccc4afc7b816b","type":"group","z":"5143c870a6bb7dc2","name":"Ping and check devices","style":{"label":true},"nodes":["2acfa9974a8468f0","31fafbb7b57e8174","483c809233cc67e9","c109f1843cdfa177","26063dc10d0fe14c","246534e72d52951e","6bb5a33ff0f201ae","parse_mac","5e326a918c2192e0","3098498d54cc3ab6","6925c66df3fe8a3a","cfa039061075d593","520f1560bde5f3aa","2d4e240851bb540f","a7013340013145d1","d808645dc8566990"],"x":30.25,"y":26.16667938232422,"w":1336.666748046875,"h":202.00000858306885},{"id":"0f89df98c7e939fc","type":"group","z":"5143c870a6bb7dc2","name":"Get Data from AdGuard Home","style":{"label":true},"nodes":["b13bda941d42d3a0","9e351c5eaa52a3ce","c48643d828600e74","180d20bb9f330ae7","0a653829e15b4b22","a4b53b33df5a98e3","67ef233d61dcd1cc","db96a2b733f7a8ef","831bf3d4297c02a1"],"x":108.66667556762695,"y":255.91668605804443,"w":1257.0000801086426,"h":158.0462350845337},{"id":"c2ca876e66f5471f","type":"group","z":"5143c870a6bb7dc2","name":"Get data from OpenWRT","style":{"label":true},"nodes":["1a1f1c3a71f1b7ab","b417a13ef84d517a","b94dc91d0fe5f058","218be8845d466ae4","5c3d1049a4180592","9d7d484f82f6b648","62f27bd13a7b2b86","52c9a55b236cb3da","362499b752c2bfd5","17e728186ab23dbd"],"x":94.91679382324219,"y":413.66663551330566,"w":1269.9998931884766,"h":196.22217178344727},{"id":"2acfa9974a8468f0","type":"inject","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Scan Trigger","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"300","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":156.25,"y":67.16667938232422,"wires":[["31fafbb7b57e8174","180d20bb9f330ae7","b417a13ef84d517a"]]},{"id":"483c809233cc67e9","type":"split","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Individual Subnets","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":595.9166889190674,"y":68.38889026641846,"wires":[["c109f1843cdfa177"]]},{"id":"c109f1843cdfa177","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Generate IPs","func":"const cidr = msg.payload.cidr;\n\nfunction cidrToIps(cidr) {\n    const [ip, bits] = cidr.split('/');\n    const base = ipToLong(ip);\n    const mask = ~(2 ** (32 - parseInt(bits)) - 1);\n    const network = base & mask;\n    const broadcast = network | ~mask >>> 0;\n\n    const ips = [];\n    for (let i = network + 1; i < broadcast; i++) {\n        ips.push({ ip: longToIp(i) });\n    }\n    return ips;\n}\n\nfunction ipToLong(ip) {\n    return ip.split('.').reduce((acc, octet) => (acc << 8) + parseInt(octet, 10), 0) >>> 0;\n}\n\nfunction longToIp(long) {\n    return [24, 16, 8, 0].map(shift => (long >>> shift) & 255).join('.');\n}\n\nmsg.payload = cidrToIps(cidr);\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":793.0277404785156,"y":69.38890838623047,"wires":[["26063dc10d0fe14c"]]},{"id":"26063dc10d0fe14c","type":"split","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Individual IPs","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":965.1389236450195,"y":69.38890361785889,"wires":[["246534e72d52951e"]]},{"id":"246534e72d52951e","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"IP","func":"msg = {\n    payload: msg.payload.ip\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1118.9166736602783,"y":69.16667747497559,"wires":[["d808645dc8566990"]]},{"id":"6bb5a33ff0f201ae","type":"enhanced-ping","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"","ipAddress":"192.168.0.1","timeout":5000,"interval":0,"count":1,"size":32,"retries":0,"x":228.58341217041016,"y":127.50001335144043,"wires":[["5e326a918c2192e0"],["5e326a918c2192e0"]]},{"id":"parse_mac","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Parse MAC","func":"const out = msg.payload;\nconst match = out.match(/..:..:..:..:..:../);\nmsg.mac = match ? match[0] : null;\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":790.9167404174805,"y":132.4999885559082,"wires":[["6925c66df3fe8a3a"]]},{"id":"5e326a918c2192e0","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Process Ping","func":"msg = {\n    pinginfo : msg.payload, \n    payload : msg.payload.target\n};\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":413.91678619384766,"y":130.16664791107178,"wires":[["3098498d54cc3ab6"]]},{"id":"3098498d54cc3ab6","type":"exec","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","command":"ip neigh show","addpay":"payload","append":"","useSpawn":"false","timer":"","winHide":false,"oldrc":false,"name":"","x":599.9167098999023,"y":131.1666603088379,"wires":[["parse_mac"],[],[]]},{"id":"6925c66df3fe8a3a","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Process Final Result","func":"const result = msg.pinginfo?.statistics?.results?.[0] || {};\n\nconst isAlive = result.alive === true;\nconst pingTime = isAlive && result.time !== \"unknown\" ? parseFloat(result.time) : null;\nconst macAddr = typeof msg.mac === \"string\" ? msg.mac.toLowerCase() : null;\n\nmsg = {\n    payload : {\n        ip: msg.ip || result.host || null,\n        alive: isAlive,\n        rtt: pingTime, // ping time in ms or null\n        mac: macAddr,\n        timestamp: msg.timestamp || result.timestamp || new Date().toISOString()\n}};\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":994.4723472595215,"y":133.3889102935791,"wires":[["cfa039061075d593"]]},{"id":"cfa039061075d593","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"SQL Queries","func":"// clean payload\nconst { ip, mac: rawMac, rtt, alive } = msg.payload;\nif (!rawMac) return null;  // nothing to do without a MAC\n\nconst mac   = rawMac.toLowerCase();\nconst ipEsc = ip;           // we assume IPv4/IPv6 strings are safe\nconst macEsc = mac;         // MACADDR format is safe\n\n// SQL literals\nconst aliveLit = alive ? 'true' : 'false';\nconst rttLit   = (rtt !== null && rtt !== undefined) ? rtt : 'NULL';\n\n// Escape single‑quotes in the JSON payload for raw_data\nconst rawJson  = JSON.stringify(msg.payload).replace(/'/g, \"''\");\n\n// Build the combined SQL\nmsg.payload = [`\nINSERT INTO network_devices (mac_address, ip_address, comment)\n  VALUES ('${macEsc}', '${ipEsc}', 'auto discovered')\n  ON CONFLICT (mac_address) DO NOTHING;`.trim(),`INSERT INTO device_scan_log (device_id, status, rtt_ms, mac_address, ip_address, raw_data)\nSELECT\n  id,\n  ${aliveLit},\n  ${rttLit},\n  '${macEsc}',\n  '${ipEsc}',\n  '${rawJson}'::jsonb\nFROM network_devices\nWHERE mac_address = '${macEsc}';`.trim()];\n\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1195.8796463012695,"y":132.79633712768555,"wires":[["2d4e240851bb540f"]]},{"id":"2d4e240851bb540f","type":"split","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Individual IPs","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":218.9166603088379,"y":186.16668891906738,"wires":[["a7013340013145d1"]]},{"id":"a7013340013145d1","type":"function","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Massage","func":"msg = {\n    query: msg.payload\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":408.9166793823242,"y":187.16668796539307,"wires":[["520f1560bde5f3aa"]]},{"id":"d808645dc8566990","type":"delay","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"","pauseType":"delay","timeout":"400","timeoutUnits":"milliseconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":1270.916748046875,"y":69.1666955947876,"wires":[["6bb5a33ff0f201ae"]]},{"id":"b13bda941d42d3a0","type":"inject","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":214.66667556762695,"y":297.91668796539307,"wires":[["180d20bb9f330ae7"]]},{"id":"9e351c5eaa52a3ce","type":"http request","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":593.6667175292969,"y":298.91668796539307,"wires":[["0a653829e15b4b22","831bf3d4297c02a1"]]},{"id":"c48643d828600e74","type":"debug","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"debug 1","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1259.6667556762695,"y":297.9166889190674,"wires":[]},{"id":"180d20bb9f330ae7","type":"function","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"Adguard URL","func":"const username = \"oksbwn\";\nconst password = \"\"; // secure this via env if needed\n\nmsg.url = `http://192.168.0.11:3000/control/querylog?search=&response_status=all&older_than=&limit=10000`;\nmsg.headers = {\n    Authorization: \"Basic \" + Buffer.from(`${username}:${password}`).toString('base64')\n};\n\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":411.6666793823242,"y":297.91668701171875,"wires":[["9e351c5eaa52a3ce"]]},{"id":"0a653829e15b4b22","type":"function","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"function 1","func":"const now = Date.now();\nconst windowEnd = new Date(now);\nconst windowStart = new Date(now - 10 * 60 * 1000);\n\nconst summaries = {};\nfor (let q of msg.payload.data || []) {\n    const ip = q.client;\n    const domain = q.question?.name;\n    const elapsed = parseFloat(q.elapsedMs || 0);\n    const isBlocked = (q.reason || '').toLowerCase().includes(\"filter\");\n\n    if (!summaries[ip]) {\n        summaries[ip] = {\n            ip_address: ip,\n            domains: new Set(),\n            total: 0,\n            blocked: 0,\n            totalLatency: 0,\n        };\n    }\n\n    summaries[ip].domains.add(domain);\n    summaries[ip].total += 1;\n    summaries[ip].blocked += isBlocked ? 1 : 0;\n    summaries[ip].totalLatency += elapsed;\n}\n\nmsg.payload = Object.values(summaries).map((s) => ({\n    ip_address: s.ip_address,\n    domains: Array.from(s.domains),\n    total_queries: s.total,\n    blocked_queries: s.blocked,\n    average_latency: +(s.totalLatency / s.total).toFixed(2),\n    window_start: windowStart.toISOString(),\n    window_end: windowEnd.toISOString()\n}));\n\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":765.6666297912598,"y":298.9166851043701,"wires":[["a4b53b33df5a98e3"]]},{"id":"a4b53b33df5a98e3","type":"split","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"Individual IPs","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":932.6666946411133,"y":297.91667079925537,"wires":[["67ef233d61dcd1cc"]]},{"id":"67ef233d61dcd1cc","type":"function","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"Query","func":"const ip_address = msg.payload.ip_address;\nconst domains = msg.payload.domains.map(d => `'${d}'`).join(', ');\nconst total_queries = msg.payload.total_queries;\nconst blocked_queries = msg.payload.blocked_queries;\nconst average_latency = msg.payload.average_latency;\n\nmsg = {\n    query: `\nINSERT INTO dns_query_summary (\n    device_id,\n    domains,\n    total_queries,\n    blocked_queries,\n    average_latency,\n    timestamp\n)\nSELECT id,\n       ARRAY[${domains}],\n       ${total_queries},\n       ${blocked_queries},\n       ${average_latency},\n       NOW()\nFROM network_devices\nWHERE ip_address = '${ip_address}';\n`\n};\n\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1089.6667022705078,"y":296.91668605804443,"wires":[["c48643d828600e74","db96a2b733f7a8ef"]]},{"id":"1a1f1c3a71f1b7ab","type":"inject","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Trigger","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":195.25001525878906,"y":456.6666374206543,"wires":[["b417a13ef84d517a"]]},{"id":"b417a13ef84d517a","type":"function","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Shell Command","func":"msg.payload = 'echo \"===NLBW===\"; nlbw -c show; echo \"===LEASES===\"; cat /tmp/dhcp.leases';\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":404.91669750213623,"y":456.66663551330566,"wires":[["218be8845d466ae4"]]},{"id":"b94dc91d0fe5f058","type":"function","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Parser","func":"/*****************************************************************\n *  NLBW + DHCP Parser  ✦  Delta + Rate Calculator  ✦  Snapshot Cache\n *  Adds zero‑traffic rows for every DHCP lease not seen in NLBW\n *****************************************************************/\n\nfunction parseSize(str) {\n    if (!str) return 0;\n    const m = str.match(/^([\\d.]+)\\s*(B|KB|MB|GB|TB)$/i);\n    if (!m) return 0;\n    const val = parseFloat(m[1]);\n    const unit = m[2].toUpperCase();\n    const mul = { B: 1, KB: 1024, MB: 1 << 20, GB: 1 << 30, TB: 1 << 40 };\n    return Math.round(val * mul[unit]);\n}\n\nlet raw = msg.payload.stdout ? msg.payload.stdout.join('') : String(msg.payload);\nif (!raw.includes(\"===NLBW===\") || !raw.includes(\"===LEASES===\")) {\n    node.error(\"Missing section headers\");\n    return null;\n}\n\nlet [, nlbwPart, leasePart] = raw.split(/===NLBW===|===LEASES===/);\n\n// ---------- DHCP parsing ----------\nconst dhcpMap = {};  // ip -> { mac, hostname, isDHCP }\nleasePart.trim().split(/\\n+/).forEach(l => {\n    const p = l.trim().split(/\\s+/);\n    if (p.length >= 4) {\n        const ip = p[2];\n        const mac = p[1].toLowerCase();\n        const host = p[3] !== '*' ? p[3] : null;\n        dhcpMap[ip] = { mac, hostname: host, isDHCP: true };\n    }\n});\n\n// ---------- NLBW parsing ----------\nconst nlbwRows = nlbwPart.split(\"\\n\").filter(l => l.startsWith(\"IPv4\"));\nconst nowMs = Date.now();\nconst nowISO = new Date(nowMs).toISOString();\n\nlet nlbw = nlbwRows.map(line => {\n    const m = line.match(/^IPv4\\s+([\\d.]+)\\s+\\(([0-9a-f:]+)\\)\\s+(\\S+)\\s+(\\d+)\\s+([\\d.,]+\\s+\\S+)\\s+\\(\\s*([\\d.,]+\\s+\\S+)\\)\\s+([\\d.,]+\\s+\\S+)\\s+\\(\\s*([\\d.,]+\\s+\\S+)\\)/i);\n    if (!m) return null;\n\n    const [_, ip, mac, l7, conn, dl, dlPkt, ul, ulPkt] = m;\n    if (dl.includes(',') || ul.includes(',')) return null;  // malformed size string\n\n    return {\n        timestamp: nowISO,\n        ip,\n        mac: mac.toLowerCase(),\n        layer7: l7,\n        connections: Number(conn),\n        download_h: dl,\n        download_bytes: parseSize(dl),\n        upload_h: ul,\n        upload_bytes: parseSize(ul),\n        download_packets_h: dlPkt,\n        upload_packets_h: ulPkt\n    };\n}).filter(Boolean);\n\n// ---------- Add zero‑traffic rows for idle DHCP hosts ----------\nconst byIP = {};\nnlbw.forEach(e => (byIP[e.ip] = e));\n\nObject.keys(dhcpMap).forEach(ip => {\n    if (!byIP[ip]) {\n        const d = dhcpMap[ip];\n        const zeroRow = {\n            timestamp: nowISO,\n            ip,\n            mac: d.mac,\n            layer7: null,\n            connections: 0,\n            download_h: '0 B',\n            download_bytes: 0,\n            upload_h: '0 B',\n            upload_bytes: 0,\n            download_packets_h: '0',\n            upload_packets_h: '0'\n        };\n        byIP[ip] = zeroRow;\n        nlbw.push(zeroRow);\n    }\n});\n\n// ---------- Attach DHCP info ----------\nnlbw.forEach(e => {\n    const d = dhcpMap[e.ip] || {};\n    e.hostname = d.hostname || null;\n    e.isDHCP = !!d.isDHCP;\n});\n\n// ---------- Delta & rate ----------\nconst prev = flow.get(\"trafficData\") || { ts: 0, data: {} };\nconst dtSec = prev.ts ? (nowMs - prev.ts) / 1000 : 0;\n\nnlbw.forEach(e => {\n    const key = `${e.ip}_${e.layer7 || ''}`;\n    const old = prev.data[key];\n\n    if (old) {\n        let dlΔ = e.download_bytes - old.dl;\n        let ulΔ = e.upload_bytes - old.ul;\n\n        if (dlΔ < 0 || ulΔ < 0) {           // counter reset\n            node.warn(`Counter reset for ${key}`);\n            dlΔ = e.download_bytes;\n            ulΔ = e.upload_bytes;\n        }\n\n        e.delta_download_bytes = dlΔ;\n        e.delta_upload_bytes = ulΔ;\n        e.download_rate_bps = dtSec ? Math.round(dlΔ / dtSec) : 0;\n        e.upload_rate_bps = dtSec ? Math.round(ulΔ / dtSec) : 0;\n    } else {\n        e.delta_download_bytes = 0;\n        e.delta_upload_bytes = 0;\n        e.download_rate_bps = 0;\n        e.upload_rate_bps = 0;\n    }\n\n    // cache latest counters\n    prev.data[key] = { dl: e.download_bytes, ul: e.upload_bytes };\n});\nprev.ts = nowMs;\nflow.set(\"trafficData\", prev);\n\n// ---------- output ----------\nmsg.payload = nlbw;\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":896.9166831970215,"y":456.66669273376465,"wires":[["9d7d484f82f6b648"]]},{"id":"218be8845d466ae4","type":"ssh-v3","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"","conf":"d85425f3479cd52d","debug":false,"x":577.9167022705078,"y":455.66663455963135,"wires":[["5c3d1049a4180592"]]},{"id":"5c3d1049a4180592","type":"function","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Fetch stdout","func":"return {\n    payload: ''+msg.session.stdout\n}","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":738.916618347168,"y":456.66669273376465,"wires":[["b94dc91d0fe5f058"]]},{"id":"9d7d484f82f6b648","type":"function","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"SQL Generator","func":"if (!Array.isArray(msg.payload)) {\n    node.error(\"Payload is not an array\");\n    return null;\n}\n\nfunction escape(value) {\n    if (typeof value === 'string') {\n        return \"'\" + value.replace(/'/g, \"''\") + \"'\";\n    } else if (value === null || value === undefined) {\n        return 'NULL';\n    } else {\n        return value;\n    }\n}\n\nlet queries = [];\n\nmsg.payload.forEach(entry => {\n    if (!entry.mac && !entry.ip) {\n        node.warn(\"Skipping entry, no MAC or IP: \" + JSON.stringify(entry));\n        return;\n    }\n\n    const ip = escape(entry.ip);\n    const mac = escape(entry.mac);\n    const hostname = escape(entry.hostname || null);\n    const layer7 = escape(entry.layer7);\n    const connections = escape(entry.connections);\n    const download_bytes = escape(entry.download_bytes);\n    const upload_bytes = escape(entry.upload_bytes);\n    const download_delta = escape(entry.delta_download_bytes);\n    const upload_delta = escape(entry.delta_upload_bytes);\n    const timestamp = escape(entry.timestamp);\n    const isDHCP = entry.isDHCP;\n    // INSERT with device_id lookup\n    queries.push(`\n  INSERT INTO network_usage (\n    device_id, ip, mac, hostname, layer7, connections,\n    download_bytes, upload_bytes, download_delta, upload_delta, timestamp\n)\nSELECT id,\n       ${ip}, \n       mac_address, \n       ${hostname}, \n       ${layer7}, \n       ${connections},\n       ${download_bytes}, \n       ${upload_bytes}, \n       ${download_delta}, \n       ${upload_delta}, \n       ${timestamp}\nFROM public.network_devices\nWHERE ip_address = ${ip}\n   OR mac_address::text LIKE ${mac}\nORDER BY CASE WHEN ip_address = ${ip} THEN 0 ELSE 1 END\nLIMIT 1;\n`);\n\n    // Optional hostname update\n    if (entry.hostname) {\n        queries.push(`\n            UPDATE network_devices\n            SET hostname = ${hostname},isdhcp=${isDHCP}\n            WHERE (mac_address::text LIKE ${mac} OR ip_address = ${ip}) \n        `);\n    }\n});\n\nmsg.payload = queries;\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1066.9166870117188,"y":454.66663551330566,"wires":[["62f27bd13a7b2b86"]]},{"id":"62f27bd13a7b2b86","type":"split","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Individual IPs","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":1268.9166870117188,"y":456.66663360595703,"wires":[["362499b752c2bfd5"]]},{"id":"362499b752c2bfd5","type":"function","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Massage","func":"msg = {\n    query: msg.payload\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":180.9167938232422,"y":509.6666965484619,"wires":[["52c9a55b236cb3da","17e728186ab23dbd"]]},{"id":"17e728186ab23dbd","type":"debug","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"debug 2","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":386.13891220092773,"y":568.8888072967529,"wires":[]},{"id":"831bf3d4297c02a1","type":"debug","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"debug 3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":929.6295776367188,"y":372.9629211425781,"wires":[]},{"id":"31fafbb7b57e8174","type":"postgresql","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Get Subnets","query":"SELECT * FROM scan_subnets;","postgreSQLConfig":"770e36a9de72967d","split":false,"rowsPerMsg":1,"outputs":1,"x":398.9166145324707,"y":68.2777738571167,"wires":[["483c809233cc67e9"]]},{"id":"520f1560bde5f3aa","type":"postgresql","z":"5143c870a6bb7dc2","g":"782ccc4afc7b816b","name":"Add to SQL","query":"","postgreSQLConfig":"770e36a9de72967d","split":false,"rowsPerMsg":1,"outputs":1,"x":594.9168243408203,"y":187.1666717529297,"wires":[[]]},{"id":"db96a2b733f7a8ef","type":"postgresql","z":"5143c870a6bb7dc2","g":"0f89df98c7e939fc","name":"Add to SQL","query":"","postgreSQLConfig":"770e36a9de72967d","split":false,"rowsPerMsg":1,"outputs":1,"x":1266.6667556762695,"y":348.9166898727417,"wires":[[]]},{"id":"52c9a55b236cb3da","type":"postgresql","z":"5143c870a6bb7dc2","g":"c2ca876e66f5471f","name":"Add to SQL","query":"","postgreSQLConfig":"770e36a9de72967d","split":false,"rowsPerMsg":1,"outputs":1,"x":392.91682052612305,"y":510.6666955947876,"wires":[[]]},{"id":"d85425f3479cd52d","type":"ssh-conf","ssh":"","name":"192.168.0.1","userlabel":"192.168.0.1"},{"id":"770e36a9de72967d","type":"postgreSQLConfig","name":"","host":"192.168.0.7","hostFieldType":"str","port":5432,"portFieldType":"num","database":"nmonitor","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","applicationName":"","applicationNameType":"str","max":10,"maxFieldType":"num","idle":1000,"idleFieldType":"num","connectionTimeout":10000,"connectionTimeoutFieldType":"num","user":"postgres","userFieldType":"str","password":"postgres","passwordFieldType":"str"}]

Flow Info

Created 4 days ago

Rating: not yet rated

view on github

Owner

oksbwn

Actions

share flow

Node Types

Core

debug (x3)
delay (x1)
exec (x1)
function (x15)
http request (x1)
inject (x3)
split (x5)

Other

enhanced-ping (x1)
group (x3)
postgreSQLConfig (x1)
postgresql (x4)
ssh-conf (x1)
ssh-v3 (x1)
tab (x1)