Audit Log

Got something cool to share with AppGini users? Feel free to post it here!
pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Audit Log

Post by pbottcher » 2019-06-29 09:00

Hi,

if you replace in auditLog_functions.php

Code: Select all

$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID", $eo);
by

Code: Select all

$fields=get_sql_fields($TableName);
$from=get_sql_from($TableName);
$fet=sql("SELECT $fields FROM $from and $tableID = $currentID",$eo);
you get the actual value instead of the PK.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-06-29 10:23

Hi pbötcher,

I will try that and report back. Thank you for this suggestion. If this is (=produces) what I had in mind, it's a truly marvelous solution.
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-06-29 11:45

Hi pbötcher,

your solution is indeed marvelous! Really great. .... But a problem occurs.

I replaced both (in function table_after_change and table_before_change) occurences of

Code: Select all

$fet=sql("SELECT * FROM $TableName WHERE $tableID = $currentID", $eo);
with your suggestion

Code: Select all

$fields=get_sql_fields($TableName);
$from=get_sql_from($TableName);
$fet=sql("SELECT $fields FROM $from and $tableID = $currentID",$eo);
The result in Auditor (I replicated the table to make it accessible to specific user groups) (image below)
The arrows point from the field to actual values - that are placed with other fields :-(
Notice the pink one on top - that is correct though (so is Log_Edit_Zeit). For the blue (last) entry there is no field given at all. Something still seems mixed up.
ec_50_strange.png
ec_50_strange.png (16.72 KiB) Viewed 27664 times
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-06-29 14:09

Hi,

to find a solution, I removed all entries from the Auditor table (to make sure, the mixup is not based on that old data).
I changed the contents of the first visible field to something else.
This field is the second field in AG and in the database.
Strangely the problem stays. Auditor now lists the first field (which is actually PK, autovalue) and shows my changes belonging to this field (which is wrong, it should be ContainerCode not ID_ContainerNum.

Taking a look at pbötchers SQL does not show me anything that could produce this problem.

AG:
ec_52.png
ec_52.png (11.18 KiB) Viewed 27663 times
Web Auditor:
ec_51.png
ec_51.png (12.87 KiB) Viewed 27663 times
Actual database:
ec_53.png
ec_53.png (18.47 KiB) Viewed 27663 times
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-06-30 06:59

Hi,

I think I solved the mystery.
If you take a look at the screenshots in my last posting and you compare the AG field order with the order in the actual database, you will notice, that they are not the same.
I changed the field order in the database to match the order of the fields in AG ... magically the Auditor list is correct now.

Thank you pbötcher for your help.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-06-30 07:17

Hi,

I want to offer the changes back to the community as version 1.5
audit_log_v1.5c.zip
(7.64 KiB) Downloaded 1975 times
Olaf

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Audit Log

Post by fbrano » 2019-11-06 11:04

I missed in Audit Log table an information which ID was changed as it is logged after insertion.
ver 23.15 1484

User avatar
fbrano
Veteran Member
Posts: 70
Joined: 2018-03-19 10:39
Location: Slovakia
Contact:

Re: Audit Log

Post by fbrano » 2019-11-06 14:02

I found out how to show modified record's ID instead auditor's table record ID.

In /admin/auditLog.php modify
line 105
from
$res=sql("SELECT `username`,`ipaddr`, `time_stmp`, `change_type`, `table_name`, `fieldName`, `OldValue`, `NewValue`, `id` FROM `Auditor` order by `time_stmp` desc limit $start, ".$adminConfig['recordsPerPage'], $eo);
to
$res=sql("SELECT `username`,`ipaddr`, `time_stmp`, `change_type`, `table_name`, `fieldName`, `OldValue`, `NewValue`, `res_id` FROM `Auditor` order by `time_stmp` desc limit $start, ".$adminConfig['recordsPerPage'], $eo);

lines 111-119
from
<td class="tdCaptionCell"><?php echo $row[8]; ?></td>
<td class="tdCaptionCell"><?php echo $row[0]; ?></td>
<td class="tdCaptionCell"><?php echo $row[1]; ?></td>
<td class="tdCaptionCell"><?php echo date('d/m/Y H:i:s',strtotime($row[2])); ?></td>
<td class="tdCaptionCell"><?php echo $row[3]; ?></td>
<td class="tdCaptionCell"><?php echo $row[4]; ?></td>
<td class="tdCaptionCell"><?php echo $row[5]; ?></td>
<td class="tdCaptionCell"><?php echo $row[6]; ?></td>
<td class="tdCaptionCell"><?php echo $row[7]; ?></td>

to
<td class="tdCaptionCell"><?php echo $row[0]; ?></td>
<td class="tdCaptionCell"><?php echo $row[1]; ?></td>
<td class="tdCaptionCell"><?php echo date('d/m/Y H:i:s',strtotime($row[2])); ?></td>
<td class="tdCaptionCell"><?php echo $row[3]; ?></td>
<td class="tdCaptionCell"><?php echo $row[4]; ?></td>
<td class="tdCaptionCell"><?php echo $row[8]; ?></td>
<td class="tdCaptionCell"><?php echo $row[5]; ?></td>
<td class="tdCaptionCell"><?php echo $row[6]; ?></td>
<td class="tdCaptionCell"><?php echo $row[7]; ?></td>
ver 23.15 1484

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2019-12-14 10:30

Hi,

I present version 1.6 of the auditor.
  • improved INSERTION: Now, all non-empty fields are written to the auditor table after insert. Until now, only the new primary key was written.
  • you can easily use a different table name for teh auditor. Simply adjust $audittablename = "auditor";
    in function table_after_change in auditLog_functions.php (and the setup sql or course).
  • changed auditor table name from Auditor to auditor (script and setup). Note: On Linux systems the tablenames are case sensitive!
Download
audit_log_v1.6.zip
(7.85 KiB) Downloaded 1854 times
Olaf

sacgtdev
Veteran Member
Posts: 75
Joined: 2020-06-10 11:14

Re: Audit Log

Post by sacgtdev » 2020-11-08 12:16

I have tried the audit log v 1.6 with the latest appgini release 5.91. It doesn't seem to work.
Any simple way if I just want to log each entry of record in another table that is exactly have the same fieldnames and additional one field for indicating '0' for old record and '1' for new record that replaced the old one. :idea:

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2020-11-13 06:55

Hi,

I have updated an app created with AG and the audit log v1.6 - it works without any problems for me. You may post what your problem seems to be.

The way you are asking for, if you simply want to copy the current record to another table use the hook after_update:
a) search in your "log" table for the current ID
b) update the old record (with current ID) in your log table to "0" for old value
c) Write the values from $data to the other table adding a 1 for new value.

Olaf

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-15 18:43

Hi,

I tested it with AP 5.92. The app does not start anymore. page is completely empty. Or maybe I did something wrong?

1.) auditor table in AppGini
how to define?:
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.) End of config.php:

Code: Select all

include ("$ currDir / auditLog_functions.php");
if (! (isset ($ _ SESSION ['dbase']) && $ _SESSION ['dbase']! = '')) {$ _SESSION ['dbase'] = $ dbDatabase;};
3.) Changes in hooks files ...

4.) auditlog_files copied

no idea what's wrong ,-)

Thanks much, Kai

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-15 19:53

So - I'm a little further now.

The audit works.

Only the login page is affected and shows a blank page.

it also seems that it is no longer possible to log out.

I can click sign out and get to a blank page. But with browser back I'm still logged in. Awesome ;-)

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-15 20:14

there is something about this function that prevents the log off, login, index page:

Code: Select all

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

function table_after_change($db_name, $TableName, $username, $userIP, $currentID, $tableID, $type)
{
	//name your auditor table as you like
	$audittablename = "auditor";

	//if INSERT, then get all initial fieldvalues set by the user and use them as 'old'
	if ($type == 'INSERTION') {
		table_before_change($TableName, $currentID, $tableID);
	}

	// GET ALL COLUMN NAMES IN THE TABLE
	$colnames = array();
	$i = 0;
	$query = sql("SHOW COLUMNS FROM $TableName", $eo);
	while ($result = mysqli_fetch_assoc($query)) :
		$colnames[$i] = $result['Field'];
		$i++;
	endwhile;

	// GET ARRAY OF VALUES AFTER CHANGE
	$_SESSION['after_change_sql'] = "";

	// as suggested by pbötcher https://forums.appgini.com/phpbb/viewtopic.php?f=4&t=1369&p=10395#p10392
	$fields = get_sql_fields($TableName);
	$from = get_sql_from($TableName);
	$fet = sql("SELECT $fields FROM $from and $tableID = $currentID", $eo);

	$row = db_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 #######
	$i = 0;

	for ($i = 0; $i < count($array1); ++$i) {

		$oldValue = $array1[$i];
		$newValue = $array2[$i];
		if ($type == 'INSERTION') {
			$oldValue = '-INSERTED-';
		}
		if ($type == 'DELETION') {
			$newValue = '-DELETED-';
		}

		$makeEntry = 1;
		if (($oldValue == $newValue)  || (($oldValue == '-INSERTED-') && ($newValue == '')) || (($newValue == '-DELETED-') && ($oldValue == ''))) 
		{ // Do not add entries where a field is empty on INSERT or DELETE, or if field has been empty before and still is empty after update
			$makeEntry = 0;
		}

		if ($makeEntry == 1)
		{ // 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] == "Note"):
					break;

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

				default:
					sql("INSERT INTO $audittablename (res_id, username, ipaddr, time_stmp, change_type, table_name, fieldName, OldValue, NewValue) VALUES ('$currentID', '$username','$userIP',NOW(),'$type','$TableName','$colnames[$i]','$oldValue','$newValue')", $eo);
					set_record_owner($audittablename, $currentID, $username); // Set/update the owner of given record
					break;
			}
		}
	}
	unset($_SESSION['before_change_sql']);
	unset($_SESSION['after_change_sql']);
	return;
}
?>

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-15 20:48

I think if I create an audit table in appgini the auditLog.php is not needed, right?

sacgtdev
Veteran Member
Posts: 75
Joined: 2020-06-10 11:14

Re: Audit Log

Post by sacgtdev » 2020-11-16 03:03

Hi,

I have updated an app created with AG and the audit log v1.6 - it works without any problems for me. You may post what your problem seems to be.

The way you are asking for, if you simply want to copy the current record to another table use the hook after_update:
a) search in your "log" table for the current ID
b) update the old record (with current ID) in your log table to "0" for old value
c) Write the values from $data to the other table adding a 1 for new value.

Olaf
I have re-tried and found out the fatal error - undefined function is causing the problem. I have resolved it by inserting the following code at the table files in hook folder:
include("$currDir/auditLog_functions.php");

I wonder why I need to do this despite I have inserted the code in config.php. Besides that, I have tried out the csv import method. Apparently, the record imported will not be logged.

On the other hand, I tempted to try out a different approach to log the changes of table in another duplicated table (history) just like what you have suggested for the steps in a, b and c. However, I think it will be inadequate to address the audit log for record imported via csv. Any idea?

sacgtdev
Veteran Member
Posts: 75
Joined: 2020-06-10 11:14

Re: Audit Log

Post by sacgtdev » 2020-11-16 03:04

SkayyHH wrote:
2020-11-15 20:48
I think if I create an audit table in appgini the auditLog.php is not needed, right?
I think you do not need it as user with permission can access to the audit table already.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2020-11-16 07:22

Hi,

I did a small change in an application after generating it with AG 5.92. ...
In config.php
I added these lines:
Directly after the <?php opening

Code: Select all

session_start();
and
in the very end, replacing the other Auditor-code

Code: Select all

//Auditor
	$_SESSION['dbase'] = $dbDatabase;
	if (!function_exists('table_before_change')) {	
		$currDir = dirname(__FILE__);		
		@require("$currDir/hooks/auditLog_functions.php");
	}
Why?
- I needed access to the database name to write to the auditor table "by hand" from another table.
- It seemed, that auditLog_functions.php was not correctly read in every file where I needed it.

Olaf

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-16 09:22

Now it's fine with me too.-i hope so...

I've done the code in __global.php. No longer in config.php.

Code: Select all

	include("$currDir/auditLog_functions.php");
	if (!(isset($_SESSION ['dbase']) && $_SESSION ['dbase'] != '')) {$_SESSION ['dbase'] = $dbDatabase;};
The tables within appgini and only have the file auditLog_functions.php in the app root directory.

An audit about uploads, downloads, password changes etc would be cool ... but it's fine for now.

Thanks much for your help

Kai

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2020-11-17 08:21

Hi Kai,
  • an audit for uploads is "included" - as uploads are simple fields that hold the filename, they are changed when an upload occurs
  • an audit for downloads is at this time tricky to implement, as downloads are all linked directly. BUT: take a look at this thread ( viewtopic.php?f=11&t=2856 ). Here you will find a method to secure your downloads directory. This is done by using htaccess to redirect all requests from this directory to a php file. You can utilize this php file to write to the auditlog all download requests :-)
  • password changes. Right now I do not know where to start. Look where the password can be changed, intercept the change by reading the "old" (hashed) pw from the database when displaying the user record. After change, draw the current pw (hash) from the db an compare both. If they are unqeual, PW has been changed and you can log that.
Olaf

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-17 14:43

Thank you Olaf,

securing the upload directory is unfortunately not that easy for me. I have to go on again. I must have something wrong with the .htaccess and protect.php solution...

A password reminder is needed. Otherwise you will not meet the requirements for GDPR / DSGVO at all ... Unfortunately a problem - and I am not a programmer ...

Greetings, Kai

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2020-11-17 14:57

Hi,

well, securing the upload directory is documented in the link and should work - of course - depending on server settings, some adjustments might be needed.
A change password reminder can be built. I would suggest counting the logins of a person and once the treshold is reached, send the reminder with the chance to change the pw.

Olaf

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-17 15:52

hm i don't know which servers it will run on after delivery. I will use the app On-premises.

This was an problem on my installation:
"Options FollowSymLinks" in .htaccess.

Thanks much, Kai

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Audit Log

Post by onoehring » 2020-11-17 18:16

Hi,

just try without that line of comment it out:

Code: Select all

#Options FollowSymLinks
Olaf

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Audit Log

Post by SkayyHH » 2020-11-17 21:24

hi, yes i did that. then it works. I may not have described it correctly :-)

Greetings, Kai

Post Reply