Windows security event log backup to SQL Server Express with PowerShell

• Author
• Recent Posts

Krishnamoorthi Gopal

Krishna is working as a Senior System Administrator for a managed IT Service provider. He has 10 years of IT experience in the insurance and healthcare industries. Krishna focuses on Windows and Active Directory administration and works with various other technologies such as VMware, Azure, Hyper-V, and PowerShell.

Latest posts by Krishnamoorthi Gopal (see all)

• Windows security event log backup to SQL Server Express with PowerShell - Fri, Mar 18 2022
• Exploiting the CVE-2021-42278 (sAMAccountName spoofing) and CVE-2021-42287 (deceiving the KDC) Active Directory vulnerabilities - Thu, Feb 10 2022
• Perform Active Directory security assessment using PowerShell - Thu, Jan 6 2022

Prerequisites

There are a few prerequisites that need to be in place for the tool to function properly.

1. SQL Server Express should be installed on the machine.
2. A database named OfflineEventDB should exist. For a fresh SQL Server installation, you can log in to the SQL Server and run the command "CREATE DATABASE OfflineEventDB" to create the database.
3. Tables named OfflineEventViewer and NodeInfo should exist in the database. If they don't exist, you need to add the tables by running the following commands:

CREATE TABLE OfflineEventViewer
(
RecordId INT NULL,
TimeCreated DATETIME NULL,
KeywordsDisplayNames VARCHAR(128) NULL,
ProviderName VARCHAR(128) NULL,
Id INT NULL,
Message VARCHAR(MAX) NULL,
Machinename VARCHAR(128) NULL
);
CREATE TABLE NodeInfo
(
Machinename VARCHAR(128) NULL,
Lastupdated VARCHAR(128) NULL,
RecentFilename VARCHAR(128) NULL
);

The PowerShell modules used in the script should be installed under the default Modules folder "C:\Program Files\WindowsPowerShell\Modules\". You can download the source code from the GitHub portal using this link.

Set up ADSecurityLogArchivingManager module

Implementing the ADSecurityLogArchivingManager

To set up ADSecurityLogArchivingManager, you need to import the module ADSecurityLogArchivingManager.psm1, which exports functions to register the node. This creates a scheduled task and syncs the security archive logs.

You can check the cmdlets offered by the module by opening a PowerShell window and running the following command:

Get-Command -Module ADSecurityLogArchivingManager

The output comprises all the functions available in the module and looks like the below:

ADSecurityLogArchivingManager module and functions

Let's take a closer look at the capabilities of each function.

Register-BackupScheduler

The Register-BackupScheduler function is designed to validate the existence of a scheduled task in the Task Scheduler. When the command is run, it first checks whether a scheduled task named BackupEventLogScheduler already exists.

If the task doesn't exist, the function creates a new task, during which it prompts you to enter the user credentials under which the task should be registered. Once the user logon details are entered, it adds other required static information, such as user principal, triggers, and actions that are available within that function.

Run the command Register-BackupScheduler in a PowerShell window to register the task.

If the task doesn't exist, a new task is created in the task scheduler, and it runs at 6 pm daily.

Prompting credentials of a privileged account

Task Scheduler registration was successful

If the task already exists, a message is printed stating that it already exists in the task scheduler.

Add-SecurityBackupNode

The Add-SecurityBackupNode function takes the ComputerName as an input parameter that you want to add to the EventLog backup in SQL Server.

Once the computer name is passed, the function tries to verify SQL connectivity using the Test-DatabaseConnection cmdlet. If the connection to the remote machine is successful, it connects to the SQL Server instance and runs a SQL query that checks for the machine entry in the NodeInfo table of the OfflineEventDB database and performs the following tasks based on the SQL query output:

If a record for the machine already exists in the database table, it prints a message that the BackupNode already exists in the database.

If a record doesn't exist for the machine, it exports the Security Event Logs for that computer to a file in .evtx format under a shared network folder using the Sync-NodeSecurityData cmdlet. It then adds a new entry for the machine in the NodeInfo table along with the shared path for the exported event log file. It also synchronizes the Event Logs for that machine to the SQL database, thereby creating an offline event viewer.

Node information table

EXAMPLE

Add-SecurityBackupNode -ComputerName "EC2AMAZ-2RP601Q"

The above command checks and adds the security log's data for the computer named EC2AMAZ-2RP601Q to the Event Log Backup list in the SQL Server.

If the machine entry already exists in the database, you will get the output as below:

Registration of existing node

For a machine that isn't added to the Event Log backup list, the output looks like the image below:

Registering a new node for backup

AutoLog-SyncScheduler

The AutoLogSyncScheduler function performs the log synchronization of security events for all the computers that are added to the Event Log backup list using the Add-SecurityBackupNode cmdlet.

The function starts by performing a database connectivity check using the Test-DatabaseConnection cmdlet. It then tries to retrieve the list of machines from the NodeInfo table and proceeds further based on the query output.

EXAMPLE

AutoLogSyncScheduler

The above command will connect to the database in SQL Server, get all machines, and synchronize security Event Logs for all the registered backup nodes.

If no machines are registered as backup nodes, we get output as below:

No nodes available for backup

For registered machines, the output of the command look likes the screenshot below:

When nodes are available for backup

Sync-NodeSecurityData

The Sync-NodeSecurityData function is a versatile helper function that plays an important role in the backup and export process for the ADSecurityLogArchivingManager module. It is responsible for backing up the security Event Logs to a file, uploading the exported data to a SQL database, and ensuring the older data gets synced up in an automated fashion.

Test-DatabaseConnection

The Test-DatabaseConnection function is a helper function used to validate the SQL Server connection and the status of the SQL Server service on the computer. It tries to establish a connection to the database and checks whether the SQL service is running for a specific computer using the WMI object model.

SQL functions

Since the ADSecurityLogArchivingManager creates an offline event viewer in the SQL Server's database, a few SQL functions are consumed in the module that help with basic SQL activities, such as executing a specific query on a database, generating data that is compatible to feed into the DB table, etc. Note that all these functions require SQL connection details passed as parameters.

Logging

The ADSecurityLogArchivingManager has a function called Write-Log that logs informational, verbose, and error output to the console host, as well as to a file that includes the current timestamp in the filename. It takes a few parameters for the string to log, the logging level, the output log file path, etc.

You can download the latest version of my ADSecurityLogArchivingManager PowerShell module from GitHub. The comments in the scripts provide further information about the usage of the functions.