User Manual

 


Query MySQL database tables with mysqlquery

Query MySQL database tables with mysqlquery

Splunk MySQL Connector includes a special search command, mysqlquery, that enables you to:

  • inspect a MySQL database schema from within Splunk (and potentially help you configure MySQL-database-backed lookups)
  • query a MySQL database and process the results within Splunk

To use this command you must have created a spec for the database that you want to search via Manager > Database specs. For more information about database spec creation, see the subsection "Create a database spec for your MySQL database," in this manual.

Note: The mysqlquery command only works with standard searches (searches that run across a distinct time range). It is not designed to work with real-time searches. In addition, mysqlquery is only available "out of the box" for users with admin-level permissions.

Syntax for querying MySQL databases

If you want to query a MySQL database with mysqlquery, the syntax for the search command is as follows:

mysqlquery spec=<string> query="<string>"

Note: The spec and query arguments are required. Also, because mysqlquery is a generating command it must always be the first command in searches that use it.

Arguments

spec
Datatype: <string>
Description: The database spec (a stanza name from databases.conf) to use. The spec values can be overridden by specifying them as arguments here, however.
query
Datatype: <string>
Description: The SQL query to run against the database identified by the spec.

Definition

The mysqlquery command enables you to run a SQL query against a database that is identified in a database spec, which you can set up in Manager by navigating to Manager > Database specs. The database spec defines a set of properties that are necessary for Splunk to identify and interact with a specific external MySQL database.

The command requires that you provide a SQL query and indicate the spec for the database that you want to run the SQL query against.

For more information about the Database specs page in Manager, see "Create a database spec for your MySQL database," in this manual.

Examples

If you want to view the tables in a MySQL database named "MyDB" you would run this:

| mysqlquery spec=MyDB query="show tables;"

If you want to select all the rows from table "MyTable" in the "MyDB" database, you would run this search:

| mysqlquery spec=MyDb query="SELECT * FROM MyTable;"

If you want to get the web access activity for all customers who purchased a rocking chair, you could run this search:

sourcetype=access_* [ mysqlquery spec=MyDb query="SELECT customer_id WHERE purchased_item=rocking_chair" ]

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!