Audit Log

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

Re: Audit Log

Post by pböttcher » 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.

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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 1448 times
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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 1447 times
Web Auditor:
ec_51.png
ec_51.png (12.87 KiB) Viewed 1447 times
Actual database:
ec_53.png
ec_53.png (18.47 KiB) Viewed 1447 times
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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 47 times
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

fbrano
Veteran Member
Posts: 45
Joined: 2018-03-19 10:39

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.

fbrano
Veteran Member
Posts: 45
Joined: 2018-03-19 10:39

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>

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 524
Joined: 2019-05-21 22:42
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 34 times
Olaf
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

Post Reply