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: 628
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: 325
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

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 325
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 312 times
Olaf

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 325
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 311 times
Web Auditor:
ec_51.png
ec_51.png (12.87 KiB) Viewed 311 times
Actual database:
ec_53.png
ec_53.png (18.47 KiB) Viewed 311 times
Olaf

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 325
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

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 325
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 23 times
Olaf

fbrano
Veteran Member
Posts: 35
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: 35
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>

Post Reply