Tracking User Activity in an Application

[djtreble]

Re: Tracking User Activity in an Application

Garry,

How do you use the above method to record what fields were changed/deleted/added? Is it possible?

Anyone able to advise?

[djtreble]

Re: Tracking User Activity in an Application

Here is another approach, that writes the data to a table. By doing that, you can query for any particular data that you want:

dim tbl as p
tbl=table.open("[pathalias.adb_path]\audit_trail")
tbl.enter_begin()
tbl.pracid=session.pracid
tbl.userid=session.userid
tbl.page=request.script_name
tbl.date=date()
tbl.time=time("0h:0m:0s am")
tbl.Ip_address=Request.Remote_Addr
tbl.enter_end()
tbl.close()

Garry,

How do you use the above method to record what fields were changed/deleted/added? Is it possible?

[joannristau]

Re: Tracking User Activity in an Application

Thanks Gary! I will try that, it would be even better!

[drgarytraub]

Re: Tracking User Activity in an Application

Here is another approach, that writes the data to a table. By doing that, you can query for any particular data that you want:

dim tbl as p
tbl=table.open("[pathalias.adb_path]\audit_trail")
tbl.enter_begin()
tbl.pracid=session.pracid
tbl.userid=session.userid
tbl.page=request.script_name
tbl.date=date()
tbl.time=time("0h:0m:0s am")
tbl.Ip_address=Request.Remote_Addr
tbl.enter_end()
tbl.close()

[joannristau]

Re: Tracking User Activity in an Application

Hi Toby,
I wanted to let you know I used your code on one of our web projects.

<%a5
'****************************************************
'Log File
dim time as T
dim url as c
dim ip as c
time = now()
ip = Request.Remote_Addr
url = Request.Request_URI+crlf()
file.append("c:\sitelog.txt", time+" * "+loginid+" * "+ip+" * "+url)
'****************************************************
%>

and it worked great! Thank you for posting it, it was just what I needed to create a custom log file.

[ChristJC]

Re: Tracking User Activity in an Application

Hi Wesley and Mike

Thanks for taking time out to reply to my question. My SQL is now working! Thanks Wesley for providing a detailed response and the great idea re error handling :) Mike, I will definitely look at the example you have attached, its advantageous to have options apart from say triggers in SQL

Have a great day

Jason

[Mike Reed]

Re: Tracking User Activity in an Application

A few years a guy named Andrew posted an "audit" function in the code archive here on the board. Here is the link: https://forum.alphasoftware.com/showthread.php?t=68530
Andrew had created an aex file that would go to each table and change the field rules so that all of the actions done by a user would be recorded in a table called "audit". I realize that you are using SQL tables, but I have attached a text file that lists each of the field rule events that are affected and what was done. In other words when a record is changed, deleted inserted and how it was recorded.

I had a customer app that used this and it worked quite well. I thought I would include it here so that you can see the steps/events and how they are handled and perhaps you might glean something useful for your app.

mike

[gobbligook]

Re: Tracking User Activity in an Application

Hi Wesley

I'm having issues with a SQL statement in the candeleterecord event which is similar to yours. Any idea where I could be going wrong:

if result.cancel = .f. THEN
dim conn as SQL::Connection
conn.open("::Name::Connection2")
dim sql_log as C
sql_log = "INSERT INTO CaseAudit (ID,Disease,DeletedBy,DeleteDate,PersonID) values ('DataSubmitted.ID','DataSubmitted.Disease',session.__protected__userid,+now()-60+,'DataSubmitted.PersonID')"
conn.Execute(sql_log)
end if
conn.close()

I've tried a few different things with the syntax etc, but to no avail

Cheers

Jason

When you are using variable strings you have to make sure you append them correctly.

sql_log = "INSERT INTO CaseAudit (ID,Disease,DeletedBy,DeleteDate,PersonID) values ('"+DataSubmitted.ID+"','"+DataSubmitted.Disease+"','"+session.__protected__userid+"','"+dtoc(now()-60)+"','"+DataSubmitted.PersonID+"')"

Also remember the "now()" function returns a date type, so you'll have to convert that to character with the dtoc() function.

You may also want to add a condition on the conn.execute line to make the routine a bit more "error" friendly:

if conn.execute(sql_log) = .t. then
....
else
....
end if

One more thing I found immensely helpful. Create a variable called Output and put a text area control on your form to display the value of this variable.

When you are building things like SQL statements or bringing multiple condition statements together it helps to trace. When done with the trace just hide the output field and comment out the code that calls it.

IE:
dim notes as C
dim conn as SQL::Connection
conn.open("::Name::connection1")
dim query as C
notes = "this is a test"
query = "exec sp_log @notes="+notes

if conn.Execute(query) = .t. then
Output = "QUERY: " + query + " RESULT: " + result + " ERROR MSG: " + conn.callresult.text
currentform.controls.output = Output
else
currentform.has_error = .t.
Output = "QUERY: " + query + " RESULT: " + result + " ERROR MSG: " + conn.callresult.text
currentform.controls.output = Output
conn.close()

[ChristJC]

Re: Tracking User Activity in an Application

Hi Wesley

I'm having issues with a SQL statement in the candeleterecord event which is similar to yours. Any idea where I could be going wrong:

if result.cancel = .f. THEN
dim conn as SQL::Connection
conn.open("::Name::Connection2")
dim sql_log as C
sql_log = "INSERT INTO CaseAudit (ID,Disease,DeletedBy,DeleteDate,PersonID) values ('DataSubmitted.ID','DataSubmitted.Disease',session.__protected__userid,+now()-60+,'DataSubmitted.PersonID')"
conn.Execute(sql_log)
end if
conn.close()

I've tried a few different things with the syntax etc, but to no avail

Cheers

Jason

[gobbligook]

Re: Tracking User Activity in an Application

Here is how I ended up doing it, I didn't need to know where the user was coming from, just who was logged in when the change was made, and what that change was. I stored this in the Validate Event action.

So if a user were to add an item to a table the vars might look like this

sql_change = "insert into temp (field1,field2) values ('"+currentform.controls.control1+"','"+currentform.controls.control2+"')"

sql_log = "insert into templog (field1,field2,field3) values ('[email protected]',getdate(),'The user did this " + currentform.controls.control1 + currentform.controls.control2 +"')"

Your method would work also, just it logs to the filesystem and that can be hard to query against. If you log into sql, the database gets bigger but you can search it so much easier. I use an AlphaDAO connection to a MS SQL 2005 database.
__________________________________
dim conn as SQL::Connection
conn.open("::Name::<saved connection>")

<validate form data here>

if CurrentForm.Has_Error = .F. then
dim sql_change as C
sql_change = "SQL Insert statement that is the actual change being made"
conn.execute(sql_change)

dim sql_log as C
sql_log = "insert into <logtablename> (<fields>) values (<values>)"
conn.Execute(sql_log)
end if
conn.close()

[Toby Locke]

Re: Tracking User Activity in an Application

Hi Hope that this may be of help
<%a5
'****************************************************
'Log File
dim time as T
dim url as c
dim ip as c
time = now()
ip = Request.Remote_Addr
url = Request.Request_URI+crlf()
file.append("c:\sitelog.txt", time+" * "+loginid+" * "+ip+" * "+url)
'****************************************************
%>

I have this code on all my web pages basically to see who go'es where. As you can see it just logs the Time - User Login ID - Their IP address - Page URL to a text file called sitelog.txt. Now I have not tested it but there is no reason why you could not put the code in the Events of the Grid Dialog - CanUpdateRecord -CanDeleteRecord - CanInsertRecord, and add another field to file.append
'************
dim message as c
message = "Update" 'or Delete or Insert depending on event
file.append("c:\sitelog.txt", time+" * "+loginid+" * "+ip+" * "+ur+" * "+message)
'************

Let me know if it works I may use it myself

Toby

[gobbligook]

I have a web project that writes/reads from a SQL server DB. The web project uses the Alpha5 security pieces to authenticate users.

My question is:

The grid components when changed write to the SQL server db, I would like to record the SQL statements that are used any time a insert/update/delete takes place and who made the change.

Basically a history of the activity on the application. The application is going to be used for an internal business process and I need to track the activity to ensure the process is being followed by all employees.

Has anyone done this? I may be "barking up the wrong tree" with my methodology, I've tossed around recording the activity on a per record basis, but the tables then would have a whole lot more management tracking data in them. I've also thought about creating a table called "history" and recording the exact expressions (this would allow me to reconstitute the db at a later time if needed), but I don't know how to retrieve these expressions from Xbasic and the Alpha5 application.

Any help would be appreciated. Thank you in advance.