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!

Using the Sitemap XML Module in a Hardened, 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.

Editing DMS Reports to be Multi-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: