In part two of my Sitecore MongoDB blog series I explained the important databases and collections created by Sitecore. In this post I will walk you through some useful MongoDB queries you can use to get useful data from Sitecore. For this post I will be using Robomongo to query and view data (as I recommended in part one) but you can use mongoshell or MongoDB Compass.

Robomongo Tricks

Before we begin, here are some tips to make querying MongoDB even easier in Robomongo:

• To make it easier to work with UUIDs in MongoDB, change the Legacy UUID mode to “Use .NET encoding”. This will make Robomongo renders the Legacy UUID values in a readable and more usable GUID format:

• To quickly grab a property value, right click on the property and select “Copy Value”. This is especially useful for legacy UUIDs as it will copy the wrapper you need in your query, e.g. NUUID("ed21b565-6fb0-2ec0-166c-e7fb3698dcf2")
• To quickly grab the property path (e.g. “Identifiers.Identifier”) to use in your find query, right click on the property and select “Copy Path”. This is especially useful for deeply embedded objects or arrays.

Querying Contact Cards

It is very easy to view a user contact cards in MongoDb. Simply query the “Contacts” collection in the  “sitecore_analytics” database:

db.getCollection('Contacts').find({})

The _id column is the unique contactId that can be used to lookup the users visit information in the Interations collection (among other collections). If you recall from part two, the _id column is indexed which provides very fast lookups even with millions of documents.

Querying Identifiers

To find the contactId, you can look it up in the Identifiers collection. Query this collection with your upper-cased identifier (username, email, etc) for a fast index lookup:

db.getCollection('Identifiers').find({"_id" : "DOMAIN\\USER@EXAPLE.COM"})

The “contact” property is actually the contactId. To ensure your user’s contactId is inserted into this collection by sitecore is by explicitly calling Tracker.Current.Session.Identify() in your code after the user logs in or fills our a form and passing the username or email address. (I recommend including the domain when identifying a user to future proof your solution for multi-site / multi-tenants).

Anonymous vs Authenticated / Identified users

Anonymous users are site visitors who you/your application has NOT uniquely identified. Viewing their contact card directly in MongoDB does not yield much data except for the visit count and value (xDB goals triggered). But Interations collection will have more information.

Identified users will have (at the very least) the “Identifiers.Identifier” property. This can be the username, email or however you explicitly identified the user in your code with: Tracker.Current.Session.Identify();

If you set other custom contact card values, they will also appear in this document, such as name, phone numbers, emails, contact card picture, etc.

Querying Interations

Interactions has a lot of useful information about the users visist to the site. (For more information, see part two).

db.getCollection('Interactions').find({ContactId: NUUID("e2a763b4-de34-46cc-a81d-30ff11723693")})

If the anonymous user did not clear their cookies between visits, Sitecore should able to track the unique user across multiple visits/sessions:

As you can see, there is a lot of data in the Interactions collection that you can use if you want to incorporate it into custom reports or even into your web site functionality.

GeoIps Collection

This query will return all cached GeoIp lookup data Sitecore has performed on your site’s behalf (if you purchased a license).

db.getCollection('GeoIps').find({
$and: [
{"BusinessName" : {$ne: "LICENSE_EXPIRED"}},
{"BusinessName" : {$ne: "N/A"}},
]
})


Take note that that this query is not indexed. However,  the _id field here is the IP address, which is automatically indexed by MongoDb, providing fast lookups should you desire to query for an IP address directly.

Form Data

This collection contains the user submitted Web Forms for Marketers (WFFM) data.

If you want to lookup the submitted form results for a particular contactId: (note that this query is not indexed)

db.getCollection('FormData').find({"ContactId" : NUUID("e8908bcc-20f8-4e20-a61e-b92dc98b3976")})

This could be very useful to pull user’s WFFM form submission data into your site to use for various reasons.

You can also query the collection using the FormId, which is the ID of the actual WFFM form Item in Sitecore. This will return form submissions by each contact:

db.getCollection('FormData').find({"FormId" : NUUID("d8cf657e-d626-4099-ba54-a2655b2d1c0a")})

Coming Up:

• How to install multiple instances of MongoDB on a single machine
• Maintenance scripts (backups, logging)
• Creating custom MongoDB databases, collections, and documents