Using Snippets to Collect Database Data

Snippets can be used to implement a variety of collection methods, including those that are built into SL1, such as Database collection. Although it is more convenient to use the built-in collection methods where possible, there are use cases when using a snippet Dynamic Application for database collection is appropriate. This section walks through snippet code that:

• Performs an SQL query to retrieve the data retention settings from a SL1 System. This data is stored in the master.system_settings_core table. The table includes a column for each system setting and a single row that specifies the values for those system settings.
• Appends a suffix to each data retention value.

Use the following menu options to navigate the SL1 user interface:

• To view a pop-out list of menu options, click the menu icon ().
• To view a page containing all of the menu options, click the Advanced menu icon ().

Creating the Dynamic Application

To create this example Dynamic Application, perform the following steps:

1. Go to the Dynamic Applications Manager page (System > Manage > Dynamic Applications).
2. Select the Actions button, then Create New Dynamic Application. The Create New Application page is displayed.
3. Supply values in the following fields:
• Application Name. The name of the Dynamic Application. This example is called "Snippet Database Example".
• Application Type. This example is a Snippet Configuration Dynamic Application. Select Snippet Configuration in this field.
• Poll Frequency. To see data as quickly as possible, select Every 1 Minute in this field.
4. This example does not have specific requirements for the other settings defined in this page. You can leave the remaining fields set to the default values. Select the Save button.

Creating a Container for the Snippet

Because collection objects are assigned a specific snippet, you must create the container for the snippet code before creating the collection objects for this Dynamic Application. To create a container for the snippet code, perform the following steps:

1. Select the Snippets tab.
2. Supply values in the following fields:
• Snippet Name. The name of the snippet. The snippet in this example is called "Database Query".
• Active State. To ensure that the snippet code is run by SL1, select Enabled in this field.
• Required. Specifies whether this snippet is required for successful collection of all other snippet requests. Select Required - Stop Collection. If this snippet request fails, the platform will not attempt to execute any other snippet requests in this Dynamic Application. Dynamic Applications that consume the cache of this Dynamic Application will halt collection.
• Snippet Code. Leave this field blank. You will add the snippet code later.
3. Select the Save button.

Creating the Collection Objects

The snippet code for this example will retrieve data from a database and populate the result_handler dictionary with the retrieved values. The collection objects will be the names of columns in the database. The snippet code will construct the database query and process the results using the arguments provided for each collection object, i.e. new or additional fields can be added as collection objects without editing the snippet code. This example includes collection objects for the following database fields:

• records_perf. The number of days for which raw performance data is retained.
• records_dev_logs_max. The maximum number of device logs that should be stored.
• records_access_logs. The number of months for which access log data is retained.

These three fields are included in this example were chosen to illustrate the three different ways the snippet code processes the collected values. You can add collection objects for the other data retention settings as desired. The column names for retention have the prefix "records_".

To create the collection objects for this Dynamic Application, perform the following steps:

1. Select the Collections tab in the Dynamic Application Editor pane.
2. Supply values in the following fields to create the first object, which will collect the values from the records_perf column of the database:

• Object Name. The name of the collection object. Enter "Raw Performance Data" in this field.
• Snippet Arguments. The arguments to pass to the snippet. The snippet code expects the arguments for each collection object to be a field name in the master.system_settings_core table. Enter "records_perf" in this field.
• Snippet. There is only one snippet for this Dynamic Application. Select Database Query in this field.

3. For the remaining fields, accept the default values. Select the Save button, then select the Reset button to clear the values you entered.
4. Repeat steps two and three to create the collection object that will collect the values from the records_dev_logs_max column of the database:

• Object Name. Enter "Device Logs Max".
• Snippet Arguments. The arguments to pass to the snippet. The snippet code expects the arguments for each collection object to be a field name in the master.system_settings_core table. Enter "records_dev_logs_max" in this field.
• Snippet. There is only one snippet for this Dynamic Application. Select Database Query in this field.
5. For the remaining fields, accept the default values. Select the Save button, then select the Reset button to clear the values you entered.
6. Repeat steps four and five to create the collection object that will collect the values from the records_access_logs column of the database:

• Object Name. Enter "Access Logs".
• Snippet Arguments. The arguments to pass to the snippet. The snippet code expects the arguments for each collection object to be a field name in the master.system_settings_core table. Enter "records_access_logs" in this field.
• Snippet. There is only one snippet for this Dynamic Application. Select Database Query in this field.
7. For the remaining fields, accept the default values. Select the Save button.

Snippet Code Walkthrough

The snippet code for this example starts by initializing three variables:

table = "master.system_settings_core"

fields = []

results = {}

• The snippet will be retrieving data from the table system_settings_core, in the database master. The table name is stored in the variable table.
• The snippet will use fields to store a list of database fields that will be included in the database query.
• The snippet will use the results to store the dictionary that will be passed to the result handler.

Next, the snippet iterates through the key values from the result_handler dictionary. The keys in the result_handler dictionary are the arguments supplied for each collection object. In this example, the arguments are the field names from the master.system_settings_core table. The field names are added to the list of fields and used to initialize the results dictionary with empty lists:

for collection in result_handler.iterkeys():

fields.append(collection)

results[collection] = []

The list of database fields is then formatted as a comma-delimited string in the variable field_list:

field_list = ','.join(fields)

Next, the snippet retrieves a database cursor using the dbc_from_cred_id method. This method is called in a try/except/else block, which will catch both types of exceptions raised by the db_from_cred_id method (ValueError and RuntimeError). If an exception is raised, the COLLECTION_PROBLEM and PROBLEM_STR variables are used to report the problem in the device log for the subscriber device:

try:

dbc = em7_snippets.dbc_from_cred_id(self.cred_details['cred_id'])

except (ValueError,RuntimeError), e:

COLLECTION_PROBLEM = True

PROBLEM_STR = e

If the db_from_cred_id method does not generate an exception, the snippet executes an SQL query using the field_list and table variables to construct a SELECT statement. The query is called in a try/except block similar to the previous section of code:

else:

sql = "SELECT %s FROM %s;" % (field_list,table)

try:

dbc.execute(sql)

db_result = dbc.fetchall()

except Error, e:

COLLECTION_PROBLEM = True

PROBLEM_STR = e

NOTE: Remember that the variable field_list contains a list of all key values in the result_handler dictionary. Remember that the variable table contains the value master.system_settings_core.

If the query executes successfully, the following block of code processes and stores the collected values:

else:

i = 0

for row in db_result:

for index, collection in enumerate(fields):

if collection == "records_dev_logs_max":

result_tuple = (i, str(row[index]) + " Records")

elif row[index] % 30 == 0 and row[index] != 30:

result_tuple = (i, str(row[index] / 30) + " Months")

else:

result_tuple = (i, str(row[index]) + " Days")

results[collection].append(result_tuple)

i += 1

result_handler.update(results)

The counter i is initialized at 0 and is incremented after each row returned by the query is processed. This counter is used to assign index values to each returned value. However, the master.system_settings_core table contains only one row, so this example returns only one index (0). This counter, and the iteration over each row in the result set, is included in this example so that the snippet code can be re-used for other database tables with only minor changes.

For each row in the result set, the snippet code iterates over the fields list. For each field, collection is the field name (also the snippet argument for the corresponding collection object) and index is the list index in the fields list. Because the fields list was used to construct the database query, the list indexes for fields match the indexes in each row.

For each field, the snippet determines the appropriate suffix:

• If the field is "records_dev_logs_max", the suffix is "Records". The retention setting for the maximum number of device logs is the only retention setting (fields with the prefix "records_") that is not stored as a number of days.
• All other retention settings are stored as a number of days. If the number of days is evenly divisible by 30 and greater than 30, the suffix is "Months".
• For all other values, the suffix is "Days".

The result_tuple variable is populated with the index (the value of the counter i) and a string value for the collection object. The tuple is then appended to list in the results dictionary that corresponds to the collection object.

Because results is a dictionary of lists that contains the same keys as the result_handler dictionary, we can return the collected data to SL1 using the update function for the result_handler dictionary.

Adding the Snippet Code

To enter the Snippet code:

• Go to the Dynamic Applications Manager page (System > Manage > Dynamic Applications).
• Find the example Dynamic Application, Example Database Collection. Select its wrench icon ().
• Select the Snippets tab.
• In the Snippet Registry pane, find the Database Query snippet and select its wrench icon ().
• In the Snippet Code field, enter the following code

table = "master.system_settings_core"

fields = []

results = {}

for collection in result_handler.iterkeys():

fields.append(collection)

results[collection] = []

field_list = ','.join(fields)

try:

dbc = em7_snippets.dbc_from_cred_id(self.cred_details['cred_id'])

except (ValueError,RuntimeError), e:

COLLECTION_PROBLEM = True

PROBLEM_STR = e

else:

sql = "SELECT %s FROM %s;" % (field_list,table)

try:

dbc.execute(sql)

db_result = dbc.fetchall()

except Error, e:

COLLECTION_PROBLEM = True

PROBLEM_STR = e

else:

i = 0

for row in db_result:

for index, collection in enumerate(fields):

if collection == "records_dev_logs_max":

result_tuple = (i, str(row[index]) + " Records")

elif row[index] % 30 == 0 and row[index] != 30:

result_tuple = (i, str(row[index] / 30) + " Months")

else:

result_tuple = (i, str(row[index]) + " Days")

results[collection].append(result_tuple)

i += 1

result_handler.update(results)

Using the Dynamic Application

The Dynamic Application in this example can be aligned with any ScienceLogic Database Server or All-In-One Appliance.

Define the Credential

To use the example Dynamic Application, you must first create a Database credential to align with the Dynamic Application. Perform the following steps to create a new Database credential:

1. Go to the Credential Management page (System > Manage > Credentials).
2. Select the Actions menu, then select Create Database Credential. The Credential Editor page is displayed.
3. Supply values in the following fields:

• Profile Name. Name of the profile. Can be any combination of alphanumeric characters.
• DB Type. Select MySQL.
• DB Name. Name of the database that will be accessed with the credential. Enter master.
• DB User. Username associated with a valid account on the database. For Database Servers and All-In-One Appliances, this is typically the root user.
• Password. Password associated with the DB User account.
• Hostname/IP. Hostname or IP address of the Database Server or All-In-One Appliance. If you are aligning this Dynamic Application to a Database Server or All-In-One Appliance that is already discovered in your system, enter "%D" in this field to use the IP address associated with the device record.
• Port. Enter "7706".

4. Select the Save button.

Align the Dynamic Application with a Device

To align this Dynamic Application to a device, perform the following steps:

NOTE: If you entered "%D" in the Hostname/IP field for your credential, you must align the Dynamic Application to the device record for a Database Server or All-In-One Appliance.

1. Go to the Device Manager page (Devices > Classic Devices, or Registry > Devices > Device Manager in the classic SL1 user interface).
2. Select the wrench icon () for the device you want to align the Dynamic Application with. The Device Properties page is displayed.
3. Select the Collections tab. The Dynamic Application Collections page is displayed.
4. Select the Action menu and choose Add Dynamic Application.
5. In the Dynamic Application Alignment modal page, select our example Dynamic Application, Snippet Database Example. Select the credential you created in the previous section. Select the Save button.
6. The page refreshes, and the Snippet Database Example Dynamic Application is displayed in the list of Dynamic Applications.

Viewing Data

The first collected value will be stored within one minute. To view the label data for this Dynamic Application:

1. Go to the Device Manager page (Devices > Classic Devices, or Registry > Devices > Device Manager in the classic SL1 user interface).
2. Select the graph icon () for the device you aligned the Dynamic Application with. The Device Summary page is displayed.
3. Select the Configs tab. The Configuration Report page is displayed.

4. In the left NavBar, select Snippet Database Example. The collected data is displayed.