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.

  • Are you looking for IPs or Geo like country, state, etc...
    – Chris Auer
    Commented Dec 1, 2017 at 1:54
  • Just an idea, but did you check the experience extractor? github.com/Sitecore/experience-extractor
    – Gatogordo
    Commented Dec 1, 2017 at 8:59
  • Yes @ChrisAuer. Login data need to be categorized on Countries, States and Cities. Also I think in Mongo the IPs are stored in Base64 format hence a conversion to Actual IPs (may be Hex) will also be needed. We are able to achieve it using Mongo Scripts though as a temporary solution. Commented Dec 1, 2017 at 16:11
  • @Gatogordo, let me explore the tool, will update you accordingly. Commented Dec 1, 2017 at 16:12
  • I was hoping on pulling the data our of Sitecore_Analytics_Index since it has the geo IP information. But it does not have event/goal/outcome data. You will need to go to Mongo for that.
    – Chris Auer
    Commented Dec 2, 2017 at 0:50

3 Answers 3


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.

  • Its a Monthly Report Richard. You mean creating a connector between Mongo and SQL, which pulls the data from Mongo convert it to the Relational DB format and save it to SQL. Is that correct ? Commented Dec 1, 2017 at 16:43
  • Yes, that's right. Be sure to add indexes to MongoDB to ensure queries are as efficient as possible. Try to structure your query using Map/Reduce or the aggregation framework, rather than relying on C# to do the rollup. You will find Mongo is very good at processing data this way. Commented Dec 1, 2017 at 17:10
  • Will give it a shot with the Map/reduce, but my understanding is correct right that Siteore OOB doesn't store the GeoIP data in Reporting DB at all? Commented Dec 1, 2017 at 19:49
  • The raw data is in Mongo. Note that Mongo is not a relational dB, so you can't "join" to the GeoIP collection Commented Dec 1, 2017 at 23:11
  • Agreed, Mongo is not relational rather it stores on BSON format. Hence we are writing Mongo Scripts (Using MongoChef as GUI) to query the Interaction collection for the required data. Commented Dec 3, 2017 at 5:11

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.


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");
            var factDetails = args.GetFact<PageEvent>();
            foreach (var pageItem in args.Context.Visit.Pages)
                foreach (var fEvent in pageItem.PageEvents)
                   // Custom logic

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.