Windows Event Forwarding to a SQL database

• Author
• Recent Posts

Jörgen Nilsson

Jörgen is a principal consultant at Onevinn in Sweden. His work focuses on enterprise client management and system management. He is a Microsoft Certified Trainer and a Microsoft Most Valuable Professional (MVP) in Enterprise Mobility. He also speaks at events such as Microsoft TechDays, Microsoft Management Summits, and TechEd.

Latest posts by Jörgen Nilsson (see all)

• Remote help for Intune and Microsoft Endpoint Manager - Tue, Jan 25 2022
• Windows 10/11 Azure AD/Intune Enterprise subscription is not valid - Mon, Nov 8 2021
• Upgrade from Windows 10 to Windows 11 with Setupconfig.ini and Intune - Wed, Sep 22 2021

When I lecture about Event Forwarding, often someone in the audience mentions the data overload problem. However, with Event Forwarding we can choose exactly the events we want to collect. In my example, I will forward events whenever AppLocker blocks files on a client.

This guide will follow five steps:

1. Configure the event service on a server
2. Configure an event subscription
3. Configure the Event Forwarding Subscription Group Policy
4. View the forwarded events in Event Viewer
5. Write the forwarded events to a SQL database using PowerShell

Configure the event service on Server 2016

Before we start, we need to configure WinRM. To display the listeners available, run the following command:

winrm e winrm/config/listener

You'll want to test whether WinRM is configured properly. The easiest way is to run the following command. You'll need a certificate in place for the server that WinRM can use.

winrm quickconfig -transport:https

Open the Event Viewer and then click Subscription. A prompt will appear asking you if you want to start the Windows Event Collector service.

Note: Event forwarding uses port 5985 if you work with HTTP and port 5986 if you use HTTPS (which you should!).

Starting the Event Collector service

Configure an event subscription

Now that we have the Windows Event Collector service running, we can create a Subscription that we can apply to the clients. We start by creating a new Subscription:

Creating a new Subscription

We give it a name and select Source computer initiated.

Applocker event

You then select the Active Directory (AD) groups that should send in the events. In this example, I use Domain Computers.

Adding domain computers

Then we select the events we want to forward:

Selecting events

Here we filter only Critical and Error events. In the By log section, we select all AppLocker logs:

Query filter

Then we go back and configure the Advanced Subscription Settings where we can choose HTTP or HTTPS. You also have to choose the time interval you want the clients to send in events.

• Normal: Every 15 minutes
• Minimize Bandwidth: Every 6 hours
• Minimize Latency: Every 30 seconds

Advanced Subscription Settings

Now we are ready to start collecting events.

Configure the Event Forwarding Subscription Group Policy

We can now configure the Group Policy that we assign to servers and clients so that they read the Subscription from the server and send the event included in the Subscriptions.

The corresponding Group Policy is: Computer Configuration > Policies > Administrative Templates > Windows Components > Event Forwarding > Configure target Subscription Manager.

Event Forwarding GPO

We can configure a list of servers to read the Subscriptions from. To add the server address, you can simply type Server=<FQDN of the collector> as shown in the screenshot below. If you use only HTTPS, you need to use the following syntax instead.

HTTPS://<FQDN of the collector>/wsman/SubscriptionManager/WEC,Refresh=<Refresh interval in seconds>,IssuerCA=<Thumb print of the client authentication certificate>

Event Forwarding server

Displaying the forwarded events in Event Viewer

Now that we've applied the Group Policy to clients, we will start to forward events to the server. In the Event Viewer, we can see how many clients have read the Subscription and are sending in events.

Event Subscriptions count

The screenshot above shows the events forwarded from AppLocker.

AppLocker event

In the above example, we can see that AppLocker blocked chromesetup.exe. However, Event Viewer is not the optimal tool to watch forwarded events.

Write the forwarded events to a SQL Database using PowerShell

I am using the solution documented here to create a SQL database and then write the data to it. The solution comprises two parts. First, a PowerShell script initially imports all the events in Forwarded Events. After that, a scheduled task incrementally imports the events only one hour back.

You can then access the information from tools such as Excel, Power BI, and SQL Reporting Services. Below is a sample report in Reporting Services.

Sample report in Reporting Services