Monitoring Magento Visitor Logs with the ELK Stack

magento logging

Magento is a powerful eCommerce platform written in PHP that provides merchants with an online shopping cart system. According to w3tech, Magento is the fourth most popular PHP-based CMS in use, trailing after general-purpose platforms WordPress, Joomla, and Drupal.

Magento ships with common e-commerce features such as shopping carts and inventory management and allows site administrators to customize their stores completely to support different business needs. Magento also has a powerful logging system that tracks user activity. The generated event logs can be used to analyze customer behavior, but the main challenge is to aggregate the logs in one centralized platform and quickly get actionable insights.

The ELK Stack (Elasticsearch, Logstash, Kibana) can be used to analyze the large volume of logs that are generated by Magento. Once collected by Logstash and stored in Elasticsearch, these logs can be analyzed in real time and monitored using Kibana’s rich visualization features.

This post will introduce you to Magento logging and describe how to establish a pipeline of visitor logs into the ELK Stack.

Enabling Magento visitor logs

Magento provides two types of logging systems out-of-the-box, one for development purposes and the other for tracking visitor activity in the its online store.

The Magento development logging framework is used by developers to log Magento system errors and runtime execution exceptions, so it is not the focus of this article. Magento visitor logging enables site admins to track user logs and provides details on a customer’s activity such as login and logout time, the devices used by the customer, and the specific stores visited by the customer.

The first step is to enable visitor logging in your Magento store. Access the Magento admin panel interface, and go to System -> Configuration:

magento admin panel

Under the Advanced section, select System from the menu:

magento system menu

Magento will now display advanced system settings. In the Log section, set the value for “Enable Log” to “Yes.” Save the configuration.

magento admin configuration

From this point onwards, visitor logs will be generated whenever a user visits the Magento store. As specified above, these logs include information about user login and logout times, customer IDs, the devices used to access the store, store IDs, and more.

These logs are stored in the MySQL database that you had provided when you had set up Magento. If you explore the database, you will find the following tables for logging purposes:

  • log_customer – information about customer login/logout times and dates
  • log_visitor – information about visitor sessions
  • log_visitor_info – visitor location information such as IP addresses and time zones
  • log_visitor_online – date and time of the first and last visit as well as the last visited URL
  • log_summary – visitor summary report by a date
  • log_summary_type – summary types and descriptions
  • log_url – visited URLs for each visitor
  • log_url_info – information about visited URLs
  • log_quote – contains quotes history by visitors
  • index_event – events that occurred for products and catalogs
  • report_event – history of visitor and customer actions in stores
  • report_viewed_product_index – history of viewed products
  • report_compared_product_index – history of compared products
  • catalog_compare_item – compared products by customer
  • dataflow_batch_export – history of batched exports
  • dataflow_batch_import – history of batched imports

As an example, access your MySQL database and run the following query:

Select * from log_customer;

magento mysql database

Collecting logs with Fluentd

The next step is to collect the logs using Fluentd, a log collector that aggregates, parses, and forwards logs to an output destination of choice.

There are a number of requirements necessary here before we start.

First, you will need Fluentd running on your system.

Next, you will use a Fluentd MySQL plugin to connect with the database and retrieve visitor logs.

Install the plugin by running the following command:

fluent-gem install fluent-plugin-sql --no-document

We will also need to install the mysql2 adapter because it is a requirement for the Fluentd MySQL plugin. To install use:

fluent-gem install mysql2

Next, we’re going to configure our Fluentd configuration file at /etc/td-agent/td-agent.conf:

   @type sql

   host <your host>
   adapter mysql2
   database Magento  # your Magento database
   username <mysql username>
   password <mysql password>

   select_interval 4s  # optional
   select_limit 500     # optional

   state_file /tmp/sql_state  # ath to a file to store last rows

      table logzio_logs  # table to get data from
      update_column visit_time  # column to track the update
      time_column visit_time
      tag Magento_logs

Enter your MySQL credentials in the file.

Please note that as a table name, we have provided the name of a table that does not exist yet: logzio_logs.

That’s our next step, but instead of creating a table called logzio_logs, we will create a table view with the data from our Magento logs:

create view logzio_logs as select * from log_customer NATURAL JOIN log_url natural join log_visitor natural join log_visitor_info;

Streaming Logs from Fluentd to

We’ve collected the logs from MySQL, and the only missing piece is to ship them to ELK.

To stream logs from Fluentd to the ELK stack, you will need to install the Fluentd output plugin:

gem install fluent-plugin-logzio

We will now update the Fluentd configuration file at /etc/td-agent/td-agent.conf:

   @type sql

   host <your host>
   adapter mysql2
   database Magento  # your Magento database
   username <mysql username>
   password <mysql password>

   select_interval 4s  # optional
   select_limit 500     # optional

   state_file /tmp/sql_state  # ath to a file to store last rows

      table logzio_logs  # table to get data from
      update_column visit_time  # column to track the update
      time_column visit_time
      tag Magento_logs

<match Magento_logs>
      type logzio
      endpoint_url< your token >

After saving the configuration file, restart Fluentd. If all goes as expected, Fluentd will start streaming data from your database into the ELK stack.

Open Kibana to view the data:

kibana magento

Visualize visitor logs in Kibana

Great! We’ve got the Magento visitor logs stored in ELK. What now?

The next step is to begin to analyze the data. Kibana has extensive querying capabilities including field-level and full-text search, regular expressions, and logical statements. You can use the fields listed on the left to get more insight into the logs and understand how they are indexed in Elasticsearch.

Once you’ve pinpointed the data in which you are interested, the next step is to visualize it. Below are a few examples of how to visualize Magento visitor logs.

Number of visits

The most obvious place to start is to visualize the number of visits made by customers per day.

In Kibana, select the Visualize tab and then the Line Chart visualization type. Under Buckets for the X-axis, select the Terms aggregation using the last_visit_at field. You can also select the order (Top or Bottom) and size (how many records to visualize). Here is the configuration:

magento user visits configuration

Hit the green play button, and you will see the line chart showing user visits over time:

magento user visits over time

Users by device

Another option is to visualize the devices used by users, this time using the pie chart visualization.

Again, select the Visualize tab and then the pie chart visualization type. This time, we’re going to use the Terms aggregation using the http_user_agent field:

magento user device configuration

And the visualization:

magento users by device

There are plenty of additional visualizations that can be created to see which store is visited the most by the customers, the amount of time spent per visit, a list of usernames, and other information. The sky’s the limit. After creating the visualizations, you can easily consolidate all of them into a single dashboard to monitor visitor activity in your Magento store:

magento dashboard

A final note

Magento has some great built-in logging features but as with any streaming of big data, the challenge is how to handle the data and gain actionable insight from it. A centralized logging solution combining Magento with the ELK Stack easily provides the ability to handle a large number of logs being generated by your visitors and then analyze that information.

Our next Magento post will be more developer-oriented and will focus on logging Magento system and execution logs with ELK. Stay tuned!

Visualize and Analyze your Data with

Stay updated with us!

By submitting this form, you are accepting our Terms of Use and our Privacy Policy

Thank you for subscribing!