April 3, 2014

Creating a Responsive Email Template for Sitecore's ECM 2.x

By: Craig Taylor
April 3, 2014

Creating a Responsive Email Template for Sitecore's ECM
My client has been using Sitecore's Email Campaign Manager (ECM) for a while now and all of their emails have used the 'standard' default templates, with most of them using the "Simple HTML Message" template. (Note: I'm using "template" in a generic sense here, and am not talking about a Sitecore data template)  As their new sites designs began to incorporate Responsive Web Design (RWD) methodologies, the question of if ECM could handle responsively-designed emails came up.

The Problem

I didn't see why the 'standard' templates wouldn't work with RWD, so I attempted to paste the entire responsive HTML into the template.  What I quickly found was that pasting HTML into the "body" field of the simple HTML template rendered everything within a "<body>" tag.  This means that I can't add my own code to the "<head>" section as that is already defined in the layout that is utilized by the "Simple HTML Message" template.  Furthermore, I couldn't put any attributes on to the body tag as that is defined in the layout for the "Simple HTML Message" template.

I would need to create my own template.

The Solution

I needed a solution that would allow me to insert content into the "<head>" and "<body>" sections of the email and not have them overwritten by anything that Sitecore does to process the message.  In order to accomplish this, you'll need to create a layout, a 'head' template, a 'head' rendering and XSLT and a message branch as follows:

Layout
I created a new layout to be the base for the responsive template.

Sitecore Responsive Email Layout


The key points of the layout are the two placeholders for the 'head' and 'body' portions of the email.  This allows us to exactly specify what each section should look like.

Sitecore Responsive Email Layout Source


Note: the "msg-target-item" placeholder is required for ECM.

Head Template
In order to allow the layout to display the 'head' template as defined in the email, I had to create an 'inner content' template that would allow the content author to specify the 'head' section. (inherits "/sitecore/templates/System/Templates/Template")

Sitecore Responsive Email Head Template


On this 'head' template, I created a field named "Head" that is of type "Rich Text" and uses "/sitecore/system/Settings/Html Editor Profiles/Message Content" as its source.

Sitecore Responsive Email Head Template


For usability purposes, I added "[Add head section here]" to the "Head" field in the standard values.  This will quickly show the content author where the proper placeholder for the head section is when editing the email in ECM.

Sitecore Responsive Email Head Section Content


"Display Head" XSLT and Rendering
I created an XSLT rendering that would write out the contents of the "head" field from the email.

Sitecore Responsive Email Head XSLT Source


Note that my "headTid" variable contains the value of the template ID of the "Head" template.

I then created a rendering item in Sitecore to reference this XSLT.

Sitecore Responsive Email Head Rendering


Responsive Message Branch

I duplicated the existing "One-Column Message" branch and named it "Responsive Message."  This ultimately provides me with an item that is based on the "AB Test Message" template, which not surprisingly, allows A/B testing.

Sitecore Responsive Email Branch


You can see that I additionally gave the branch a new icon to differentiate it from the other message types, which is a Sitecore best-practice.

Within the "Message Root" item, I removed the "Footer" content item that was copied over from the "One-Column Message" branch and added my new "Head" content item.  I additionally changed the copy contained in the "Body" field of the "Content" content item from "[write your message here]" to "[Provide your responsive HTML here]".

I edited the presentation details to include all the necessary controls

Sitecore Responsive Email Presentation Details


From this, you can see that I have the required "Target Item" and "Process Personalization Tokens" controls in place.  Note: order is important for those at the top and bottom, respectively.  I added my "Display Head" control and set the placeholder to the "msg-head" placeholder.  The "Set Page Title" control takes the title of your ECM email and inserts it into the email that is dispatched.  The "Display Body" takes the "body" section of the ECM email and inserts it.

How to Use the Responsive Email Message Template

After getting all the pieces in place, the only step left is to allow your new branch to show up in the list of available message types when creating a new message.  Navigate to the "default" message type under "One Time Message" and set the insert options to allow the new "Responsive Message" branch.

Sitecore Responsive Email Message Default

Sitecore Responsive Email Insert Options


Note: You will have to do this for each Email Campaign Manger root that you have that needs to use this template.

Also, to ensure that your pretty new template has a pretty thumbnail image to show up in the SPEAK interface, make sure you populate the "Thumbnail" field of the "Appearance" section for your "Responsive Message" branch.

Sitecore Responsive Email Thumbnail


Now, when you choose a "One Time Message", our new template shows up.

Sitecore Responsive Email One Time Message


And while you're editing your message, you can clearly see where to place both the "head" and "body" sections.

Sitecore Responsive Email Head Body Edit


Note: For the CSS that is included in the HTML, I updated the configuration of Sitecore to allow us to store and serve CSS files.

So there is a bit of configuration involved in getting all the pieces together, but it's more time-consuming than difficult and your marketers will love you for it.

What's Next?

Now that I have a simple responsive template that can be used to copy-paste HTML into, I will be advancing this proof of concept to create specific responsive templates that my client can use.  I will take the responsive HTML that is created for these specific templates and implement it into a template that includes placeholders for specific content as demonstrated in this post: Building custom newsletter templates for Sitecore's ECM 2.x.

I'll also get this generic template into the Sitecore Marketplace so that you can simply install it and go.

Additional Reading

Pieter Brinkman's blog post about creating email templates: Sitecore ECM: How to create a e-mail template from Scratch – Part I
Mark Ursino's blog post about managing CSS in the media library: Managing CSS in the Sitecore Media Library
Frank van Rooijen's blog post about creating email templates in ECM 2.x: Building custom newsletter templates for Sitecore’s ECM 2.x 

February 5, 2014

Sitecore Clean Up Databases Tool Fails

By: Craig Taylor
February 5, 2014

Sitecore Clean Up Databases Tool Fails
We recently ran into an issue with a custom agent that we wrote for a client.  The agents job is to automatically sync images in a network location with those in the Sitecore media library.  The agent runs on a set schedule and was working great until the DBAs noticed that our Sitecore databases were consuming massive amounts of disk space.  Upon further examination, both the 'master' and 'web' databases were in excess of 700 GB.

The Problem

The master and web databases were growing at an exponential rate and the times of growth were tied to when our custom agent was processing.  We were quickly running out of disk space on our database servers.  I looked at the size of the images contained in the sync folder and knew that it should only be about 10GB.  It looked like Sitecore was maintaining old blob records.  I knew that Sitecore provided a "Clean Up Databases" (Sitecore 6.6) utility under "Control Panel\Database" to help with exactly this type of problem.

"Clean Up Databases" allows you to select the database(s) you would like to 'clean' and performs the following actions: (thanks to @DanSolovay and http://bit.ly/1fMv2mF)
  1. Removes items that have parents, but the parents are not in the item tree.
  2. Removes invalid language data.
  3. Removes fields for non existing items.
  4. Removes orphaned items.
  5. Removes unused blob records.
  6. Removes fields from orphaned items removed in step 4.
  7. Rebuilds the Descendants table (which stores parent/child relationships).
  8. Clears all caches.
I was interested in was removing the unused blob records that were apparently sticking around.  I ran the utility and let it go.  It ran and ran and ran.  I continued to check on the utility and could see that the size of the blobs table was slowing returning to a 'normal' size.  After 6 hours, I saw that an exception was thrown and that SQL Server then backed-out of the transaction, returning the database to the pre-clean up size.  After opening up the logs, I could see that the error was:

Exception: System.Data.SqlClient.SqlException

Message: The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

It turns out I was trying to delete so much data that the utility used up all the available locks for SQL Server.

The Solution

I figured that I needed some way to remove the unused blobs in smaller batches so that we could acquire the locks and then release them as data is deleted.  I contacted Sitecore Support to come up with a way to do just that.  With their insight into what was happening behind the scenes, we came up with a script that can be run manually to clean up the blobs table.

DECLARE @UsableBlobs table(
    ID uniqueidentifier
    );

INSERT INTO 
    @UsableBlobs    
select convert(uniqueidentifier,[Value]) as EmpID from [Fields]
where [Value] != '' 
and (FieldId='{40E50ED9-BA07-4702-992E-A912738D32DC}' or FieldId='{DBBE7D99-1388-4357-BB34-AD71EDF18ED3}') 
delete top (XXXXX) from [Blobs] 
where [BlobId] not in (select * from @UsableBlobs)

Using a temp table, the script looks for all valid blobs and stores them.  It then finds all the blobs not in the temp table and deletes them.  Simply replace the "XXXXX" in order to control how many records are deleted at once.  I started with one record to prove the concept and then went to 1000 and eventually was deleting 200k records at once with no issues.

I was able to clean up the databases manually and recover the space for the database servers.  We modified our scheduling of the agent to run less frequently and additionally created a 'clean-up' agent that ensures that we don't run into this issue again.

It should be noted that this script shouldn't replace the "Clean Up Databases" utility, but can be used in cases where things have already gotten out of hand.  When possible, use the built-in Sitecore utilities to manage the databases.

Also note: Please make backups of your data before running any scripts you find on the Internet.

Additional Reading

My Stack Overflow question regarding this issue: http://bit.ly/1dp3109
Dan Solovay's Stack Overflow answer about the "Clean Up Databases" Utility: http://bit.ly/1fMv2mF

December 6, 2013

Creating an HTTP Referrer Condition

By: Craig Taylor
December 6, 2013

Creating an HTTP Referrer Condition

Having the ability to tailor the site experience to your users is one of the major selling points of Sitecore.  One way to customize this user experience is to change content based on the HTTP referrer.

You can see Sitecore's out-of-the-box conditions in the content tree under "\sitecore\System\Settings\Rules\Conditional Renderings\Conditions".  While Sitecore has a folder called "Referrers" here that contains conditions that involve the referrer, there is not one included that compares the name of the referrer. I was surprised to see that Sitecore didn't already have a built-in condition for this, but it doesn't matter too much as Sitecore is easily extensible.  Let's create a new condition to check against.

Creating the Condition

Creating our new condition involves two steps:

  1. Writing code that can check the referrer.
  2. Wiring up the condition to our code that we created in step #1.
We need to write the code for the condition.  Either create a new solution or open up an existing solution that you would like to add your condition to.  I have opened a 'common' solution that contains common code that can be used across multiple sites.

using Sitecore.Rules;
using Sitecore.Rules.Conditions;
using System.Web;

namespace Client.Common.Conditions
{
    public class HttpReferrerContains : WhenCondition where T : RuleContext
    {
        public string HttpReferrer { get; set; }

        protected override bool Execute(T ruleContext)
        {
            if (!string.IsNullOrEmpty(HttpReferrer))
            {
                if (HttpContext.Current.Request.UrlReferrer != null && HttpContext.Current.Request.UrlReferrer.ToString().ToLower().Contains (HttpReferrer.ToLower()))
                {
                    return true;
                }
            }
            return false;
        }
    }
}

So what's going on here?  We are creating a new class that implements "WhenCondition" and "RuleContext."  We specify a public string (HttpReferrer) that will be passed into this class from Sitecore.  We override the base "Execute" method so that we can perform our own check to see if the referrer matches the value that we are looking for as passed in from Sitecore.  If the current UrlReferrer matches the value that we passed in from Sitecore, we return a "true" and we return "false" otherwise.

Now we move on to wiring up the condition inside of Sitecore.  Let's go down to the "\sitecore\System\Settings\Rules\Conditional Renderings\Conditions" folder mentioned earlier.  Right-click on the "Referrers" subfolder and insert a new condition.

Sitecore Conditions

The two fields we are interested in here are "Text" and "Type."  The "Text" field is where you specify how the condition will appear to the content author.

Sitecore Condition Text Field
The first portion of this field is the text that is displayed to the content author.  Inside the square brackets, the field consists of 4 parameters:

  1. 1st parameter: Name of the parameter being passed into your custom code.
  2. 2nd parameter: Name of a macro item.  Here, you can choose an item from "\sitecore\System\Settings\Rules\Common\Macros" and have that macro pop up when the content author clicks the 'clickable' word.
  3. 3rd parameter: Path passed to the macro parameter. (2nd parameter)
  4. 4th parameter: The 'clickable' word.
Our example generates the following box:

Sitecore Rule Set Editor


When the content author clicks on "specific value", they are prompted to enter a value to compare.  Also note that "where" is clickable as well and can be switched to "except where."

In the "Type" field, we add a reference to the assembly we created/added to in step #1.

Sitecore Condition Type Input

And that's it!  Now, when you go to personalize a component on the page, you can do so based on the HTTP referrer.

Additional Reading

See how Pieter created a condition and used a slightly different approach where you store the referrers as Sitecore items in the content tree as opposed to allowing free-text entry into the condition by the content author: http://newguid.net/sitecore/2011/sitecore-rules-engine-how-to-create-a-custom-condition/ 

October 22, 2013

Publishing Your XML Sitemaps to Your Content Delivery Servers

By: Craig Taylor
October 22, 2013

Publishing Your XML Sitemaps to Your Content Delivery Servers
My most-recent post talked about how you can utilize the Sitecore Sitemap XML module in order to automatically build robots.txt and XML sitemap files in a mulit-site, hardened environment so that the search engines can see your sitemap(s).  This works great, but a new issue arises if you have separate Content Managment (CM) and Content Delivery (CD) servers.

The Problem

When your content is published, the Sitemap XML module waits for the "publish:end" event to start building the robots.txt and XML sitemap(s).  It does so and stores the files in the file system.  The issue with this is that the processing and creation of files all happens on the CM server, which is behind a firewall.  The CD servers are the web-facing servers that will be serving the sitemap and robots.txt files.  We need the files on the CD servers.

The Solution

I went through a number of iterations in my head about how I could get the robots.txt and XML sitemap files to the CD servers.  I thought about scheduled jobs in Sitecore.  I thought about using an external application that is used for file replication.  I thought about a custom application inside of Sitecore so that a content author could manually kick off a deploy.

After running some of these ideas by a fellow Arke employee and Sitecore MVP, Andy Uzick, I came to realize that I was over thinking my solution by far.  This sort of problem had been solved long ago and is *WAY* simpler than I had originally thought.

The solution is to change the "SitemapXML.config" to subscribe to the "publish:end:remote" event instead of the "publish:end" event.  This event fires after a successful publish and let's remote servers perform the actions specified.  In our case, this lets the SitemapXML module know to build the robots.txt and sitemap(s) files.  Just ensure that you have the SitemapXML.DLL and SitemapXML.config files present on all your CD servers and you're good-to-go!

Very simple solution and now I don't have to manually copy files between servers!

October 17, 2013

Using the Sitemap XML Module in a Hardened, Multi-site Environment

By: Craig Taylor
October 17, 2013

using the sitemap xml module in a multi-site environment

We are using the Sitecore Sitemap XML module (2.0) for one of our clients.  Well, we're using it for a number of our clients, but let's just talk about the one client for now.

The module is pretty neat in that it generates a robots.txt and sitemap.xml files for your sites automatically on publish.  It also has the ability to automatically submit the generated sitemap(s) to a number of specified search engines.

The Problem

The module allows for multi-site instances through the configuration file.  You simply specify the Sitecore sites that should be included and the module will generate the necessary robots and sitemap file entries.  So if the module is already allowing for multi-site instances, what's the problem then?  The problem is that if you have followed the security hardening guide for your Sitecore environment, (you did, didn't you??)  you have prevented Sitecore from serving XML files.  The search engine crawlers are expecting the sitemap in an XML format and since Sitecore has been hardened, it refuses to serve the sitemap files.

The Solution

What we need to do is allow Sitecore to serve the sitemap XML files, but not serve up any other XML files that we want to keep secure. (license.xml file comes to mind. . . )

The first step is to tell sitecore to ignore requests to the sitemap files.  I have a naming convention in place in which all sitemaps on the client's instance are configured as "sitemap_<site_name>.xml."  With this standard in place, I updated "IgnoreUrlPrefixes" setting in the web.config as follows:

<setting name="IgnoreUrlPrefixes" value="/sitecore/default.aspx|/trace.axd|/webresource.axd|...|/sitemap_*.xml" />

This let's Sitecore know that it should not try to resolve to an item in the content tree if a request comes in for an XML sitemap.

The second step is to allow Sitecore to serve the XML file.  My client is using IIS 7, so I updated the handlers in the "system.webServer" node in the web.config.  I added a handler that only lets the sitemap XML files be served:

<add verb="GET" path="sitemap_*.xml" type="System.Web.StaticFileHandler" name="allow xml sitemap" />

I now have a multi-site solution that will automatically generate and manage the robots.txt and XML sitemaps that can be properly indexed by the search engines while still remaining secure.

September 20, 2013

Editing DMS Reports to be Multi-Site Friendly

By: Craig Taylor
September 20, 2013

Making DMS Reports Mult-Site Friendly
My client is using a single instance of Sitecore with multiple sites.  While reveiwing Sitecore's built-in reports for DMS, I noticed something that was going to make it difficult to produce reports that could be broken down by site.

Specifically, when looking at the "Latest Visits" report and when drilling down to the "Visit Detail (Session)" report, I noticed that I can't differentiate between the separate sites.  I would only see the visits as "/home" or "/contact-us."  It is impossible to know which site on the instance the visitor visited.

The Solution

In order to see which site was visited, we need to edit the default report provided by Sitecore.  In this example, we'll look at the "Visit Detail (Session)" report.  This can be accomplished in two steps:

  1. Edit the SQL Query for the report
  2. Edit the Report

Edit the SQL Query for the report

The SQL query for the "Visit Detail" report is located at \sitecore\System\Settings\Analytics\Reports SQL Queries\Visit Pages.  We need to update this query to include the site that was visited.  Luckily, DMS logs this information and stores it in the "Visits" table, in the "MultiSite" column.  We can add this "MultiSite" column to our query and once updated, the (SQL) query should look like this:
SELECT [Duration], [Url], [PageId], [PageId] as [PageIdObject], [Visits].[MultiSite] as [Site]
FROM [Pages] INNER JOIN [Visits] ON [Pages].[VisitorId] = [Visits].[VisitorId] AND [Pages].[VisitId] = [Visits].[VisitId]
WHERE 
  [Visits].[VisitId] = @VisitId
ORDER BY [DateTime], [VisitPageIndex]
I've given my "MultiSite" column an alias of "Site" for clarity.

Note: Even if you're not using Oracle, you might as well update the Oracle script as well in case a database switch is made later down the road:
SELECT "DURATION", "URL", "PAGEID", "PAGEID" as "PAGEIDOBJECT", "VISITS"."MULTISITE" as "SITE"
FROM "PAGES" INNER JOIN "VISITS" ON "PAGES"."VISITORID" = "VISITS"."VISITORID" AND "PAGES"."VISITID" = "VISITS"."VISITID"
WHERE 
  "VISITS"."VISITID" = :VisitId
ORDER BY "DATETIME", "VISITPAGEINDEX"

Edit the Report

Now that the report query has been updated to include which site was visited, we need to update the report to display this information.  While viewing the report, you can click the "Design" button in order to open the Stimulsoft Reports Designer application to edit the report.  I'm sure this might work well, but I wasn't able to get it to work correctly.  I instead went straight to the ".mrt" file in order to edit the report.  The mrt file for the "Visit Detail" report is located at \Website\sitecore\shell\Applications\Analytics\VisitDetail.mrt.  Open it up in your favorite text editor and you will see that it's just XML.

I searched in this file for where the other columns of the report query were being used.  I found the list of columns that were added and I added an additional column for my 'site' column:
<value>Site,System.String</value>
I also incremented the 'count' value for the columns.  The complete section looks like this:
<VisitPages Ref="5" type="Stimulsoft.Report.Dictionary.StiSqlSource" isKey="true">
        <Alias>VisitPages</Alias>
        <Columns isList="true" count="8">
          <value>Url,System.String</value>
          <value>Duration,System.Int32</value>
          <value>Hour,Hour,System.Int32,VisitPages.Duration_x002F_3600</value>
          <value>Minutes,Minutes,System.Int32,VisitPages.Duration_x002F_60</value>
          <value>PageId,System.Guid</value>
          <value>PageIdObject,System.Object</value>
          <value>DurationTimespan,DurationTimespan,System.TimeSpan,new_x0020_TimeSpan_x0028_0_x002C__x0020_0_x002C__x0020_0_x002C__x0020_0_x002C_VisitPages.Duration_x0029_</value>
          <value>Site,System.String</value>
        </Columns>
        <CommandTimeout>0</CommandTimeout>
        <Dictionary isRef="1" />
        <Name>VisitPages</Name>
        <NameInSource>DataConnection</NameInSource>
        <Parameters isList="true" count="1">
          <value>_x0040_VisitId,,14,0</value>
        </Parameters>
        <SqlCommand />
      </VisitPages>

This allows the report to make use of the field, but we now need to add the field to the report.  I then searched for where the text was being built for displaying the page visited.  I found this and added the site name in:
<Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>

The complete section looks like this:
            <Text21 Ref="58" type="Text" isKey="true">
              <Brush>Transparent</Brush>
              <ClientRectangle>4.83,0,13.97,0.4</ClientRectangle>
              <ComponentStyle>GeneralText</ComponentStyle>
              <Conditions isList="true" count="0" />
              <Font>Arial,10</Font>
              <Margins>0,0,0,0</Margins>
              <Name>Text21</Name>
              <Page isRef="11" />
              <Parent isRef="55" />
              <Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>
              <TextBrush>Black</TextBrush>
              <TextOptions>HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,</TextOptions>
              <Type>Expression</Type>
            </Text21>

The Results

Now, when drilling down from the "Latest Visits" report to the "Visit Detail" report, we can see that the (redacted) site name is now present on our report:
DMS Visit Detail Multisite Report

August 1, 2013

Creating a GeoIP Lookup Provider for Sitecore using MaxMind’s GeoLiteCity Database

By: Craig Taylor
August 1, 2013

GeoIP lookup provider in Sitecore
Sitecore ships with a built-in GeoIP lookup provider that is configured to use MaxMind’s lookup service. This service is provided for free with a “number of free lookups for testing and implementation convenience” (Engagement Analytics 6.6 Configuration Reference) This service works great, but what happens when your ‘number of free’ lookups expires?

GeoIP Options

When your free intro period expires, you essentially have two options if you want to continue to perform GeoIP lookups.
  1. Migrate to the MaxMind-Sitecore integrated service. (http://www.maxmind.com/en/sitecore) 
  2. Write your own, custom lookup provider.
While the MaxMind-Sitecore integrated service is amazingly simple to implement and run, I wanted to see what else could be used.  

The provider is essentially a ‘black box’ to Sitecore.  It doesn’t matter if it uses a web service, a local database or even a local file to return data lookups.  I decided to try to integrate with a local version of the GeoLiteCity database that is available for free download and use from MaxMind. (http://dev.maxmind.com/geoip/legacy/geolite/)

Creating a new Lookup Provider

Creating a new provider is as simple as creating a class that overrides the “GetInformationByIp” method.  
All we need to do is map the path to the database:

string geoLitePath = Settings.GetSetting(ModuleName + ".DatabaseLocation.GeoLiteCity", "~/app_data/GeoLiteCity.dat");

Pass the IP address to the look up service:

var lookUpService = new LookupService(HostingEnvironment.MapPath(geoLitePath), LookupService.GEOIP_STANDARD);

var location = lookUpService.getLocation(ip);

and populate the “WhoIsInformation” object so that Sitecore can use it:

WhoIsInformation information = new WhoIsInformation();
...
if (location != null)
{
    information.Country = location.countryCode ?? string.Empty;
    information.Region = location.regionName ?? string.Empty;
    ...
}

return information;



Update the Config

After you have created your new provider, update the “Sitecore/lookupManager/Providers” node in the web.config to include a reference to it.  It’s best to do this through a patch include file.


      
        geoLite
        
          />
        
      

And that’s it!  Sitecore makes it easy to change your GeoIP provider to anything else you may want to use. 
You can find complete source code and everything necessary to run this solution in the Sitecore Marketplace.