SQLite3 Db hacking for MunkiReport
Making modules for MunkiReport is easier than ever. Seriously.
please make:module
It’s easier than ever to make modules for MunkiReport (check out the recent MDOYVR MunkiReport workshop) and since the heavy lifting is done you can concentrate on the business logic (what makes sense) and the commands to execute or the scripts to run (python, shellI, etc). Worry about actionable data and less about the tables and views.
If you testing in production (which you should never do, always test is a testing environment) then you may happen to change a module (tables and fields etc) but keep the module name the same. This will confuse your database and you will need to erase it from the db to continue. In SQL speak this is “Drop table”. (You could also delete the munkireport Db and start again, but this is for those crazy enough to test in production and may want to keep the other data).
Sqlite commands for Munkireport
- Maintenance mode (current way)
sudo ./please down Application is now in maintenance mode.
Old way –> sudo touch /Users/Shared/MunkiReport/munkireport-php/storage/framework/down
2. Edit MunkiReport db
sudo /usr/bin/sqlite3 /path/to/MR/app/db/db.sqlite
3. Exit maintenance mode (current way):
sudo ./please up MunkiReport is now live.
Old way –> sudo rm /Users/Shared/MunkiReport/munkireport-php/storage/framework/down
Note: if you forget to get out of maintenance mode then clients can’t check in
“ERROR: Server error: MunkiReport is in maintenance mode, try again later.”
4. Migrate Db
sudo ./please migrate
Please use please migrate (or migrate db in web admin) if making changes to a module or else. That is, if you’re crazy to do this in production.
Server An error occurred while processing: \fancy_module_processor
Server Error: SQLSTATE[HY000]: General error: 1 no such table: fancy_module (SQL: select * from "fancy_module" where ("serial_number" = D09TP1QLH1K3) limit 1)
5. SQL hacking:
If you’re testing in prod and change a module’s fields but keep the same module name this will confuse your database and you will need to erase the entire db and start again or just erase this module from the db to continue. In SQL speak this is called “Drop table”.
A. List tables
For tables, the type field will always be ‘table’ and the name field will be the name of the table. So to get a list of all tables in the database, use the following SELECT command:
SELECT name FROM sqlite_master
WHERE type=’table’
ORDER BY name;
B. Drop table
Go through the list of tables and confirm the one you want to drop. Then do it. You’re backed up anyway, right? I mean, the data will come back when the clients check in again. So don’t worry.
Drop table to remove “fancy module” table from Db
DROP TABLE fancy_module;
3. Exit
.quit
REFERNCE:
MunkiReport WIKI – https://github.com/munkireport/munkireport-php/wiki
Jon Crain’s module making blog series – https://joncra.in/2018/11/30/creating-munkireport-modules.html
SQLite FAQ – https://www.sqlite.org/faq.html#q5
DAM SAN 