xDB Reports with Powershell


First things first. To collect analytics you have to make sure you tell Sitecore you are not a robot:


Also, if you are running locally you may as well disable robots:

<setting name="Analytics.AutoDetectBots" set:value="false"/>

Next, if you have a way to identify your users you have to do so explicitly. The site I am working on is kind of special – it’s 100% behind a login – so one might think nothing is anonymous except the login page. I thought so too but learned the hard way that all analytics will be anonymous unless you tell Sitecore who the user is:

if (Tracker.IsActive && Sitecore.Context.User.IsAuthenticated)

Please remember that contacts can lock and don’t identify anonymous users.

It would probably make a lot of sense if Sitecore identified authenticated users using their names by default. With everything collected in memory and flushed on session end you would have all the time in the world to identify your contact differently. That said, a newly identified contact that hasn’t previously visited your site is flushed to xDB immediately (and locked at that time with a lease timestamp). This is probably why Sitecore decided not to identify contacts automatically.

Last Login Date

I am after a simple thing – a report of who used the site today or on any given day. Simple, right? Our site uses Sitecore users so first thing I tried was LastLoginDate. Can’t give me “any given day” but should easily give me today. Turns out, with federated authentication we don’t get that login date tracked. It’s not something that Sitecore does, I believe – it’s the underlying membership provider that we’re not using. Next stop is xDB.

xDB with Powershell

It’s really easy to get things from your xDB. Here’s a simple example. I wanted it in Powershell (via SPE) to get the report without having to deploy any new code to a running production instance. SPE does’t yet have commandlets that would wrap the Mongo/xDB APIs but, hey, Powershell can interop with .NET, right?

Turns out, calling those generic methods is hard. Calling generic methods with overloads – a torture. Generic methods with overloads that accept various forms of generic arguments and lambda expressions – close to impossible. It’s all reflection-based with MakeGenericMethod() and Invoke() and while you can use a script block with inline param() as a delegate, creating a particular form of Expression<Func<TTypeOne, TTypeTwo>>… good luck with that. I want those two hours of my life back!

The solution I found was to inline some C# with Add-Type and emit a helper type that would wrap the most “difficult” parts of xDB/Mongo APIs interaction. The rest was easy. Here’s an excerpt from the script that generates the report I was after:

Add-Type @'
    using MongoDB.Bson;
    using MongoDB.Driver;
    using MongoDB.Driver.Builders;
    using Sitecore.Analytics.Model;
    using Sitecore.Analytics.Data.DataAccess.MongoDb;
    using Sitecore.Analytics.Model.Entities;
    using Sitecore.Analytics.Tracking;
    public class QueryRunner 
        public static MongoCursor<VisitData> QueryVisits(MongoDbDriver driver, IMongoQuery query)
            return driver.Interactions.FindAs<VisitData>(query);
'@ -ReferencedAssemblies @(

# [skipped] Ask the user to give a $date via Read-Variable

$driver = [Sitecore.Analytics.Data.DataAccess.MongoDb.MongoDbDriver]::FromConnectionString("analytics");
$contacts = [Sitecore.Configuration.Factory]::CreateObject("tracking/contactManager", $true)

$query = [MongoDB.Driver.Builders.Query]::GTE("StartDateTime", $date)

[QueryRunner]::QueryVisits($driver, $query) | `
    %{$contacts.LoadContactReadonly($_.ContactId)} | `
    ?{$_.Identifier -ne ''} | `
    Select -ExpandProperty Identifier | `
    Get-Unique | `
    Get-User | `
    %{ [PSCustomObject]@{
        "User" = $_.Name
        "Name" = $_.Profile.FullName
        "Property" = (Get-Item . -ID $_.Profile.GetCustomProperty("Property"))."Field Name"
    }} | `
    Show-ListView -Property User, Name, Property


Querying the visits was easy. You get them straight from the Interactions collection. The same didn’t work for contacts though. There is a type similar to VisitData that used to represent a Contact document but it’s obsolete. The new entity model version of a contact doesn’t seem to be designed for direct xDB queries so that’s why I am using the ContactManager. It probably has to do with the contacts leasing and locking, not sure. The idea to use ContactManager came from a perfectly timed blog post from @briancaos.

My instance is single-tenant so no need to use QueryBuilder and add a site name condition when fetching visits. If I had to, I would package that into the emitted type. QueryBuilder is where you would find those overloaded method signatures with lambda expression as arguments. Powershell v4 has no good way of handling it. Or maybe I and google and stackoverflow together didn’t yet learn how.

If you have any questions please write a comment and I will be happy to explain this whole thing further. And please let me know if you think I missed something obvious that would have made it a lot easier.

Pavel Veller

Add a Comment

Your email address will not be published. Required fields are marked *

Or request call back