TRAFEX TRAFEX Consultancy Consultancy
Using Logstash, Elasticsearch and Kibana to get insight in my spending

Using Logstash, Elasticsearch and Kibana to get insight in my spending

March 2, 2014

Last weekend I came up with the idea to import my bank transactions into ElasticSearch to see if I can get some nice statistics out of it with Kibana. In turned out to be a interesting “weekend project” and gave some insights in my spending.

Importing into Elasticsearch

First I need to import the CSV files from my bank (Dutch ING bank) into ElasticSearch. Logstash is the ideal tool for this.

Config

I’m using the following config:

Starting import

The following command starts Logstash standalone with the embedded ElasticSearch and the webinterface.

java -Des.http.netty.maxInitialLineLength=16384 -jar logstash-1.3.3-flatjar.jar agent -f logstash-ing.conf -- web

Logstash will immediately start importing the CSV files into ElasticSearch. The ‘maxInitialLineLength’ needs to be raised to avoid the error:

TooLongFrameException: An HTTP line is larger than 4096 bytes.

Getting statistics out of it with Kibana

I now have all the bank transactions into ElasticSearch, but I want to get some statistics out of it. Logstash comes with a web interface called Kibana. Kibana is reachable on http://localhost:9292. The first thing I need to do is change the ‘index pattern’ to match the format I use in my config.

Index settings

After that I can select a greater time span and should see the number of transactions per day in the histogram.

Transactions per day

Incoming/outgoing per week

Seeing the number of transactions per day is nice, but I’m more interested in the amounts. I change the histogram to let it show the total amount per day:

  1. Click on the configure icon
  2. Go to ‘Panel’
  3. Choose ‘total’ as Chart value
  4. Fill in the field name which stores the amount, in my case this is ‘amount’
  5. Save!

Now the histogram shows me the total amount instead of the number of transactions. If you click on ‘view’, you can change the interval to for example 1 week and you can choose to see lines instead of bars. Now I see the total amount, but I want to see the difference between incoming and outgoing. This can be done by creating 2 queries: one that filters on incoming, one that filters on outgoing. In my case I have a ‘increasedecrease’ field that I can use to query on. ‘af’ means outgoing, ‘bij’ means incoming.

By click on the colored dot in front of the query you can ‘pin’ the query and alias it.

This gives a nice result.

Incoming/outgoing

Totals

To see the total amount and the amount per query I create a new ‘stats’ panel like this: Total amount

Now I can see the amount incoming/outgoing for the selected time.

Total amount

This gave me a nice start to dive deeper into my spending and create new queries based on a specific description, for example to see how much I spent on my?mortgage. Try it yourself, it’s fun and gives you?enormous possibilities!

Don’t forget to save your dashboard!

Go back

Recent articles

Articles

Checklist: Prepare your website for Go Live

When you’re ready to launch your website or web application, you can use this checklist to be fully prepared for the Go Live and have a smooth transition!

Read More

Articles

The key components of Kubernetes autoscaling

Autoscaling is an important feature of Kubernetes. With this feature you always have enough resources for the workload, and when a node becomes unhealthy it gets replaced without effecting the workload. But you won’t get it automatically by just deploying your Pods on Kubernetes.

Read More

Articles

Create a DB backup from a pod running MySQL on Kubernetes

When using the MySQL docker image you can easily create a DB dump with this one-line CLI command.

Read More