Detailed tracking of user behaviour

[D Oliveira]

Hello friends AppGineers,

I'm trying to accomplish a more resourceful way of tracking user modifications on records.

The goal is to track which field the user has changed, not just the table, field name and timestamp
but also the contents of the fields that were changed, in another words trying to generate a log of something like this :

Code: Select all

Bob has changed table Office, 
Field_1 was previously "YES" and now is changed to "NO", 
Field_2 was previously "testing" and now is changed to "working",
Field_3 was previously "none" and now is changed to "filled" 

Is the best way to accomplish this through javascript or PHP?

Im thinking a .change() function triggered by every field to stack a log of information but that means every single field would have to trigger the change function and that sounds not very efficient.

How would you guys go about solving that? stacking text in a variable through .change() functions or through PHP tweaking the table functions before_insert and after_insert?

[pbottcher]

Hi,
did you check the audit plugin provided here:

viewtopic.php?f=4&t=1369&hilit=audit

This gives you actually pritty much what you asked for.

[D Oliveira]

Thanks for sharing this post.

That is a well thought solution but also very complex and it functions differently than what I have previously idealized.

We don't need a new table to store that information, the goal is to keep a log attached to each record in a hidden field that can be displayed if the user fills a check-box. I came up with:

tablename-dv.js

Code: Select all

/* create a text field named 'log' in your table */

/* tablename-dv.js */

$j('#field1').on('change', function(){

var field_name = 'Field 1';

var table_name = 'Tablename';

var content = $j('#test').val();

var utc = new Date().toJSON().slice(0,19).replace(/-/g,'/');
var utc2 = utc.replace(/T/g,' ');
var now = utc2;


var url1="getMemberInfo.php"; 
jQuery.ajax({
url: url1,
dataType: 'json',
success:function(response){
	if( response ){
			var user = response;	
			
			var old_log = $j('#log').val();

			var current_edit = '\n\n'+user+' has changed table: '+table_name+' and field: '+field_name+'to: '+content+' at '+now+'\n\n';


			var new_log = current_edit+old_log;

			$j('#log').val(new_log);

			console.log(new_log);


	}
}

});



});

getMemberInfo.php

Code: Select all

<?php
$curr_dir = dirname(__FILE__);
include("{$curr_dir}/defaultLang.php");
include("{$curr_dir}/language.php");
include("{$curr_dir}/lib.php");

$mi = getMemberInfo();
echo json_encode($mi['username']);
?>

That way the sample log field would look like this:

Code: Select all

tiger_admin has changed table: Tablename and field: Field 1to: rrr at 2022/02/11 22:06:27



tiger_admin has changed table: Tablename and field: Field 1to:  at 2022/02/11 22:06:22



tiger_admin has changed table: Tablename and field: Field 1to: test at 2022/02/11 22:06:21



tiger_admin has changed table: Tablename and field: Field 1to: teste at 2022/02/11 22:06:16



tiger_admin has changed table: Tablename and field: Field 1to: hi at 2022/02/11 22:06:14

now... you may ask why not use default appgini datestamp last edit?

The answer is simple: with this solution you also know which fields have been changed by the user and not just the table, if anyone has a better insight I would love to hear because this code will have to be replicated for each field in the table and that is very laborious.

[pbottcher]

Hi,

well if the log plugin is too complex, just a little thought about your solution. As it works on the Client side (js), it might not reflect the real record modification, but rather the user interaction on your page.
Think about having a field. I input "text1" into that field, you create a log entry. I change the entry to "text2" you add another entry. I quit the page without saving the record. You log will state that the record was changed to an incorrect value at this time.

Second thought about only having an additional field for storing the log entries. What if you have an large = blob field. Your additional field would fill up rather quickly with the log information and there might not be enough space to capture all the changes. Especially if there a frequent changes.

Just my thought. I would still go for an additional table to log the changes and use the server side to handle the effective changes happening on the record as does the audit plugin.

[jsetzer]

The goal is to track which field the user has changed, not just the table, field name and timestamp

In the past I did this by adding a changes table holding...

• id
• tableName
• pkValue
• createdOn
• createdBy
• data (text field)

On after_insert, after_update and before_delete I save the record's data as JSON into the data field next to meta data. So this is the first step. Using createdOn or id column I am able to exactly sort changes per table/pk. I created a custom page for displaying all changes in descending order.

CBBc5NDT91.png (117.21 KiB) Viewed 3110 times

Additionally I modified the DV of that log-entries table to show differences:

Wlk5S1xGca.png (125.17 KiB) Viewed 3110 times

I've implemented some kind of diff-algorithm which automatically displays new entries, modified entries and deleted entries

chrome_qK6TzMNATv.png (4.86 KiB) Viewed 3110 times

I hate repeating code, so I've wrapped everything in a PHP class per Business Object (AKA relevant table) inheriting from a baseclass. On every table-hook I only have to write this:

Code: Select all

function TABLENAME_after_insert($data, $memberInfo, &$args){
    $object = new \NAMESPACENAME\TABLENAME();
    return $object->afterInsert($data, $memberInfo, $args);
}
function TABLENAME_after_update($data, $memberInfo, &$args){
    $object = new \NAMESPACENAME\TABLENAME();
    return $object->afterUpdate($data, $memberInfo, $args);   
}
function TABLENAME_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){
    $object = new \NAMESPACENAME\TABLENAME();
    return $object->softDelete('id', $selectedID, 'status_id', 3, $memberInfo, $args);
}

Known issues

I'm not satisfied with storing the foreign keys of lookups, only. It would be better if the library could automatically resolve the foreign keys and additionally save data from the looked up record. See 3rd screenshot: Instead of showing status_id diff "4" to "2" I'd like to see for example diff "Status NEW" to "Status CONFIRMED" or whatever is the meaning of 4 and 2. Don't know how to say. Hope you got my point.

I hope this gives you an idea.

[D Oliveira]

Thank you both for your insights an suggestions, your idea will work like a charm Jan!

To solve the lookup problem you need a new field called lookup_info, and whenever you save the ID like 4 or 2 mark the first character of the string as º or another very unique character to identify which field to refer for lookup_info instead of the data field. Using substring to the first character for identification and ajax to retrieve the fields content.

Your solution does exactly what I have envisioned previously, many thanks.

Cheers!

[D Oliveira]

Think about having a field. I input "text1" into that field, you create a log entry. I change the entry to "text2" you add another entry. I quit the page without saving the record. You log will state that the record was changed to an incorrect value at this time.

Second thought about only having an additional field for storing the log entries. What if you have an large = blob field. Your additional field would fill up rather quickly with the log information and there might not be enough space to capture all the changes. Especially if there a frequent changes.

To solve for that you need a log_permanent LONG TEXT field with a maximum length of 4,294,967,295, use the log field just to temporarily get the modifications that the user is making and through the before_update() function you add the temporary log to the permanent log and that way there will be no conflict of overwriting.

[jsetzer]

To solve the lookup problem

Well, everything is serverside, wrapped in PHP classes. Nowadays it is not a problem to resolve the lookup's master record, but at that time (2016) AppGini did not have a good function for reading lookup-relations.