Database Logging with MSSQL

Step-By-Step Guides

Article created 2004-09-14 by Timm Herget.
Last Updated 2011-07-27 by Florian Riedl.

Database Logging with MSSQL

This is a very quick step-by-step guide. It essentially is a step in multiple
configurations. You can refer to this guide whenever you need to add
database logging to one of your services.

Though we need to add some sidenotes for issues with 32/64bit systems. If you have a operating system
which is a 64bit edition, the installer for EventReporter, MonitorWare Agent or WinSyslog will automatically
install the appropriate binaries (64bit) on the system. The problem is now, that generally the 32bit drivers for ODBC
would work fine, but 64bit applications can only use drivers that are for 64bit as well. Therefore it is best
to make sure, that you have installed the 64bit ODBC drivers as well. This does only apply for MSSQL and MySQL databases. If you are trying to use a JET database with Adiscon’s products on a 64bit system, you’re in bad luck, since there are no 64bit ODBC drivers available.

MSSQL Enterprise Manager

1. To create a new Database, open up the Microsoft SQL Enterprise Manager.

2. Right-click on “Databases” and select “New Database”.

3. Select a Database Name there and click “OK”.

ODBC Data Source Administrator

After you created the new Database, go to the Control Panel -> Administrative Tools and open up “Data Sources (ODBC)”.
The following Window will appear:

4. Click on “System DSN” and then “Add…”.

5. Select “SQL Server” as Driver from the List and click “Finish”.

6. Choose a Datasource Name, Description and select the Server where the Database is. In our example we use “localhost”.
Click on “Next”.

7. Select “SQL Server Authentication” and type in your MSSQL Login ID and Password. Click on “Next”.

8. Select “Change the default Database to:” and choose your new created Database, in our example we use “MyMWDB”. Click on “Next”.

9. Leave all at default settings and click “Finish”, a test Window will appear:

10. Click on “Test Data Source”, normally the following Window should be displayed:

11. If not, go back and check your Settings, if yes, Click “OK” and exit the System-DSN Wizard.

Monitor Ware Line Product

12. To define a new rule set, right click “Rules”. A pop up menu will
appear. Select “Add Rule Set” from this menu.

13. Then, a wizard starts. Change the name of the rule set to whatever name you
like. We will use “Database Logging” in this example. The screen
looks as follows:

14. Click “Next”. A new wizard page appears:

15. Select only Database Logging. Do not select any other options for this sample.
Also, leave the “Create a Rule for each of the following actions”
setting selected. Click “Next”. You will see a confirmation page.
Click “Finish” to create the rule set.

16. The wizard closes and the client shows a newly created rule set.

17. As you can see, the new Rule Set “Database Logging” is present. Please
expand it in the tree view until the action level of the “Database
Logging” Rule and select the “Database Logging” action to
configure.
You will see the following Window now:

18. Type in your DSN, User-ID and Password now and press “Save”.

19. Click on the “Create Database” Button to let the Programm create the Adiscon-Table-Layout in your Database.

Done 🙂

Centralized logging in a hybrid environment (Windows/Linux)

Created 2011-03-11 by Florian Riedl

This article will describe how to setup centralized logging in a hybrid environment. Basically, we will have various major steps, that show different configuration of several clients, which forward their log data to a central loghost. There, everything will be stored into a database and processed further for alerting.

To describe the situation basically, we want all machines on the network send their log data to a central syslog server (if possible). For the central log server we take a windows machine running MonitorWare Agent (www.mwagent.com). Here we can receive syslog, monitor local log files and the Windows EventLog. Data shall be stored into a database and several email alerts shall be configured. The other steps describe the configuration of simple Windows workstations and servers, as well as Linux servers.

For TCP transmission we will use port 514 (default) for UDP and port 10514 for TCP. We want to use TCP mainly, because it ensures the transmission of the syslog messages. This is due to UDP being connectionless and thus it can occur (and will) that messages get lost.

The Client machines in this example consist of several different types of machines. We have regular Windows Workstations. Here we will use EventReporter (www.eventreporter.com). In addition to our central server, we have some other Windows Servers which will get MonitorWare Agent as well and some Linux machines which have rsyslog (www.rsyslog.com) installed. These machines will send their log messages via TCP syslog to the central server.

Additionally to these clients, we will mention some other devices and appliances (just roughly), like firewalls, switches and routers.

Step 1:

This is the first and biggest step. We will configure the central server first. The reason is simple. If this is already running, we can setup the clients and it will directly start logging everything. We assume, this is a Windows Server where MonitorWare Agent is installed. The central log server shall provide the following functionality:

syslog receiver with TCP (for devices that can send TCP syslog)
syslog receiver with UDP (for devices that can only send UDP syslog)
monitor the local Windows EventLog
monitor local textfile-logs
store all log messages into a database
send email alerts to an admin on error or critical log messages

Continue reading on Step 1

Step 2:

In step 2 we will set up the regular Windows clients. These are usually the workstations the people work on. We will use EventReporter here. It can pull all log messages from the Windows EventLog and forward them via TCP syslog. Thus the following functionality is mandatory:

monitor the local Windows EventLog
forward all log data via TCP syslog

Continue reading on Step 2

Step 3:

Now we will configure the other Windows servers. Again, we will use MonitorWare Agent because it has the most functionality. We need the following functions to be setup here:

monitor the local Windows EventLog
monitor local textfile-logs
forward all log data via TCP syslog

Continue reading on Step 3

Step 4:

Now we get to the Linux servers. Here we need to use a completely different product – rsyslog. For a first-time user, this might look a bit strange. The configuration we want to have here needs the following:

monitor local log messages
monitor local textfile-logs
forward all log data via TCP syslog

Continue reading on Step 4

Step 5:

This is rather just a note on other devices and appliances that are not yet covered. Often devices (like routers, firewalls or switches) have the possibility to send log data to a syslog server. Usually, this only works via UDP and some machines are even capable of sending logs via TCP. Since there is such a huge mass of different systems and devices, we cannot give correct steps for everything. Please refer to the user manual that came with the device or contact the manufacturer for information about how to configure the devices for sending syslog.

If you already know how to configure it, let it send it’s log messages to the central server on port 514 for UDP or (if possible) port 10514 for TCP.

Conclusion

We now have a setup that stores all the log data that machines on the network will generate to a central database for storage. Most of the clients on the network send their log data securely via TCP to the central log storage. Some machines were rather quick to set up, others needed more effort. Usually the effort rises with the amount of features that will be used. Thus we thought of this setup to be quite simple.

If you have any remarks or ideas of improvement for this guide, please let us know and send an email to info@adiscon.com.

Database Logging with MSSQL in MonitorWare Agent 4.0

Step-By-Step Guides

Article updated 2006-06-19 by Timm Herget.

Database Logging with MSSQL in MonitorWare Agent 4.0

This guide helps you to add database logging to any of your services available in MonitorWare Agent 4.0.

Microsoft SQL Enterprise Manager

1. To create a new Database, open up the Microsoft SQL Enterprise Manager.

2. Right-click on “Databases” and select “New Database”.

3. Select a Database Name there and click “OK”.

ODBC Data Source Administrator

After you created the new Database, go to the Control Panel -> Administrative Tools and open up “Data Sources (ODBC)”. The following Window will appear:

4. Click on “System DSN” and then “Add…”.

5. Select “SQL Server” as Driver from the List and click “Finish”.

6. Choose a Datasource Name, Description and select the Server where the Database is. In our example we use “localhost”. Click on “Next”.

7. Select “SQL Server Authentication” and type in your MSSQL Login ID and Password. Click on “Next”.

8. Select “Change the default Database to:” and choose your new created Database, in our example we use “MyMWDB”. Click on “Next”.

9. Leave all at default settings and click “Finish”, a test Window will appear:

10. Click on “Test Data Source”, normally the following Window should be displayed:

11. If not, go back and check your Settings, if yes, Click “OK” and exit the System-DSN Wizard.

MonitorWare Agent 4.0

12. To define a new rule set, right click “RuleSets”. A pop up menu as shown below appears. Select “Add Rule Set” from this menu.

13. Then, a wizard starts. Change the name of the rule set to whatever name you like. We will use “Database Logging” in this example. The screen looks as follows:

14. Click “Next”. A new wizard page appears:

15. Select only Database Logging. Do not select any other options for this sample. Also, leave the “Create a Rule for each of the following actions” setting selected. Click “Next”. You will see a confirmation page. Click “Finish” to create the rule set.

16. The wizard closes and the client shows a newly created rule set.

17. As you can see, the new Rule Set “Database Logging” is present. Please expand it in the tree view until the action level of the “Database Logging” Rule and select the “Database Logging” action to configure.
You will see the following Window now:

18. Type in your DSN, User-ID and Password now and press “Save”.

19. Click on the “Create Database” Button and you are shown a pop up screen as shown below:

Here you can specify the required fields and click on create button to get it all done.