Assure1 Event Generic DB Aggregator¶

The Assure1 Event Generic DB Aggregator is a generic integration that connects to a database and executes a query to collect rows of data. The results are then parsed with customizable rules and de-duplicated events are created within Assure1.

Generic DB Aggregator Setup¶

Review the query in the SelectSQLFile file referenced in the configuration to see the data that will be selected for processing. Update the query as needed.
Review the logic in the rules files referenced in the configuration to see the processing that will be done on the events that are selected:
- LoadRules will be executed during application startup to load data that might be needed during processing.
- IncludeRules will be read during application startup to load additional files that might be called during processing.
- BaseRules will be executed for each event that is selected from the query above.
Update the logic as needed.
Enable the default Service, unless a specific configuration option is needed:

Configuration -> Broker Control -> Services

Default Service¶

Field	Value
Package Name	coreCollection-app
Service Name	Event Database Aggregator
Service Program	bin/core/collection/GenericDBd
Service Arguments
Service Description	Database Aggregator that reads event lines from results of a sql query
Failover Type	Standalone (Supported: Standalone, Primary/Backup)
Status	Disabled
Privileged	(Checked)

Default Configuration¶

Name	Value	Possible Values	Notes
BaseRules	collection/event/db/base.rules	Text, 255 characters	Relative path to Base Rules.
BranchDir	core/default	Text, 255 characters	relative path to Rules dir.
IncludeRules	collection/event/db/base.includes	Text, 255 characters	Relative path to Include Rules.
LoadRules	collection/event/db/base.load	Text, 255 characters	Relative path to Load Rules.
LogFile	logs/EventDatabase.log	Text, 255 characters	Relative path to Log File.
LogLevel	ERROR	OFF, FATAL, ERROR, WARN, INFO, DEBUG	Logging level used by application.
PollTime	60	Integer	How often aggregator will poll the database in seconds.
SelectSQLFile	collection/event/db/lookup.sql	Text, 255 characters	Path to SQL file containing database query, relative to $BranchDir.
ShardID	1	Integer	Database shard to be used.
SourceSchemaName		Text, 255 characters	Database Schema name matching an entry in the Databases configuration; Connects and supports failover as per that configuration. Cannot be used in conjunction with "DatabaseID".
Threads	3	Integer	Number of process threads created. The aggregator takes a third of this value (rounded up) for database threads unless overridden by the "DBThreads" application configuration.
Capture	Disabled	Enabled/Disabled	Optional - If enabled, saves the raw message in the Log.
DatabaseID		Text, 255 characters	Optional - Specific Database entry in the Databases configuration. Connects but does not support failover. Cannot be used in conjunction with "SourceSchemaName".
DBThreads		Integer	Optional - Number of database threadsto be created. If not specified, defaults to a third (rounded up) of "Threads" application configuration.
DSN		Text, 255 characters	Optional - Details the database connection information and driver. DEPRECATED - Use SourceSchemaName or DatabaseID
FieldSetFile		Text, 255 characters	Optional - Path to csv file containing custom list of fields that will be used when inserting data. (Requires InsertSQLFile.)
InsertSQLFile		Text, 255 characters	Optional - Path to file containing custom SQL Insert statement for handling of event inserts. (Requires FieldSetFile.)
Password		Text, 255 characters	Optional - Database login password. DEPRECATED - NOT ENCRYPTED, Use the Database configuration password
Username		Text, 255 characters	Optional - Database login username. DEPRECATED - Use the Database configuration username

Best Practices¶

The following list shows you the best practices for working with this application:

When connecting to a non-Assure1 database, the recommended approach is to create an entry in the Databases UI (Configuration -> Databases -> Databases), however, if a particular database type is not available, see the "FreeTDS Configuration and Use" documentation for additional examples using FreeTDS to connect to other databases.
When configuring the application settings, if a particular database type can not be configured on the "Databases" UI, the configuration can use the "DSN", "Username", "Password" and other settings, but several of these have been marked as DEPRICATED within the application.

Rules¶

This collector uses the Assure1 standard rules architecture, which are 100% Perl syntax. Refer to the following articles to assist in rules creation:

Tokens¶

The aggregator exposes the following tokens for rules processing.

Token	Description
$Event	Reference to the hash that is used to create and insert the Event data into the database. Keys map to the fields within the table used and values assigned are inserted in the database to that field. (e.g. $Event->{'IPAddress'} = '192.0.2.1' to assign the event IP address to '192.0.2.1') At least the 'Node' and 'Summary' fields must be set, or no event is inserted.
$EventData	Resulting data from query. Use $EventData->{'FieldName'} to access the "FieldName" data.
$discard_flag	Flag for discard (0 = No, 1 = Yes)
$AppConfig	Hash reference to the application configuration name-value pairs that were configured. (i.e. use $AppConfig->{'Host'} to retrieve the set value for 'Host'.)
$CustomHash	Custom key, value cache available across all rules. Contents commonly defined in Load Rules then used in Base or other rules. NOTE: This variable is a shared object and any additional sub hashes or arrays must be shared before use or it will cause the error: "Invalid value for shared scalar". Instantiate the sub hash/array using '&share({})' e.g. $CustomHash->{SubObject} = &share({});
$StorageHash	Internal cache used as the StorageHash option when calling rules functions such as FindDeviceID(). NOTE: The structure of this cache is subject to change! Not recommended for custom global storage or manual manipulation; use $CustomHash.

Example Integration¶

In this example, the aggregator will connect to a MS SQL Server on a VMWare VCenter Server.

Go to the Databases UI:

Configuration -> Databases -> Databases
Create a new entry with the following options set:
- Database Name => VMWare VCenter
- Database Type => MS SQL Server/Sybase
- The rest of the settings should be set as needed in the environment.
- Click Submit.
Go to the Rules UI:

Configuration -> Rules
Expand the folder path: core -> default -> collection -> event
Select the event folder, then click on the Add -> Add Folder button. Enter the folder name vcenter, then click Submit. Enter a commit message, then click OK.

Click on the vcenter folder, then click Add -> Add File. Enter the following:

File Name => vcenter.sql

Logic

    SELECT T.DESCRIPTIONID  AS Description, 
           DATEDIFF(s, '1970-01-01 00:00:00', T.QUEUE_TIME) AS EventTime, 
           T.USERNAME       AS UserName, 
           VM.IP_ADDRESS    AS IP, 
           VM.DNS_NAME      AS DNS
      FROM [VIM_VCDB].[dbo].[VPX_TASK] AS T
 LEFT JOIN [VIM_VCDB].[dbo].[VPX_VM]   AS VM 
        ON T.ENTITY_ID = VM.ID
     WHERE TOOLS_STATUS != 0

Click Submit, then enter a commit message, then click OK.

Click on the "vcenter" folder, then click "Add -> Add File". Enter the following:

File Name => vcenter.rules

Logic

$Log->Message('ALWAYS', "Running Rules");

$Severity = 1;

if( $EventData->{Description} =~ "shutdown" ){
    $EventType = "VMState - Down";
    $Severity  = 5;
}
elsif( $EventData->{Description} =~ "PowerOn" ){
    $EventType = "VMState - Up";
    $Severity  = 0;
}
$Event->{'EventType'}     = $EventType;
$Event->{'FirstReported'} = $EventData->{EventTime};
$Event->{'IPAddress'}     = $EventData->{IP};
$Event->{'Node'}          = $EventData->{DNS};
$Event->{'OwnerName'}     = $EventData->{UserName};
$Event->{'Severity'}      = $Severity;
$Event->{'SubMethod'}     = "VCenter Events";
$Event->{'Summary'}       = $EventData->{Description};
$Event->{'EventKey'}      = $Event->{'SubMethod'} . $Event->{'Node'} . $Event->{'EventType'} . $Event->{'Summary'};

Click "Submit", then enter a commit message, then click "OK".

Click on the "vcenter" folder, then click "Add -> Add File". Enter the following:
- File Name => vcenter.includes
- Logic
```
# this file is intentionally being left blank.
```
- Click "Submit", then enter a commit message, then click "OK".
Click on the "vcenter" folder, then click "Add -> Add File". Enter the following:
- File Name => vcenter.load
- Logic
```
# this file is intentionally being left blank.
```
- Click "Submit", then enter a commit message, then click "OK".
Go to the Services UI:

Configuration -> Broker Control -> Services
Select the "Event Database Aggregator", then click the "Clone" button. Set the following:
- Service Name => VCenter Event Database Aggregator
- Status => Enabled
- BaseRules => collection/event/vcenter/vcenter.rules
- IncludeRules => collection/event/vcenter/vcenter.includes
- LoadRules => collection/event/vcenter/vcenter.load
- SourceSchemaName => VMWare VCenter
- Click "Submit".
Verify the service starts and events are received.

Administration Details¶

The following list shows you the technical details you’ll need for advanced administration of the application:

Package - coreCollection-app
Synopsis - ./GenericDBd [OPTIONS]

Options:

 -c, --AppConfigID N   Application Config ID (Service, Job, or Request ID)
 -?, -h, --Help        Print usage and exit

Threaded - Multi-Threaded