Audit Log

primitive_man · Post by **primitive_man** » 2014-11-07 13:26

--
-- SQL Table structure for `auditor` table
--

CREATE TABLE IF NOT EXISTS `auditor` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`ipaddr` varchar(25) DEFAULT NULL,
`time_stmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`change_type` varchar(10) DEFAULT NULL,
`table_name` varchar(40) DEFAULT NULL,
`fieldName` varchar(40) DEFAULT NULL,
`OldValue` text,
`NewValue` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

Functions to Add to: incCommon.php

#########################################################
###### AUDIT LOG FUNCTIONS #######
#########################################################

####### GET TABLE DATA BEFORE ANY CHANGES ########

function table_before_change($TableName, $currentID, $tableID) {
$_SESSION['before_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
// STORE THEM IN A SESSION VARIABLE FOR LATER COMPARISON
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['before_change_sql'] = $row;

return;
}

### GET CHANGES AND ADD TO AUDIT LOG ###

function table_after_change($db_name, $TableName, $username, $userIP, $currentID, $tableID, $type){

// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array(); // Create Empty Array
$colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'");

$i = 0;
while ($row = mysql_fetch_array($colquery)) {
foreach($row as $key => $val) {
$colnames[$i] = $val; }
$i++; }

// GET ARRAY OF VALUES AFTER CHANGE
$_SESSION ['after_change_sql'] ="";
$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID");
$row = mysql_fetch_row($fet);
$row = str_replace("\n", "<br/>", $row);
$row = str_replace("'", "\'", $row);
$_SESSION ['after_change_sql'] = $row;

// ARRAY OF ORIGINAL VALUES // ARRAY OF UPDATED VALUES
$array1 = $_SESSION ['before_change_sql']; $array2 = $_SESSION ['after_change_sql'];

####### COMPARE BEFORE AND AFTER VALUES AND ADD CHANGED DATA ENTRIES TO AUDIT LOG #######
$del="-DELETED-";
$i = 0;
for ($i = 0; $i <= count($array1); ++$i) {
if ($array1[$i] != $array2[$i])
{// Adds the INITIAL Values and CHANGED values to the auditlog database table

switch (true) {
// START Examples of how to exclude specific fields from being added to the audit Log
case ($colnames[$i] == "editDate"):
break;

case ($colnames[$i] == "editedBy"):
break;

case ($colnames[$i] == "id_cnote"):
break;

case ($colnames[$i] == "fk_res"):
break;
// END of exclusion examples

case ($type == "DELETION"):
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$del')", $eo);
break;
default:
sql("INSERT INTO auditor (username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$array1[$i]','$array2[$i]')", $eo);
break;
}

}
}

unset($_SESSION['before_change_sql']);
unset($_SESSION['after_change_sql']);

return;
}

#########################################################
###### END OF AUDIT LOG FUNCTIONS #######
#########################################################

Lines to Add to config.php -:
--------------------------
// IF SESSION VARIABLE NOT SET OR BLANK, SET SESSION VARIABLE 'dbase' with Database Name
if (!(isset($_SESSION ['dbase']) && $_SESSION ['dbase'] != '')) {$_SESSION ['dbase'] = $dbDatabase;};

LINES TO ADD TO ** EACH ** TABLES HOOK THAT YOU WISH TO AUDIT
------------------------------------------------------------
Note: DO NOT add 'table_before_change' function to the tablename_before_insert hook as the $selectedID has not been created yet!

tablename_init Hook:

//////////////GET AND SET TABLE NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tablenam'] = $options->TableName;
//////////////GET AND SET TABLE ID FIELD NAME TO SESSSION VARIABLE//////////////////
$_SESSION ['tableID'] = $options->PrimaryKey;
$tableID = $_SESSION ['tableID'];

tablename_after_insert hook:

// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "INSERTION");

tablename_before_update hook:

// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $data['selectedID'], $_SESSION['tableID']);

tablename_after_update hook:

// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "UPDATE");

tablename_before_delete hook:

// INITIAL TABLE VALUES
table_before_change($_SESSION['tablenam'], $selectedID, $_SESSION['tableID']);

tablename_after_delete hook:

// CHANGED (UPDATED TABLE VALUES) ADDED TO AUDIT LOG
table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $selectedID, $_SESSION['tableID'], "DELETION");

primitive_man · Post by **primitive_man** » 2014-11-07 15:32

Slight change to the Audit Log in order to get it to work with MySQL & MySQLi Databases:

mysql_fetch_row should be changed to : db_fetch_row
and
mysql_fetch_array should be changed to : db_fetch_array

primitive_man · Post by **primitive_man** » 2015-01-20 18:50

No Comments? No one tried it?

shkdxb · Post by **shkdxb** » 2015-08-26 03:50

thanks for sharing the method primitive_man.

is it possible to include as hook instead of IncCommon.php functions? i am afraid when i do any change in Appgini and upload new files this file will be overwritten and i hava to go through the process again. if it is hooks folder, it is safe to make changes through Appgini. is it possible to include audit log as hooh function?
thanks for any help.

primitive_man · Post by **primitive_man** » 2016-01-03 16:34

You may download the files and detailed installation information below: (tiny download, approx. 7kb)

shkdxb · Post by **shkdxb** » 2016-02-21 13:11

Thanks Primitive

could able to install and get the audit log working.i Used 'res_ID' field to show my records ID.

when i search for any record by inputting record ID, inside "Type here to filter data" search box, it is searching only on the visible page and not searching entire Table "auditor".

is it possible to extend the search to entire table?.

primitive_man · Post by **primitive_man** » 2016-03-06 20:21

This is possible but a few alterations have to be made:

1. In auditLog.php ->

A) Add the following code just after 'include("$currDir/incHeader.php");' ->

Code: Select all

	if(!isset($_SESSION ['auditLogRecords'])){
		$_SESSION ['auditLogRecords'] = "10";
		$auditLogRecords = "10";
		} 
	
    if($_SESSION ['auditLogRecords'] == "All"){
		$_SESSION ['auditLogRecords'] = "10000";
		} 	
		
    $auditLogRecords = $_SESSION ['auditLogRecords'];

B) Find and replace all instances of:

Code: Select all

$adminConfig['recordsPerPage']

with

Code: Select all

$auditLogRecords

C) Just below:

Code: Select all

<div class="page-header"><h1>Audit Log</h1>

add the following:

Code: Select all

<table id="t01" class="table table-striped">
<tr><b>
<th>Records Per Page?</th>
</b>
</tr>
<td class="tdCaptionCell">
<form role="form" action='' method='post'>
<select name="changerecNumbs" id="changerecNumbs">
  <option class="optionText changerecNumbs" id="changerecNumbs" value="">Select</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="10">10</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="20">20</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="30">30</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="40">40</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="50">50</option>
  <option class="optionText changerecNumbs" id="changerecNumbs" value="All">All</option>
</select>
</form>
</td>
</table>

D) Adding the following CSS information to the bottom of the css styles:

Code: Select all

table#t01 th{
    background-color: #033C73;
    color: #FFFFFF;
  text-align:center;
}
table#t01 td {
text-align: center; 
}
h1 {
color: #033C73;
}

.optionText {
	
color: black;	
}

2. In adminfolder/incHeader.php add the following script to the header:

Code: Select all

<script type="text/javascript">
$j(function() {
 $j("#changerecNumbs").change(function() {
     $j.ajax({
         type: "POST", 
         url: "updatealrs.php",
         data: "recnum="+$j("#changerecNumbs").val(),
		 success: function() {
    window.location.reload(true);
}
     });
 });
});
</script>

3. Create a file called updatealrs.php in the admin folder, it's contents should read ->

Code: Select all

<?php
$currDir=dirname(__FILE__);
require("$currDir/incCommon.php");
include("$currDir/incHeader.php");


if (isset($_POST['recnum'])){
$thenum = $_POST['recnum'];
$_SESSION ['auditLogRecords'] = $thenum;}
?>

LIMITATIONS
Because this method is reliant upon sessions storage, it isn't persistent - i.e., When you log off any changes you've made to the number of records that you can view will default back to 10.
The maximum number of filterable rows is 10,000 (theoretically)

Instead of using session storage for the number of rows to show, it'd be easy to adapt this method to save your preference in the database - I already use this method for changing the theme dynamically, changing record view preferences, archiving the audit log to a txt file on the server and a whole host of other things

shkdxb · Post by **shkdxb** » 2016-03-18 05:53

Thanks primitive

Instead i created a separate Table in Appgini Application Builder and used same Table name and Field names as yours. now this is part of my main application accessible through Child records as well as with separate Table/ details view with full search function of Appgini

but your code is very excellent which helped me achieve this.

primitive_man · Post by **primitive_man** » 2016-04-15 10:03

I did think of this... but my requirements were somewhat different at the time... though I should have offered this as a better way to add the paging/search options on the audit log page.

I'm currently working on a method of restoring previous values from within the Audit Log Table - and building this as a free plugin, though finding info on plugin building is difficult.

primitive_man · Post by **primitive_man** » 2016-06-07 12:42

CHANGE TO AUDIT LOG FUNCTION:

A section of the 'table_after_change' Function as been updated:
FROM:

Code: Select all

// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array(); // Create Empty Array
$colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'");
$i = 0;
while ($row = db_fetch_array($colquery)) {
foreach($row as $key => $val) {
$colnames[$i] = $val; }
$i++; }

TO:

Code: Select all

// GET ALL COLUMN NAMES IN THE TABLE
$query = sql("SELECT * FROM $TableName LIMIT 1");
$i = 0;
$colnames = array(); // Create Empty Array  
    if ($result = mysqli_query($query)) {
        $finfo = $result->fetch_fields();
        foreach ($finfo as $val) {
		$colnames[$i] = $val->name;
		$i++;			
        }
        $result->free();
}

REASON: Some hosting companies restrict access to the INFORMATION_SCHEMA section of their MySQL Databases - thus, the previous version will often throw an error (Non-Fatal) and retrieve no results.

primitive_man · Post by **primitive_man** » 2016-07-28 16:29

PLEASE IGNORE THE ABOVE -- CORRECT VERSION BELOW!

primitive_man wrote:CHANGE TO AUDIT LOG FUNCTION:

A section of the 'table_after_change' Function as been updated:
FROM:
Code: Select all
// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array(); // Create Empty Array
$colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'");
$i = 0;
while ($row = db_fetch_array($colquery)) {
foreach($row as $key => $val) {
$colnames[$i] = $val; }
$i++; }
TO:
Code: Select all
// GET ALL COLUMN NAMES IN THE TABLE
$colnames = array();$i = 0;
           $query = sql("SHOW COLUMNS FROM $TableName");
            while($result = mysqli_fetch_assoc($query)):
                    $colnames[$i] = $result['Field'];
					$i++;
             endwhile;
REASON: Some hosting companies restrict access to the INFORMATION_SCHEMA section of their MySQL Databases - thus, the previous version will often throw an error (Non-Fatal) and retrieve no results.

vaalonv · Post by **vaalonv** » 2018-01-18 16:55

Hi primitive_man,

First of all i must thank you for the great example you shared with us i have found it very useful. I have a small concern when deleting the data from Appgini application level some error codes come in the screen, please see below.

# Time Memory Function Location
1 0.0012 353456 {main}( ) ...\informatat_personale_view.php:0
2 0.0454 3053200 DataList->Render( ) ...\informatat_personale_view.php:295
3 0.0456 3061776 call_user_func:{C:\wamp64\www\app_name\datalist.php:319} ( ) ...\datalist.php:319
4 0.0456 3064440 informatat_personale_delete( ) ...\datalist.php:319
5 0.0463 3064920 informatat_personale_before_delete( ) ...\informatat_personale_dml.php:219
6 0.0463 3064968 table_before_change( ) ...\informatat_personale.php:96
7 0.0463 3065296 sql( ) ...\auditLog_functions.php:10

( ! ) Warning: Missing argument 2 for sql(), called in C:\wamp64\www\app_name\auditLog_functions.php on line 27 and defined in C:\wamp64\www\app_name\admin\incFunctions.php on line 221
Call Stack
# Time Memory Function Location
1 0.0012 353456 {main}( ) ...\informatat_personale_view.php:0
2 0.0454 3053200 DataList->Render( ) ...\informatat_personale_view.php:295
3 0.0456 3061776 call_user_func:{C:\wamp64\www\app_name\datalist.php:319} ( ) ...\datalist.php:319
4 0.0456 3064440 informatat_personale_delete( ) ...\datalist.php:319
5 0.1405 3076680 informatat_personale_after_delete( ) ...\informatat_personale_dml.php:684
6 0.1405 3076848 table_after_change( ) ...\informatat_personale.php:101
7 0.1405 3077168 sql( ) ...\auditLog_functions.php:27

( ! ) Warning: Missing argument 2 for sql(), called in C:\wamp64\www\app_name\auditLog_functions.php on line 37 and defined in C:\wamp64\www\app_name\admin\incFunctions.php on line 221
Call Stack
# Time Memory Function Location
1 0.0012 353456 {main}( ) ...\informatat_personale_view.php:0
2 0.0454 3053200 DataList->Render( ) ...\informatat_personale_view.php:295
3 0.0456 3061776 call_user_func:{C:\wamp64\www\app_name\datalist.php:319} ( ) ...\datalist.php:319
4 0.0456 3064440 informatat_personale_delete( ) ...\datalist.php:319
5 0.1405 3076680 informatat_personale_after_delete( ) ...\informatat_personale_dml.php:684
6 0.1405 3076848 table_after_change( ) ...\informatat_personale.php:101
7 0.2079 3085472 sql( ) ...\auditLog_functions.php:37

Any help will be highly appreciated

vaalonv · Post by **vaalonv** » 2018-01-20 22:06

Hi everyone,

I just wanted to share the solution with other Appgini users through a small change on primitiv_man code.

in auditLog_functions.php we need to add $eo at the end of each SQL query like in example 1 bellow. The same needs to be done in the SQL queries for the corresponding table in Hooks follder that we want to audit like in the example 2

Example 1 - $colquery =sql("SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='$db_name' AND `TABLE_NAME`='$TableName'",$eo)

Example 2 - table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $selectedID, $_SESSION['tableID'], "DELETION", $eo);

Through those changes the Audit over user activity is done without errors

bescott53 · Post by **bescott53** » 2018-08-20 21:56

Not sure if anyone is still looking at this, but, I got this to work brilliantly except for the fieldname not be copied into the auditor table, anyone managed to get this working?

Jevgeni · Post by **Jevgeni** » 2018-10-21 07:22

bescott53 wrote: ↑
2018-08-20 21:56
Not sure if anyone is still looking at this, but, I got this to work brilliantly except for the fieldname not be copied into the auditor table, anyone managed to get this working?

Have same problem. Field is empty

pböttcher · Post by **pböttcher** » 2018-10-26 13:42

Hi,

where did you put the code to retrieve the table columns?

// GET ALL COLUMN NAMES IN THE TABLE
$query = sql("SELECT * FROM $TableName LIMIT 1");
$i = 0;
$colnames = array(); // Create Empty Array
if ($result = mysqli_query($query)) {
$finfo = $result->fetch_fields();
foreach ($finfo as $val) {
$colnames[$i] = $val->name;
$i++;
}
$result->free();
}

bescott53 · Post by **bescott53** » 2019-02-02 23:38

I realised I missed adding the line of code to the config.php file! All sorted now

lawrencekeru · Post by **lawrencekeru** » 2019-04-24 15:46

Works like magic u guy... EXCELLENT JOB

onoehring · Post by **onoehring** » 2019-05-24 12:26

Hi pböttcher,

I tried to install your add on but I received php errors when trying to save an edited record.
Could you maybe post the code version that is the latest one? I tried to incorporate your changes, from your comment on 2018-10-26 15:42 I am not sure if any changes are needed.
Yes, I did use Notepad++ to apply changes to the two tables I was testing on.

Thanks already
Olaf

onoehring · Post by **onoehring** » 2019-06-11 06:47

Hi,

I successfully integrated your script in my application.
I also did some changes in the docs. This way, Auditor should still work after the application is regenerated by AppGini.

Adjustments:
- Trick: Added in docs. Remove access to Auditor from Admin menu, but use regular AppGini table instead, so Auditor stays even when application is regenerated.
- Trick: Remove changes from 'application_root/lib.php', instead place code in config.php which stays in place, even when the application is regenerated.
- Changes to auditLog_functions.php, added , $eo to SQL queries following <a href="memberlist.php?mode=viewprofile&u=8816 (vaalonv tip)

audit_log_v1.1.zip: (6.67 KiB) Downloaded 1359 times

Olaf

onoehring · Post by **onoehring** » 2019-06-11 06:49

Hi,

but I still see a problem: If a dropdown field is used, the foreign key is shown in Auditor. Probably more useful would be the value that has been shown to the user in the web frontend.

Is there a (preferably easy) way to accomplish that?

Olaf

onoehring · Post by **onoehring** » 2019-06-11 08:25

Hi,

I added the ID of the changed/added/deleted record to the audit in this version.

audit_log_v1.2.zip: (6.68 KiB) Downloaded 1354 times

Olaf

onoehring · Post by **onoehring** » 2019-06-11 08:41

Hi,

another change implements primivteman's comment ( viewtopic.php?f=4&t=1369&p=10167#p6121 )

audit_log_v1.3.zip: (6.86 KiB) Downloaded 1353 times

Olaf

onoehring · Post by **onoehring** » 2019-06-18 14:22

Hi,

I noticed, that fieldName was not filled, So I incorporated this suggestion and it works ( viewtopic.php?f=4&t=1369#p6121 ).

audit_log_v1.4.zip: (7.07 KiB) Downloaded 1333 times

Olaf

onoehring · Post by **onoehring** » 2019-06-29 06:34

Hi,

I am planning to improve the Auditor script and have an idea how to do it :
The auditor saves the values of the fields. As you might guess from my former post, it would be nice if not only the foreign keys are saved but the actual value as well. I have an idea how to do this, but it might be easier.
So my question is:
Is there a way to read from the generated application not only the actual field value, but determine, if this comes from a different table - and further: What that value is?
If this is possible, Auditor probably needs only little change to write the value the user sees on screen to the Auditor table as well.

I also plan to improve readability for the regular user further, and not show only the table column names, but the labels the user sees on screen. As a result, one can easily export the final list and do some analysis/restructuring using e.g. Excel pivot tables.

Olaf

AppGini Community Forums

Audit Log

Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log

Re: Audit Log