How do I index a MySQL database table

[ti786]

I want to use Splunk to index the data in a MySQL database table for analysis - how do I do this?

I have installed the DBconnect App and followed the documentation to set it up - the DB connection is working and I have setup a DB input to tail the MySQL table, but I am not seeing anything indexed - where can I check this has worked and is there anything else I need to do for the table to be continously indexed?

I have successfully ran a SELECT query in Splunk to ensure I can get the data.

How do I know my Database Input is working and where can I see the the table has been indexed in Splunk - basically what do I need do next?

Thanks

Here are the requested details:

The DB Input Settings are:

dbmon-tail://Usage/Usage Input

Input Type: Tail

Database: Usage

Table Name: Usage

Rising Column: ID

Do I need to complete the following fields and should the above Input Type need to be Dump the first time or will Tail do the same thing the first time the DB is indexed?

Sourcetype:

Index:

Host Field value:

Recent Debug Error Log (what do the following mean?):

2013-06-02 20:57:27.473 dbx9054:ERROR:Splunkd - Splunkd REST Service keep-alive failed! Session key probably expired. (java.io.IOException: Server returned HTTP response code: 401 for URL: https://127.0.0.1:8089/servicesNS/nobody/-/authentication/current-context/context)
java.io.IOException: Server returned HTTP response code: 401 for URL: https://127.0.0.1:8089/servicesNS/nobody/-/authentication/current-context/context
    at sun.reflect.GeneratedConstructorAccessor43.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at sun.net.www.protocol.http.HttpURLConnection$6.run(HttpURLConnection.java:1458)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.net.www.protocol.http.HttpURLConnection.getChainedException(HttpURLConnection.java:1452)
    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1106)
    at sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:254)
Show all 30 lines

2013-06-02 20:52:27.463 dbx7974:ERROR:Splunkd - Splunkd REST Service keep-alive failed! Session key probably expired. (java.io.IOException: Server returned HTTP response code: 401 for URL: https://127.0.0.1:8089/servicesNS/nobody/-/authentication/current-context/context)
java.io.IOException: Server returned HTTP response code: 401 for URL: https://127.0.0.1:8089/servicesNS/nobody/-/authentication/current-context/context
    at sun.reflect.GeneratedConstructorAccessor43.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at sun.net.www.protocol.http.HttpURLConnection$6.run(HttpURLConnection.java:1458)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.net.www.protocol.http.HttpURLConnection.getChainedException(HttpURLConnection.java:1452)
    at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1106)
    at sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:254)
Show all 30 lines

[jpass]

I'm not sure what the issue is in your case but here's some issues I've had that you can look at.

• If your db input uses a query to pull the data, make sure the query isn't the issue. My first problem was that I didn't follow instructions. This is a working query:

select * from mytable {{WHERE $rising_column$ > ? }}

• Another issue I encountered was when I used "AS" to rename fields. For example: Select id AS myprimarykey, anotherfield, anotherfield2 FROM myTable {{WHERE $rising_column$ > ?}}'. I would use 'myprimarykey' as the rising column which wouldn't work.

• I believe your query also needs to explicitly call the field you use for your tail. (select id,fname,lname,username,timestamp FROM mytable)

• I encountered an issue where I wasn't receiving data because at some point (during testing / setup maybe) the input had run and had recorded the maximum rising column value. I solved this by resetting the rising value for the input.

See this post: How to reset tail.rising value: http://splunk-base.splunk.com/answers/68572/splunk-db-connect-how-to-reset-tailrising-state

Once you get things up and running you'll be happy with DB Connect.

[macycron]

You can also take a different approach, if the data in the MySQL table is static. Export the data from the MySQL table to a flat file. Then add the data via splunk interface. Map the fields accordingly so that your timestamp value is extracted.

[ti786]

Unfortunately, the MySQL data is not static, hence why I need to use the tail.

[ziegfried]

This datatype should work. The simplest way to check is to go to the Splunk search interface and search for:

source=dbmon*

[ti786]

The datatype of ID is BIGINT UNSIGNED - it is a unique autoincrementing numeric value per record eg: 1,2,3,4,5,6.

Thanks for the REST error, the Splunk instance was suspended. I will restart it

Being a noobie, it could be the database may have been indexed - how/where do I checj this in Splunk?

[ziegfried]

The errors indicate that the REST authentication key of DB Connect has expired. This typically happens when the Splunk instance is running in a VM which is being suspended. Make sure to restart Splunk after you resume the suspended VM.

[ziegfried]

What's the datatype of the ID column (the one you've specified as rising column)? Can you provide a few example values?

[ti786]

Please see updated question with requested details.

[ziegfried]

Please provide more information about your database input settings - especially which table/query and rising_column. Please also take a look if there are any errors in the DB Connect log (you can see them by opening "Recent DB Connect Errors" in the Searches tab in the DB Connect app).