DTracker version 1.1

DTracker is a small program written in C++ for the Windows platform. The program tries at predetermined intervals to connect to an ODBC compatible database and execute a predefined SQL statement. The result (success or failure) is logged in a disk file for later analyses.

The program can be used to collect statistics about the availability of database systems as seen from a user workstation. The resulting log is saved in a format that is easily imported into a spreadsheet program (like MS Excel) or into a user written program.

By checking the connection from the user point of view the collected statistics will represent the actual availability of the system in question, including such events as:

• Network outage (routers, phone lines)
• Server down (hardware or software)
• Database maintenance (planned or unplanned)
• Table unavailable (locked, in-error)
• User access (password changed, userid deleted)

The program is small and very easy to set up on any 32-bit Windows workstation (or server)

The program can also notify operators about problems in the availability of important network lines or database systems. If you do not have a more sophisticated (and expensive) tool for surveillance, this is a nice free tool that allows you a lot of control for very little work.

Jorgen Grosbol at ISS Data in Copenhagen, Denmark has written the DTracker program in 1997.

The DTracker program is freeware.

Program installation

The DTracker program requires a 32-bit Windows platform to run (Windows 95 or later, NT 3.51 or later). You must also have access to a 32-bit ODBC driver for the database being tracked. The program does not work with 16-bit ODBC drivers.

The program comes in a ZIP file containing 4 files:

1. Dtracker.exe, which is the program itself
2. Dtracker.hlp, which is the Windows help file
3. ReadMe.txt, which is the help file as a standard ASCII text file
4. Guide.htm, which is the help file in HTML Internet format

UNZIP the files into a temporary directory. Then copy the Dtracker.EXE file and the Dtracker.hlp files to some directory of your choosing. The ReadMe.txt file and the Guide.htm file contain the same information as the help file, so you can delete them if you have no further use for them. Note that the HTML file can be viewed (and printed) using any standard Internet browser.

You should create icons for the program and the help file in a folder or directly on your desktop. Simply click in the relevant folder with the right mouse button, choose New and enter the name of the EXE file in the following wizard dialog.

The program does not depend on any external DLL's or ActiveX components.

Note that the program does not contain any ODBC drivers. These you have to obtain from your database vendor. They are normally free of charge and can be downloaded from the vendors Homepage on the Internet.

Program usage

Double-click the program icon to start it. The program will display a dialog where you can enter a Run ID. The Run ID is simply a text or a name identifying the options that should be used during this program execution. You can choose any text, but typically you would want to use the name of the database or table being tracked. Once you have entered a name into the dialog box, you press the OK button.

DTracker will now display its main window. The first time you start it the background will be colored yellow and the will be a gray text box showing the text OK. The background color tells you the state of the connection being watched. The colors have this meaning:

• RED: an error occurred last time the program tried to connect to the database.
• GREEN: the timer is running and the last connection to the database was OK.
• YELLOW: otherwise

If an error occurred the last error message received from the database system is displayed in the gray text box. Otherwise the text OK is displayed. If you are colorblind you will have to rely on the message area to check the current status of the program. The color intensity is quite different (whit the green color being the lighter), so most people should hopefully be able to determine the current status from the color alone.

Below the gray text box are 6 buttons that allow you to control the program. They are:

• Minimize: Minimizes the window to an icon so that it won't clutter up your display, but otherwise lets the program go on checking the database connection at the specified intervals.
• Options: Displays a dialog allowing you to set the program options. See the paragraph about Program Options for details.
• Test: Tries to connect to the database and logs the result without waiting for the timer to expire.
• About: displays a small dialog containing program name and version, compilation date and copyright information.
• Help: displays this user guide
• Stop: stops the program. If the timer is running you are asked if you really want to stop now. Otherwise the program just stops.

You have to set the options to make the program do something useful; so unless you have already given up on the program you should click the options button and go on customizing (see the next chapter).

Program Options

The options are saved under each Run ID. Each time you start the program you choose the Run ID to be used, and this in turn chooses the relevant option set. The options are automatically saved into and retrieved from the Windows registry.

The options determine the actions of the program. The options are:

• Title: Enter a descriptive test in this field. The text will be displayed in the dialog title bar and in the ToolTip (when the program in minimized). Typically describe in a few words what the program is tracking.
• ODBC source: Enter the ODBC source name for the database you want to access. The source name must already be defined to Windows. If you haven't already got a32-bit ODBC driver for your database of choice you must get one before you can use the DTracker program. Most drivers are free and can be downloaded directly from the database supplier's Internet site. You can use the pull-down box to see what ODBC sources are already available on your system.
• Userid and Password: If your database system requires you to sign on you should enter your user-id and password here. Please note that passwords are case dependent on many systems. Also note that the passwords are saved (in scrambled form) in your Windows registry. Se the chapter on security for password considerations.
• Logfile Name: Enter the name of your log file here. Each time the program tests a database connection it will write a line to the log file. You should periodically archive and clear the data in the log file to conserve disk space. The format of the log file lines is described in the Log File Format chapter.
• SQL Statement: Here you enter the SQL statement that the program should try to issue each time the time interval expires. You can enter any kind of SQL statement, but generally a simple SELECT that returns a few rows will do. See the chapter on security for other considerations.
• Interval (hh:mm): Specifies the interval between tests in hours and minutes. If you leave the value at the default (0 hours and 0 minutes) no tests will be performed.
• Start Inerval At (hh:mm): Specifies the hour and minute where the first test should be performed. Normally you would leave this option at its default 0:0 value. If you run several DTrackers having the interval you could this option to ensure that they did not all try to access the database system at the same time.
• Run Minimized: Set this flag to have the program start up in iconic form. This is useful when using the automation feature described later.
• Display on errors: Set this flag to have the program redisplay the program window in case of errors. In this way you can have the program quietly checking the connection in the background until an error occurs.
• Log status in file: This flag should be set unless you do not want the program to write information to the log file on disk.
• Timer active: This flag should be set unless you do not want the automatic timer to activate the test procedure at the specified intervals.
• Log file delimiter: If you change this parameter from its default (a semicolon) the program will use the new value to delimit each information written to the log file. Most spreadsheets and database programs accept data delimited by semicolons, but just in case here is your chance to use a character or string that better suits your tastes.

As noted at the bottom of the Options display you can use the following special character sequences to have your SQL and filename changed before they are actually used. The following codes can be used:

• *1* is replaced by the Run ID defined for this particular program execution
• *2* is replaced by a sequence number starting with 1. The sequence number is incremented each time a log reword is written to the log file
• *3* is replaced by the current year in the form yyyy (4 digits)
• *4* is replaced by the current month in the form mm (2 digits)
• *5* is replaced by the current day in the form dd (2 digits)
• *6* is replaced by the current hour in the form hh (2 digits)
• *7* is replaced by the current minute in the form mm (2 digits)
• *8* is replaced by the current second in the form ss (2 digits)

Log File Format

Each time the program connects to the database system and issues the specified SQL statement a line is written to the Log file. The line has the format:

The delimiter (here semicolon) can be changed in the Options window. The fields in the log line are described below:

• Date: The current date in the form yyyy-mm-dd (for year, month, day)
• Hour: The hour where the test was performed
• Minute: the minute where the test was performed
• Second: the second where the test started
• Delay: number of seconds used to connect to the database, issue the SQL statement and disconnect from the database.
• Sequencenumber: a sequence number starting with one. The number is reset each time the program is started and increments each time a new log record is written go the file.
• Status: the number zero (0) if the connection was unsuccessful, the number one (1) if the connection was succesfull.
• Errormessage: this field contains the ODBC error message returned if the test was unsuccessful; otherwise the field contains the text OK.

Security

If your database requires you to enter a password it will be saved in the Windows registry in coded form. While it is not very likely that anyone looking directly into the registry will be able to use the coded password for anything, you should still consider the following before deciding to use the program on a workstation placed in an unsecured environment:

• A malicious user could open the options dialog in the DTracker program, enter his or her own (evil) SQL statement and let the program execute it (by pressing the Test button). The program would then re-code the password saved in the Windows registry and use it to access the database.

For this reason you should either not run the program on generally accessible terminals where security could be an issue or you should ensure that the userid in question do not have any dangerous permissions in the database system (a simple GRANT for SELECT on a single table would suffice).

Automatic start of the program

You can configure the program to start automatically each time Windows starts up by doing the following:

1. Place a shortcut to the program in the Windows Start folder. The easiest way to do this is to simply drag the DTracker icon from the Explorer display to the Start folder.
2. Set up the parameters. Do this by double-clicking the program icon in the Start folder and enter the relevant Run ID and the related parameters in the options. Specifically you should set the Run minimized flag.
3. Set up the correct Run ID for this automatic execution. Do this by clicking with the right mouse button on the icon in the Start folder. Choose Properties and select the Shortcut tab. The path should read something like:
   
   C:\MyDirectory\DTracker.exe
   
   Now add the name of the Run ID after the filename like this:
   
   C:\MyDirectory\DTracker.exe "MyrunID"
   
   Finally press OK to exit from the properties dialogs.

The program should now start and run iconized each time Windows is started. If you log information to a log file you should periodically archive the file and empty it.

You can set up any number of copies of the DTracker program in the Start folder, each with its own Run ID and option set. The program versions may share the same logfile if the timers are set to execute at different times.

Registry entries

Normally you would not want to fool around in the Windows registry, but just in case here is some information on where the program saves its parameters:

1. The Run ID names are saved in parameters named:
   HKEY_CURRENT_USER/Software/ISS_DATA/DTRACKER/Options/RunIdNumbern
   where n is a sequence number starting with zero.
2. The actual options for each Run ID are saved in parameters named:
   HKEY_CURRENT_USER/Software/ISS_DATA/DTRACKER/Optionsgggg/pppp
   where gggg is the Run ID and pppp is the actual parameter name.

Talking to the author

My name is Jorgen Grosbol. You can reach me at my E-mail address:

jorgengros@cybernet.dk

Please remember that this is a freeware program given to you as-is. That means that you are responsible for anything the program does if you use it. I really think it does exactly what this documentation says it does, but in the final analyses it is your responsibility and not mine, so do check the program carefully before trusting it.

Please write me if you have suggestions for improvement or if you find bugs or other problems in the program. While I cannot guarantee that I can answer all mail, I would still like to hear your comments on the program.