Audiot logging for editor.

advania

Hey guys,

I need to add some sort of audit logging for create/delete and edit functions on the data-tables editor. Any examples and help
would be much appreciated.

I'm looking to dump data into an audit table on each action.. something really simple.. doesn't have to dump username or anything.. just data trail.. date+action+data_before+data_after

Example for an edit

[field: date] = {2017-01-10 : 19:05:23}
[field: action] = {edit}
[field: data_before] = {id=1, name="foo baarson", age=110, info="too old"}
[field: data_after] = {id=1, name="bar fooson", age=110, info="too old"}

Example for a create

[field: date] = {2017-01-10 : 19:05:23}
[field: action] = {create}
[field: data_before] = {}
[field: data_after] = {id=1, name="bar fooson", age=110, info="too old"}

Example for a delete

[field: date] = {2017-01-10 : 19:05:23}
[field: action] = {delete}
[field: data_before] = {id=1, name="foo baarson", age=110, info="too old"}
[field: data_after] = {}

This way you can write a recovery script to the side.. like an 'undelete' button.. or even have one inline in the table.

allan

The server-side events would be the way to do this. There is an audit log example on the page - although obviously you'll need to customise it to suit your exact needs.

Allan

advania

That's perfect, thanks!

gcac

This is great functionality built into the examples. I have implemented this as well, but am struggling with how to capture what the values were before the update.

I'd like to capture the data before the field(s) are edited and store them as a JSON object in the log table. That way I could implement the log in such a way as to show 'user x changed value y from a to b'. What I do now is store the values changed into a JSON field in my log table using:

        formOptions: {
            main: {
                submit: 'changed'
            }
        },

I tried expanding the example to use this: (I'm using Datatables / Editor PHP libraries)

    ->on( 'preEdit', function ( $editor, $id, $values, $row ) {
        logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values );
    } )

Here the $values are the client-side ones (ie. edited). I can't seem to wrap my head around how to grab what the values were before the edit.

EDIT: I see now where the preEdit and postEdit only differ in the sense of the postEdit only runs after the data is successfully entered in the db.

Any ideas / pointers appreciated..

Thanks.
N

allan

Hi,

What I would suggesting doing in this case is getting the values as they are before the editing, directly from the database (Editor from the client-side doesn't submit the old values - just the new ones).

So you could do something like:

$existing = $db->select( 'gcac', [ ...fields... ], [ 'id' => $id ] )->fetch();

The full reference documentation for the database class is available here.

Allan

gcac

@allan : AWESOME. Exactly what I need. I don't know how I overlooked it but I had been looking for the docs on the database class. Thanks so much!

N

gcac

@allan Alright I am well on the way I think. Thanks for the pointers. This is what I am currently using:

logChange function:

function logChange ( $logTable, $db, $action, $id, $values, $existing) {
    $db->insert( $logTable, array(
        'user'   => $_SESSION['username'],
        'action' => $action,
        'newValues' => json_encode( $values ),
        'prevValues' => json_encode( $existing ),
        'row'    => $id,
        'when'   => date('Y-m-d H:i:s')
    ) );
}

Outside my Editor::inst

$prevValues = array();

Editor definitions:

    ->on( 'preEdit', function ( $editor, $id, $values ) {
        global $prevValues;
        $prevValues = $editor->db()->select( 'weirip_ips', '*', [ 'id' => $id ] )->fetch();
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row) {
        global $prevValues;
        $existing = $prevValues;
        logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values, $existing );
    } )

This works but while my log table newValues column has only the changed columns included in the JSON (Im using formOptions: submit->changed in the main), the prevValues column contains all the db fields (clearly because I am selecting all the fields with the select: method).

Working to try to detect somehow and only store in the prevValues field in the log table the fields that were changed on edit, so basically newValues would only contain changed fields, and prevValues would contain only the same fields, but with the previous values.

allan

Thanks for posting that!

To get only the changed field's values, I think you'd need to loop through the list of fields that was submitted in order to extract that information.

Allan

gcac

I'm stuck on this again. Forgive the silly code as I am just testing. In looking at the database docs, I should be able to pass an array of fields to the select statement:
(I do array_shift because the table name is appended at the start of the array in the $values such as
[weir_ips: [name => 'test]])

   ->on( 'preEdit', function ( $editor, $id, $values ) {
        $_SESSION['logtest'] = array_keys(array_shift($values));
        $prevValues = $editor->db()->select( 'weirip_ips', $_SESSION['logtest'], [ 'id' => $id ] )->fetch();

    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row) {
        global $prevValues;
        $existing = $prevValues;
        logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values, $existing );
    } )

Here the $_SESSION['logtest'] ends up being the key names (fields) changed in the editor operation. So I try to pass that to my query, to get the values of those fields to enter into the log as the previous values. Except when I check the log table it seems to be an empty array for the prevValues.

[]

I can echo `$_SESSION['logtest'] after an edit and it shows the key(s) correctly:

Array ( [0] => name )

My thought here was I could pass that in, select only those fields and then pass them to my logChange() function above.

I must be overlooking something stupid. (likely I just dont understand some of the basics).

Scratching my head.... :-)

tangerine

$editor->db()->select( 'weirip_ips', $_SESSION['logtest'], [ 'id' => $id ] )->fetch();

I may be wrong (again...) but I didn't know PHP accepts that array notation: [ 'id' => $id ]

Have you tried "array( 'id' => $id )" ?

gcac

@tangerine I don't think PHP does natively but that is using the Editor Database class and is pulled from one of the examples. They query works fine if I specify * in the place of $_SESSION['logtest']. It just returns all the fields.

gcac

Got it working. Not sure where my mind was, but the following seems to work great. I end up with a row in my log table with a user, action, date, id of the row that was edited, one JSON field with the keys and new values and one JSON field with keys and old values. Should be easy enough to parse that back out and display it.

logChange():

function logChange ( $logTable, $db, $action, $id, $values, $existing) {
    $db->insert( $logTable, array(
        'user'   => $_SESSION['username'],
        'action' => $action,
        'newValues' => json_encode( $values ),
        'prevValues' => json_encode( $existing ),
        'row'    => $id,
        'when'   => date('Y-m-d H:i:s')
    ) );
}

Editor PHP init: (I used array_shift because I am using joins in my Editor instance, and values returned a two-dimensonial array, with one element. The key for that element was the table name. The single element is an array of the changed values). I grab those and proceed.

   ->on( 'preEdit', function ( $editor, $id, $values ) {
        global $prevValues;
        $fields = array_keys(array_shift($values));
        $prevValues = $editor->db()->select( 'weir_ips', $fields, [ 'id' => $id ] )->fetch();

    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row) {
        global $prevValues;
        $values = array_shift($values);
        logChange( 'gcac_log', $editor->db(), 'EDIT', $id, $values, $prevValues );
    } )

Thanks for all the great advice.

N

allan

but I didn't know PHP accepts that array notation: [ 'id' => $id ]

From 5.4+ it does. For 5.3 and before you'd need to use array() to create a new array.

Looks good - great to hear you've got it working as you need now!

Allan

mrgogo

Current PHP version: 5.6 Not Work,
Please Help

<b>Warning</b>: array_keys() expects parameter 1 to be array, string given in <b>/home/mrgogo/public_html/***/***/***/****.php</b> on line <b>74</b><br />
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `datatables_demo` WHERE `id` = '25'' at line 1","data":[],"ipOpts":[],"cancelled":[]}

function logChange ( $logTable, $db, $action, $id, $values, $existing) {
    include( '../../users/init.php' );
    $db->insert( $logTable, array(
        'user'   => $user->data()->username,
        'action' => $action,
        'newValues' => json_encode( $values ),
        'prevValues' => json_encode( $existing ),
        'row'    => $id,
        'when'   => date('c')
    ) );
}

->on( 'preEdit', function ( $editor, $id, $values ) {
     global $prevValues;
     $fields = array_keys(array_shift($values)); //this line 74
     $prevValues = $editor->db()->select( 'datatables_demo', $fields, [ 'id' => $id ] )->fetch();
 
 } )
 ->on( 'postEdit', function ( $editor, $id, $values, $row) {
     global $prevValues;
     $values = array_shift($values);
     logChange( 'staff-log', $editor->db(), 'EDIT', $id, $values, $prevValues );
 } )

gcac

Looks like you are using a single array (not nested arrays). The array_keys() function needs an array. Try removing the array_shift().

$fields = array_keys($values);

N

mrgogo

Thank you very much for your help

mrgogo

"fetch" not working My Current PHP version: 5.6 Could it be because of this?
[ 'id' => $id ]

My Database Results:
PrevValues = "{"first_name":"Angelica","last_name":"Ramos","position":"Chief Executive Officer (CEO)","office":"London","extn":"5797","start_date":"2017-12-21 01:13:00","salary":"1200000"}"

New Values = "Angelica1"

allan

Are you still getting an SQL error? What are the values of the variables you are passing into the select function?

Allan