lastphp Database Summary / Activity tool

lastphp is a simple tool for getting summaries of database columns from different databases and displaying them on a webpage.

It’s intended use is to view the current user count / last post time across a number of online services via their SQL databases.

This makes it easy to build a page with a summary of activity of you apps and services.

It can also display some basic disk/memory usage info for keeping an eye on remote servers that don’t require a full monitoring setup.

Installation

• Tested with PHP 7.2+
• Will require PHP’s database module for your database of choice – see database compatibility here
• Simply unzip, and point your vhost to the www directory.
• SSL is highly encouraged
• DO NOT EXPOSE THE ROOT APPLICATION DIRECTORY TO THE WORLD AS THE config.json FILES WILL CONTAIN YOUR DATABASE CREDENTIALS

Configuration

• Copy config.example.json to config.json
• Key Configuration
  • Edit the ‘key’ option to something unique and secure – you’ll need this to access the page after you’re done configuring
• Database Configuration
  • Required configuration & example values
    • ‘database_type’ => ‘mysql’
    • ‘database_name’ => ‘name’
    • ‘server’ => ‘localhost’
    • ‘username’ => ‘your_username’ The security of the app is not guaranteed, so I recommend a user with read only permissions only for the required columns
    • ‘password’ => ‘your_password’
  • Optional configuration & example values
    • ‘charset’ => ‘utf8mb4’
    • ‘collation’ => ‘utf8mb4_general_ci’
    • ‘port’ => 3306
    • ‘prefix’ => ‘PREFIX_’ *Table prefix
    • ‘logging’ => true Disabled by default for performance
    • ‘socket’ => ‘/tmp/mysql.sock’ MySQL socket (shouldn’t be used with server and port)
  • For each database, you can pass an array of fields to get data from and the info you want from them. Fields have the folowing configuration options, all of which are required
    • ‘description’ will be the description put next to the retrieved value in the result data
    • ‘table’ is the database table
    • ‘field’ is the field/column name
    • ‘info’ is the data to retreive – SUM, MIN, MAX, COUNT, AVG or LAST – last is not standard SQL and will be converted to DESC LIMIT 1 with the supplied field
• Repeat with as many databases and fields as you like to build your summary page
• The relays array in configuration can be populated with URLs. lastphp will attempt to pull JSON from those urls and simply repeat it in the results
  • Both the ‘description’ and ‘url’ fields are required for relays
• Disk info can be used to keep an eye on free disk space, name and path must both be populated
• System info can display uptime, memory usage so that can be monitored
• All configuration arrays must be present but don’t need to be populated
• config.json format MUST match the format given in the example
• Passing an invalid config.json file will result in the application not functioning at all

Usage

• Simply visit the page on your server with the key passed in the URL params, eg. https://lastphp.yourserver.com/?key=xxx
• For a pretty html version of the data which automatically refreshes every 5 minutes, add the pretty path to the URL, eg. https://lastphp.yourserver.com/pretty/?key==xxx
• Bookmark the page for easy access

Download (PHP 7.2+)

• 1.0.0

Provided ‘as-is’ with no warranty expressed or implied. I assume no liability or responsibility for any damage this software causes. Use at your own risk. Find this useful? Drop me an email and let me know 🙂