Issue : Unusual inbound network IP traffic causing slow loading of application and slower query performance.

Comments · 1609 Views

Task : To create a dynamic and real-time report/visualization to track users (network traffic) accessing the web analytics application
1. Provide a real-time report to customer to track the distribution of their users over the world
2. Avoid slow app load
3. Provide High Availa

Application Description:

A Web application which is hosted as service (SaaS) in Cloud Foundry (PaaS) using SAP Cloud Platform (SAP CP).  The service lets you collect, report, and analyze your website usage data that helps you to identify meaningful patterns from various digital channels. The service can offer insights on how well your websites perform in key channels. These insights can lead you to implement critical improvements that help you optimize web usage to measure organizational goals, drive strategy, and improve the overall user experience of your web applications.

Problem Analysis:

Understanding client end-users: I started with studying the end-users of the clients by understanding from where they operate based on the documentation and inforamtion provided from the client end. Also, creating the same issue in the test instance to further get a feel of the issue and find possible approach.

Technical Analysis

Using SQL queries and GUI console to check the range of IP addresses of the users and segment them based on the countries and IP range. Also, as an initial approach tried to optimize the SQL queries by reducing time consuming sub-queries and costlier clauses such as GROUP BY. This solution reduced the app load problem to a considerable extent. Further it was necessary to provide a visualization report of accessing users (IPs).

Visualization PoC: 

1. First step was to make use of the IP addresses that are captured in the database for every user login. In order to achieve this, I researched on finding an open-source service which provides mapping of IP addresses to different countries across the globe. MaxMind utility was one such potential service which provided free and open-source distribution of the IP-to-country mapping. It provided manual download of the CSV file data and also an API. Initially, manual download was adopted to verify the utillity suffices the solution to generate a report. Also, the data models were changed in order to capture the country information in the database.

2. The data acquired from MaxMind service contained different data in multiple CSV files which needed to be merged in single relational and structured format as that of the data models. I wrote a python script that performed following operations:

  1. The IP and country CSV files are merged into a single CSV file
  2. A connection with the database table which contains the user IP information is made
  3. The country information is populated in the country column against every IP address

In the final stage the script was completely automated and pushed onto Cloud Foundry as a service with the application stack.

Report Generation:

Using the country and IP information, a world-map report was generated which plotted number of active users accessing the application in real-time. The report was implemeted using Java REST API for data ingestion and Javascript libraries to create the report framework according to the application stack requirement and client specification.

Report use-cases:

  1. To provide IP tracking (users) to the client in the form of report/visualization on the dashboard
  2. To help the client understand and differetiate between real and malicious traffic accessing their dashboard and reports.
  3. The report data served as a trigger point for the malicous attack and defense mechanism utility in the application to perform necessary action.

Note: Due to policies and copyright resctriction it is not possible for me share the report or code snapshot.

Comments