|Geek Out with our CTO, Isaac Mosquera|
From time to time, we’re able to pull Isaac away from his job architecting Socialize to write a post. This is one such post. It’s more technical than most of our content, but if you want to geek out with Isaac, you’ll probably enjoy it. Want to continue the conversation? Leave a comment below or hit him up on Twitter.
We’re big fans of Splunk here at Socialize and we use it to log everything from machine data to application specific data. One of the problems we run into is when querying our application data it results in a lot of aggregated data but with no real information like ‘application name’ for IDs in events because that information wasn’t logged with the event. For example, the query:
* index="event_logs_index" bucket="mobile_sessions" | stats count(label) as session_count by label value_application_id
I’ve aggregated session counts for a few sample apps but since the application name isn’t logged with the session event, I can only show the application ID:
This is where MySQL lookups come into play. In Splunk 4.3, there is a MySQL connector which helps connect to the database to query about additional data. I won’t describe in detail the steps to configure MySQL lookups since Splunk already has great documentation on how to get this done, but at a high level you need to do the following:
- 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.
Once you have the lookups configured it’s rather simple to add a lookup to the query.
| lookup application_lookup id as value_application_id OUTPUT application_name
Using the MySQL lookups we were able to use the field ‘value_application_id’ to lookup the application name in the ‘socialize_applications’ table in the MySQL database and output the display_name. Splunk also allows for other lookup types like CSV and python scripts that can help add information to your events.
Let me know if you have questions below or contact me on twitter: @imosquera