In this guide, I'll go through the steps to audit user logon and logoff events using Microsoft SQL Server and Windows PowerShell.
Avatar

Although Windows audits user logon and logoff events in the Event Viewer by default, Microsoft offers no solution to view the user logon and logoffthese events on every workstation in your environment collectively. However, with PowerShell and SQL Server, you can create a central store of all logon and logoff events for your entire network.

For this guide, you will need the following tools installed on your workstation:

  • Remote Server Administration Tools (RSAT; specifically, Windows Active Directory Users and Computers, and Group Policy Management)
  • Microsoft SQL Server Management Studio (SSMS)
  • Windows PowerShell

Launch the Active Directory Users and Computers MMC snap-in. Create two new security groups called dbo.WSLogons and dbo.WSLogoffs.

Database Groups

Database Groups

Add the Domain Users security group to each group's membership. If you have multiple domains, you will need to add the Domain Users security group from each domain.

dbo.WSLogons group membership

dbo.WSLogons group membership

dbo.WSLogoffs group membership

dbo.WSLogoffs group membership

Launch SSMS and connect to the database engine of your server. Create a new database or select an existing one that will hold your logon and logoff event tables. For this guide, I am going to create a new database called WKS_SCS and will assign myself as the database owner.

Create database

Create database

Click OK to create the database. Send the keystroke Ctrl+N to create a new query, and then copy and paste the following:

USE [WKS_SCS]
GO

/****** Object:  Table [dbo].[WSLogons]    Script Date: 4/7/2017 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WSLogons](
	[Date] [nchar](15) NULL,
	[Time] [nchar](15) NULL,
	[Username] [nchar](15) NULL,
	[Domain] [nchar](15) NULL,
	[Computer] [nchar](15) NULL
) ON [PRIMARY]

GO

This query will create the dbo.WSLogons table. Make sure to change the text "WKS_SCS" to the name of the database where you want to create your logon event table. Also, note that the number 15 represents the number of allowed characters for entry into a table cell. If your environment contains NetBIOS names longer than 15 characters, you will need to increase the number. Execute the query and refresh your database.

To add the dbo.WSLogoffs table, repeat the previous steps using the following query:

USE [WKS_SCS]
GO

/****** Object:  Table [dbo].[WSLogoffs]    Script Date: 4/7/2017 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WSLogoffs](
	[Date] [nchar](15) NULL,
	[Time] [nchar](15) NULL,
	[Username] [nchar](15) NULL,
	[Domain] [nchar](15) NULL,
	[Computer] [nchar](15) NULL
) ON [PRIMARY]

GO

Execute the query and refresh your database. When you expand your database, you should now see the dbo.WSLogons and dbo.WSLogoffs tables.

WSLogons and WSLogoffs tables

WSLogons and WSLogoffs tables

While still in your expanded database, navigate to the Security node. Right-click on the node and choose the option New > User. When the new database user wizard opens, configure the options as follows:

General options

  • User name: DOMAIN\dbo.WSLogons
  • Login name: DOMAIN\dbo.WSLogons
  • Default schema: dbo
General options

General options

Membership options

Check the box next to the role membership db_datareader.

Membership options

Membership options

Securables options

Add the dbo.WSLogons table by going to Search > Specific objects… > Object Types and selecting Tables. Grant the permissions Insert, Select, and Update.

Securables options

Securables options

Repeat the previous steps and create a new database user for DOMAIN\dbo.WSLogoffs. But this time, grant the necessary securable permissions for the dbo.WSLogoffs table. With our tables and permissions set up, we can now create the logon and logoff scripts that will gather and write the relevant information to the database tables.

Launch PowerShell, and then copy and paste the following:

# Declare Variables
$Date = Get-Date -Format d
$Time = Get-Date -Format t
$Username = $env:USERNAME
$Domain = $env:USERDOMAIN
$Computer = $env:COMPUTERNAME

# Write logon activity to database
    # Connect to SQL server
    $SQLSERVER = "SCS-CFGMGR-MP"
    $SQLDB = "WKS_SCS"
    $SQLCONNECTION = "Server=$SQLSERVER; Database=$SQLDB; Integrated Security = True"
    $SQLQUERY = "INSERT INTO dbo.WSLogons (Date,Time,Username,Domain,Computer) VALUES (@Date,@Time,@Username,@Domain,@Computer)"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $SQLCONNECTION
    $connection.Open()
    # Log event
    $command = $connection.CreateCommand()
    $command.CommandText = $SQLQUERY
        $command.Parameters.AddWithValue("@Date", $Date) | Out-Null
        $command.Parameters.AddWithValue("@Time", $Time) | Out-Null
        $command.Parameters.AddWithValue("@Username", $Username) | Out-Null
        $command.Parameters.AddWithValue("@Domain", $Domain) | Out-Null
        $command.Parameters.AddWithValue("@Computer", $Computer) | Out-Null
    $command.ExecuteNonQuery()
    # Disconnect
    $connection.Close()

Make sure you change the values for $SQLServer and $SQLDB to the SQL server and database you'd like to use. This script is going to write all user logon events to the dbo.WSLogons table you just created. To create the script that will write all user logoff events to the dbo.WSLogoffs table, create a new script in PowerShell, and then copy and paste the following:

# Declare Variables
$Date = Get-Date -Format d
$Time = Get-Date -Format t
$Username = $env:USERNAME
$Domain = $env:USERDOMAIN
$Computer = $env:COMPUTERNAME

# Write logoff activity to database
    # Connect to SQL server
    $SQLSERVER = "SCS-CFGMGR-MP"
    $SQLDB = "WKS_SCS"
    $SQLCONNECTION = "Server=$SQLSERVER; Database=$SQLDB; Integrated Security = True"
    $SQLQUERY = "INSERT INTO dbo.WSLogoffs (Date,Time,Username,Domain,Computer) VALUES (@Date,@Time,@Username,@Domain,@Computer)"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $SQLCONNECTION
    $connection.Open()
    # Log event
    $command = $connection.CreateCommand()
    $command.CommandText = $SQLQUERY
        $command.Parameters.AddWithValue("@Date", $Date) | Out-Null
        $command.Parameters.AddWithValue("@Time", $Time) | Out-Null
        $command.Parameters.AddWithValue("@Username", $Username) | Out-Null
        $command.Parameters.AddWithValue("@Domain", $Domain) | Out-Null
        $command.Parameters.AddWithValue("@Computer", $Computer) | Out-Null
    $command.ExecuteNonQuery()
    # Disconnect
    $connection.Close()

Again, make sure you change the values for $SQLServer and $SQLDB to the SQL server and database you'd like to use. Save both scripts to a network location you can access with Group Policy Management. For this guide, I will be adding both snippets of code to my existing logon and logoff scripts located on my domain's NETLOGON share.

Finally, we are going to deploy the logon and logoff scripts through Group Policy. Launch the Group Policy Management MMC snap-in, and create a new Group Policy Object (GPO) in the organizational unit (OU) that contains the computer objects of the logons and logoffs you wish to audit.

To add your logon script, navigate to User Configuration > Policies > Windows Settings > Scripts > Logon. Select the PowerShell Scripts tab and click Add. Browse to the script, or if you know the complete path with the extension, enter it in the text field. Repeat this process to add your logoff script.

Add logon script

Add logon script

To add your logoff script, navigate to User Configuration > Policies > Windows Settings > Scripts > Logoff. Select the tab “PowerShell Scripts” and click “Add”. Browse to the script or if you know the complete path with extension, enter it in the text field. Your GPO should resemble the following report:

QuickWKS user lockdown report

QuickWKS user lockdown report

Log on to and log off from a workstation located in the OU where you're deploying your scripts. Launch SSMS and connect to the database engine of your server. Expand the database that contains the dbo.WSLogons and dbo.WSLogoffs tables. Right-click on the dbo.WSLogons table and select Edit Top 200 Rows. You should see a new row entry that contains your logon information. For this guide, I have multiple row entries, as I have been using this auditing method for a few weeks.

dbo.WSLogons table

dbo.WSLogons table

This method can be useful if you detect a security breach on one of your workstations, and you would like to see which computers a user has logged in to on a certain day. You can accomplish this by executing the following query:

Select * FROM dbo.WSLogons WHERE Date='4/13/2017' AND Username='arpazik'

In my case, this will bring up the following results:

Subscribe to 4sysops newsletter!

dbo.WSLogons query result

dbo.WSLogons query result

avataravatar
8 Comments
  1. Avatar
    Stead Halstead 6 years ago

    I’m curious about the decision to use two tables, rather than one table with a column for action equalling “LOGON” or “LOGOFF” to make generating a user audit log of all log on/off activities very simple?

    I appreciate the post, I’m eager to try this out! Thanks!

    • Avatar

      Hi Stead– I actually never thought about it that way! I suppose you could do that but what would you call the table then? I can’t think of a clever/logical name but if you can I encourage you to post it.

      • Avatar
        Stead Halstead 6 years ago

        Hi Alex – Hmm… LogOnLogOffLog is probably a little too long! Maybe AccountUsageLog

        avatar
        • Avatar

          Perhaps WKSUsage? LogOnLogOffLog seems like a bit of a tongue twister. But thanks for the suggestions!

  2. Avatar
    Stead Halstead 6 years ago

    Alex – I did think of one situation in our environment that is not caught by this script. Users that remain perpetually logged on, just unlocking/relocking their computer when they leave. Part of me would like to capture this data too (in the event that someone logged on during normal hours, locked, then came back at 2am to do something malicious), but I recognize this would capture a lot of locking activity during the day too.

    I wonder if windows will support Lock/Unlock scripts as well as log on/off scripts someday!

    Thanks again!

    • Avatar

      You can actually do that! You would have to deploy two scheduled tasks via group policy whereas each task runs a variant of the script I wrote upon being triggered by either a workstation lock or unlock. The trigger option for workstation lock and unlock is available scheduled tasks (at least Windows 7).

  3. Avatar
    yoyoyo 6 years ago

    Imagine that you have 10000 windows servers and even more users constantly logging on/off. It better to parse events from AD itself to a DB periodically?

  4. Avatar
    Neil 6 years ago

    Hi There is there any trouble shooting we can do if the logoff is not writing to the DB.

    Logon is writing to DB but logoff is not.

    I believe i followed your instructions to the T.

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2023

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account