Audit Log

[sacgtdev]

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

Regards,
Stephen

[onoehring]

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]

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*/

[onoehring]

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]

Thanks.

[SkayyHH]

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]

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]

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]

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.

[onoehring]

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]

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]

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

[onoehring]

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]

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

[onoehring]

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]

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

[onoehring]

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

[onoehring]

Hi,

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

[onoehring]

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

[landinialejandro]

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

[onoehring]

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

[onoehring]

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

[onoehring]

Hi,

updated docs once again (could not remove my old post).

Download v1.73
https://dl.olaf-noehring.de/?t=5a2018f1 ... 6da6233d85

Olaf

[onoehring]

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

[onoehring]

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 (10.3 KiB) Viewed 8826 times

Olaf