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.

Controlling Sitecore’s isLanguageFallbackValid cache size

Hi everybody,

I ended up doing some digging today and thought I’d share my findings.  I’m working with a client who’s 8.2 Sitecore instance is filling up the isLanguageFallbackValid cache and then some.  This cache size appears to be controlled via Sitecore’s “Caching.SmallCacheSize” setting.  I thought I’d share as a quick Google search didn’t immediately reveal anything.

While we’re on the subject of caching and sizing I should probably mention the “Caching.DisableCacheSizeLimits” setting.  Disabling the cache size limit can be a great way to see what your Sitecore instance is actually using so you can plan accordingly (via /sitecore/admin/cache.aspx).  Whether or not you can leave this set on is another matter.  This article by Uli Weltersbach is worth a read: https://reasoncodeexample.com/2013/03/20/sitecore-cache-settings-for-slackers/

-Dan

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,

Theme based Razor View Engine.

In one of our project there was a need to load different razor views (.cshtml) based on Theme (a value on the site settings) say In Multi site environment a component should render (UI) differently based on site theme.

One of the nice feature of ASP.NET MVC framework is its pluggability. This means you can completely replace the default view engine(s) with a custom one, so here Solution is to create custom view engine with logic to render view based on Theme.

Note: Organize Views under folders specific to theme, example

ThemeA specific Banner view at /Areas/Views/Component/ThemeA/Banner.cshtml.

ThemeB specific Banner view at /Areas/Views/Component/ThemeB/Banner.cshtml.

First we have to store the Theme value for a site- There are many ways to do this, the option we used is to Write a Pipline (Url resolver/Context Resolver) to parse the request before it reaches the MVC view engine and store the Theme value in HttpContext

HttpContext.Current.Items[“theme”]=“ThemeA”

Now Create a custom view engine, Inherit from RazorViewEngine and overwrite FindPartialView and FindView functions

public class ThemeRazorViewEngine : RazorViewEngine
{

//Below are the default paths (with or without Area along with Shared) MVC razor view engine looks for views and partial views
private readonly string[] _areaLocationFormat = new string[] { "~/Areas/{2}/Views/{1}/{0}.cshtml", "~/Areas/{2}/Views/{1}/{0}.vbhtml","~/Areas/{2}/Views/Shared/{0}.cshtml", "~/Areas/{2}/Views/Shared/{0}.vbhtml" };

private readonly string[] _viewLocationFormat = new string[] { "~/Views/{1}/{0}.cshtml", "~/Views/{1}/{0}.vbhtml","~/Views/Shared/{0}.cshtml", "~/Views/Shared/{0}.vbhtml" };

public ThemeRazorViewEngine()
{
base.set_AreaViewLocationFormats(this._areaLocationFormat);
base.set_AreaMasterLocationFormats(this._areaLocationFormat);
base.set_AreaPartialViewLocationFormats(this._areaLocationFormat);
base.set_ViewLocationFormats(this._viewLocationFormat);
base.set_MasterLocationFormats(this._viewLocationFormat);
base.set_PartialViewLocationFormats(this._viewLocationFormat);
base.set_FileExtensions(new string[] { "cshtml", "vbhtml" });
}

public override ViewEngineResult FindPartialView(ControllerContext controllerContext, string partialViewName, bool useCache)
{
string theme = HttpContext.Current.Items["theme"] //Get Theme Value stored in context
if (theme == null)//if no theme set, let it load from original location
{
base.set_AreaPartialViewLocationFormats(this._areaLocationFormat);
base.set_PartialViewLocationFormats(this._viewLocationFormat);
}
else
{
base.set_AreaPartialViewLocationFormats(this.GetLocationFormatByTheme(theme, true).Concat<string>(this._areaLocationFormat).ToArray<string>());
base.set_PartialViewLocationFormats(this.GetLocationFormatByTheme(theme, false).Concat<string>(this._viewLocationFormat).ToArray<string>());
}
return base.FindPartialView(controllerContext, partialViewName, false);
}

public override ViewEngineResult FindView(ControllerContext controllerContext, string viewName, string masterName, bool useCache)
{
string theme = HttpContext.Current.Items["theme"] //Get Theme Value stored in context
if (theme == null)//if no theme set, let it load from original location
{
base.set_AreaPartialViewLocationFormats(this._areaLocationFormat);
base.set_PartialViewLocationFormats(this._viewLocationFormat);
base.set_AreaMasterLocationFormats(this._areaLocationFormat);
base.set_MasterLocationFormats(this._viewLocationFormat);
}
else
{
base.set_AreaViewLocationFormats(this.GetLocationFormatByTheme(theme, true).Concat<string>(this._areaLocationFormat).ToArray<string>());
base.set_ViewLocationFormats(this.GetLocationFormatByTheme(theme, false).Concat<string>(this._viewLocationFormat).ToArray<string>());
base.set_AreaMasterLocationFormats(this.GetLocationFormatByTheme(theme, true).Concat<string>(this._areaLocationFormat).ToArray<string>());
base.set_MasterLocationFormats(this.GetLocationFormatByTheme(theme, false).Concat<string>(this._viewLocationFormat).ToArray<string>());
}
return base.FindView(controllerContext, viewName, masterName, false);
}

private string[] GetLocationFormatByTheme(string theme, bool isArea)
{
List<string> strs = new List<string>();
if (!isArea)
{
//Append the theme value to the path so view engine will look for a view/partial view at that location
//For example look for ThemeA specific view at Views/{1}/ThemeA/{0}.
strs.AddRange(this.GetPaths("~/Views/{1}/", theme, "/{0}.cshtml"));
strs.AddRange(this.GetPaths("~/Views/{1}/", theme, "/{0}.vbhtml"));
strs.AddRange(this.GetPaths("~/Views/Shared/", theme, "/{0}.cshtml"));
strs.AddRange(this.GetPaths("~/Views/Shared/", theme, "/{0}.vbhtml"));
}
else
{
strs.AddRange(this.GetPaths("~/Areas/{2}/Views/{1}/", theme, "/{0}.cshtml"));
strs.AddRange(this.GetPaths("~/Areas/{2}/Views/{1}/", theme, "/{0}.vbhtml"));
strs.AddRange(this.GetPaths("~/Areas/{2}/Views/Shared/", theme, "/{0}.cshtml"));
strs.AddRange(this.GetPaths("~/Areas/{2}/Views/Shared/", theme, "/{0}.vbhtml"));
}
return strs.ToArray();
}

Now add this custom view engine to the view engine collection in App_Start/

public void Process(PipelineArgs args)
{
ViewEngines.get_Engines().Clear();//Clear all engines and add only the custom view engine
ViewEngines.get_Engines().Add(new ThemeRazorViewEngine());
}

Now we are all set, if a Banner is requested from ThemeA specific site, a view will be loaded from /Areas/Views/Component/ThemeA/Banner.cshtml.

if a Banner is requested from ThemeB specific site, a view will be loaded from /Areas/Views/Component/ThemeB/Banner.cshtml. 

If there is a need to create Banner view specific to ThemeC, all we have to is create a view under folder ThemeC.

Happy Coding.

Sitecore Sitemap XML

Sitecore Sitemap XML module is one of the brilliant Sitecore Marketplace module. We have been using it frequently in many of our Sitecore implementations and it works seamlessly. Here I would like to share a side effect of this module which we encountered today. We all have lower environments other than PRODUCTION i.e DEV, QA, STAGING and would wish that the content from the lower environments should not be indexed by any bots. Restricting bots to crawl a website content is all together a different topic and that is not what I wanted to write in this post.

The word of caution is if you are using this module and have not created a robot.txt on your website root directory. Sitecore Sitemap XML will autogenerate robot.txt and place a reference of sitemap.xml into it. It updates robot.txt for every publishing you do as the code is hooked into publish:end event of Sitecore.

There might be a thought in your mind as to why lower environment is accessible over the internet or publicly? Well the environments might be hosted on cloud and in this global village where people work from different part of the worlds the lower environments are required to be accessible from anywhere.

The code / package on Sitecore Marketplace is not updated so would recommend to visit GitHub repository at SitecoreSitemapXML. It also contains a fix made by hloken in his commit, where he introduces a setting in the config file specifying whether to generate / update the robot.txt.

So proud to be part of Sitecore Community where you always find solution to your problems.

Page Level Testing and Creating Page Level Test in Sitecore

Page Level Testing:

Page level testing allows testing the complete page (and not just a part of content) against its own version or against any other page built using the same template.

Please note for Page level testing:

  1. Page level tests can only be implemented for the pages built using the same template.
  2. If the pages to be tested are in the workflow state, the test cannot be implemented.
  3. It is possible to test one page against multiple pages

Configuring Page Level Test in Sitecore:

Let us say you want to test a different product pages (New Product 1 page) against another product page (New Product 2 page)

Step 1: Ensure that

  • Both the pages selected for testing are based on same template
  • Both the page are not in a workflow state

Step 2: From the Sitecore Launch Pad, navigate to Experience Optimization

pic_1

Step 3: Click on the blue button at top left – “Create page test”

Pic_2.jpg

Step 4: Select the original page to test.

pic_3

Step 5: Select the pages to test against the original selected page. Click on the plus icon to select the pages. Save test.

pic4

In the Add page dialog, select one of the following options:

  • Use existing: select an existing page to test against.
  • Previous page version: select the previous version of the current page (if one exists).
  • New page version: create a new version of the current page. You can edit the new version when you review the test.

addpage
select-page
pic_7

Step 7: Go to Review and Start Tab. After selecting the pages for testing, Sitecore displays the information about the test also you can adjust a number of parameters for the test. Sitecore displays previews of the pages that you have selected, with one variation selected and the other variations in the carousel under the selected variation. To select a different one, click any variation in the carousel.

Please note the following fields on the Review and Start tab:

An Information bar: It tells how long the test is expected to be running before it can be declared as a winner.

pic_8

My expected effect of changes: It makes a guess about the change in engagement value of the changes you have made.

pic_9

Percentage of visitors exposed to test: Specify the percentage of visitors that you want exposed to the test.

pic_10

For example, if you set the percentage at 40%, 40% of visitors will see one of the variations that you have created. The original variation is part of these. The remaining visitors – 60% in this example – will see the original variation. Sitecore calculates test results based on the visitors it exposes to the test.

Statistics: Select the statistical confidence level that the test needs to reach before it can declare a winner. You select one of 90%, 95%, or 99%. The higher the level, the longer the test needs to run.

pic_11

Test objective: Select a test objective. You select either one of the goals specified in the Marketing Control Center, or select Trailing Value/Visit (the total engagement value per visit – but only counting page views occurring after the visitors encountered the page being tested).

Select how to pick a winner: Following options are listed:

  • Automatically select a winner based on the test objective
  • Automatically select a winner based on the test objective, unless it significantly decreases engagement value
  • Manually select winner

pic_12

Duration: Specify the minimum and maximum time for the test to run:

pic_13

Minimum: select 3, 7, or 14 days.

Maximum: select 14, 30, or 90 days.

Select the appropriate options and click on Start test.

Content Testing and Creating Content Test in Sitecore

Content Testing

This type of test helps to identify how different content influences the site visitors. For example, you have a page that promotes the users to download a demo product and you can test how different slogan text can influence the number of downloads. This is where you can use the Content Test. To be able to implement the content test, the content to be tested should be stored in the item field and not as a data source item of a component.

A content test works by testing changes to the content, between versions. So to have the content test set up, ensure that the Workflow has been set up correctly.

Creating Content Test in Sitecore

Let us say, you need to market a new product on the website, so create two different slogans for the same product and select the slogan which is more likely to have the visitors interested more in the product. You select two different slogans to be tested for a product as:

  1. COOKIE CRISP – CHOCOLATEY CHIPS…LITTLE PIECES…BIG TASTE…
  2. COOKIE CRISP – THEY’RE MAGICALLY DELICIOUS

Step 1: Navigate to the new product page in the Sitecore and enter the slogan in the applicable field as “COOKIE CRISP – CHOCOLATEY CHIPS…LITTLE PIECES…BIG TASTE…”

New Product page

Step 2: Save, Submit, Approve without test and Publish.

Step 3: Open the new product page in Experience Editor. Lock and Edit the page. Experience Editor will now create the second version of the page. Now you add the new slogan as “COOKIE CRISP – THEY’RE MAGICALLY DELICIOUS”. The second item version inherits all the fields values, except the changed fields. The changed fields are fields that will take part in the test. Save and Submit.

pic_2

Step 4: Click on Approve with test.

Step 5: You can now preview and start the test. Preview and start test dialog contains pre-generated screenshots and settings for the test (min/max test duration, traffic allocation, test objective etc.). Select the expected effect of changes and click on Start test button.

pic_3

Step 6: Open the new product page in a new browser session, you will see two different variants of the slogans reflecting upon refreshing the new product page.

pic_final_1 pic_final_2
Please note that a Content Test can only be started for 2 item versions. It’s impossible to run a Content Test on more than 2 item version but it’s possible with Page Level Test which is covered in my next blog. Click here to read the blog