Keeping Your Sitecore Tree Organized

There’s one big thing that should be on everyone’s mind when working with a lot of content.  That big thing is organization of the Sitecore content tree.  With a single site solution, organization can be a little less serious.  But, once you start to deal with a multi-site solution, organization is key.  There are so many different ways for a content tree to go from being very cleanly organized to being one of the most confusing things to try and navigate.  Admittedly, I like my content tree like how I have my folders on my computer, very organized.  That way, when you need to find something quickly, it is very easy.  If it’s disorganized, then it ends up being a longer process to find the item.  Which, multiplied by however many components you have on a page, could really affect how quickly you can create a site.  So, when I see a Sitecore tree that seems to have items spread out all over the place, I want nothing more than to help the client clean it up and give them the tools to keep it clean.

Component Tree 1

Let’s take a look at this screenshot.  In this multi-site solution, we have created components (or modules) to be added separately to the page allowing the content editor to essentially be able to build out a webpage from a blank slate.  So, due to the number of components that will be created, it is important to keep the components folder organized in order to quickly find the datasource you’re looking for.  As a developer, when setting up a components folder, one of the easiest, but more restrictive, ways is to create folders yourself and only allow the content editor to insert that type of component in that folder (eg. Creating a Rich Text folder and only allow a Rich Text component to be inserted below it).  This isn’t the way I wanted to implement this organization strategy since I didn’t exactly know if the client wanted them to be organized by site, by component type, by both, etc.  This way also doesn’t easily lend itself to growth since it’s more restrictive for the content editor.  So, a more flexible way to implement this is to create a new folder template and give all your components as an insert option to this folder.  But, the key step here is to also add the same folder template as an insert option as well.  This will allow the content editor to make folders as they please and organize the Sitecore tree to how it’ll make sense to them.

Component Folder Insert Options

Now, these were all ways as a developer to help the content editor.  But, a content editor should utilize these tools that the developer has implemented.  If a developer hasn’t implemented a good way to organize the Sitecore tree, it is absolutely worth asking.  This will only help out with confusion in similarly named components, identically named components that are different templates, etc (as mentioned in Keeping Item and Field Names Unique).  As a content editor, it is also worth thinking about how you want your data structured in Sitecore before you start building out your content.  This way, you could build out your folder structure logically before adding any sort of content.  If you don’t want the ability to create folders of your own, but want the restriction of only being able to add a specific component(s) under a certain folder, work with your developer and implement it together.  This way, you’ll have a way to organize your data that you’ll be happy with.  Personally, I’m a fan of giving the content editor the ability to structure their data as they please.  This allows the solution to easily expand if additional sites are added.  This also gives the ability for the content editor to change their mind about how the tree is structured.  Let’s say the content editor just wanted all the components to be grouped together regardless of site.  Well, after the content editor starts to get hundreds of components, that organization process may not be sustainable anymore.  Instead of needing to go to the developer and paying to have an additional ability to change how the content tree is organized, that ability is already there and the content author can alter that item structure without any negative effects to the site.

Component Tree 2

In the end, as a developer, the most important thing is to deliver a solution that the client is happy with.  Knowing full well that there will be some complex data that will need to be made easier for a content editor to work with, organization is key.  As you can see in the “Component Tree 2” screenshot, I have organized the components from the “Component Tree 1” screenshot into easier to understand folders by using the same insert options as shown in the “Component Folder Insert Options” screenshot.  If there are even more components, and there still are too many items under each folder, you can easily add another level of the site that they’re created for.  But, for this situation, just doing the one level is enough.  It is all dependent on how much content is going to be going into each site and how easily it is to understand where to find those items.

 

 

 

Keeping Item and Field Names Unique

There are many things that can help with confusion as a developer and as a content author.  One of the biggest confusions I’ve come across has been duplicate item names, fields, and placeholders.  Sitecore will render whatever the first instance of that name is.  So, if you have an item with the same name as another, it will render the one higher up in the tree.  The same idea expands to placeholders and field names as well.  There’s a way to work with the placeholders and not be required to create multiple placeholders that have the same function called “Dynamic Placeholders“. However, the other two require some discipline as a developer and a content author.

As a content author, to avoid general confusion, giving unique names to items will avoid a possible situation where content you’ve entered is not showing up properly or not at all.  I’ve run into a few situations where the client has asked me to troubleshoot why the content they’ve updated was not displaying like they were expecting.  When I started troubleshooting, I noticed that there were items that had the same name.  It turned out they were editing the lower of the items which meant the rendered content wasn’t actually updating.  In addition, I had also noticed that other renderings were missing content entirely.  After looking into this situation, I found out that there were different templates that were utilizing the same name as well.  So, the content author added a Rich Text block to the page and correctly pointed the Datasource.  However, the item above it was an Accordion template that shared the same name.  Since Sitecore uses the path to get to the Datasource as opposed to a GUID, the rendered page was trying to render the Accordion template in the Rich Text rendering, which didn’t render properly.  One of the easiest ways to do this is to either put all items that belong together in a folder or name each one differently but name them in a way that a content author will understand that they go together (see green highlighted area).

As a developer, however, one of the things that will avoid general confusion for content authors is to name your template fields well.  Naming something “Content” can work if you’re creating a template that’s going to be inherited in to multiple places.  However, if you are creating a field specifically for a template type, it’s worth naming your fields more specifically.  As you see in the screenshot, there are two fields named “Page Title”.  One field is being inherited while the other field was added to the template itself with the same name.  This means, the first one will be the field that gets rendered while the second one is about as useless as it can be.  To avoid these types of situations, it’s good to plan out your templates before you create them to see what fields are being inherited and what fields you’ll need to create.

Sitecore UserName & Password Hardening

All organizations have processes & procedures in place to reduce the surface of vulnerability. One of the way to reduce attacks typically includes having a password policy and Sitecore Experience Platform is no exception to it. The way by which one installs Sitecore and configure has a significant effect on the security of a website. Sitecore has in place a fair amount of documentation related to Security Hardening and Sitecore Security Hardening Guide. Recently I was working on a Request For Proposal (RFP) which had lot of questionnaire related to password policy, I was aware that password policy can be enforced for Sitecore but was not satisfied that I have complete details and specifics to implement the password policy.

This blog post is about a voyage “Sitecore Username & Password Hardening” during which I drilled down the information for various settings and options available in Sitecore related to username and password policy implementations. If you are interested to harden a Sitecore implementation join me on this voyage and keeping reading the blog post further.

There are good number of documents & blogs, see “Nice Reads” section of this post for all such details.

Let’s start with some fundamentals to be aware about,

  1. Sitecore uses .NET security engine and is built on top of .Net Membership Provider
  2. All information related to roles, users, passwords and access is stored into “core” database. Hah! Why am I writing this should this not be known? Well trying to share information and making sure I cover all audiences for the post from including starters, mid-level and experienced Sitecore geeks.
  3. It is possible to replace or extend default configurations by writing a custom provider.

Let’s start with some specifics now,

UserName

Jason’s post USING EMAIL ADDRESSES AS YOUR SITECORE USERNAME is almost a clone of Brian’s post Sitecore allow email adress as user name

Rhys Godfrey’s post Sitecore–Changing the Username validation rule is all you have to understand for setting by UserName Validation.

I do not want to bombard duplicated information.

Password

Q. Have you been asked to setup and enforce password policy?

Q. How to configure it for Sitecore?

Q. Where to start?

A. Here’s the answer, as said earlier Sitecore is built on .Net Membership provider start by looking <configuration>/ <system.web>/<membership> section of a web.config file on your Sitecore installation.

Q. What I need to look for?

A. The “sql” provider and the attributes of the provider.

Attributes Value Type
minRequiredPasswordLength int
minRequiredNonalphanumericCharacters int
requiresQuestionAndAnswer bool
requiresUniqueEmail bool
maxInvalidPasswordAttempts int
 passwordAttemptWindow int
enablePasswordRetrieval bool
passwordFormat string
passwordStrengthRegularExpression string

 

  • minRequiredPasswordLength : The attribute name itself is self-explanatory, it allows to specific the minimum number of character a password should have. The default value is 1 and of-course it cannot be 0 otherwise you will see below error,

1) minRequiredPasswordLength

What happens when some tries to setup password where length is less than minimum required?

Sitecore displays message and makes you aware of possible reasons,

Reasons

  • minRequiredNonalphanumericCharacters: Specify minimum number of non-alphanumeric characters a password must contain.
  • requiresQuestionAndAnswer: Sitecore do not support this attribute and setting it to true will not allow for creation of a new user.

3) requiresQuestionAndAnswer

  • requiresUniqueEmail : Validates if there is already another user with same email address or not. If found New User will not be created and a message will be displayed.

4) requiresUniqueEmail

  • maxInvalidPasswordAttempts: User will be locked out after sur-passing the number of times an incorrect password is specified for login to Sitecore.

Sitecore login page do not specifically mentions about the user being locked out.

maxInvalidPasswordAttempts

The information for a “Locked Out” user is available in User Manager,

locked out

  • passwordAttemptWindow


The PasswordAttemptWindow property works in conjunction with the MaxInvalidPasswordAttempts property to help guard against an unwanted source guessing the password or password answer of a membership user through repeated attempts. When a user attempts to log in with, change, or reset his or her password, only a certain number of consecutive attempts are allowed within a specified time window. The length of this time window is specified in the PasswordAttemptWindow property, which identifies the number of minutes allowed between invalid attempts.

If the number of consecutive failed attempts that a user makes to reset his or her password equals the value stored in the MaxInvalidPasswordAttempts property, and the time elapsed since the last invalid attempt is less than the number of minutes specified in the PasswordAttemptWindow property, then the membership user is locked out. The user is locked out by setting the IsLockedOut property to true until the user is unlocked by a call to the UnlockUser method.

If the interval between the current failed attempt and the last failed attempt is greater than the PasswordAttemptWindow property setting, the current invalid attempt is counted as the first. If a valid password answer is supplied before the maximum number of allowed invalid attempts is reached, the count of invalid password-answer attempts is set to 0 (zero). If a valid password is supplied before the maximum number of allowed invalid attempts is reached, the count of invalid password attempts and the count of invalid password-answer attempts are set to 0 (zero).

So for example if the values set for the attributes are maxInvalidPasswordAttempts=”5″ passwordAttemptWindow=”1”. A user will be locked out after he makes 5 unsuccessful attempts. If the user has made less 4 unsuccessful attempts and then he/she tries after “passwordAttemptWindow” (in our example 1 minute) the user will not be locked out as the attempt would have been set to 0 counter.

  • enablePasswordRetrieval
  • This attribute holds a value indicating whether the current membership provider is configured to allow users to retrieve their password. Default value is false and if you turn it on you might see below error as this attribute works in conjunction with passwordFormat.
  • enablePasswordRetrieval
  • passwordFormat
  • This attribute describes the encryption format for storing passwords for users. The values can be,
    Attribute Value Description
    Clear Not secure, do not use. Passwords are not encrypted.
    Encrypted Not secure, do not use. Passwords are encrypted using the encryption settings determined by the machineKey Element (ASP.NET Settings Schema) element configuration.
    Hashed Passwords are encrypted one-way using the SHA1 hashing algorithm.

    Hashed is the default one!

    How does it looks in Membership database (MS SQL) when these formats are changed?

    Below image is for “Clear” format

  • Clear
  • Below image is for “Encrypted” format
  • Encrypted
  • passwordStrengthRegularExpression
  • The PasswordStrengthRegularExpression property gets the regular expression used to evaluate password complexity from the provider specified in the Provider property. This is one of the most important attribute that would allow to implement strict password policy and set various rules related to passwords like,
    • Is greater than seven characters.
    • Contains at least one digit.
    • Contains at least one special (non-alphanumeric) character.

    All the above attributes are considered while a user is being setup into Sitecore from User Manager. Sitecore Login page ignores these attributes and just validates if the username and password is correct which make sense as otherwise consider a case that users were already setup and later on password policy was enforced to have at least one non-aplhanumeric character. Such users will not be able to login even if their password matches to whatever it’s is stored in database.

    I collected information related to sql provider from Membership Class and if you are looking for more information here is the place where you should watch out for.

    Nice Reads

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 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.

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,