Meshview

Meshview

Meshview is software that will monitor MQTT topics for Meshtastic traffic and build a local SQLite database. It includes a local web site with canned queries and graphic display of the data.

This repo gives very easy instructions on how to set it up for Bayme.sh traffic. It took about 10 minutes to get this running on a Macbook pro. Overnight the database grew to 16MB.

The database is the file packets.db. You can query it by hand using SQLite.

Schema

CREATE TABLE node (
	id VARCHAR NOT NULL, 
	node_id BIGINT,      
	long_name VARCHAR, 
	short_name VARCHAR, 
	hw_model VARCHAR, 
	firmware VARCHAR, 
	role VARCHAR,         -- CLIENT, CLIENT_MUTE, etc
	last_lat BIGINT,      -- the last reported latitude
	last_long BIGINT, 
	channel VARCHAR,      -- the preset. Not sure what happens if you're not using a preset
	last_update DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (node_id)
);


CREATE TABLE packet (
	id BIGINT NOT NULL,    -- every packet has a unique identifier generated by the originating node
	portnum INTEGER, 
	from_node_id BIGINT, 
	to_node_id BIGINT, 
	payload BLOB, 
	import_time DATETIME, 
	channel VARCHAR, 
	PRIMARY KEY (id)
);

CREATE TABLE packet_seen (
	packet_id BIGINT NOT NULL, 
	node_id BIGINT NOT NULL,   -- the node that sent this report to the MQTT service
	rx_time BIGINT NOT NULL, 
	hop_limit INTEGER, 
	hop_start INTEGER, 
	channel VARCHAR, 
	rx_snr FLOAT, 
	rx_rssi INTEGER, 
	topic VARCHAR,             -- the MQTT topic that this report was sent to
	import_time DATETIME, 
	PRIMARY KEY (packet_id, node_id, rx_time), 
	FOREIGN KEY(packet_id) REFERENCES packet (id)
);

CREATE TABLE traceroute (
	id INTEGER NOT NULL, 
	packet_id BIGINT, 
	gateway_node_id BIGINT, 
	done BOOLEAN, 
	route BLOB, 
	import_time DATETIME, 
	PRIMARY KEY (id), 
	FOREIGN KEY(packet_id) REFERENCES packet (id)
);

Notes on the database

packet table

Each packet and any response have a unique packet id. One row in this table for each packet id.

Note that some packet types, like traceroute, grow their payload size over time. This table has the payload blob of the first message that makes it through the MQTT logging process. For instance, the payload size of a traceroute might grow from 0 to 140 bytes as nodes pass it along. However the payload size in the packet table might be any size in that range.

packets_seen table

One row for each time the packet_id is reported by an MQTT gateway. The number of times a packet is seen "by a gateway".

Note that for traceroute packets only the outbound packets are in this table.

traceroute table

Traceroute packets reported. This table holds both outbound and reply packets. A traceroute that is on its way to a destination has the Done field set to 0. The packets that are returning have Done set to 1. The size of the traceroute Route blob will grow as more nodes forward on the traceroute request or reply.

Interesting Queries

(Thanks to nullrouten)

Node location

All nodes with GPS position information

    SELECT 
        node_id,
        last_lat / 1e7 AS lat,
        last_long / 1e7 AS lon,
        channel
    FROM node
    WHERE last_lat IS NOT NULL 
      AND last_long IS NOT NULL
    ORDER BY node_id;

SNR

Received Signal to Noise Ratio of each packet in 1dB bins

    SELECT a.channel, snr_bucket, COUNT(*) as num_seen
    FROM (
    SELECT 
        n.channel,
        -- floor to 1 dB bins, accounting for negatives
        CASE 
            WHEN rx_snr >= 0 THEN CAST(rx_snr AS INT)
            ELSE CAST(rx_snr AS INT) - (rx_snr < CAST(rx_snr AS INT))
        END AS snr_bucket
    FROM packet_seen ps
    JOIN packet p ON ps.packet_id = p.id
    JOIN node n ON ps.node_id = n.node_id
    WHERE rx_snr IS NOT NULL
    ) a
    GROUP BY a.channel, snr_bucket
    ORDER BY a.channel, snr_bucket;

Top 15 Nodes sending packets

    SELECT 
        n.node_id,
        n.long_name,
        n.short_name,
        n.channel,
        COUNT(DISTINCT p.id) AS total_packets_sent,
        COUNT(ps.packet_id) AS total_times_seen
    FROM node n
    LEFT JOIN packet p ON n.node_id = p.from_node_id
        AND p.import_time >= DATETIME('now', 'localtime', '-24 hours')
    LEFT JOIN packet_seen ps ON p.id = ps.packet_id
    GROUP BY n.node_id, n.long_name, n.short_name
    HAVING total_packets_sent > 0
    ORDER BY total_times_seen DESC
    LIMIT 15;