Tag: PowerShell
Debugging Sitecore 9 Analytics Issues

Debugging Sitecore 9 Analytics Issues

With Sitecore 9 the way analytics is recorded and processed changed. Rather than everything being done by the IIS application, the architecture changed to include:

  1. A second IIS application called xconnect
  2. A windows services called Sitecore XConnect Search Indexer

As well as this Mongo was replaced by:

  1. SQL Server databases
  2. A SOLR XDB Core

Sitecore 9 also introduced data being secure in transit as well as at rest which means all traffic is encrypted using certificates.

While all these peices can be considered good, it does also create more points of failure that becomes harder to debug. So after spending a decent amount of time debugging why no analytic reports were loading and then why no data was appearing in the reports, I've made a checklist to go through.

Debugging Sitecore Analytics Checklist

1. XConnect Connection String

The main Sitecore application has connection strings for where it will find the XConnect service. They will look like this:

1 <add name="xconnect.collection" connectionString="https://mysite.xconnect" />
2 <add name="xconnect.collection.certificate" connectionString="StoreName=My;StoreLocation=LocalMachine;FindType=FindByThumbprint;FindValue=EF7F38B623E6664359110F2C6EB6DA00D567950F" />

One gives the path to the XConnect service and the other gives the path to find the certificate.

Firstly check that the XConnect path is correct and that you can access it and secondly check that the thumbprint corresponds with a certificate in the certificate store.

You can see what certificates are on your machine using this PowerShell script:

1Get-ChildItem -Path "cert:\LocalMachine\Root" | Format-Table Subject, FriendlyName, Thumbprint
2Get-ChildItem -Path "cert:\LocalMachine\My" | Format-Table Subject, FriendlyName, Thumbprint
3Get-ChildItem -Path "cert:\CurrentUser\My" | Format-Table Subject, FriendlyName, Thumbprint

2. Check certificate expiry date

Having a certificate is a good start, but it could still have expired.

Find the certificate in the certificate store by hitting start > manage computer certificates and find it in one of the folders.

Check the expiration date. If it's in the past then it's not going to work. This is common because the installation script for Sitecore 9 will set the expiration date to a year after install by default.

If it has expired you will need a new cert, you can create this by using the same script that you used to install sitcore origionally (Just the certificate bit).

1#Switch to correct vesion of SIF
2Remove-Module -Name SitecoreInstallFramework
3Import-Module -Name SitecoreInstallFramework -RequiredVersion 1.2.1
4
5#define parameters
6$prefix = "SitePrefix"
7$PSScriptRoot = "C:\resourcefiles9.0"
8$XConnectCollectionService = "$prefix.xconnect"
9$sitecoreSiteName = "$prefix.sc"
10
11#install client certificate for xconnect
12$certParams = @{
13 Path = "$PSScriptRoot\xconnect-createcert.json"
14 CertificateName = "$prefix.xconnect_client"
15 RootCertFileName = "SIF121Root"
16}

3. Check security permissons on the certificate

If you have a certificate and it's still valid then it could be that the app pool the site is running in doesn't have access to read the certificate.

To check this:

  1. Right click the certificate
  2. All Tasks
  3. Manage Private Keys
  4. Check that the app pool user for your site is listed in the list of users and that it has read permission. If it's not there add it using the name
    IIS APPPOOL\app pool name

4. Check License Files

Partner licenses only last for a year so if your using one of those it may have expired.

We're all used to checking the license file in the Sitecore application but XConnect has a license too.

These will be in:
sitename.xconnect\App_data\
sitename.xconnect\App_data\jobs\continuous\IndexWorker\App_data
sitename.xconnect\App_data\jobs\continuous\AutomationEngine\App_Data

Only the first will be used when your viewing the site, but it's worth knowing about the others to, incase you ever run a job manually.

5. Check manual rebuild of indexes

You can trigger a manual rebuild of the xDB index by following these instructions:

https://doc.sitecore.com/developers/90/sitecore-experience-platform/en/rebuild-the-xdb-index-in-solr.html

Remember in point 4 that it has it's own license file. It also has it's own connection strings.

6. Check XConnect site works in a browser

If you open XConnect in a browser you should recieve no certificate errors and a timestamp saying how long XConnect had been running for.

7. Check certificates are in the right store

This stack overflow post was a big help for me (https://stackoverflow.com/questions/26247462/http-error-403-16-client-certificate-trust-issue ). I was at the point where everything seemed right, but moving the certificates as shown here got it to the point of the analytics reports loading.

Windows 2012 introduced stricter certificate store validations. According to KB 2795828: Lync Server 2013 Front-End service cannot start in Windows Server 2012, the Trusted Root Certification Authorities (i.e. Root) store can only have certificates that are self-signed. If that store contains non-self-signed certificates, client certificate authentication under IIS returns with a 403.16 error code.

To solve the problem, you have to remove all non-self-signed certificates from the root store. This PowerShell command will identify non-self-signed certificates:

1Get-Childitem cert:\LocalMachine\root -Recurse |
2 Where-Object {$_.Issuer -ne $_.Subject}

In my situation, we moved these non-self-signed certificates into the Intermediate Certification Authorities (i.e. CA) store:

1Get-Childitem cert:\LocalMachine\root -Recurse |
2 Where-Object {$_.Issuer -ne $_.Subject} |
3 Move-Item -Destination Cert:\LocalMachine\CA

Checklist for data not going into Analytics

If you've got to the point of the analytics reports working, but not showing any data, this is my checklist for making sure data goes in. In my case I was trying to log site searches as per my article from a few years ago https://himynameistim.com/2017/09/13/populating-the-internal-search-report-in-sitecore/ there wern't any errors, but no data ever showed.

1. Enable Analytics Debugging

In Sitecore.Analytics.Tracking.config there is a setting to set the analytics logging level to debug. You will also need to set the log level on log4net root in Sitecore.config to debug.

2. Disable Robot Detection

In my case Sitecore thought I was a robot. Changing these settings will disable that:

1<setting name="Analytics.AutoDetectBots" set:value="false" />
2<setting name="Analytics.Robots.IgnoreRobots" set:value="false" />

3. Test analytics is tracking something

One of the hardest parts about analytics is it's not instant. The initial tracking only goes into the DB at the end of the users session and that's only for collection. It won't appear in the reports until processing has happened. So to speed this up:

Create a page called kill.aspx as follows. This will end the users session and trigger the data to be fed into the DB.

1<%@ Page language="c#" %>
2
3<!DOCTYPE html>
4<html>
5 <head>
6
7 </head>
8 <body>
9
10<div>Session Abandoned</div>
11<% Session.Abandon(); %>
12
13 </body>
14</html>

Next do something on the site that will cause some tracking to get added to the DB. In my case it was the search. Then go to kill.aspx to force session abondon.

Check the logs. You should see something like this...

125016 11:19:13 DEBUG [Analytics]: The CommitSession pipeline, ProcessSubscriptions is skipped - there is no subscriptions for location id: 4ebd0208-8328-5d69-8c44-ec50939c0967

Check the DB an entry should have gone into a shard db for [xdb_collection].[Interactions] table

To speed up processing, restart the main sitecore application.

Setting up local https with IIS in 10 minutes

Setting up local https with IIS in 10 minutes

For very good reasons websites now nearly always run under https rather than http. As dev's though this gives us a complication of either removing any local redirect to https rules and "hoping" things work ok when we get to a server, or setting local IIS up to have an https binding.

Having https setup locally is obviously a lot more favourable and what has traditionally been done is to create a self signed certificate however while this works as far as IIS is concerned, it still leaves an annoying browser warning as the browser will recognise it as un-secure. This can then create additional problems in client side code when certain things will hit the error when calling an api.

mkcert

The solution is to have a certificate added to your trusted root certificates rather than a self signed one. Fortunately there is a tool called mkcert that makes the process a lot simpler to do.

https://github.com/FiloSottile/mkcert#windows

Create a local cert step by step

1. If you haven't already. Install chocolatey ( https://chocolatey.org/install ). Chocolatey is a package manager for windows which makes it super simple to install applications. The name is inspired from NuGet. i.e. Chocolatey Nuget

2. Install mkcert, to do this from a admin command window run

1choco install mkcert

3. Create a local certificate authority (ca)

1mkcert -install

4. Create a certificate

1mkcert -pkcs12 example.com

Remember to change example.com to the domain you would like to create a certificate for.

5. Rename the .p12 file that was created to .pfx (this is what IIS requires). The certificate will now be created in the folder you have the command window open at.

You can now import the certificate into IIS as normal. When asked for a password this have been set to changeit

Installing Sitecore 9 when you've installed 9.1

Installing Sitecore 9 when you've installed 9.1

Installing Sitecore 9 was never the easiest of things, particularly when you compare it to how relatively simple Sitecore 8 was. But if you install Sitecore 9.1 on the same machine and then try your trusty Sitecore 9.0 script you may find it's got even harder and there's a bunch of new issues to worry about.

Multiple version of SIF

The first issue your probably going to run into is an error saying a name parameter is missing. Your script hasn't change, but what has changed is the default version of SIF that's now running.

So the first change you need to make is to ensure your running the correct version of SIF. You can do this either by adding the command to your script or running this before calling you install script. It will take effect for the duration of your PowerShell session.

1#Switch to correct vesion of SIF
2Remove-Module -Name SitecoreInstallFramework
3Import-Module -Name SitecoreInstallFramework -RequiredVersion 1.2.1

If you want to check what the active version of SIF is you can do this in a PowerShell window using

1Get-Command -Module SitecoreInstallFramework | Select-Object -Property name, version

Certificates Error - Part 1

Now we're calling the right version of SIF, the next issue I encountered was to do with certs. Specifically I got this error:

1TerminatingError(New-SignedCertificate): "Cannot process argument transformation on parameter 'Signer'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Security.Cryptography.X509Certificates.X509Certificate2"."
2Install-SitecoreConfiguration : Cannot process argument transformation on parameter 'Signer'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Security.Cryptography.X509Certificates.X509Certificate2".

This is due to the certificate for Sitecore 9.1 that has been installed. You can remove the certificate but then your Sitecore 9.1 install will break instead.

Alternatively add a "RootCertFilename" to the certificate definition:

1# Install client certificate for xconnect
2$certParams = @{
3 Path = "$SCLocation\xconnect-createcert.json"
4 CertificateName = "$prefix.xconnect_client"
5 RootCertFileName = "SIF121Root"
6}
7Install-SitecoreConfiguration @certParams -Verbose

Certificate Error - Part 2

This error looks exactly the same as the error above but you've already added that Root Cert File Name, so what's happening now.

1Install-SitecoreConfiguration : Cannot process argument transformation on parameter 'Signer'. Cannot convert the
2"System.Object[]" value of type "System.Object[]" to type
3"System.Security.Cryptography.X509Certificates.X509Certificate2".
4At C:\resourceFiles9.0\install.ps1:47 char:1
5+ Install-SitecoreConfiguration @certParams -Verbose
6+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7 + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
8 + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration

The error is saying that it expected to find 1 certificate but found many instead. Each time you run the script the number of thumbprints also keeps going up.

TBH I'm not overly certain what causes this as most of the time you get the one root cert and your done forever more. But somehow you get a second and then get you in a loop of repeatedly deleting certificates only to discover they still exist somewhere. You know it's also not the certificate for the 9.1 install as the certificate has the new name you added to it.

For me the issue was although I had deleted them from my personal certificates, trusted root certificates, and even the c:\certificates folder they were being added to, what I needed to do was run this in PowerShell.

1Get-ChildItem -Path "cert:\LocalMachine\Root" | Where-Object { $_.subject -like "*SIF121Root*" }
2 | Remove-Item

If your wanting to find out what certificates are installed on your machine you can run these

1Get-ChildItem -Path "cert:\LocalMachine\Root" | Format-Table Subject, FriendlyName, Thumbprint
2Get-ChildItem -Path "cert:\LocalMachine\My" | Format-Table Subject, FriendlyName, Thumbprint
3Get-ChildItem -Path "cert:\CurrentUser\My" | Format-Table Subject, FriendlyName, Thumbprint

Make sure your config is actually correct

This one is really just my mistake. Multiple versions of Siteocore have meant rather than just having one "C:\reousrcefiles\" folder on my machine (as per instructions), I now have a few with the version post-fixed to the end. It only becomes apparent that the path in the install.ps1 file is wrong part way through the install process.

Invert list selection with Sitecore PowerShell

Invert list selection with Sitecore PowerShell

I recently needed to run a script on a block of Sitecore content in invert the selection of a checklist and multilist. As I couldn't find any example of how to do this at the time, I thought I'd share what I wrote.

1#script to update tier
2Get-ChildItem -r -Path "master:\content\Home" -Language * | ForEach-Object {
3 if ($_.PSobject.Properties.name -match "Tier") {
4 [String[]]$tiers = $_.Tier -split "\|"
5
6 $_.Editing.BeginEdit()
7 $newtiers = Get-ChildItem 'master:\content\Lookups\Tiers\' | Where-Object { $tiers -notcontains $_.Id }
8 $_.Tier = $newtiers.Id -join "|"
9 $_.Editing.EndEdit()
10 }
11}

Get-ChildItem -r -Path "master:\content\Home" -Language * | ForEach-Object {

This line is getting the child items from the home node in the master db. The -r specified that it should be recursive and the -Language * specifies to include all languages. The results are then piped to a for each loop.

if ($_.PSobject.Properties.name -match "Tier") {

The field I needed to update was called Tier, as this was included in multiple templates I checked that the object included a field called Tier, rather than checking the template types.

[String[]]$tiers = $_.Tier -split "\|"

List fields in Sitecore get stored as a pipe separated list of the item Id's for the selected items. In order to do a comparison in Powershell I needed to turn the string back into an array using the split command. Notice the backslash that is needed before the pipe.

$_.Editing.BeginEdit()

To edit an item you need to begin an edit

$newtiers = Get-ChildItem 'master:\content\Components\Lookups\Tiers\' | Where-Object { $tiers -notcontains $_.Id }

This is where we get the new list of tiers to set as the selected ones for the item. The Get-ChildItem command is retrieving the original options that could be selected and the where-object statement is then excluding the ones that are in the $tiers array we just made.

$_.Tier = $newtiers.Id -join "|"

To save the new list we need to convert the results of the query into a pipe separated list using a join.

$_.Editing.EndEdit() } }

End the editing to save and close the the if and loop statements.