Database structure (mariadb) : tables, host inventories, etc.

[InternalISTeam]

Hello,
we are looking for the documentation about the data structure in meshcentral.
The goal is to extract data about agents obviously : Antivirus and firewall enabled, OS version, etc.
Where are these data please ?

(Before i arrived in my team, there was an old instance / version of meshcentral with mongodb (not mariadb), and the data were easily findable in the DB...)

Thanks !!

[si458]

the isnt really a structure as such as the data is all in JSON format in the column doc
this allows support to move between different databases

events - contains events
power - contains the timeline data , when device was on or off
serverstats - contains info about meshcentral server
smbios - contains info about devices (think details tab if i remember)
plugin - contains plugin info stuff
main - contains all the data for users, groups, devices
eventids - linked to events table

[InternalISTeam]

Hello again,
thanks for your fast reply :)

I have another question, I suppose from your reply the main table is my target. The doc column has a json (just as you explained) containing an _id and a lastEvent and that's it.

To what refers the "alnode" content in the doc column, can you be more specific about database content and how to use data inside please ? (Is it encrypted ?)

Thanks a lot !

[si458]

after looking at the code its something to do wth errors from an agent (i think?)

MeshCentral/meshagent.js

Lines 1613 to 1621 in cac505e

	case 'errorlog': { // This is the agent error log
	if ((!Array.isArray(command.log)) || (command.log.length == 0) || (parent.parent.agentErrorLog == null)) break;
	var lastLogEntry = command.log[command.log.length - 1];
	if ((lastLogEntry != null) && (typeof lastLogEntry == 'object') && (typeof lastLogEntry.t == 'number')) {
	parent.fs.write(parent.parent.agentErrorLog, obj.dbNodeKey + ', ' + Date.now() + ', ' + str + '\r\n', function (err) { });
	db.Set({ _id: 'al' + obj.dbNodeKey, lastEvent: lastLogEntry.t });
	}
	break;
	}

[InternalISTeam]

What do you mean exactly ?
There is no error on agent "inventory" because we can see infos on Meshcentral UI (attachment) :

So why we cannot find these infos in DB ? Where can we looking at please ?
Thanks !

[si458]

that type of infomation is listed under "node//(NODEIDHERE)" of the id column
and to get the details tab of information, you would search "sinode//(NODEIDHERE)" of the id column

[InternalISTeam]

Sorry, not clear for me :)
in the main table, we have that kind of record :

MariaDB [meshcentral]> select * from main limit 1;
+--------------------------------------------------------------------------+------+--------+-------+---------+-----------------------------------------------------------------------------------------------------------+
| id                                                                       | type | domain | extra | extraex | doc                                                                                                       |
+--------------------------------------------------------------------------+------+--------+-------+---------+-----------------------------------------------------------------------------------------------------------+
| alnode//$$CZwCvQ@LXtQUKFLUM0T1eKXtWHyNBTXSMgDRVuCIHOffr$Jd1XZACmbmzK@FvA | NULL | NULL   | NULL  | NULL    | {"_id":"alnode//$$CZwCvQ@LXtQUKFLUM0T1eKXtWHyNBTXSMgDRVuCIHOffr$Jd1XZACmbmzK@FvA","lastEvent":1737106932} |

how can we extract, for example, the "firewall enabled" info from that ?

About smbios table, it is empty...

About events table :

MariaDB [meshcentral]> select * from events limit 1;
+-------+---------------------+--------+------------+------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id    | time                | domain | action     | nodeid                                                                 | userid | doc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+---------------------+--------+------------+------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 21376 | 2025-01-07 16:28:07 |        | changenode | node//Heq6ZRlNcNX4aCMNvBlnIBM97ApgdJwVzTFIl92@jYqnzMlzia2$U2rky8TTW6Lo | NULL   | {"etype":"node","action":"changenode","nodeid":"node//Heq6ZRlNcNX4aCMNvBlnIBM97ApgdJwVzTFIl92@jYqnzMlzia2$U2rky8TTW6Lo","domain":"","msg":"Changed device PSD15641 from group workstations: host","time":"2025-01-07T15:28:07.696Z","ids":["node//Heq6ZRlNcNX4aCMNvBlnIBM97ApgdJwVzTFIl92@jYqnzMlzia2$U2rky8TTW6Lo","*","mesh//wZNnCc2QCled@y@A9XO1UKOG70wIxmKAptPKfSpyg2ryPvWBIok6bP6wUQOCzm9b","ugrp//8GahMcqyit0SBrhDt5hjkWT3XVZ94VoMUf1XeSvYnDzraxnie3Hlj2A9w3SxoQvh","ugrp//E3MzF7PzZvJv0Tjue4T78gUsSOtBIPOpKbWMPnSZDM5XuqQhL1H32HaPjKFKV6vI","ugrp//fyBeUYAVavEPcdXLfCu@kA6$WMf0vcZzx4gtZtY$w7uYzPgxXQmH2WU7Accsju0S"]} |

[InternalISTeam]

Hello, an idea so :) ?

[si458]

its very hard to explain the structure but basically everything goes into the main table
and then each type of thing gets an ID which can be uniquie per device and group and user but also adds extra things on
alnode//(NODEID) - links to agent errors
node//(NODEID) - info about the node
mesh//(NODEID) - info about the mesh
user//(USERID) - info about the user
etc...
the is loads of them and hard to list every single one

[InternalISTeam]

and do you have an idea why the data structure has changed to more complicated one :) ? (i maean for inventory, monitoring, etc. it will be an headache to extract data)

[si458]

It hasn't changed at all
It's been like this since beginning of time
It's done this way so it can be used with any database
Mysql, postgres, mongodb, nedb, sqlite etc...

[InternalISTeam]

ah ok, i thought there was a big update on that since our first version, we will have a look how to do so.
Thanks a lot for your quick answers !