Meshview - Revision history

Jschrempp: /* packet table */

2025-09-15T00:11:35Z

packet table

← Older revision		Revision as of 20:11, 14 September 2025
Line 70:		Line 70:

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

			Portnum:

			* 1: "Text"
			* 3: "Position"
			* 4: "Node Info"
			*67: "Telemetry"
			*70: "Traceroute"
			*71: "Neighbour Info"
			*73: "Map Report"

	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.		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.

Jschrempp: /* Notes on the database */

2025-09-15T00:04:38Z

Notes on the database

← Older revision		Revision as of 20:04, 14 September 2025
Line 67:		Line 67:
	== Notes on the database ==		== 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 ==		== Interesting Queries ==

Jschrempp: /* Interesting Queries */

2025-09-14T23:55:11Z

Interesting Queries

← Older revision		Revision as of 19:55, 14 September 2025
Line 64:		Line 64:
	);		);
	</pre>		</pre>

			== Notes on the database ==



	== Interesting Queries ==		== Interesting Queries ==

Jschrempp at 20:41, 14 September 2025

2025-09-14T20:41:09Z

@@ Line 102: / Line 102: @@
      GROUP BY a.channel, snr_bucket
      ORDER BY a.channel, snr_bucket;
 </pre>

Jschrempp: /* Schema */

2025-08-16T17:46:51Z

Schema

← Older revision		Revision as of 13:46, 16 August 2025
Line 12:		Line 12:
	CREATE TABLE node (		CREATE TABLE node (
	id VARCHAR NOT NULL,		id VARCHAR NOT NULL,
	node_id BIGINT,		node_id BIGINT,
	long_name VARCHAR,		long_name VARCHAR,
	short_name VARCHAR,		short_name VARCHAR,
	hw_model VARCHAR,		hw_model VARCHAR,
	firmware VARCHAR,		firmware VARCHAR,
	role VARCHAR,		role VARCHAR, -- CLIENT, CLIENT_MUTE, etc
	last_lat BIGINT,		last_lat BIGINT, -- the last reported latitude
	last_long BIGINT,		last_long BIGINT,
	channel VARCHAR,		channel VARCHAR, -- the preset. Not sure what happens if you're not using a preset
	last_update DATETIME,		last_update DATETIME,
	PRIMARY KEY (id),		PRIMARY KEY (id),
Line 28:		Line 28:

	CREATE TABLE packet (		CREATE TABLE packet (
	id BIGINT NOT NULL,		id BIGINT NOT NULL, -- every packet has a unique identifier generated by the originating node
	portnum INTEGER,		portnum INTEGER,
	from_node_id BIGINT,		from_node_id BIGINT,
Line 40:		Line 40:
	CREATE TABLE packet_seen (		CREATE TABLE packet_seen (
	packet_id BIGINT NOT NULL,		packet_id BIGINT NOT NULL,
	node_id BIGINT NOT NULL,		node_id BIGINT NOT NULL, -- the node that sent this report to the MQTT service
	rx_time BIGINT NOT NULL,		rx_time BIGINT NOT NULL,
	hop_limit INTEGER,		hop_limit INTEGER,
Line 47:		Line 47:
	rx_snr FLOAT,		rx_snr FLOAT,
	rx_rssi INTEGER,		rx_rssi INTEGER,
	topic VARCHAR,		topic VARCHAR, -- the MQTT topic that this report was sent to
	import_time DATETIME,		import_time DATETIME,
	PRIMARY KEY (packet_id, node_id, rx_time),		PRIMARY KEY (packet_id, node_id, rx_time),

Jschrempp: /* Meshview */

2025-08-16T17:35:54Z

Meshview

← Older revision		Revision as of 13:35, 16 August 2025
Line 3:		Line 3:
	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.		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.

	[https://github.com/pablorevilla-meshtastic/meshview 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~~. This software creates a SQLite database~~.		[https://github.com/pablorevilla-meshtastic/meshview 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.		The database is the file packets.db. You can query it by hand using SQLite.


	== Schema ==		== Schema ==

Jschrempp at 17:35, 16 August 2025

2025-08-16T17:35:15Z

← Older revision		Revision as of 13:35, 16 August 2025
Line 1:		Line 1:
			== 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.		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.

	[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database.		[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database.

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


	== Schema ==		== Schema ==

Jschrempp at 17:33, 16 August 2025

2025-08-16T17:33:28Z

← Older revision		Revision as of 13:33, 16 August 2025
Line 1:		Line 1:
			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.

	[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database.		[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database.

Jschrempp at 17:30, 16 August 2025

2025-08-16T17:30:43Z

@@ Line 1: / Line 1: @@
-[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database. If you want to query it, this is the schema:
+[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database.
 <pre>
@@ Line 56: / Line 58: @@
 	FOREIGN KEY(packet_id) REFERENCES packet (id)
 );
 </pre>

Jschrempp: Created page with " [https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database. If you want to query it, this is the schema:

 CREATE TABLE node ( 	id VARCHAR NOT NULL,  	node_id BIGINT,  	long_name VARCHAR,  	short_name VARCHAR,  	hw_model VARCHAR,  	firmware VARCHAR,  	r..."

2025-08-16T17:15:22Z

Created page with " [https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database. If you want to query it, this is the schema: <pre> CREATE TABLE node ( id VARCHAR NOT NULL, node_id BIGINT, long_name VARCHAR, short_name VARCHAR, hw_model VARCHAR, firmware VARCHAR, r..."

New page

[https://github.com/pablorevilla-meshtastic/meshview 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. This software creates a SQLite database. If you want to query it, this is the schema:

<pre>
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)
);
</pre>