Tuesday, April 13, 2010

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. Thank you for your code. PowerObjects has commercial version of similar product (http://www.powerobjects.com/blog/2010/05/04/powerlog-%e2%80%93-usage-tracking-and-analyses-right-in-dynamics-crm/), but your example is more helpful for developers!

    ReplyDelete
  2. Видел. Спасибо. Подумаю о коммерциализации ;)

    ReplyDelete
  3. Speak English, man!))

    ReplyDelete
  4. 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!!!!

    ReplyDelete
  5. Hi Andy,
    Would this work with the CRM 2011 online version? If not, is there a way to do it?

    Regards,
    Ankur

    ReplyDelete
  6. Hello Ankur,

    My name is Andriy (as it is written at the header of this blog).

    Possibly yes, it would work but you should think where you would store visit information...

    ReplyDelete
  7. 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

    ReplyDelete
  8. Hello Ankur,

    Plugin must be redeveloped to be used in 2011 version. For online version of CRM it is better to use Azure to store this data. If you want to purchase development of this plugin drop me an email to a33ik@bigmir.net

    ReplyDelete
  9. Great stuff again Andriy!

    Does this take into account if the user is using the Outlook client that is constantly accessing CRM?

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. 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."

      Delete
    3. Yes, my functionality will "think" that user works with CRM.

      Delete
  10. Since CallerOrigin is deprecated in 2011, would you still suggest using this option:
    http://social.microsoft.com/Forums/en/crmdevelopment/thread/ad130915-aeea-4e8f-9043-602e76ed04f6

    ReplyDelete
    Replies
    1. Even in case it is deprecated it is possible to get information about your login using following trick - http://a33ik.blogspot.com/2012/06/callerorigin-in-plugins.html

      Delete