Transforming Raw JSON Logs to CSV
--
I recently had to provide error data stored in Splunk logs to a business user (meaning non-technical). I decided to take the raw JSON log entries and convert them to CSV. Here’s how I did it.
The first step was to export the log entries and massage them into a usable format. In the case of Splunk, I searched for the log entries I needed and exported them as raw data. This gave me something that looked like this.
Given this data, we can make a few tweaks, then use jq to turn it into CSV. If you aren’t familiar with jq, Google it and install it. It’s a very handy command-line tool for working with JSON data. To do this we’ll turn this into a JSON file, put all of those lines in an array, and put commas at the end of each line. I did this manually in Neovim.
Now comes the fun part. Let’s turn this into a CSV file. Fire up your terminal and type this command.
This will produce something that looks like this.
What we did here is iterate over the top-level array and pull out all values with a key of app. This will form the foundation of what we want to do. We want to spit out a single line for app, time, msg, and level. This next iteration get’s us closer by turning each object into an array of strings.
