Log events via PHP to database with Windows or Linux command line

Windows and Linux

Here’s a neat example (for Linux and Windows systems) of how to easily capture (log) and store the result of some script or program or really anything, in a central database table. Sure you could use just a log file, but spinning up an instance of MySQL (or SQLite) is trivial, and in the long run the ability querying the table makes it worth it.

Code here in GitHub: [icon name=”github” class=”” unprefixed_class=””] https://github.com/acbrandao/PHP/tree/master/phpDBLogEvent

A typical scenario this script may be; you just finished running some shell script, like for example an Rsync script and you want to capture the last result (tail /usr/log/myrsync.log ) whether it succeeded or failed, and you want it to go into your centralized database log table.. so you can be alerted of any issues.

With this code , Now you can simply invoke this script using something like curl or wget (on linux) you can extend this to any script that records status of cron jobs, file changes, rsync results, the list is endless.

In the example below I’ll just invoke this script using a simple tail -n1 passing one line from a log file, and posting it to the database, you can of course run this in a cron, or a windows scheduled task.

Step 1. Let’s begin assuming your using SQL database . Create the log database table (Sample MySql Create Statement)

CREATE TABLE `log` (
	`log_id` INT(11) NOT NULL AUTO_INCREMENT,
	`time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`ip` VARCHAR(35) NOT NULL DEFAULT '',
	`user` VARCHAR(30) NULL DEFAULT NULL,
	`description` VARCHAR(128) NOT NULL DEFAULT '',
	`category` VARCHAR(10) NULL DEFAULT NULL,
	PRIMARY KEY (`log_id`),
	INDEX `idxtime` (`time`),
	INDEX `description` (`description`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1

If you have SQLite you can use the create table DDL.

CREATE TABLE IF NOT EXISTS 
log(
id INT PRIMARY KEY, 
time DATETIME, 
ip VARCHAR(35), 
user VARCHAR(30), 
description VARCHAR(64), 
category VARCHAR(32));

Note: if using a SQLite database file db.sqlite3 , don’t forget to set the write permissions , so PHP/PDO can  actually write to the file, typical examples are:

chown -R www-data:www-data /var/databases/myapp/
chmod -R u+w /var/databases/myapp/

Step 2: Now let’s write a PHP script that will insert / Log the web request what you want to capture, I’ll use standard PHP PDO library for this, of course you can use any library or framework that does the job and allows you to connect to the database and insert the record.

<?php
//Script used to insert entires into the Activity log table 
//Connect to Databse
$host = '127.0.0.1';
$db   = 'test_db';
$user = 'db_user';
$pass = 'db_password';

echo "Connecting to the database \n";

// DSN string varies based on your database, refer here for details: http://php.net/manual/en/pdo.construct.php
// $dsn = "sqlite:c:/path/db.sqlite3 ;dbname=name_of_your_db"

$dsn = "mysql:host=$host;dbname=$db";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
    
} catch (PDOException $e) {
     throw new PDOException($e->getMessage(), (int)$e->getCode());
}

//Now grab the URL paramters do some sanitization
if (isset($_REQUEST['user']) &&  isset($_REQUEST['message']) )
{
  //Apply your own filtering here.. 
   $user=filter_var( trim($_REQUEST['user']) , FILTER_SANITIZE_STRING);
   $message=filter_var( trim($_REQUEST['message']) , FILTER_SANITIZE_STRING);
   $category=filter_var( trim($_REQUEST['category']) , FILTER_SANITIZE_STRING);

  //optional used to prevent unapproved URL requests
    $checksum=isset($_REQUEST['checksum'] ) ? trim($_REQUEST['checksum']) : null ;

$data = [
    'user' => $user,
    'ip' => $_SERVER['REMOTE_ADDR'] ,
    'message' =>  $message,
    'category' => $category 
  ];

//OPTIONAL add a checksum verification to prevent any app from calling this script and flooding your log
// Check sum could be  a simple md5 (some_field + salt )  which equals  the checksum created at source,
// if ((md5( $message))== $checksum)

try {
  $sql = "INSERT INTO `test_db`.`log` (`ip`, `user`, `description`,`category`) VALUES (:ip, :user, :message,:category) ";
  $stmt= $pdo->prepare($sql);
  $stmt->execute($data);

    echo "$message inserted  Successfully code: $result \n";
} catch (PDOException $e) {
     throw new PDOException($e->getMessage(), (int)$e->getCode());
      echo "Your request $message encountered and Error: code $result";
}
    
   }
else
  echo "Invalid request see API /user=  /message=";

?>

Note: To prevent malicious or errant scripts from flooding your log table add a checksum verification prior to inserting the rows. One method to do this is using a simple md5 (some_field + salt ) which equals the checksum created at source script, then simply run the check before inserting the row.

if ((md5( $message + $salt_value))== $checksum)
{
 //run your insert code here.
}
else
echo "Invalid Checksum, did not insert record";

Step 3 (Linux): Now let’s create a simple CURL based script to invoke the call.

#!/bin/sh

#Get the most recent 1 line from  Apache error logs
tail -n1  /var/log/apache2/error.log  > apache_errors.txt

# echo the results to a variable to include in the CURL post paramters
text_file=`cat apache_errors.txt`
echo "$text_file"

#now call post the information to the PHP script
curl -d "user=CRON&message=$text_file&category=linux" -X POST http://localhost/lab/logevent.php

Step 3b (Windows): If you have a windows system and want to invoke the PHP logevent.php page, write a Windows PowerShell which can make an invoke a web call. Using Invoke-WebRequest powerShell cmdlet feature introduced in PowerShell 3.0 you cna make web requests and pass paramters, and much more.. Also note there’s Invoke-RestMethod and JSON commands also available

#Windows Power
#$content = [IO.File]::ReadAllText($output)
$content='Compeleted Backup C:/Shared to //nas/Public/Shared '
$postParams = @{user='WindowsTask'; message=$content; category=WINDOWS}

#now call post the information to the PHP script
#Invoke-WebRequest is a powerShell feature introduced in PowerShell 3.0.
Invoke-WebRequest -Uri http://localhost/lab/logevent.php -Method POST -Body $postParams

Conclusion

There you go a simple set of stps that allow you to quickly and effectively record , log results from a variety of different systems.