- 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.
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.
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.
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.
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
Membership options
Check the box next to the role membership db_datareader.
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.
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.
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:
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.
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!
Read the latest IT news and community updates!
Join our IT community and read articles without ads!
Do you want to write for 4sysops? We are looking for new authors.
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!
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.
Hi Alex – Hmm… LogOnLogOffLog is probably a little too long! Maybe AccountUsageLog
Perhaps WKSUsage? LogOnLogOffLog seems like a bit of a tongue twister. But thanks for the suggestions!
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!
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).
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?
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.