Event Log Consolidation to SQL Server

Event Log Consolidation to SQL Server

May 5th, 2021

In this article I am going to show you how to configure Corner Bowl Log Manager to consolidate Event Log entries from various servers to a central SQL Server database.

Table of Contents

How to Configure Microsoft SQL Server

The software includes an onboarding wizard which is what I am going to use to configure Microsoft SQL Server.

  1. To open the wizard select Tools -> Onboarding Wizard.
  2. Once you skip past the introduction page you are prompted to assign Logon as Credentials. In order to pull Security Event Log Entries from remote sources we need to assign the service with Domain Administrator credentials. If the server the service is running is not on a domain then local admin credentials will suffice. In the later case, you will need to configure impersonation for each remote host you want to download Event Logs from. I am not on a domain so I'm going to setup local administrator credentials.
  3. Once entered, click Update and Restart.
  4. If you look in the upper right corner you will notice the connection icon briefly displays the disconnect icon while the service restarts and the console re-connects.
  5. Now that the service credentials are set we can continue.
  6. The next tab is the Email Settings tab which for this video is out of scope so let's just skip past this step.
  7. The next tab is the Primary Data Provider Settings tab which is where we need to change the default File System configuration to Microsoft SQL Server.
  8. Under the Type drop-down select Microsoft SQL Server.
  9. Notice the name references the file system. If we click on the Generate Name button the name is updated and now references SQL Server.
  10. Notice the role is set to Log Database. There are three roles. Log Database, Archive Log Database and Auxiliary Log Database. The Log Database contains all of the latest consolidated log entries. The Archive Log Database contains older entries, for example entries older than 14 days. Finally, Auxiliary Log Databases can be used to load older database backups for auditing and analysis purposes.
  11. Specify the hostname your SQL Server is installed.
  12. The database name, lm_repository, listed will be automatically created in a moment.
  13. Specify the authentication method to use, either Integrated Windows Authentication or SQL Server.
  14. I am going to use Integrated Windows Authentication which means the account the service is running under is used.
  15. If at a later time you find queries are timing out you can increase the execution timeout here.
    Configure SQL Server
    Configure SQL Server
  16. Finally, click the Next button.
  17. Once the database and control tables are created the wizard will advance to the Archive Data Provider Settings tab.
  18. Notice now the Role is set to Archive Log Database.
  19. Under the Type drop-down select Microsoft SQL Server.
  20. Update the name.
  21. Notice the database name is now set to lm_archive.
  22. Specify the authentication method.
  23. Finally, click the Next button.
  24. The last step consolidates the Application, System and Security logs from system the service is installed to the log repository database we just configured.
  25. Click Next and you can verify the software is able to populate the database we just configured.
    Centralized Event Log Consolidation
    Centralized Event Log Consolidation
  26. We have now configured the databases.

How to Configure the Event Log Consolidation

Now that we have added the hosts the next step is to assign an Event Log Consolidation Template to the hosts.

Templates contain configuration parameters such as the number of days to download and the frequency to download. The software comes pre-installed with an Event Log Consolidation Template that consolidates the Application, System and Security Event logs. I am going to show you the pre-installed template then assign it to the lot of new computers.

  1. Below the Hosts node you will find the Templates node. Expand Templates, Sample Templates then Log Consolidation. Right click on Event Log Consolidation and select Template Properties.
  2. Once loaded we can see the General tab is used to assign a name, enable and disable execution and the Schedule of execution.
  3. By default this template downloads entries every hour and evenly distributes each download over the hour among the assigned hosts. For example, if you assign 60 hosts to this template the software will download entries form a single and different server every minute. After 60 minutes the first server will be downloaded again.
  4. The Logs tab is pretty self-explanatory. Use it to select the logs to consolidate.
  5. The Options tab enables you to assign a Consolidation Filter which enables you to dump entries you are not interested in. You have several options here.
  6. If you have a heavily loaded domain controller you may need to limit the initial download of the Security Event Log to one day then build the database from that point on, otherwise you may receive a Quota Violation.
  7. The batch download option is provided in an attempt to allow large Security Event Logs to be initially downloaded. Again this is an attempt to work around the Quota Violation.
  8. Finally, we want to select a Retention Policy. The retention policy is another template that defines the number of days to retain in the Primary and Archive Log Repositories.
  9. If we double click on the assigned template, we can see the template properties.
  10. The schedule is set to Daily.
  11. If I select the Options tab we can see the policy is set to Archive entries older than 30 days and retain entries for 120 days for a total of 150 days.
  12. Back in the Event Log Consolidation Template if I click the Monitor tab, we have the option to add filters and actions to apply immediately after downloading. Event Log Monitoring is out of scope for this tutorial so let's just skip past this step.
  13. The Push Template tab enables you to configure the software to install an agent on the remote machine or if inaccessible you can manually install the agent. Once installed Templates with this option selected are sent to the remote agents when they connect and entries downloaded using a proprietary binary protocol that is approximately 12 times faster than WMI. This feature is also out of scope of this tutorial so let's just skip past this step.
  14. The Actions tab enables you to get notified when there is a problem downloading entries such as a firewall blocking WMI which manifests itself as an RPC Server Unavailable error.
  15. Now we are finally ready to assign the hosts to the template. You have the option of manually assigning each host or assigning groups. You can even assign the entire tree to the template. I am going to select the imported group then click Save and Close.
    Centralized Event Log Consolidation Template
    Centralized Event Log Consolidation Template
Last Updated: May 5th, 2021