SQL CRYSTAL REPORTS

Scrambler Alert SQL Database
with Crystal Reports

Install SQL Express (FREE)

Install Crystal Reports Runtime (FREE)

SCRAMBLER ALERT SQL DATABASE AND REPORTING MODULE

Scrambler Alert 5.1 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 Scrambler Alert.

Initially, we recommend you test this on a separate PC for which we 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 (Scrambler.cfg), then restore via a shortcut which has the following switches after the exe run line:

                                    config=Scrambler import

SOFTWARE REQUIREMENTS

Scrambler Alert 5.1.3 incl. Scrambler Alert 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.msiCrystal 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 Scrambler Alert 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 Scrambler Alert Reports directory to create the database. Note that the MSSQL Instance does not have to be located on the same system as Scrambler Alert is installed. In the \Reports folder of the Scrambler Alert 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=Scrambler, with password=Scrambler5 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 Scrambler Alert 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 (Scrambler), Description (Scrambler Alert 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=Scrambler Alert and Password=Scrambler5. 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 Scrambler Alert

Now execute Scrambler Alert 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 = Scrambler

Password = Scrambler5

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 Scrambler Alert 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 Scrambler Alert writes all your Monitored Objects to the database. After this initial update, only changed MOs will be updated on future configuration saves.

Using Scrambler Alert Report Viewer

The Scrambler Alert Report Viewer (ReportViewer.exe) and some pre-configured reports are to be found in the \Reports folder of the Scrambler Alert install. Pre-configured reports include the following: Alarm Log ReportEvent Log ReportMonitored Object List by IDMonitored Object List by TYPEMonitored Object List by SEVERITYNotification Log ReportProcessing Errors Report To run ReportViewer go via the START menu,

  START/PROGRAMS/SCRAMBLER ALERT/SCRAMBLER REPORT VIEWER

or click the ReportViewer.exe file in the \Reports folder of the Scrambler Alert 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 Scrambler Alert 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 Scrambler Report ViewerClick Select ReportChoose 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 Scrambler Alert 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.