NIGHTWATCH SQL DATABASE AND REPORTING

Nightwatch 5 supports archiving of monitoring information to a Microsoft SQL database.You can now have a powerful set of tools for a wide variety of reporting in relation to the system monitoring and alerting carried out by Nightwatch.

 

Initially, I recommend you test this on a separate PC for which I can supply a demo licence key. The Monitored Objects from the production server can be eaily copied over for testing by Settings/EXPORT to a file (Nightwatch.cfg), then restore via a shortcut which has the following switches after the exe run line: 
    

                                 config=NightWatch import

SOFTWARE REQUIREMENTS 

Nightwatch 5 incl. Nightwatch Report Viewer

 

+ Microsoft SQL Express 2005  +  Crystal 10 Runtime  (both FREE of charge)

  

If you already have SQL and Crystal Reports then you can use those.

 

If you do not have SQL or Crystal already the following free software can be

downloaded off the internet:

 

SQL Express 2005 – Sqlexpr32.exe

SQLServer2005_SSMSEE.msi

Crystal 10 Runtime – CRRedist2005_x86.msi

Microsoft .Net Framework 2.0 – dotnetfx_2_0.exe

 

If you install SQL 2005 Express, it is recommended that you also download and install the Microsoft SQL Server Management Studio Express administration tool.

 

Creating the Nightwatch Database in SQL

 

Once you have an MSSQL Instance available (ie SQL is installed and running),  use the CreateDB.sql script (see below) located in the Nightwatch Reports directory to create the database. Note that the MSSQL Instance does not have to be located on the same system as Nightwatch is installed.

 

In the \Reports folder of the Nightwatch install there is a script file called

 

CreateDB.sql

 

Double-click it to create the database.

 

If that does not work, run SQL Server Management Studio Express and open a Query Analyzer window. Load the script into the query window and execute it while logged into the MSSQL Instance as the DB Admin.

 

Failure is likely to be a security problem, eg you are not logged on as Administrator or you are not the owner of the db etc.

 

The script will also add a login=Nightwatch, with password=Nightwatch5 to the database and set that login to be the db_owner of the database. This user will be set to use SQL Authentication.

 

Note: Integrated Authentication cannot be used with the archive database.

 

Creating the DSN (Data Source Name)

 

Once the database is installed, on the system where Nightwatch is installed, you must create a Data Source Name (DSN).

 

Go to START/Control Panel/Administrative Tools, open the Data Sources (ODBC) applet. Click the System DSN tab and click ADD. Scroll down and highlight SQL Server. Click Finish.

 

Then complete the boxes for Name (Nightwatch), Description (Nightwatch Database) and Server which is your PC/server’s computername followed by \SQLEXPRESS such as Gregs_PC\SQLEXPRESS for example.

 

In the subsequent screens, make sure the SQL Authentication is chosen and not Windows NT authentication. In the login boxes Username=Nightwatch and Password=Nightwatch5.

 

If you already have a DSN that points to that Instance,  you do not need to create new one. At the end make sure you can successfully connect to the database with the connection tester.

 

Setting up Nightwatch

 

Now execute Nightwatch and go to the More Options window. Select the Database tab. Enable the use of the database and select the DSN from the drop down list. Enter user name and password.

 

Username = Nightwatch

Password = Nightwatch5

 

Finally, go the Options Window and select the Logging tab. Check the Monitored Object types you wish to have recorded in the database. You do not have to enable disk file logging, just select the MOs to be included in database logging.

 

Now, save the Nightwatch configuration by going to Settings/Save Configuration to Registry. Note that the first time you save the configuration after enabling the database, there will be a significant delay while Nightwatch writes all your Monitored Objects to the database. After this initial update, only changed MOs will be updated on future configuration saves.

 

Using Nightwatch Report Viewer

 

The Nightwatch Report Viewer (ReportViewer.exe) and some pre-configured reports are to be found in the \Reports folder of the Nightwatch install.

 

Pre-configured reports include the following:

 

Alarm Log Report

Event Log Report

Monitored Object List by ID

Monitored Object List by TYPE

Monitored Object List by SEVERITY

Notification Log Report

Processing Errors Report

 

To run ReportViewer go via the START menu,

 

 START/PROGRAMS/NIGHTWATCH/NIGHTWATCH REPORT VIEWER

 

or click the ReportViewer.exe file in the \Reports folder of the Nightwatch install.

 

When you run the ReportViewer application you will need to select a report by using the recent reports list under the File menu or by clicking the Select Report button. If you have not previously connected to the database, you will be prompted for the SQL Instance and login information. This will be retained but can be changed under the File menu.

 

When you select a report using Nightwatch ReportViewer, a series of prompting windows will appear to allow report parameters to be entered. Clicking the “Skip” button will assume default values.

 

All of the included reports will ask for the name of the Monitoring System. If have only one monitoring system or want to report all systems you have, click on Skip. If you wish to select a specific system, enter it's name in the field and click Accept.

 

Once SQL is setup it should be just a case of

 

Run Nightwatch Report Viewer

Click Select Report

Choose one (say) Notification log.rpt

Keep clicking SKIP until the report is displayed

 

You can then navigate the report with the > and < buttons, print it off etc.

 

ReportViewer will remember your answers to each parameter prompt from the last time you ran a report and pre-fill the fields with those values. You must still click Accept to keep the value. Clicking Skip says you do not wish to supply a value.

 

On log type reports, you will be prompted for a date range. You can select from the pre-defined time periods in the drop-down list or click Skip to enter a custom date range.

 

Each report has a default sort defined, but you may be allowed to select different sort fields from a drop-down list.

 

Some reports allow for selection of records to focus the report on items of interest. If asked, you can choose a select field from the drop-down list and a comparison operator from the second list. Then enter the value to be used in the record comparison to select records included on the report.

 

ReportViewer can be run on the Nightwatch host system or from any other system you wish. For other systems, you must be able to map a drive to the Reports directory of the hosting system or you can just copy the reports directory to the system that will be running the ReportViewer.

 

Modifying reports and creating your own reports

 

You can create your own reports in any reporting tool you wish, but the ReportViewer application can only be used with Crystal Reports and then only reports that have been designed with the conventions of the included standard reports.

 If you wish to modify the reports, copy them and then modify the copies. You must have a Crystal Reports design environment or Visual Studio 2005 to modify the reports.