Query Sitecore xDB MongoDatabases via RoboMongo Part 3

In my previous two blog post Query Sitecore xDB MongoDatabases Part 1, I shared information about configuring RoboMongo for making it to understand CSUUID and the second post Query Sitecore xDB MongoDatabases Part 2 informing about executing queries from Mongo Shell. This post would be the last in the series where I would share queries that would be handy to be executed from RoboMongo. One would wonder as to why mentioning queries if already there is a blog post out there for Mongo Shell. There are few differences in the syntax for RoboMongo and Mongo Shell and hence this post.

Differences

The differences that stands out for queries for Mongo Shell vs RoboMongo are,

  • Instead of referring to the collection name COLLECTION_NAME.find() as compared to Mongo Shell, RoboMongo uses getCollection method.
  • GUIDS would have to be enclosed in the call for CSUUID for e.g CSUUID(“7801dccd-6d8c-4c6e-8327-f66a7c773698”)

Similarities

  • Similarity is both Mongo Shell and RoboMongo are case sensitive so .find({}) and .Find({}) are interpreted differently where former is correct to search for a record and the later would result in an error. Case sensitivity holds true for fields names too so finding a record for “ContactID” would get you results but not for “contactid”
  • find, and, or, sort and limit syntax is similar for Mongo Shell and RoboMongo.
  • Fields hierarchy separated by “.”

find()


db.getCollection('Contacts').find({})
Query for GUIDS
 db.getCollection('Contacts').find({"_id" : CSUUID("7801dccd-6d8c-4c6e-8327-f66a7c773698")})

RoboMongo-Guid

Query for data without GUIDS. Fields hierarchy separated by “.”

 db.getCollection('Contacts').find({"System.VisitCount" : 6})

RoboMongo-Field

Note: _id from Contacts collections maps back to ContactId in Interactions collection


db.getCollection('Interactions').find({"ContactId" : CSUUID("7801dccd-6d8c-4c6e-8327-f66a7c773698")})

and


db.getCollection('Contacts').find({$and:[{"System.Value":0},{"System.VisitCount":10}]})

RoboMongo-and

or


db.getCollection('Contacts').find({$or:[{"System.VisitCount":6},{"System.VisitCount":10}]})

RoboMongo-or

sort()

--For descending order.
db.getCollection('Interactions').find({"ContactId" : CSUUID("7801dccd-6d8c-4c6e-8327-f66a7c773698")}).sort({"SaveDateTime":-1})
--For ascending order.
db.getCollection('Interactions').find({"ContactId" : CSUUID("7801dccd-6d8c-4c6e-8327-f66a7c773698")}).sort({"SaveDateTime":1})

limit()


db.getCollection('Interactions').find({"ContactId" : CSUUID("7801dccd-6d8c-4c6e-8327-f66a7c773698")}).sort({"SaveDateTime":-1}).limit(3) can be used for fetching top 3 records.

Query Sitecore xDB MongoDatabases Part 2

Let’s query Mongo Database and fire some queries on collections to see what all Sitecore stores in the collections. We will also try to learn the syntax of mongo queries something similar that we use to do in our academic’s days trying to remember SQL queries as

create table tablename

OR

select * from tablename where fieldname=value orderby fieldname.

This post is a continuation of my previous blog post Query Sitecore xDB MongoDatabases Part 1 where I shared information about how to set up RoboMongo so that it can understand GUID. Here I would be sharing few queries that can be executed from mongo shell but nothing about executing queries from RoboMongo, well this allows me to write a 3rd post in the series where I will show how to query mongo db via RoboMongo.

A point worth noting is Mongo is case sensitive so Find() and find() are different and the former will throw an error saying it is not a function.

Of course, the mongo shell needs to be running from where one can query mongo databases. So how do I run mongo shell?

  1. Open command prompt and change the directory to /mongo/bin
  2. Execute “mongo” and you will be connected to test db.
  3. Execute “use admin”. Mongo shell will switch database to admin and display message “switched to db admin”. “admin” is the database where all information related to security is stored.
  4. Execute db.auth(“username”,”password”) command. Once successfully authenticated mongo shell will display “1”
  5. Now for connecting to the desired mongo db execute “use databasename” statement for e.g use sc82up1play_analytics

mongo_shell

If authentication is not enabled on mongo instance you can skip #3 and #4 steps. Why would one leave a mongo instance vulnerable and be hacked so as a best practice authentication, should always be enabled and if you want to learn how to enable it follow below links,

Let’s start! We now have enough background

find()

To query data from MongoDB collection, you need to use MongoDB’s find() method. It would list all the records from a collection and is equivalent to SELECT statement in SQL.

Syntax of find() method is

db.COLLECTION_NAME.find()

For listing all records from Contacts or Interactions collections of sitecore_analytics database you will fire up,

  • db.Contacts.find()
  • db.Interactions.find()

For getting a record to match a field value syntax would be

db.COLLECTION_NAME.find({“key:value”}) 

Or

db.COLLECTION_NAME.find({“key.key:value”})

if the fields are nested we use dot notation to separate out top-level fields.

find

Examples

db.Contacts.find({“System.VisitCount”:”5″}) where value of “VisitCount” is 5 and it falls under a top level field “System”

db.Interactions.find({“_t”:”VisitData”} ) is an example of top level field.

and in MongoDB

Syntax for and is,

db.collection.find({$and: [{key1: value1}, {key2:value2}]})

Key refers to as fieldname and value is what you would have the record for.

and

Example

db.Contacts.find({$and:[{“System.VisitCount”:5},{“System.Value”:10}]})

or in MongoDB

Syntax for or is,

db.collection.find({$or: [{key1: value1}, {key2:value2}]})

or
Example

db.Contacts.find({$or:[{“System. Value “:5},{“System.Value”:10}]})

sort()

Syntax for sort() is,

db.COLLECTION_NAME.find().sort({KEY:1})

To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order. If the sorting preference is not specified, then sort() method will display the documents in ascending order. sort() is equivalent to OrderBy in SQL.

sort_ascending

sort_descending

Examples

db.Contacts.find({}).sort({“System.Value”:-1})

db.Contacts.find({}).sort({“System.Value”:1})

If we want to find out the latest record in Interactions collection there are two fields “StartDateTime” and “SaveDateTime” which can be queried,

db.Interactions.find().sort({“StartDateTime”:-1}).pretty()

db.Interactions.find().sort({“SaveDateTime”:-1}).pretty()

limit()

As the name says limit() can be used to limit the number of records to be displayed on mongo shell

Syntax for limit() is

db.COLLECTION_NAME.find().limit(NUMBER)

sort() and limit() can be used to match TOP statement in SQL.

limit-2

limitsort

Examples

db.Contacts.find().limit(2)

db.Contacts.find().sort({“System.Value”:1}).limit(2)

db.Contacts.find().sort({“System.Value”:-1}).limit(2)

db.Interactions.find().sort({“SavedDateTime”:-1}).limit(1)

Here‘s the end of the post and all I have to share on Mongo for now. I will soon be writing another post to talk on the above basic mongo queries in RoboMongo.

Query Sitecore xDB MongoDatabases Part 1

Mongo databases are integral part of Sitecore xDB and they are the main data stores for the Sitecore Experience Platform. Experience Analytics, Experience Profile and Experience Optimization all in a way or other depends on Mongo databases. It always good to have some hands-on queries related to mongo in case troubleshooting or understanding of sitecore analytics data is required. In this blog post I am sharing some ground up work that needs to be done before firing some queries to mongo. It is definitely on my plan to write another part of this blog post to list down basic mongo queries.

Sitecore sends a persistent session cookie “SC_ANALYTICS_GLOBAL_COOKIE” to identify a potential contact or repeated visits from a single user. The value of the cookie is a GUID and can be found into “Contacts” collection of sitecore_analytics mongo database. RoboMongo can be used to connect and fire up a query to filter data. Mongo stores GUID as a value and not string so executing below query in RoboMongo will not fetch any results.

db.Contacts.find({_id:'{3b4b5488-d3a0-40b1-9099-9b896c55abf0}’})

or

db.getCollection(‘Contacts’).find({_id:'{3b4b5488-d3a0-40b1-9099-9b896c55abf0}’})

Mongo has provided javascript helper functions for parsing and displaying UUIDs. CSUUID is one of them which is used to query mongo db for finding a contact. So, your query would look like

db.Contacts.find({_id:CSUUID(‘{3b4b5488-d3a0-40b1-9099-9b896c55abf0}’)})

Below are the steps that needs to be followed for making CSUUID available,

  1. Download uuidhelpers.js.
  2. If .mongorc.js does not exists on your user profile create one.
  3. Update .mongorc.js file by adding load(“C:\\Users\\bpatel\\uuidhelpers.js”); to it. In my case I have placed .mongorc.js and uuidhelpers.js at the same path.
  4. Load .mongorc.js in Robomongo.Load Mongorc
  5. Make sure to change Legacy UUID Encoding to Use .Net Encoding.netuuidNow firing the query db.Contacts.find({_id:CSUUID(‘{3b4b5488-d3a0-40b1-9099-9b896c55abf0}’)}) will fetch a result. RoboMongo dispalys the type of encoding that is being used,

    luuid_diff

    After the Encoding is switched it displays NUUID aka .NET UUID.

    nuuid_diff
    Point to notice is the GUID too changes as for Legacy encoding it was 88544b3b-a0d3-b140-9099-9b896c55abf0 and for .NET Encoding it is 3b4b5488-d3a0-40b1-9099-9b896c55abf0

Stay tuned for next part of this blog post for some basics of mongo queries.

Second post in the series is now available,

Mongo Backup and Restore

While working on Sitecore at times one can come across a simple task for backing up and restoring mongo databases. Did that made you think? Why me? It should have been done by a DBA. Let’s ignore Mr. Anger from your Inside Out and learn how to do that.


Mad at Mongo


To me the task seemed simple and my initial thoughts were let’s get connected to RoboMongo or Mongo Management Studio, visit any one menu or right click on any one of the mongo database and I will find an option to backup my databases. Well reality is always bitter and I did not have those easy options. At this point of time I was Sad from Inside Out.

Do not know Mongo Backup & Restore

I learned some commands from “Back Up and Restore with MongoDB Tools” and would like to share.

Run the command prompt as administrator and navigate to Mongo/Bin folder in my case the path was C:\Databases\Mongo26\bin. From the command prompt issue below commands,

Backup

1) Backup all databases on the Mongo Instance

mongodump

mongodump

This command will be backup all the databases available on the mongo instance. The backup files will be created under \bin\dump folder

Explorer

2) Specific Database

mongodump --db speakplay_analytics --out c:/Backup/dump/speakplay_analytics
Parameter Description
–db Path of the database for which you want to create a backup
–out Destination path of the backup



Mongo specific database


3) Specific Database behind authentication

mongodump.exe --db sc8prod_analytics --authenticationDatabase admin --username xxxx --password xxxx --out F:\MongoBackup\sc8prod_analytics

In case if authentication is enabled extra parameters needs to be passed on to mongodump command,

Parameter Description
–authenticationDatabase DB in which the user is defined, mostly it would be “admin” database
–username Self explanatory
–password Self explanatory



Authentication

4) Specific Collection

mongodump  --db speakplay_analytics --collection Interactions

If you want to restore specific collection (table) from a mongo db use –collection parameter to do so.

Restore

1) Restore a database

mongorestore --db speakplay_analytics dump/speakplay_analytics/Interactions.bson

2) Restore a collection

mongorestore --collection Interactions --db speakplay_analytics dump/speakplay_analytics/Interactions.bson

This now makes me Joy from Inside Out as I now know Mongo basics.

Happy now

Creating Keyfile For MongoDB Replication Set

Creating a keyfile is one of the required steps while you are configuring a MongoDB replication set for a Sitecore solution. In this post I will mention Why and How you can generate a keyfile. This post is a smaller extract for my upcoming post mentioning about creation of MongoDB Replication Set for Sitecore.

WHY a keyfile is required?

In a replication set all the participants, Mongod instances should talk to each other (authenticate each other) by supplying a password for inter-process authentication. The content of the key file is the shared secret used for all internal authentication.

How to create keyfile?

In order to create a keyfile you need to have OpenSSL installed, below are the links to download it,

Once OpenSSL is installed, open command prompt and navigate to \OpenSSL-Win32\bin and execute below command,

openssl rand -base64 741 > mongodb-keyfile

This will generate the required keyfile in the bin folder as seen in below screen, you can now deploy this file on all the mongod instances which are participating in replication set.

MongoDB Replication Set Keyfile

As mentioned earlier in this post, I am preparing myself to write another post maybe over the weekend showing how to create a full functional MongoDB Replication Set for a Sitecore Solution..

Sitecore 8: xDB – Using a Shared Database User on MongoDB Results in an Exception

When you use authentication with SQL, you have the choice of using a server level user, or a database level user.  Usually, you would choose to use a server level user and grant access to the user per database.  If you want to do the same thing in MongoDB, you can create a user in the admin database, and then grant rights to each individual database that you would want your user to access.  Then, in your connection string, you set the authSource parameter to the admin database, as in:


<add name="analytics" connectionString="mongodb://shareduser:password@10.1.1.15,10.1.1.16/sitecore_analytics?authSource=admin" />

If you are using this model on Sitecore 8 with MongoDB, you may come across the following error:

Cannot obtain number of chunks from config database
Exception: MongoDB.Driver.MongoCommandException
Message: Command 'count' failed: not authorized on config to execute command { count: "chunks", query: { ns: "sc8prod_analytics.Interactions" } } (response: { "ok" : 0.0, "errmsg" : "not authorized on config to execute command { count: \"chunks\", query: { ns: \"sc8prod_analytics.Interactions\" } }", "code" : 13 })
Source: MongoDB.Driver
 at MongoDB.Driver.Operations.CommandOperation`1.Execute(MongoConnection connection)
 at MongoDB.Driver.MongoCollection.RunCommandAs[TCommandResult](IMongoCommand command, IBsonSerializer resultSerializer, IBsonSerializationOptions resultSerializationOptions)
 at MongoDB.Driver.MongoCollection.RunCommandAs[TCommandResult](IMongoCommand command)
 at MongoDB.Driver.MongoCollection.Count(IMongoQuery query)
 at Sitecore.Analytics.Data.DataAccess.MongoDb.MongoDbCollectionShardingInformation.GetNumberOfChunks()
 at Sitecore.Analytics.Data.DataAccess.MongoDb.MongoDbInteractionStorage.MergeVisits(Guid dyingContactId, Guid survivingContactId)

We decompiled the source code and determined the root cause of this to be that the Sitecore code is trying to access the config database (which incidentally may or may not exist).

public int GetNumberOfChunks()
{
 MongoDatabase sisterDatabase = this.collection.Database.GetSisterDatabase("config");
 MongoCollection<BsonDocument> collection = sisterDatabase.GetCollection("chunks");
 long num = collection.Count(Query.EQ("ns", this.collection.FullName));
 if (num > (long)2147483647)
 {
 throw new Exception("System has detected too many chunks in the collection.");
 }
 return (int)num;
}

The solution here was to grant the shared user read rights to the config database that didn’t exist:

db.grantRolesToUser(“MONGOUSER”,   [ { role: “read”, db: “config” } ] )