Introduction

In this post I will introduce you to some common queries I use when debugging data that have been flushed to the mongoDB and hopeful demystify the mongoDB part of the xDB offering a bit.

It has now been a while since Sitecore introduced their xDB offering.
I still run into quite a few people who gets this “concept” a bit mixed up.
I hear people refer to the xDB as being “the mongo database”. That is not completely true.
The mongo database (or short mongoDB) is the collection part of the xDB offering, used for collecting and storing massive quantities of raw data.

The analytics data are later reduced, grouped and added to the reporting database and the analytics index for later use during personalization and various report extracts.

Agents are running, handling the data processing and data aggregation.
I might go into detail about what is going on during the processing in a later blog post.

Flushing data

When you need to test anything related to xDB data you need to flush the data as the data is not available before the session ends for each user visiting the site.
You can easily force this flush by creating a page that flush the data to the mongoDB immediately.

Create a file:
abandon.aspx

With following code:

<% Session.Abandon();%>
<html>
<head>
<title>Session abandoned</title>
</head>
<body>
<h1>Session abandoned</h1>
<h3>Data for abandoned session is now available in mongo database</h3>
</body>
</html>

Add this file somewhere in you web structure. Easiest location is the root folder.
Access the page and the data is flushed.
Afterwards the data should be available in mongoDB.

Flush data using chrome extension

What I find to be a simple and good testing tool for creating new visits and new contacts is to use the chrome extension “Sitecore Analytics Testing Tools”

It can be found here:
https://chrome.google.com/webstore/detail/sitecore-analytics-testin/pecalkbdlhhhcoenmcjnmhgnncnkdgak

What this tool is doing is basically to:

  • flush the session (New Visit)
  • removing the cookie SC_ANALYTICS_GLOBAL_COOKIE (New Contact).

The tool also includes the capability of setting up various ip addresses around the world for testing the GeoIP service which is also very useful.

I might later create a separate blog post about how to setup and test the Sitecore GeoIP service.

Another very interesting post is Martina’s thorough blog post:
https://mhwelander.net/2016/08/24/whats-in-a-session-what-exactly-happens-during-a-session-and-how-does-the-xdb-know-who-you-are/

Installing MongoDB – Sitecore SIM for the win

I will not come into detail as to how you install MongoDB.
There are various posts about this already out there. E.g.
https://briancaos.wordpress.com/2014/10/01/sitecore-and-xdb-setting-up-mongodb-on-your-developer-machine/
or
http://www.nttdatasitecore.com/Blog/2016/August/Setting-up-MongoDB-in-production

Personally I find setting up MongoDB locally is rather painless using the Sitecore SIM tool.
This tool is also very useful for a number of other reasons, so if you are not using it already go check it out here:
http://dl.sitecore.net/updater/sim/

Robomongo GUI tool

I typically use Robomongo for this task and it will be the tool shown in my screenshots.

Robomongo can be downloaded from here:
https://robomongo.org/download

I will also recommend you have a look at MongoChef. This is also a great GUI tool.:
http://3t.io/mongochef

There are several other tools out there too. Select the one that sooth you.

For inspiration look here:
http://mongodb-tools.com/

Connection to MongoDB through Robomongo

After installing Robomongo (or any other GUI tool for that matter) after launching the application a dialog is showing asking you to connect to MongoDB.

You can create a new connection to either you local Mongo or any developer or test server.
In the below example I have just created a connection to my local instance running Sitecore 8.2 rev. 160729.

After configuring a connection you are ready to connect and perform some queries.

1-mongo-localhost-connection

Query syntax

As the mongoDB is not your typical MS SQL database you cannot do your common SQL queries, but instead you need to use the mongoDB query syntax.

You can see the official query syntax here:
https://docs.mongodb.com/manual/tutorial/query-documents/

Another good reference in understanding and learning the new syntax is to look at the SQL-to-MongoDB mapping chart found here:
https://docs.mongodb.com/manual/reference/sql-comparison/

Now lets look at some useful queries to use in this collection.

Find latest saved interaction by SaveDateTime

Sitecore uses the Interactions collection to store information about user visits.
You often want to look at your latest interaction when testing. The one you just flushed.

I find it easiest to get the latest interaction by the time it was saved using the following query:

db.Interactions.find().limit(1).sort({SaveDateTime: -1})
Screenshot from basic Sitecore 8.2 installation:
 2-find-latest-saved-interaction

After finding the interaction you can then investigate it further. E.g. you can identify the ContactId which is useful if you want to look at the specific contact in the Contacts collection or find all interactions done by this contact.

Find interaction(s) by ContactId

In some cases it is relevant to find interactions based on a given ContactId.
This scenario can be useful e.g. if you want to test interactions that has not been performed on your current machine. E.g. from a colleague or if you want to find some previously performed interaction(s).

The id of the contact can be found by looking at the cookie SC_ANALYTICS_GLOBAL_COOKIE.

The cookie value is added in the following format, the first part being the contact id:

ce7c863e52054ba0b97e61f7162540e8|True

This string needs to be converted into a GUID in order to use it. You can do this by hand or use a simple converter tool. E.g. http://guid-convert.appspot.com/

After convertion the string to a GUID you can use it directly to find an interaction based on a contact id like so:

db.Interactions.find({ContactId:NUUID("ce7c863e-5205-4ba0-b97e61f7162540e8")})

So as you might see here you can find several interactions from the same contact:

Screenshot from basic Sitecore 8.2 installation:
3-find-interaction-by-contactid

You might find it annoying that you need to convert the string into a grid. A post from Adam Conn is a good reference as to how id’s are stored in MongoDB. He also describes another approach on how to search by id. You can find his post here:

working with ids in sitecore xdb

Find contact by ContactId

With the ContactId in hand you can also find the contact in the Contacts collection. This is often only interesting if the contact has been identified and in cases where you need to look at data that have been added to the contact. Otherwise the number of visits is pretty much the only interesting information in the Contacts collection.

db.Contacts.find({_id:NUUID("ce7c863e-5205-4ba0-b97e61f7162540e8")})

Find interactions by page url

If you look at a single interaction hit in Robomongo you can see that the document contains various sub documents due to the flexible schema of MongoDB. It is easy to search in sub documents. This is done by using “.” annotation. So If you want to find interactions related to a specific path you can do this in the following way.

db.Interactions.find({"Pages.Url.Path":"/"})

Wrapping up

I will wrap this up before the post gets too long 🙂 I could show you a lot more examples, but at least I hope that this post will help you get started looking into the MongoDB. It is not that difficult when you first get started.

Advertisements