We recently had Dan Goldburt from Splunk stop by and help us upgrade to the new Splunk DB Connect. Because log based data is typically a bunch of numerical IDs it’s hard to make much sense from it. Splunk DB Connect adds meaningful data to log based event data. It also allow us to output aggregated Splunk results back into the DB for users or other systems to consume. Here are some of our use-cases:
- Splunk allows us to quickly find the top app ids by social actions and top app ids by downloads. Joinig with the DB let’s us look up application name and category in our MySQL DB.
- Finding applications by id which are using a deprecated API endpoint. We can lookup their app id in one database and then join that with a customer e-mail in our user’s email in another database. We can then export that list to a mail service and notify users that our endpoints are being deprecated proactively.
- Output from our RTB (real-time bidding) platform for statistics into a mySQL database for reporting, i.e. how many impressions did we display, how many people clicked on it, how many people completed the call to action.
- Output from referral log events into an aggregated stats table in MySQL database for reporting, e.g. How many people clicked on the “like” link that we propagated to facebook/twitter.
Here are the migration steps we took to get the DB migration to be upgraded
- backed up /etc to /mnt
- copied over MySQL/savedsearches.conf into search/savedsearches.conf (deleted duplicate ‘user web activity search’ from MySQL), and as part of this also copied over viewstates.conf
- downloaded DB Connect
- copied MySQL/dbspecs to dbx/database.conf – did minimum modification: a. added type=mysql; b. global find/replace of schema/database; c. deleted of insert and select batch size settings; d. optionally, add readonly = false (defaults to true); e. because of a possible bug, cleartext password don’t get auto-encrypted. Workaround=go to the UI and re-enter the password.
- downloaded and installed mysql jdbc driver
- restarted splunk
- Went through app setup (successfully auto-detected java)
- verified that we can connect to all databases by going through dbinfo view and trying each one in the drop-down (deleted sz-webusers-whatever that didn’t actually exist)
- for each lookup in MySQL/transforms.conf, re-create through the database lookup UI (under the search app, which will create dblookup.conf and transforms.conf). To keep the same name, add _mysql to the MySQL transforms stanzas (needs restart) – also as part of this I migrated all MySQL/transforms.conf to search/transforms.conf
- Verify that lookup searches still work. No search language changes necessary!!
- Search for “mysqlquery” and “mysqlinfo” search commands. To update: a. replace “mysql” with “db”; b. chop off “spec=” and “query=”
example:| mysqloutput spec=api-dev table=analytics_splunk_
application_referrals insert=”application_id,event_ date,referral_count,referral_ type”| dboutput database=api-dev table=analytics_splunk_ application_referrals type=insert *(by specifying *, dboutput will only use the fields from Splunk that have corresponding columns)(dboutput is data-type aware, and if the database column is a date it expects the Splunk field to be in epoch time)
Thats it! Within a few hours we’re up and running with the new Splunk DB Connect. Thanks Dan Goldburt from Splunk for coming over and helping us upgrade.