CRM Extensions

CRM Usage Report

One of customer wanted to have the possibility to see who and when was working in CRM. I knew that MVP David Jennaway had created such solution based on IIS’s logs. No matter how I tried – I failed to implement it. Logs weren’t written to log database. Also this approach doesn’t work in IFD deployment scenario.

So I left this idea and begun develop own solution.


Solution will consist of 3 parts:
1. DataBase.
2. Plugin which will fill log database (it will registered on most usable messages – Execute, RetrieveMultiple, Retrieve, Create, Update, Delete).
3. Report which will display the data.

DataBase. I decided not to use the CRM database to store users’ activity information because of performance. I’ve created table to store data. This table can be created with following script:

CREATE TABLE [dbo].[UserLog](
 [UserId] [uniqueidentifier] NULL,
 [UserName] [varchar](max) NULL,
 [OrgName] [varchar](max) NULL,
 [RecordDateTime] [datetime] NULL,
 [SourceHost] [varchar](max) NULL
) ON [PRIMARY]

GO

 

Plugin

Plugin function is to retrieve information about user, organization and IP address of the user’s computer. The code of the plugin:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;
using System.Data.SqlClient;
using System.Web;

namespace UserActionsLogger
{
    public class UALogger : IPlugin
    {

        #region Privates

        private readonly string _connectionString = string.Empty;

        #endregion Privates

        #region CTOR

        public UALogger(string config, string secureConfig)
        {
            _connectionString = config;
        }

        #endregion CTOR

        #region IPlugin Members

        public void Execute(IPluginExecutionContext context)
        {
            //Check that author is application (not async service or webservice)
            if (context.CallerOrigin == CallerOrigin.AsyncService || 
                context.CallerOrigin == CallerOrigin.WebServiceApi)
                return;

            try
            {
                //IP retrieving
                string hostname = string.Empty;
                HttpContext webContext = HttpContext.Current;
                if (webContext != null)
                    hostname = webContext.Request.UserHostName;
                if (webContext != null && hostname != string.Empty)
                    hostname = webContext.Request.UserHostAddress;

                //User Name retrieving
                Guid curentUserId = context.UserId;
                ICrmService crmservice = context.CreateCrmService(true);
                systemuser su = (systemuser)crmservice.Retrieve(EntityName.systemuser.ToString(), curentUserId, new ColumnSet(new string[] {"fullname"}));
                string username = su.fullname;

                //Removing of system accounts
                if (username.ToUpper() == "SYSTEM" ||
                    username.ToUpper() == "INTEGRATION")
                    return;

                //Savig of the data
                using (SqlConnection connection = new SqlConnection(_connectionString))
                {
                    connection.Open();

                    using (SqlCommand cmd = new SqlCommand("", connection))
                    {
                        cmd.CommandText = "Insert Into UserLog(UserId, UserName, OrgName, RecordDateTime, SourceHost) Values(@UserId, @UserName, @OrgName, @recordDateTime, @SourceHost)";
                        cmd.Parameters.AddWithValue("@UserId", curentUserId);
                        cmd.Parameters.AddWithValue("@UserName", username);
                        cmd.Parameters.AddWithValue("@OrgName", context.OrganizationName);
                        cmd.Parameters.AddWithValue("@RecordDateTime", DateTime.Now);
                        cmd.Parameters.AddWithValue("@SourceHost", hostname);

                        cmd.ExecuteNonQuery();
                    }

                    connection.Close();
                }
            }
            catch { }
        }

        #endregion IPlugin Members
    }
}

 

It is required to pass connection string to log DB in config property at the plugin’s step registration. Sample how to register the plugin’s step:

Report

SQL Query of the report:

--Creation of temp table to store time intervals
Create Table #TimeTable(StartDate DateTime, EndDate DateTime)

--Filling time intervals table
while @StartDate < @EndDate
Begin
    Insert Into #TimeTable 
    Values(@StartDate, DATEADD(minute, @Delta, @StartDate))

    Set @StartDate = DATEADD(minute, @Delta, @StartDate)
End

--Retrieving data for reports
Select
    Distinct
    t.StartDate
    ,t.EndDate
    ,UserName
    ,SourceHost
From 
    #TimeTable t
    Inner Join UserLog u 
        on u.RecordDateTime > t.StartDate 
        And u.RecordDateTime <= t.EndDate

--Temp table's deletion
Drop Table #TimeTable

 

Result:

Source code of plugin and report:

14 Comments

  1. You are my CRM hero! Love this! Now if only you could get a SSIS project that imports (create, Update) from an external source together!!!!

  2. Hi Andriy,

    I am sorry about mis-spelling your name.
    I can store the data in a local DB. The part I am not sure of is, how to use plugins with the online version of CRM 2011? Would you be able to guide me in this respect? I am pretty new to CRM and would really appreciate your help and guidance.

    Regards,
    Ankur

  3. Hello Mike,
    It doesn't matter which kind of CRM client is used (Web or Outlook) – all calls are done using WebServices and that means that my plugin will be triggered. But why do you need such kind of functionality? CRM 2011 already has OOB user actions logging.

  4. Was just wondering since the Outlook client is always synching with CRM and I did not know if this would be making constant calls to CRM, and thus show that the user is "accessing" CRM when all they do is have their Outlook open. If they have Outlook open every day but don't touch CRM from within it, I wouldn't want that to qualify as "logging in."

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.