Custom Report from xDB (MongoDB) based in Geo Ips

Question

We are on Sitecore 8.2 rev. 170614 (8.2 Update-4) using MongoDB On-Premise mode with custom SSL Setup.

Need to submit a custom report to business based on various logins (and related data) categorized by GeoIP Location.

Since in xDB the IPs are stored in Mongo only not in SQL Reporting Database, how we can consolidate both data and create a report.

Though we are working on creating MongoDB script for the same but in the Phase-2 we need to fetch data from Client's Legacy Database which is again on SQL.

Answer 1

Sitecore itself stores the raw analytics data in Mongo, then continually processes the data into a SQL database to make it easier to report on. You can do the same thing, depending on how fresh you need the data to be.

If it needs to be up to date all the time, consider adding a processor to your xDB Aggregation system. This piggy-backs on the built-in Sitecore process to handle data incrementally and store it in a new SQL table in a shape considerate of your reporting needs.

If it only needs to be updated periodically, say daily, it is worth creating a separate batch process, again run on xDB, that can handle the data and prepare it for reporting without slowing down the normal processing system.

Answer 2

Extracting the inputs from Chris Auer and Richard Hauer, consolidating below the best fit solution in our scenario.

As a temporary solution we have built MongoDB scripts on Interaction collection for all the data required from Mongo. Since using MongoChef as GUI tool we are also able to export the data in CSV format. Imported the CSV in a SQL table and playing around for the additional joins on Legacy DB tables for required reporting.

As a permanent solution, since it is a Monthly report for us hence rather tweaking the OOB aggregation framework, we are planning to introduce a custom batch process to pull the required documents from Interaction collection and populate a custom table in our SQL DB.

Thanks Chris Auer and Richard Hauer for the guidance.

Answer 3

A custom aggregator processor can be defined and it will pull data from the xDB( MongoDB) and add these data to custom SQL tables to the reporting database.

Sample code for the reference.

 public class PageEventProcessor : AggregationProcessor
  {
    protected override void OnProcess(AggregationPipelineArgs args)
    {
        Assert.ArgumentNotNull(args, "args");
        try
        {
            var factDetails = args.GetFact<PageEvent>();
            foreach (var pageItem in args.Context.Visit.Pages)
            {
                foreach (var fEvent in pageItem.PageEvents)
                {
                   // Custom logic
                }
            }
        }
    }
}