Audit trail

[jhedonghae]

hello! i'm trying to implement an audit trail in my system and what i wanted is that when the user click the login button, infos like username, level of access, time in and date will be save in the database and fortunately i have done it and here is my code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    con = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
    da.SelectCommand = New OleDbCommand("Select* from AuditTrail", con)
    cmd1 = New OleDbCommandBuilder(da)
    If con.State = ConnectionState.Closed Then con.Open()
    ds = New DataSet
    da.Fill(ds, "AuditTrail")
    ds.Tables("AuditTrail").Rows.Add()
    lastrow = ds.Tables("AuditTrail").Rows.Count - 1
    myrow = ds.Tables("AuditTrail").Rows.Item(lastrow)
    myrow.Item("Username") = Textbox1.Text
    myrow.Item("LevelOfAccess") = LevelofAccessTextBox.Text
    myrow.Item("TimeIn") = time.Text
    myrow.Item("Dates") = dates.Text
    ds.Tables("AuditTrail").GetChanges()
    cmd1.GetInsertCommand()
    da.Update(ds, "AuditTrail")
    con.Close()
end sub

my problem is that on how to save the time out because the time out is in different form..

[Reverend Jim]

If you are just saving it into the database then you don't need datasets and datatables. But I suggest you use a datetime field instead of separate date and time. To add the login record you can do

Dim con As New ADODB.Connection
con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")

Dim query as String = "insert into AuditTrail (UserName,LevelOfAccess,DateIn,TimeIn) "
                    & "  Values(" & Quote(TextBox1.Text)      & "," 
                    &               Quote(LevelOfAccess.Text) & ","
                    &               Quote(Now())              & ")"

con.Execute(query)
con.Close

Private Function Quote( field As String) As String
    Return "'" & field & "'"
End Function

The query to update the audit trail when the user logs out is

Dim query as String = "update test1 set DateOut=getdate() where DateIn="
                    & "  (select MAX(DateIn) from Test1 where UserName = '" & username & "')"

Keep in mind that you may need cleanup code to resolve records where a DateOut is not inserted due to program, machine or network failure.

[adam_k]

I preffer to use session IDs for audit trails. It will ensure that you are logging out the correct session, and allows you to log users activities with only 1 unique ID that can point to a number of info that you wouldn't be logging otherwise. Usually this means to me a table for assigning session IDs that would track logins and logouts and a log table that would track changes made by user.
In the user_logins table I might add - depending on the app - system vars from Environment class like UserName and MachineName.
This way I know the user, the workstation and the username used to login into my app.

[jhedonghae]

@Reverend Jim : the DateOut is not saving in the database i tried this code

 Dim query As String = "update AuditTrail set DateOut=getdate() where DateIn=" _
                & "  (select MAX(DateIn) from AuditTrail where Username = '" & login.Textbox1.Text & "')"

[jhedonghae]

i revise my code for the login: this is working

Dim con1 As New ADODB.Connection

   con1.Open("Provider=Microsoft.ace.oledb.12.0;Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")

   Dim query As String = "insert into AuditTrail (Username,LevelOfAccess,DateTimeIn) " _

               & "  Values(" & Quote(Textbox1.Text) & "," _

               & Quote(LevelofAccessTextBox.Text) & "," _

               & Quote(Now()) & ")"

   con1.Execute(query)

   con.Close()

while this is the code for the logout: but not working

Dim query As String = "update AuditTrail set DateTimeOut=getdate() where DateTimeIn=" _

                & "  (select MAX(DateTimeIn) from AuditTrail ')"

[Reverend Jim]

Sleep is good. Coffee is better.

Let's say your audit table looks like

UserName    DateIn                 DateOut
--------    -------------------    -------------------
Jim         2012-03-27 11:30:23    2012-03-27 11:45:19
Jim         2012-03-29 08:15:12

And the current date is today. The reason that record two has no value for DateOut could be one of

• The network failed
• The server died
• The user kicked out the power cord on his computer

Or several other reasons. In any case, when Jim goes to log back in he will end up with the following

UserName    DateIn                 DateOut
--------    -------------------    -------------------
Jim         2012-03-27 11:30:23    2012-03-27 11:45:19
Jim         2012-03-29 08:15:12
Jim         2012-04-01 13:24:17

This is not good because it looks like Jim has logged in twice without logging out. Come to think of it, if it is possible for a user to log in on two different machines at the same time then you might add a machine name field to the audit table (a good idea in any case). Let's keep it simple for now. When the app starts you will have to check for unclosed logins and put some value in to "close" them. Let's assume that has been done and the audit table now looks like

UserName    DateIn                 DateOut
--------    -------------------    -------------------
Jim         2012-03-27 11:30:23    2012-03-27 11:45:19
Jim         2012-03-29 08:15:12    2012-03-29 24:00:00
Jim         2012-04-01 13:24:17

If you were to use the query

UPDATE AuditTrail SET DateTimeOut = now() where Username ='Jim'

You would end up with

UserName    DateIn                 DateOut
--------    -------------------    -------------------
Jim         2012-03-27 11:30:23    2012-04-01 13:30:24
Jim         2012-03-29 08:15:12    2012-04-01 13:30:24
Jim         2012-04-01 13:24:17    2012-04-01 13:30:24

Note that ALL DateOut values in all records have been modified. What you need instead is

UPDATE AuditTrail SET DateTimeOut = now() where Username ='Jim' and DateOut = ''

This says to only update records for that user where a DateOut value has not been entered. This updates only the most recent record for that user.

[jhedonghae]

but what if the username is not always JIM?

[Reverend Jim]

Doesn't matter. I used an example with hard coded values. If instead of

"where UserName = 'Jim'"

you used

"where UserName = '" & txtUserName.Text & "'"

then it would do what you want. By specifying the username in the where clause you affect only records for that username. By also specifying DateOut = '' you further restrict to affect only the specific username who has not logged out.

[jhedonghae]

i tried it but it's still not working...

[Reverend Jim]

Can you zip and post the entire project or is it too large (or confidential)?

[jhedonghae]

problem solved;) tnx