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.

Sitecore PXM 8.0: Add Vulgar Fraction Support

Background

Sitecore Print Experience Manager (PXM) is great; you can create dazzling printable documents that reuse your existing Sitecore content. You can enable your designers to add a personal touch to each document, or configure automation to allow document creation on the fly.

One of the primary components of PXM is its InDesign Connector plugin (IDC). It facilitates communication between Sitecore PXM and InDesign, allowing a designer to:

  • add Sitecore content to an existing InDesign document
  • create and update PXM Projects, which are a Sitecore representation of an InDesign document
  • manipulate master documents, which are essentially InDesign templates
  • save content changes directly into Sitecore items

“Vulgar” Fractions

Vulgar fractions are simply fractions that are “non-standard.” Examples of standard fractions are below. These are the ones Sitecore PXM supports out-of-the-box:

  • ¼ – ¼
  • ½ – ½
  • ¾ – ¾

There are a whole host of others, however, that can be added. Some include:

  • ⅛ – ⅛
  • ⅗ – ⅗
  • ⅚ – ⅚

How To Add Support for the Vulgar Fractions to PXM

  1. Open Website\Print Studio\Data\special.xml – this is the file that is used to map an HTML code (e.g. ⅛) to the corresponding UTF-8 character value (e.g. 8539).
  2. If you want to keep your file (roughly) in order by index, scroll down to the entry with index=”8501″.
  3. Insert the following elements:
     <codeposition index="8528" entity="frac17"/>
     <codeposition index="8529" entity="frac19"/>
     <codeposition index="8530" entity="frac110"/>
     <codeposition index="8531" entity="frac13"/>
     <codeposition index="8532" entity="frac23"/>
     <codeposition index="8533" entity="frac15"/>
     <codeposition index="8534" entity="frac25"/>
     <codeposition index="8535" entity="frac35"/>
     <codeposition index="8536" entity="frac45"/>
     <codeposition index="8537" entity="frac16"/>
     <codeposition index="8538" entity="frac56"/>
     <codeposition index="8539" entity="frac18"/>
     <codeposition index="8540" entity="frac38"/>
     <codeposition index="8541" entity="frac58"/>
     <codeposition index="8542" entity="frac78"/>
    
  4. Recycle the Sitecore PXM AppPool.

Bonus

If you're curious, this configuration is used by the Sitecore.PrintStudio.PublishingEngine.Text.Parsers.Html.HtmlEntityHelper to map between the HTML code  (e.g. &frac18;) to the corresponding UTF-8 character value (e.g. 8539).

Sitecore Email Experience Manager – Installation & Configuration

Let’s get Sitecore Email Experience Manager(EXM) to work. By work I mean let’s install, configure and send some emails from EXM. When I landed on this territory of Sitecore ecosystem I had no idea what it all takes to get EXM working but finally playing with around for few hours I got a hold of it. So, if you are looking to start with EXM with no background knowledge of how to install & configure it this blog post would help you get started.

Download

Latest version of Email Experience Manager can be downloaded from dev.sitecore.net. EXM is bundled up as a Sitecore package which can be installed on any Sitecore instance using Installation Wizard from Sitecore. Once the zip is downloaded point to note here is it is not a Sitecore package, the file name too has a disclaimer attached mentioning (not sc package). It contains many other packages too.

 Email Experience Manager Package

Installation

EXM Installation guide has all the details that are required to install EXM. Here I am going to mention an abstract of what is available in the guide. As a prerequisite before installing EXM module two steps needs to be completed,

1) Add two empty connection strings with the names master and exm.web. For example:

<add name="exm.master" connectionString="" />

<add name="exm.web" connectionString="" />

2) Add the two connection strings CryptographicKey and EXM.AuthenticationKey. The keys must be represented in hexadecimal format by 64 characters, where you can use the symbols 0-9 and A-F. For example:

<add name="EXM.CryptographicKey" connectionString= "E040C938FC9E4EBC3E93330B0F7837F284207B8180DB64CB5B6ABEB1AFBF6F5B" />

&nbsp;

<add name="EXM.AuthenticationKey" connectionString= "9D80B4E56AEE694058567BD89C936FB88F2DB1272A4E88F419B6501919E0BB25" />

Installation guide does mentions a note that should not be ignored,

Note

For security reasons, do not use the example key provided above.

How to generate these keys? Well I used Random.Org.

EXM Keys

EXM Keys 1

Copy the string generated in some text editor, remove all the white spaces and update connectionString value for both EXM.CryptographicKey and EXM.AuthenticationKey. As an example my string now looked like

<add name="EXM.CryptographicKey" connectionString="1acda6a3dd6178045eb13b28e4c5dac2cde2339ded292205743fc495d5edba84" />

<add name="EXM.AuthenticationKey" connectionString="ceb73b557b29f685d4b0ef6b2baeaba78ef539a9903d00adb9ad3783d60d4c7a" />

Let’s swing back to some action now! EXM installation is pretty simple and works like a charm as any other Sitecore package works. Install Email Experience Manager 3.4.1 rev. 170105.zip using Installation Wizard.

Installation will also dump EXM SQL database files so to complete the installation, move the following database files to the /Databases folder and attach them to a SQL Server instance.

o Sitecore.Exm.ldf

o Sitecore.Exm.mdf

o Sitecore.Exm_Web.ldf

o Sitecore.Exm_Web.mdf

I was eager to know what are the visual changes that were made to the content tree after the installation. Hope you too will be looking out for it, Are you?

After successful installation, the first visual change you will notice is on Launchpad which now have Email Experience Manager link under the Marketing Applications.

EXM Launchpad

Content Tree will have a new node “/sitecore/content/Email Campaign” with Message Types and Messages. The module is installed at “/sitecore/system/Modules/E-mail Campaign Manager”

EXM Tree

Configuration

There are few configuration changes that needs to happen before we can start the actual usage of EXM and send out some test emails.

1) Update connectionstrings.config file to have below exm.master and exm.web connection strings. As an example,

<add name="exm.master" connectionString="user id=user;password=password;Data Source=(server);Database=Sitecore_EXM" />

<add name="exm.web" connectionString="user id=user;password=password;Data Source=(server);Database=Sitecore_EXM.WEB"/>

2) In the /App_Config/Include/Sitecore.Analytics.Tracking.config file, ensure the value of the ClusterName setting is set to your instance host name. For example

<setting name="Analytics.ClusterName" value="sc82up2exmplay"/>

3) Lastly setup the message transfer agent. EXM has made two options available for setting up an agent based on client’s need.

  1. Sitecore Email Cloud
  2. Custom SMTP

Sitecore has already provided “The Sitecore Email Cloud compared to the custom SMTP” compassion that will help you to make a decision.

As a playground, it’s better to use Custom SMTP which will leave out licensing issues and subscription to App Centre. On a production environment, you might of course want to configure Sitecore Email Cloud.

Configuring EXM for Custom SMTP

To start using the Custom SMTP:

1) In the Website\App_Config\Include\EmailExperiencefolder, add the suffix .disabled to the end of the following file names:

  • EDS.Providers.Sparkpost.config
  • EDS.Providers.Sparkpost.Sync.config

2) In the Website\App_Config\Include\EmailExperiencefolder, remove the suffix .disabled from the following file names:

  • EDS.Providers.CustomSmtp.config.disabled
  • EDS.Providers.CustomSmtp.Sync.config.disabled

3) Update pop3Settings in EDS.Providers.CustomSmtp.config

4) Update smtpSettings in Sitecore.EDS.Providers.CustomSmtp.Sync.config

Making life better for a developer I have created xml patch files, ready for use that can be dumped into Sitecore instance at path Website\App_Config\Include\zzz

I used Gmail SMTP configuration to send out emails hence these files have all the details related to Gmail Pop3 and SMTP except username and password.

Lastly you might want to put on verbose logging to troubleshoot EXM issues if any faced after installation and while sending emails. To enable it update below setting to true in file Website\App_Config\Include\EmailExperience\Sitecore.ExM.Framework.config

<setting name="EXM.Debug" value="true" />

EXM Now Do Some Work!

Opening Email Experience Manager from Launchpad will take you to below screen where there is not data yet to report.

EXM Report

1) Update Default settings, more information can be found at The EXM default settings

EXM Default Settings

2) Create an email campaign. As can be seen from screen shot there are two types of it. For simplicity, I went ahead creating a Regular email campaign. Looking for more information see Types of email campaigns.

EXM Email Types

EXM has some inbuilt template that can be used while creating a campaign.

EXM default templates

I selected “Modern” as my campaign’s template. Are you wondering how do I get it? It is available in the EXM package as “Email Experience Manager Sample Newsletter 3.4.1 rev. 170105.zip” hence installing this Sitecore package will get a new template into EXM.

EXM templates

3) Creating a campaign is a five-step process. First one is to specify General information about the campaign.

EXM General

EXM reports From Email field to be mandatory if it is not specified

EXM Mandatory Fields

4) Select Recipients, in order to create a list of recipients check Creating A Contact List section of this blog post

EXM Recipients

5) Message Details

EXM Message Details

6) Review: Before sending out actual emails we can review the email by sending it out to our self

EXM Review

I placed myself as the reviewer and was able to get an email as below,

EXM Inbox

7) Delivery: Final step for creating an email campaign. Hitting Send Message starts sending out emails and keeps posted about the activities.

EXM Delivery

Moving back to dashboard now will have has some data to be reported

EXM Dashboard

Creating a Contact List

1) Open List Manager for Sitecore Launchpad

List Manager

2) Hit Create, different options are available to make it quick and easy for me I used Empty Contact List.

List Manager Create

3) Enter information related to Contact List and Hit Save

List Manager Save

4) Click on Contact Drop Down and click on Create and add new contact

List Manager Contact Dropdown

5) Enter Email address, First name and Last name values and Save it.

New Contact

6) Few warnings appears as contact is being created and indexed. Have patience I understand the eagerness to send out emails from EXM.

Contact List Index

7) On refreshing the page added contact will appear on the list

Contact Added

Tips for Troubleshooting Email Sending

1) EXM records all activities in two log files, so checkout for any errors into these log files.

  • Eds.log
  • Exm.log

2) Gmail blocks signing in from unsecure apps which it did for me too. Turning “On” Allow less Secure App setting will allow EXM to send email message vis Gmail SMTP. I received and email from Gmail stating they have blocked access for an unsecure app.

Google Unsecure

Enable App

Other way to enable this setting in Gmail is to,

  • Login into Gmail
  • Go to settings -> Account and Import
  • Click on Other Google Account Settings

Other Account Settings

  • Under Sign-in & Security click Connected apps & sites

Sign-In & Security

  • Enable Allow less secure apps. You may want to turn this setting off once playing around with EXM is completed.

Enable APP Other

Finally EXM is done and dusted. There is more to learn on EXM but whatever is noted down in the blog post helped me get started.

Sitecore Query XPath for Finding all Fields with Field Fallback Disabled

Summary

The query below returns all fields on your templates that have the field Enable field level fallback disabled. This can be useful if you are creating a multi-lingual site and want to turn field fallback on for the fields on your data templates, but aren’t sure for which it’s already been done.

/sitecore/templates/User Defined//*[@@templatename='Template field' and @Enable Shared Language Fallback!='1']

How Does it Work?

Let’s break down the components of the query:

/sitecore/templates/User Defined//*[@@templatename='Template field' and @Enable Shared Language Fallback!='1']

Start at the root of the user-defined templates. This should be adjusted if you’re creating your templates elsewhere. (In a Habitat module, for example.)

/sitecore/templates/User Defined//*[@@templatename='Template field' and @Enable Shared Language Fallback!='1']

Look at all descendants (recursively check all children).

/sitecore/templates/User Defined//*[@@templatename='Template field' and @Enable Shared Language Fallback!='1']

Only return items of template Template field whose Enable Shared Language Fallback checkbox is not checked (this can include an empty value or 0).

** Note that the Item Name of the Enable Shared Language Fallback field is used here; the name of the field you see when editing a field in the content editor will be its title: Enable field level fallback.

Issues or suggestions? Let me know with a comment.

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,