Audit Log

Got something cool to share with AppGini users? Feel free to post it here!
sacgtdev
Veteran Member
Posts: 54
Joined: 2020-06-10 11:14

Re: Audit Log

Post by sacgtdev » 2020-12-04 08:43

Does anyone have any idea on audit log approach to address crud action for both form editing action and csv import?

Regards,
Stephen

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

Re: Audit Log

Post by onoehring » 2020-12-04 11:17

Hi,

you can always call the corresponding function table_after_change always "by hand". Thus:
Set a "starting point" when the CSV import begins (maybe the latest primary key value in the table where you import to),
After the import is done, loop through all imported records and write them to the audit_log table, beginning at your "starting point"

Olaf

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

Re: Audit Log

Post by sacgtdev » 2020-12-18 03:12

Hi Olaf,

Your idea is good. It means that I just need to do additional step after csv import to loop the newly inserted records and update it into the audit log.

A quick way to loop the inserted records is using the mass update plugin. There are some code which I not able to apprehend (if statement for MASS_UPDATE_TOGGLE_CHECKBOX ).

Would you able to give some comment on the following code whether the coding is correct? Thanks.
/*Code extract from mass update generated php file*/
/*Start here*/
$new_value = makeSafe($_REQUEST['newValue']);

/* prepare a safe comma-separated list of IDs to use in the query */
$cs_ids = array();
foreach($ids as $id) $cs_ids[] = "'" . makeSafe($id) . "'";
$cs_ids = implode(', ', $cs_ids);

$tn = 'Table1';
$field = 'to_reviewer';
$pk = getPKFieldName($tn);


$query = "UPDATE `{$tn}` SET `{$field}`='{$new_value}' WHERE `{$pk}` IN ({$cs_ids})";

/* Try to insert the second query */
$query2 = "INSERT QUERY into Table 2 (auditor table) ";

/* Can you explain the if statement for the following code and the purpose of reconstructing the $query statement */
if($new_value == 'MASS_UPDATE_TOGGLE_CHECKBOX')
$query = "UPDATE `{$tn}` SET
`{$field}` = IF(ISNULL(`{$field}`), '1', IF(`{$field}`, '0', '1'))
WHERE `{$pk}` IN ({$cs_ids})";

$e = array('silentErrors' => true);
sql($query, $e);
sql($query2, $e);

/*End here*/

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

Re: Audit Log

Post by onoehring » 2020-12-18 09:59

Hi,

I do not understand what you want to know from me. I do have the mass update plugin as well, but I have never used it yet, so I can not say much about it.
Why don't simply try your code .. until it works?
As I see it, there are several code problems.

Not needed (as overwritten later on):

Code: Select all

$query = "UPDATE `{$tn}` SET `{$field}`='{$new_value}' WHERE `{$pk}` IN ({$cs_ids})";
There is no loop through all records. Purpose?

But $query2 is not a valid insert query. You will need to change this. Maybe you wrote the query this way simply to visualize it that there needs something to be done.

Code: Select all

$query2 = "INSERT QUERY into Table 2 (auditor table) ";
Should be something like

Code: Select all

$query2 = "INSERT INTO auditor_table_name (field1,field2,fieldN) VALUES (value_for_field1,value_for_field2,value_for_fieldN);";
You can actually add more values and run one insert-sql

Code: Select all

$query2 = "INSERT INTO auditor_table_name (field1,field2,fieldN) VALUES
(value_for_record1_field1,value_for_record1_field2,value_for_record1_fieldN),
(value_for_record2_field1,value_for_record2_field2,value_for_record2_fieldN),
...
(value_for_recordM_field1,value_for_record3_fieldM,value_for_recordM_fieldN);";
(also see: https://www.mysqltutorial.org/mysql-ins ... ement.aspx and https://www.w3schools.com/php/php_mysql_insert.asp )
/* Can you explain the if statement for the following code and the purpose of reconstructing the $query statement */
You will need to reconstruct the query statement for each record. BUT: This seems to be an example only where a CHECKBOX has been checked. I would think this is not the correct sql statement you are looking for.

Try this?!
--------------
I do not understand why you are making it complicated (sorry, no bad feelings here) but why not try this:
a) grab the latest primary key (assumed: autonumber) from the table where you will run your CSV import. Save that value to some variable
b) run the CSV import
c) use a simple (while-)loop to add all records that have a primary key larger than the one in your variable from step a) to the auditor table.


Olaf

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

Re: Audit Log

Post by sacgtdev » 2020-12-28 13:04

Thanks.

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-03 21:47

hello all,

unfortunately i still have a problem with the audit log.

When i create a new record the username is always "admin". Even if another user is logged in! When changes are made, the correct user is noted in the audit log as user.

Attached the code in a sample file.

Can you please tell me what I have wrong.

Thank you very much

Kai

.....

Code: Select all



	function Employees_de_init(&$options, $memberInfo, &$args){$_SESSION ['tablenam'] = $options->TableName;
$_SESSION ['tableID'] = $options->PrimaryKey;$tableID = $_SESSION ['tableID'];

		return TRUE;
	}


	function Employees_de_before_insert(&$data, $memberInfo, &$args) {

		return TRUE;
	}


	function Employees_de_after_insert($data, $memberInfo, &$args){table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "INSERTION");

		return TRUE;
	}


	function Employees_de_before_update(&$data, $memberInfo, &$args){table_before_change($_SESSION['tablenam'], $data['selectedID'], $_SESSION['tableID']);

		return TRUE;
	}


	function Employees_de_after_update($data, $memberInfo, &$args){table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $data['selectedID'], $_SESSION['tableID'], "UPDATE");

		return TRUE;
	}


	function Employees_de_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){table_before_change($_SESSION['tablenam'], $selectedID, $_SESSION['tableID']);

	 return TRUE; 
	}


	function Employees_de_after_delete($selectedID, $memberInfo, &$args){table_after_change($_SESSION ['dbase'], $_SESSION['tablenam'], $memberInfo['username'], $memberInfo['IP'], $selectedID, $_SESSION['tableID'], "DELETION");



SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-03 22:21

Sorry - it is still different!

Only if the admin adds a dataset this is also written as "INSERTION" in the audit log. If another user creates a record, this is not logged in the audit log. Changes and deletions are logged by all users.

Many thanks for help,

Kai

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-04 21:52

Is it perhaps possible that in the programming of the AuditLog code allows the insert only with super admin rights? I can not get it so that a user other than the super admin can create records and these are then written to the audit log. No matter if the user has write permissions for the auditlog or not.

Thanks for help, kai

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-05 06:58

Hi,

please, could someone confirm that the AuditLog logs new entries from every user and not just from the admin.

I get no further with it.

Thanks so much.

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

Re: Audit Log

Post by onoehring » 2021-01-05 07:36

Hi,

well, I can confirm that this is NOT the case:
Is it perhaps possible that in the programming of the AuditLog code allows the insert only with super admin rights?
and
that the AuditLog logs new entries from every user and not just from the admin.
It does work in two apps I developped. I hope, that others will confirm this as well - as it is intended to work for all usergroups.

Olaf

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-05 08:45

Hello Olaf,

thank you very much. This helps me in any case that it is not the AuditLog Code.

How did you include the AuditLog?

I have

Code: Select all

	// Audit Log
	include("$currDir/auditLog_functions.php");
	if (!(isset($_SESSION ['dbase']) && $_SESSION ['dbase'] != '')) {$_SESSION ['dbase'] = $dbDatabase;};

in hooks/__global.php

Maybe __global.php is read when inserting a record only as Super Admin.

I did not get the AuditLog to run via the inclusion in config.php.

Thanks so much, Kai

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-05 23:39

So with me definitely no records are logged if another user than super admin creates the record.

And I noticed another problem. If fields remain empty when inserted, the audit log writes the fields incorrectly in the log.

The call via the config.php somehow does not work under the current appGini version. However, when I include in the _global.php.

I can't even imagine that it would work for others. What do you do differently from the documentation and the integration described therein?

Does anyone have another idea what could be wrong?

Thank you very much, Kai

Image

Image

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

Re: Audit Log

Post by onoehring » 2021-01-06 11:30

Hi Kai,

I actually added the include to the /config.php - and a session_start() there as well
/config.php:

Code: Select all

<?php
	session_start();
	
... all the standard config ...

	//Auditor
	$_SESSION['dbase'] = $dbDatabase;
	if (!function_exists('table_before_change')) {	
		$currDir = dirname(__FILE__);		
		@require("$currDir/hooks/auditLog_functions.php");
	}
To your last posting:
And I noticed another problem. If fields remain empty when inserted, the audit log writes the fields incorrectly in the log.
The audit is a little dumb at this time. You need to have the same order of fields in your AG project AND the database to log/assign entries to the correct columns. Please check this - I have messed up some data in the past myself.



Olaf

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-06 12:55

Hi Olaf,

thanks a lot for the info. The sorting of the database fields is a great tip. But with that it makes it difficult for me to use this for customers. If I later make a database change in Appgini, the AuditLog is shot ;-)

Kind regards, Kai

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

Re: Audit Log

Post by onoehring » 2021-01-06 13:50

Hi Kai,

I agree. At this time it's a nuisance to alter MySQL tables in that way you have fields in AG.
If and when I find the time, I might update the AuditLog so that this order is irrelevant. Of course, anyone else reading this can do so as well ;-)

Olaf

SkayyHH
Veteran Member
Posts: 349
Joined: 2015-04-27 21:18
Location: Germany

Re: Audit Log

Post by SkayyHH » 2021-01-06 20:39

Hi Olaf,

that would be great. Unfortunately I can't help you with that ;-) I have removed it from the app for now.

Greetings, Kai

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

Audit Log v1.7

Post by onoehring » 2021-01-19 09:44

Hi,

I present version 1.7 of the auditor.
- added possibility to easily define the name of your auditor table in the beginning of the the files auditLog_functions.php and /admin/auditLog.php
- changed code in the way that now the order of fields in the database must not match the order of fields in your AppGini application anymore. In previous versions the order must be the same, otherwise it would mess up the logging. Now this problem should be solved.
- added a new function Audit_Manually which allows checking for changes on another table and documenting those (see description below for more information).
- transformed docs to PDF for easier editing
- changed audit_tableSQL.sql to make larger fields for table and fieldnames

Download v1.7
https://dl.olaf-noehring.de/?t=9ba36cc8 ... 27fdf5ce3c

Olaf

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

Re: Audit Log

Post by onoehring » 2021-01-19 11:26

Hi,

there is an error in 1.7. I removed the file until I have fixed the error.
Olaf

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

Re: Audit Log

Post by onoehring » 2021-01-19 11:42

Hi,

the bug occured, when a record was deleted. Bug should be removed in this version 1.71 now.

Download v1.71
https://dl.olaf-noehring.de/?t=e5d64f06 ... 4433389ca9

Olaf

User avatar
landinialejandro
AppGini Super Hero
AppGini Super Hero
Posts: 117
Joined: 2016-03-06 00:59
Location: Argentina
Contact:

Re: Audit Log

Post by landinialejandro » 2021-01-20 16:09

Hello

I took the courage to make this application a plugin, so that its installation and implementation is easier.
I hope you do not bother about this and I remain available to delete it if it is not to your liking.

The code remained original, just try not to modify the AppGini core files to prevent them from being deleted every time it is rendered again.
So I put all the initialization code in a file called scripts.php, if there is anything to change it is only done there.
I also did a js so that the Audit button on the admin side is added automatically without modifying the AppGini code.
All the necessary files go inside the audit folder under the hooks folder, except for the file that goes in the admin folder.
everything is installed and configured from the plugin automatically, no code editing is necessary. including creating the new audit table.
In short, only the __global.php file is modified, where the include of the scripts.php is added, and each of the hooks of the different tables.

Here I leave the link to GitHub, if anyone wants it, you can request it and I add it as a collaborator.

I hope it is useful
Alejandro.
AppGini 5.94 - Linux OpenSuse Tumblewweed.

Some of my posts that may interest you:
:arrow: Landini Admin Template: Template for Appgini like AdminLTE
:arrow: Profile image plugin: add and changue image user profile
:arrow: Field editor in table view: Configurable fast edit fields in TV
:idea: my personal page

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

Re: Audit Log

Post by onoehring » 2021-01-21 05:55

Hi landinialejandro,

this looks very promising. I will test it - and man - if it works (which I expect) this is a great help for all to use the AuditLog.
I very much like, that you simply placed all files from the Zip into the subfolder. This should make it so easy to update.

Olaf

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

Re: Audit Log

Post by onoehring » 2021-01-21 08:00

Hi,

I updated the docs to account for landinialejandro's plugin.
Changes:
• Added link to docs for the wonderful plugin extension from landinialejandro which makes installation a walk in the park. Please see below
• Modified file structure of the zip files that holds the audit log to adjust for use in combination with the plugin.
• Changed formatting of the docs for better readability
• Restructured docs for plugin
• Added hint for auditor tablename to docs

Download v1.72
https://dl.olaf-noehring.de/?t=bb53a985 ... 92413b8a59

Olaf


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

Re: Audit Log

Post by onoehring » 2021-01-21 09:36

Hi landinialejandro,

inspecting the code of the plugin, it seems, that the plugin does the installation only - but no "uninstall" - is that correct?
As other plugins allow uninstall, I am wondering and think this is worth knowing.

Olaf

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

Re: Audit Log

Post by onoehring » 2021-01-21 12:31

Hi,

script seems to work fine.
I was able to use the script and add the auditor to all tables.
I encountered some strange thing and some bug (?not sure!).

Strange: The "false" circled on the right should probably say something like "ok"
Error: Even when I checked the checkbox the files were changed.
Am I correct or just writing crap?
l24.png
l24.png (10.3 KiB) Viewed 2602 times

Olaf

Post Reply