Prediction Through Mapping Visualizations with CartoDB and Splunk

Note: Parts of this post may be deprecated due to Splunk version.

When Splunk started using their own mapping visualization in version 6, it left something to be desired. Sure, you can still use Google maps, and depending on the type of data you’re trying to visualize, those options may be the best way to go. But let’s say you’ve got some data you want to put into a map, and that data is not sensitive. If that’s the case, then you are in luck.

Enter CartoDB, a service that allows you to simply drag and drop data (csv etc) into their web interface to create awesome-looking maps – even maps that animate. And are you ready for the best part? No coding required to get set up with a simple animated map. This tutorial won’t go over every specific detail on how CartoDB works, but they have some awesome documentation if you’re interested in learning more.

If you want to follow along, you have two paths you can take – you can either watch the screencast below or you can follow along with the steps taken in this blog post. Of course, if you’re feeling really adventurous, you could always do both. 

This tutorial assumes you have some data in Splunk you feel comfortable exporting to an external service like CartoDB. You also need an install of Splunk and access to the command line for your Splunk install.

“Creating amazing maps with your data” Screencast

Necessary File:

CartoDB Python File created by Andrew W. Hill

Sensitive Security Precautions **Please Read**

As said above, make sure you aren’t using sensitive data. You will be exporting your data to their database and while they say its secure, you probably don’t want any sensitive personal or company data being exported to them. Also, by default, your maps and data are publically available. They do offer an Enterprise edition, that allows you to use your own databases, but by default your visualizations and tables are publicly accessible. You can make your table data private and your map public, but not with a free account, so at the very least you will need to sign up for a free trial of one of their paid plans in order to make your data private.

Note: If you make your map private, you won’t be able to embed it into Splunk.

Let’s Get Started with Our CartoDB Setup

If you haven’t already, go to www.cartodb.com and sign up for a free account. Once you have that all set, go to your CartoDB dashboard and click on New Table > Start from scratch > Create empty table. You should then be taken to a blank table with something like this showing:

CartoDB dashboard

Click on the ‘untitled_table’ in the top left and rename it to whatever you like. This is the name of the table we will be exporting data to a little later on. Note: Technically, you don’t have to create a table here. Later on when we export our data to CartoDB on the command line, you can choose to create a new table instead of overwriting an existing table – the choice is yours.

Python all the things

We are also going to be using a Python file to export our data to CartoDB from the command line. The specific one I used can be found here. Huge thanks to Andrew W. Hill for having created the Python file, which made the whole experience much simpler.

Copy that Python file into $SPLUNK_HOME/var/run/splunk – this is where we will be running the command as Splunk will automatically output the .csv file to this directory.

Output data from Splunk

For my example, I will be exporting out some UFW data using the following command in Splunk:

UFW data

As stated before, by default, when I run this command it will output the cartodb.csv file to $SPLUNK_HOME/var/run/splunk/ .

Once you’ve run the command in Splunk, you would then go to $SPLUNK_HOME/var/run/splunk/ and look for cartodb.csv or whatever you happened to name your output file.

Create a Dashboard

Create a new dashboard with one panel. I usually just use a dummy search to fill in the requirements of having a search in order to create the panel. Once that is created, export the Simple XML to an HTML dashboard.

Inspect the code for the HTML dashboard and look for something like this:

Take note of <div></div> we will be replacing it with the iframe at the end of this tutorial.

Get Your API Key

Before we can export out the data to CartoDB, you will need an API Key, so go into your CartoDB account and click on ‘account name’ < ‘Your API keys’

You should see something like this:

Your API keys

You will need your API key for the next part.

Export Data to CartoDB

You should now have your cartodb-utils.py and <exported_data>.csv files in $SPLUNK_HOME/var/run/splunk

Option 1: Overwrite Current Table

If you created a table earlier in this tutorial, then you will want to overwrite the table you created with the following command:

Option 2: Create New Table

On the other hand, if you did not create a table earlier, or you just want to export your data to a new table, you can do so with this command:

Note: All the available command line options are available to view within the cartodb-utils.py file.

Run the command

Now, before you run the command make sure you have the correct API key and account name being used. Also, if you are overwriting a table, make sure you have the correct table name that you want to overwrite. If you are creating a new table then you don’t need to worry about the table name.

Once you run it, you should get back SUCCESS Table data replaced if you overwrote the data or SUCCESS <new_table_name> if you decided to create a new table.

Modify your map in CartoDB

Go back to your CartoDB account and select your new table. You should see the new data added. Now click on ‘Map View’ and your map should be populated. Click on the icon that has a number one in it on the right hand side of the map:

Map View

By default, the ‘Simple’ visualization is selected, but we want our map to animate. So scroll over until you see the ‘Torque’ visualization. Click on that and watch as your map becomes awesome and animates. You can modify the visualizations further if you choose, but thats outside the scope of this tutorial. If you want to slow down the default animation, then reduce the number in the ‘Steps’ dropdown.

Export your map to Splunk

Before you can export your map, you need to turn it into a Visualization. Click on ‘Visualize’ in the top right corner of your map view. Name your visualization, then click ‘Create visualization’.

By default, the visualization will have a lot of extra options including a search bar and send button, but you can disable all of this if you want to by click on the ‘Options’ button at the bottom of the map.

Now, click the ‘Share’ button at the top right and copy the iframe snippit.

Go back to your HTML dashboard and look for <div></div> and replace it with the iframe code. Click ‘Save’ and you should see something like this:

CartoDB

And thats it, you now have an animated map in Splunk.

Final Thoughts

There is a lot you can do with CartoDB, way more than what is covered in this tutorial. I highly recommend checking out CartoDB’s documentation to get a better idea of what is possible, but hopefully this tutorial serves as a good starting point.

We could also automate this and run the search at a specific time and then run the python script to export the data to CartoDB, thus creating a map that updates automagically.