Define MySQL database lookups in Splunk Web
Define MySQL database lookups in Splunk Web
- 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.
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
SELECTquery, such as 100.
- the Insert batch size, which is the number of records to batch in a single
INSERTquery 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.)
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
<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.
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
For more information about the
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
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
0345 in your event, you need Splunk to find the
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
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
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
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
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.