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, 
	last_lat BIGINT, 
	last_long BIGINT, 
	channel VARCHAR, 
	last_update DATETIME, 
	PRIMARY KEY (id), 
	UNIQUE (node_id)
);


CREATE TABLE packet (
	id BIGINT NOT NULL, 
	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, 
	rx_time BIGINT NOT NULL, 
	hop_limit INTEGER, 
	hop_start INTEGER, 
	channel VARCHAR, 
	rx_snr FLOAT, 
	rx_rssi INTEGER, 
	topic VARCHAR, 
	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)
);

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;