User Manual

 


Define MySQL database lookups in Splunk Web

Define MySQL database lookups in Splunk Web

You can configure MySQL-database-backed lookups through Splunk Web. You'll follow these steps to do this in Manager:

  • Create a database spec with the Database specs page.
  • Optionally use the Browser view to explore your MySQL databases to find specific database tables.
  • Create a MySQL database-backed external lookup using the Lookup definitions page.
  • Optionally create automatic MySQL lookups with the Automatic lookups page.

Create a database spec for your MySQL database

Database specs enable Splunk MySQL Connector to interface with different MySQL databases. You need to define a database spec for each MySQL database that you need Splunk to interact with. A database spec is a set of properties that are necessary to define a MySQL database connection. Each database spec includes the host, port, name, and access username/password for a specific database server. You cannot create an external lookup for a MySQL database without this information.

Db spec details.png

Follow these instructions to create a new database spec or update an existing database spec in Manager:

1. In the Splunk MySQL Connector app, navigate to Manager > Database specs. From there you can click New to create a new spec or edit an existing spec.

2. Give the spec a unique, easily identifiable Db spec name, and then provide:

  • the database server Host, such as localhost.
  • the TCP Port where the database server is listening for connections, such as 3306.
  • the database Schema to connect to - this is also known as the database name in MySQL terms, such as splunkdb.
  • the Username and Password for database authentication. We recommend that you use a username/password for a user who has restricted access to the database server to avoid security issues. Also, it's important to note that the password is stored in cleartext.
  • the Lookup batch size, which is the number of records to batch in a single lookup SELECT query, such as 100.
  • the Insert batch size, which is the number of records to batch in a single INSERT query when adding/updating records, such as 100.

Note: There is no "standard" lookup batch size or insert batch size; the appropriate value will vary depending on a number of factors. If the values of the fields that you are looking up are large, this could result in extremely large queries that might be rejected by the server (depending on the max query size setting on the server). Similarly, if the query batch size is too small, you could have a lot of undersized queries being sent to the MySQL database, which could lead to a waste of time when it comes to query parsing and reading.

3. Click Save to save your new or updated database spec.

4. If necessary, edit the permissions of the database spec to properly set sharing and control access for others (the spec will only be available to you when you first create it). For more information about permissions and knowledge object sharing, see "Curate Splunk knowledge with Manager" in the Knowledge Management Manual.

Use the Browser view to investigate your MySQL databases

Once you have one or more valid database specs created, you can use the Browser view to quickly review your MySQL databases and drill down through their specs to find tables that you might want to use for a MySQL database lookup.

Note: You cannot use the Browser view if you have not defined at least one valid MySQL database spec. See the procedure above for details on how to configure database specs.

For more information about the Browser view, see the topic "Investigate MySQL databases with the Browser view," in this manual.

Create an external MySQL database lookup

After you create a database spec for a MySQL database server, you can easily define an external database lookup in Manager and tie it to that spec (or any other database spec that you have access to). This "database lookup definition" is similar to the external fields lookups that you can define by navigating to Manager > Lookups > Lookup definitions, except that you don't need to define a Command or any arguments, because the database spec takes care of that. (For more information about standard external fields lookups, see "Look up fields from external data sources" in the Knowledge Manager Manual.)

Db lookup definition.png

To create a MySQL database lookup:

1. In the Splunk MySQL Connector app, navigate to Manager > Database lookup definitions. From there you can click New to create a new database lookup definition, or you can review the details of an existing database lookup definition by clicking its name in the list. To edit the database lookup definition, you will have to go to the Lookup definitions page in Manager we mention above.

2. If you're creating a new database lookup definition, give it a unique, easily identifiable Name. Note that you won't be able to change this name through Manager after you save your new lookup definition. You can adjust it in transforms.conf however. For more information see "Define MySQL database lookups with configuration files" in this manual.

3. Tie the database lookup definition to the Database spec that you defined earlier by selecting it from the list.

4. In Supported fields, enter a comma-delimited list of the fields that are in the database table. These are the fields upon which lookups can be performed.

5. If your table doesn't already exist in the MySQL database server, select Create the database table to have Splunk create it for you.

6. How you use Table name depends on whether you are performing a lookup on an existing database table or if you are creating a new table with the Create database table checkbox.

  • If you are connecting to an existing database table enter the name of that table in Table name.
  • If you are creating a new database table for this lookup, enter the name you want the table to have in this field.

If you leave Table name blank, by default Splunk will name the table lookup_<name>, where <name> is the name you've given this database lookup definition.

7. If you have selected Create database table you need to provide a Primary key. If you provide a primary key be sure that it is also included in the Supported fields list.

8. Click Save to save your new or updated database lookup definition.

9. If necessary, edit the permissions of the database lookup definition to properly set sharing and control access (the database lookup definition will only be available to you when you first create it). Its permissions should match those of the database spec that is associated with it. For more information about permissions and knowledge object sharing, see "Curate Splunk knowledge with Manager" in the Knowledge Management Manual.

Editing a database lookup definition

Database lookup definitions are essentially a kind of lookup definition. After you use the Database lookup definition page to create a database lookup definition, you will have to go to the Lookup definition page to edit it. Navigate to Manager > Lookups > Lookup definitions and click on the name of the database lookup definition that you want to edit. Remember, you can only edit lookup definitions when you have write permissions for them (see Step 9 of the database lookup creation procedure, above).

Create an automatic lookup

Now that you've defined a database lookup definition that is tied to a valid database spec, you can invoke it manually with the lookup search command to add information sourced from that database to the events returned by a particular search. But it's far more efficient to configure Splunk to run the lookup automatically whenever you perform a search. You set up automatic database lookups in Manager the same way you do for standard lookups: through the Automatic lookups page.

Note: This is equivalent to defining the lookup in transforms.conf. For more information about that, see the subsection "Defining database-backed lookups with configuration files," below.

Db autolookup.png

1. From the MySQL Connector app, navigate to Manager > Lookups > Automatic lookups. From there you can click New to create a new automatic lookup, or edit an existing automatic lookup.

2. Give your automatic lookup a unique, easily identifiable Name.

3. Tie the automatic lookup to the database lookup definition that you defined earlier by selecting it from the Lookup table list.

4. Under Apply to and named, you need to associate the automatic lookup with a specific host, source, or source type. For example, if you're dealing with website access events, you might choose a source type of access_combined_wcookie.

For more information about the host, source, and sourcetype fields in your events, see "About default fields" in the Getting Data In Manual.

5. Under Lookup input fields you enter the fields that you are trying to match in the MySQL database.

For example, say you're a website administrator who would like to track the first and last visits for each registered user of your site. Although your site visit events have user ID values, they don't have this first/last visit information. But you do have access to a MySQL database table whose records have "first time visited" and "last time visited" values for each user ID associated with your site. All you have to do is match up the User ID field in your events with the records in your MySQL database that share those User ID values, along with corresponding "first site visit" and "last site visit" values for each User ID. The user ID field in your events is called userid, and the user ID field in the MySQL database is called usr_ID.

You start by matching the value of the userid in each of your events with the usr_ID value in the database. If the value of userid is 0345 in your event, you need Splunk to find the user_ID of 0345 in the database. To set this action up, in Lookup input fields you would enter userid = usr_ID.

6. Under Lookup output fields, you are doing the opposite of the last step: you are identifying the MySQL database field/value combination that you want to add to your event data. This is a field/value combo that corresponds with the input field that Splunk matches in the MySQL database (see step 5).

To continue the example in step 5: Say your event has an userid value of 0345, and that this matches the usr_ID field's value of 0345 in the MySQL database.

In the database, that specific usr_ID field/value combination corresponds to a first_visit_time field with a value of 04/14/2011 4:35:34 pm. You want to add that field/value combination to the event with a userid value of 0345, but you want the field to be called first_site_visit.

To arrange this, in Lookup output fields, enter first_visit_time = first_site_visit. With this mapping, Splunk adds 04/14/2011 4:35:34 pm to the original event (with a userid of 0345), as a value of a new field called first_site_visit. This mapping ensures that first site visit timestamps are added to all web access events that have a valid userid value.

7. Select Overwrite field values only if the field that you are mapping to your event data from the lookup database already exists in your events and you want to replace it with the database values. If you are adding a new field, do not select this.

8. Click Save to save your changes.

From here on out, as Splunk indexes events with a userid field, it will add corresponding first_site_visit and last_site_visit field/value combinations.

Use the lookup comand

You can also further enrich your results by using the lookup command to manually invoke lookups that have been properly defined in Manager. For more information about how to do this see the Search Reference topic on the command.

This documentation applies to the following versions of MySQL: 1.0 , 1.0.1 View the Article History for its revisions.


You must be logged into splunk.com in order to post comments. Log in now.

Was this documentation topic helpful?

If you'd like to hear back from us, please provide your email address:

We'd love to hear what you think about this topic or the documentation as a whole. Feedback you enter here will be delivered to the documentation team.

Feedback submitted, thanks!