Creating and saving Users logs

[sidyusuf]

hi, i have an application with several many forms including login and mdi forms... What i need to do is to store the loggedin users logs like which user open a particular form at what time,from which ip or system,what he/she INSERT,DELETE and UPDATE or any other kind of related activity.
i m using vb.net and sql server 2008r2...
Any help would really be appreciated.

[Reverend Jim]

We need more information

• is this a single instance app on a single comoputer
• is the log a text file or database table
• is the log on a single computer or is it on a shared server
• how much data will be retained

For a simple application with a small log file it is sufficient to have a text log file and just append records as logged. For a multi-user application on a network then a database log file that is updated by more than one user concurrently is more appropriate. A database may also be more appropriate for a single user where a large amount of data will be retained.

[sidyusuf]

• application is multiuser running on many computers
• sys_logs is the name of table on sql server
• data will be retained as per the user activities

[Reverend Jim]

It should be a simple matter to add logging. You'll have to decide what information you want to store (table structure). Because each record will have the same structure you will have to pick a structure that allow you to track all of the different actions. Without knowing more about the actions you want to track I can only suggest something like

sys_log
    ActionTime     DateTime    Primary Key
    UserID         Integer     Primary Key
    ActionType     SmallInt
    Details        varchar(#)

Pick an appropriate length for Details varchar(#). The contents of Details would be interpreted based on the value of ActionType. For example, If ActionType=1 then Details could be a SQL command. ActionType=2 could be "Opened form x".

Depending on the rapidity of user actions or the granularity of the logging, you may run into a problem with Primary Key duplication (if you log two actions with the same ActionTime and UserID).

[sidyusuf]

i realy appreciate your reply.....and i do have below kind of table structure:

• UserID nvarchar(10)
• Datetime datetime
• Machine nvarchar(50)
• Logs nvarchar(max)

though i am displaying user's loggedin datetime,machine name on mdi form...
but how can i track activites/Logs like which form was opened byuser at what time...what Insertion/deletion or update was done by him/her further.
Can you provide me the code behind if any.

[Reverend Jim]

That's one of the reasons I suggested a field to log the event type. That way you could filter the logs and look for specific events. You could create ActionType codes to whatever granularity you like. For example, when a user opens a form you could log that with EventType=1 and store the form name in the Logs (not a very descriptive name for a field by the way) field. If the user does a select query then you could store EventType=2 and the Logs field could be "SELECT * FROM MyTable WHERE ...". For simple updates to a log table I would just use an ADO.NET object. Normal protocol for a connection to a database is to leave it open for as short a time as possible but because a log table is likely to sustain heavy and repeated use my decision would be to open it at app start and leave it open. If you decide to use a SqlClient object the code is very similar.

First you will need to add a Reference to ADODB to your project. You'll find this under the .NET tab. Then you'll need to import the namespace by

Imports ADODB

You'll need a connection object which you can use directly to execute queries that do not return data. If you plan to open it at the start and leave it open then you can put the open command in the Form Load event. Iif you want to open and close it evry time you log an event then you can put the code in the same sub you use for writing. Let's assume that UserID and Machine are also global variables.

Private Sub LogEvent(EventType As Integer, Details As String)

    'This block will open the database connection if it is not already open. If the
    'connection was closed then it will be closed when the sub exits. This allows you
    'to use the samne code here unmodified if you decide that you want the connection
    'to always stay open.

    Dim closeOnExit As Boolean = False

    If logcon.State <> ADODB.ObjectStateEnum.adStateOpen Then
        closeOnExit = True
        logcon.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
    End If

    Dim qry As String = "INSERT INTO sys_logs (UserID,GETDATE(),Machine,EventType,Details) " _
                      & "VALUES(" _
                      & "'" & UserID  & "'," _
                      & "'" & Machine & "'," _
                      & EventType    & ","  _
                      & "'" & Details & "')"

    logcon.Execute(qry)

    If closeOnExit Then
        logcon.Close()
    End If

End Sub

Normally I recommend using SqlClient with parameterized queries but in this case if you are not allowing the user to compose his/her own queries there is no risk of SQL injection attacks so this form should be safe. However, if you decide to go with SqlClient I can show you how to code it up.

[Reverend Jim]

What the heck. It's a slow day. To use SqlClient your sub would be

Private Sub LogEvent(EventType As Integer, Details As String)

    'This block will open the database connection if it is not already open. If the
    'connection was closed then it will be closed when the sub exits. This allows you
    'to use the samne code here unmodified if you decide that you want the connection
    'to always stay open.

    Dim closeOnExit As Boolean = False

    If logcon.State <> ConnectionState.Open Then
        closeOnExit = True
        logcon.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
    End If

    Dim cmd As New SqlCommand("", logcon)

    cmd.CommandText = "INSERT INTO sys_log (UserID,EventTime,Machine,EventType,Details) " _
                    & "VALUES(@UserID,GETDATE(),@Machine,@EventType,@Details)"

    cmd.Parameters.AddWithValue("@UserID   ", UserID)
    cmd.Parameters.AddWithValue("@Machine  ", Machine)
    cmd.Parameters.AddWithValue("@EventType", EventType)
    cmd.Parameters.AddWithValue("@Details  ", Details)

    cmd.ExecuteNonQuery()

    If closeOnExit Then
        logcon.Close()
    End If

End Sub