Database import
The Quick Queries module can be used to query events from a database. In that case, events must of course be stored in that database.
A script is provided under $PRODUCT_HOME/Tools/bin to enable the import of events into a database. Other mechanisms can be used, as long as events are stored in a single table where the column and columns names correspond to the slots and slot names (in their internal format) of the EVENT (or TopLevelClass as set in QuickQueries.conf) class.
The “Import” script: SQLimport.pl is a Perl script that exports latest event information from the cell and imports it in the SQL database. For the DB import to work correctly, this script must run periodically. After each execution, the script saves the time of the last execution in a file named .last_run_ts_<configname>. The next execution of the script will restart from the timestamp set in that file.
The script will consider events that have a mc_modification_time more recent than the last script execution. This slot is updated when slots listed in the mcell.modify configuration file of the cell are changed. Make sure that all relevant slots are referenced there.
Configuration
Additional parameters must be set in QuickQueries.conf ([SQLImportParameters] section) to configure database usage. You can use the ProactivePack console (ProactivePack Administration => Module Configuration => QuickQueries => Edit) to configure those parameters.
|
Parameter |
Description |
Default/Typical value |
|
[SQLImportParameters] |
||
|
NotesOpsParseAbortThreshold |
Maximum number of notes or operations within a single event that the Import Script should look for. Notes/operations above that threshold will not be stored. |
50 |
|
length_default |
Maximum length (in bytes) for string fields. During insertion, if the length of the field exceeds this value, the field value is truncated up to that value. Note that this general default length can be overridden, see other length_* parameters below. |
128 |
|
length_msg |
Maximum length (in bytes) for the message field. During insertion, if the length of the field exceeds this value, the field value is truncated up to that value |
512 |
|
length_severity |
Maximum length (in bytes) for the severity field. |
12 |
|
length_mc_original_severity |
Maximum length (in bytes) for the severity field. |
12 |
|
length_mc_priority |
Maximum length (in bytes) for the priority field. |
10 |
|
length_mc_original_priority |
Maximum length (in bytes) for the priority field. |
10 |
|
length_mc_long_msg |
Maximum length (in bytes) for the mc_ long_msg slot |
512 |
|
length_mc_notes_note |
Maximum length (in bytes) for the notes “note” field. |
512 |
|
length_mc_operations_text |
Maximum length (in bytes) for the operations text field. |
512 |
|
length_mc_operations_rule |
Maximum length (in bytes) for the notes “rule” field. |
128 |
|
length_mc_operations_action |
Maximum length (in bytes) for the notes “action” field. |
128 |
|
length_mc_operations_policy |
Maximum length (in bytes) for the notes “policy” field. |
128 |
|
length_<slotnamehere> |
You can specify your own length limits on string fields by adding entries of the format length_<slotname> |
N/A |
|
SourceCell |
Source cell for exports. The source cell is also queried by the generate script to obtain the list of event slots for the EVENT_REP_MAIN table |
N/A |
|
SendResultCell |
Name of the cell that will receive notification events after the execution of the Import Script (to indicate success and errors) |
N/A |
|
ExcludedClassesForImport |
Space separated list of event classes that are not relevant to store. |
MC_CELL_PROCESS_ERROR MC_CELL_TICK ADMIN_CONFIG_INFO |
|
NoFreezeOnFail |
In the case one or more event could not be imported into the DB, indicates if the next import should be performed normally (ignore error) or resume from the last successful import (in that case the assumption is that the issue will be fixed somehow) |
1 |
|
FirstRunTimeWindow |
The first time the Import Script runs, this parameter (expressed in seconds) determines how far back in the past the script goes to retrieve events and store them in the database. |
3600 |
|
SafetyTimeOverlap |
When the Import Script runs, it « starts » from the timestamp stored in the .last_run_ts file. The “SafetyTimeOverlap» parameter (expressed in seconds) is substracted from the last_run_ts to ensure that events created/modified around the export execution are not lost. |
120 |
|
LogResultFile |
Path to the logfile used by the import script |
"c:\temp\mysqlimport.log"
|
|
LogResultToFile |
Boolean (0 or 1) - Indicates whether execution results/messages are logged to a file |
1 |
|
LogResultFileSize |
Maximum size (in bytes) after which the logfile is rotated |
5242880 |
|
LogResultFileNumber |
Number of rotated logfiles kept |
5 |
|
SendResultAsEvent |
Boolean (0 or 1) - Indicates whether execution results/messages are sent to the cell specified in the “SendResultCell” parameter |
1 |
|
TruncationNoWarning |
Comma separated list of slots for which truncation at import does not cause a “WARNING” notification event being sent to <SendResultCell>. For those truncations and if everything else went ok, the notification event will be ok. If the value is set to “ALWAYS”, all truncation warnings will be disregarded. |
msg,mc_long_msg |
|
ResultEventClass |
The name of the event class used to notify the “SendResultCell” of execution results. Case sensitive. |
PROACTIVEPACK_DB_IMPORT_EV |
|
TolerateMissingColons |
A flag indicating if the import script will tolerate missing colons in the main event table. If the flag is set to no, import will fail if a slot does not have a corresponding column in the table. |
1 |
|
DynamicColumnCreation |
Allows the dynamic creation of columns in the events table in case new slots are created and must be reported against. Requires that the DBUser has ALTER TABLE rights on the EVENT_REP_MAIN table (which is by default the case) |
1 |
|
CleanupLogResultFile
|
Path to the logfile used by the DB cleanup utility |
../../log/ppack_db_cleanup.log"
|
|
CleanupLogResultToFile |
Boolean - Indicates whether cleanup execution results/messages are logged to a file |
1 |
|
CleanupLogResultFileSize |
Maximum size (in bytes) after which the logfile is rotated |
5242880 |
|
CleanupLogResultFileNumber |
Number of rotated logfiles kept |
5 |
|
DBEventsCleanupDays |
Age (in days) of events after which the events will be considered for deletion by the cleanup utility. If set to 0 or less, no cleanup will occur. |
0 |
Creating the database tables
Once the parameters in the previous sections have been correctly set, you can generate the SQL statements to create the schema, and optionally run those statements directly from the ProactivePack console.
The page should open on the SQL statements that are required to create the database, user, and tables:
From there you can download and save the contents of the page as a .sql file, and then apply it yourself on the database server using your preferred DBA tool (MySQL: Workbench, PHPMyAdmin,.. ; ORACLE: sqlplus, SQLDeveloper,PostgreSQL : pgadmin…).
Periodic DB import
The Import Script must be scheduled to run periodically. As the script invokes the TrueSight “mquery” and “mclassinfo” commands, these commands must be available to the script (as well as a properly set MCELL_HOME and mcell.dir).
The easiest option is to use the ProactivePack scheduler to schedule the import. For this, navigate to ProactivePack Administration -> Modules -> QuickQueries and click on the “Schedule DB import” button.
Then define the desired schedule. We recommend to use a 30 minutes period between each execution of the script, unless event volumes are particularly high and/or reports must be run in “near real time” against the DB, in which case you may want to set a shorter period. The Import Script remembers its last execution time, so running the script at irregular time intervals, although not advised, does not disrupt the export.
The script can manually be invoked using
perl SQLimport.pl <config file name>
E.G. :
perl SQLimport.pl ../../QuickQueries.conf
i There is no housekeeping mechanism for the database – events will just be added as they arrive.
To monitor database usage, select the “DB Information” action in the Administration -> Modules -> QuickQueries menu, to obtain a report like this:
Cleanup utility
A cleanup utility can be used to delete old event data from the QuickQueries database. The utility uses the configuration parameter DBEventsCleanupDays (see section) to prune the database based on the age (mc_date_modification) of the events.
The utility works either interactively (manual confirmation of the operation) or automatically if supplied with the “-y” switch.
The utility is located under $PRODUCT_HOME/tools/bin.
SQLcleanup.pl [-h] [-y] <configFile>
-h This message
-y Force the script to work automatically. Else, it runs interactively
<configFile> Path to the configuration file QuickQueries.conf