Saving data in a database

The current Big Brother server can save various data into a database and allow a customer to do post processing with the saved data.

The Big Brother server database component provides standard ODBC operation. It supports Oracle, mySQL and SQL Server at this moment. You (or someone within your organization that handles the databases) need to install a supported database and set up the ODBC running environment. The database can be installed on the Big Brother local server or any remote host that the Big Brother server can reach.

 

If you use SQL Server, make sure that the user defined for Big Brother access to the database is given permission as "Grant" on connect, insert, delete, update, select and execute.

 

1. Creating tables in the database

 

After a database is installed and configured, you  need to create the Big Brother required tables in the database for data storage. To do this, Big Brother provides three scripts in the $BBHOME/etc/database directory: (where $BBHOME is the root of the BB installation)

• mysql_createtable.sql – create BB required tables in MySql database.

• oracle_createtable.sql – create BB required tables in Oracle database.

• sqlserver_createtable.sql – create BB required tables in SQL Server database.

 

 

2. Setting up ODBC Environment

 

To provide Big Brother database functionality, customers are required to install and configure an ODBC manager and driver on the Big Brother server host:

 

For the Windows platform, the ODBC manager comes standard on a Windows installation. You need to find and install the ODBC driver for the installed database and configure the data source:

• Install the ODBC driver.

• Open Control Panel -> Administrative Tools -> Data Source (ODBC).

• Select “System DSN”.

• Click the “Add” button.

• From the driver list, select a driver that supports your database.

• Click the “Finish” button.

• In the pop up dialog box, set up the Data Source and the required parameters.

• Click the “Test Connection” button to make sure the database can be connected.

• Click “OK” to finish the data source setup.

 

 

3. Enabling the Big Brother Database feature

 

By default, Big Brother saves data in flat files.  To indicate to Big Brother should save the data in flat files or a database  or both, edit the bbdef.cfg file and define either SAVE_IN_FILES and/or SAVE_IN_DB in the RUNOPTS setting.  If none are defined in RUNOPTS then SAVE_IN_FILES will apply.  You also have the choice to define which data store will be used for input by Big Brother.  If you want to use data from flat files, specify USEFILES in RUNOPTS, otherwise define USEDB to use a database.  If both are defined then USEFILES has precedence.  Note that USEFILES by itself implies SAVE_IN_FILES and the same applies to USEDB which implies SAVE_IN_DB.

 

e.g. in bbdef.cfg

RUNOPTS="USEDB SAVE_IN_FILES SAVE_IN_DB"

 

This indicates to Big Brother to save the data in both a database and to flat files and use the database as input for processing.

 

The $BB_HOME/etc/bbdb.cfg file is the configuration file for the Big Brother Database feature:

 

#

# bbdb.cfg

#

# Big Brother Professional Edition Server

# Version 4.20

#

# Database configuration file for Big Brother Professional Edition Server

#

# This program is Copyright (c) 1997-2009

# Quest Software Inc.

# All Rights Reserved

#

 

# if you enable database export, you need to set:

#

# data source name

#

DATA_SOURCE="myodbc"

 

# and an account for the database operation:

# user name

#

USER_DB="dbuser"

 

# and password

#

PASSWORD_DB="dbpasswd"

 

# By default, data that causes a status change will be saved into

# database. However, if you

# like to save all incoming data into database, you can set

SAVEALL_DB="no"

 

# A BB Server name can be defined in database so when multiple BB Servers share a database,

# the collected data can be recognized from different BB Servers. If BBSVR_NAME is not

# defined, the BB Server machine name will be used as default.

#BBSVR_NAME=""

 

 

4. Table Structure in Database

Currently, there are eight tables are used for Big Brother data storage: bbserverstate, bbservers, bblogdata, bbcurrentlog, bbstatuschangedata, bbperfdata, bbrecovery and bbacklog.

 

• bbserverstate table: This table stores the current of  the Big Brother server (starts/stops). It has the following columns:

id Record id

bbserver_id BB server name ID (maps to a name in the bbservers table)

bbstate 1: BB server starts, 0: BB server stops

changetime Time when bbstate changes, i.e., time for BB server starts/stops.

 

• bblogdata table: If “SAVEALL_DB” is set to “yes’ in the bbdb.cfg file, this table stores the all incoming status data(not just when a color state changes as in bbstatuschangedata). It has the following columns:

id Record id

bbserver_id BB server name ID

hostname The host name of the status data

servicename The service name of the status data

currenttime Current time

color The presented color of the status data

bbcontent The status data contents

receivedfrom The host ip of the status data

 

• bbcurrentlog table: This table stores the latest status data only (simulates bbvar/logs). It has the following columns:

id Record id

bbserver_id BB server name ID

hostname The host name of the status data

servicename The service name of the status data

currenttime Current time

color The presented color of the status data

bbcontent The status data contents

receivedfrom The host ip of the status data

expirytime Time at which the status log is considered invalid (expired)

 

• bbstatuschangedata table: This table stores the status data that cause the status (color) change (mimicks bbvar/hist/allevents). It has the following columns:

id Record id

bbserver_id BB server name ID

hostname The host name of the status data

servicename The service name of the status data

lasttime The time when the previous status (color) started

currenttime Current time

oldcolor Previous color

newcolor Current color

bbstate            -1: first time, no previous state

0: state not changed

1: state recovered from warn/alarm

2: state escalated.

bbcontent The status data contents

duration The duration (seconds) of the previous status (color) lasted

 

• bbrecovery table: This table stores the information when a service status is back to normal (similar to bbvar/acks/recoveries). It has the following columns:

id Record id

bbserver_id BB server name ID

hostname The host name of the status data

servicename The service name of the status data

currenttime Current time

downduration The duration (seconds) of the service that was in warn/alarm state.

 

• bbacklog table: When an acknowledgement to a notification is set on the BB page, the related information is stored in this table (bbvar/acks/acklog). It has the following columns:

id Record id

bbserver_id BB server name ID

currenttime Current time

npacknumber BB internal use

ackdelay The delay time (minutes) set in the acknowledgement

acknumber The 7 digits acknowledgement number in the notification

npfilename BB internal use

hostandservice Host and service the notification is sent for

color Color of the notification that is acknowledged

ackmessage The message set in the acknowledgement

 

bbperfdata table: Contains the trending data provided by tests

bbserver_id BB server name ID

location Location name associated with the entry (if a test was associated with a location)

hostname The host name of the status data

servicename The service name of the status data

instancename The name of the instance (ie a drive letter)

instanceobject  The object name associated with the instance (ie %used)

logtime Time in UNIX format

logdatetime

logcolor Color associated with the servicename

instancecolor Color associated with the instance and instance object

objectvalue Value of the object (integer/float)

logdataid Link to the id of the log entry that provided the trending data (if SAVEALL_DB="yes" in bbdb.cfg)

 

bbservers table: Maps IDs to BB server names (as multiple BB servers could use the same Database for data store)

bbserver_id BB server name ID

bbserver BB server name

 

 

 

There's also a tool to import existing BB data.  It's called dbimport and is located in BBHOME/bin.

 

 

Set BB Server Host, BBVAR directory, Data Source, DB User name and password, click "Import".