Audit Windows logon and logoff events with PowerShell and SQL Server

• Author
• Recent Posts

Alexander Pazik

Alexander is a Systems Engineer and IT Project Manager with a strong background in Active Directory, PowerShell, System Center, and VMware. You can connect with him on LinkedIn and learn more about the services offered by his consulting company Alxco Consulting

Latest posts by Alexander Pazik (see all)

• Configuration Items in Configuration Manager (SCCM, MECM) - Mon, Aug 22 2022
• Create and read SCVMM custom properties with PowerShell and the VMM Console - Mon, Apr 18 2022
• Prevent ransomware attacks on network shares with File Server Resource Manager (FSRM) - Mon, Mar 7 2022

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

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.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

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

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

Membership options

Check the box next to the role membership db_datareader.

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

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

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

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

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:

dbo.WSLogons query result