If anyone finds this blog, I am currently in the process of moving from Typepad. My blog can be found at www.wookiecrm.typepad.com

Finally, I manged to take and pass the Microsoft Dynamics Extending Exam yesterday. The exam itself is relatively straight forward, but there are one or two tricky areas to do with deployment across multiple platforms.

Next stop the Applications Exam and then on to SQL and formal .Net qualifications.

Recently, I was asked by a customer if it was possible to execute some javascript when an Opportunity is marked as won. As you all know, the value of statecode is in fact on a separate dialog box and technically, there is no way to capture the event. However, this is another way around it, and that is the use the event.Mode value to determine which action raised the OnSave event in the first place.

Here is the list of events:

• None : 0
• Save : 1
• SaveAndClose : 2
• Delete : 3
• Load : 4
• Deactivate : 5
• Reactivate : 6
• Email Send : 7
• Email Reply : 8
• Email Forward : 9
• Kb Submit : 10
• Kb Reject : 11
• Kb Publish : 12
• Kb UnPublish : 13
• Kb Rate : 14
• Lead Unqualify : 15
• Lead Qualify : 16
• Quote Accept : 17
• Quote CreateOrder : 18
• Order ProcessOrder : 19
• Opportunity AddRelatedOrder : 21
• Opportunity AddRelatedQuote : 22
• Opportunity AddRelatedInvoice : 23
• Quote CreateRevision : 24
• Quote CloseQuote : 25
• Order CancelOrder : 26
• Invoice Close : 27
• Quote GetProducts : 28
• Quote Activate : 29
• Email ReplyAll : 30
• Contract Hold : 31
• Contract ReleaseHold : 32
• Contract Cancel : 33
• Contract Renew : 34
• Product ConvertToKit : 35
• Product ConvertFromKit : 36
• ContractDetail Cancel : 37
• Contract Invoice : 38
• Contract Clone : 39
• Incident Cancel : 40
• Email Assign : 41
• Change SalesStage : 42
• SalesOrder GetProducts : 43
• InvoiceGetProducts : 44
• TemplateMakeOrgAvailable : 45
• TemplateMakeOrgUnavailable : 46
• Assign : 47
• IncidentAssignToUser : 49
• OrderLock : 50
• OrderUnlock : 51
• InvoiceLock : 52
• InvoiceUnlock : 53
• ConvertResponse : 54
• ReportMakeOrgAvailable : 60
• ReportMakeOrgUnavailable : 61
• WorkflowAddCheckStep : 62
• WorkflowUpdateCondition : 63
• WorkflowCreateAction : 64
• SendInvite : 65
• WorkflowAddElseIfStep : 66
• WorkflowAddElseStep : 67
• WorkflowDeleteStep : 68

Example Usage :

if(event.Mode == 5) //deactivation

{

// Do some custom logic here

}

If you dont know which action triggered the event, you can use the following line in the onSave event of the form to find out :

alert(event.Mode);

Before you start, you should have a complete installation of CRM4.0 on a server. In this demonstration, the server is running Server 2003 and SQL 2005. An existing installation of CRM is being used as the test environment.

Introduction

Mobile Express for Microsoft Dynamics CRM 4.0 has finally been released by Microsoft. The intention is to provide a mobile solution for accessing a company CRM from any device regardless of platform. Mobile Express doesn’t just work on Microsoft platforms, but also Apple ones. All you need is a browser and the component to be installed on the server. However, during this investigation of the component, it has become apparent that there are a few more steps in between. In theory, installation of the mobile client should be relatively quick, but there are several configuration considerations.

1) The installation of CRM needs to be IFD (internet facing) which can be a difficult process to achieve especially when you consider that IFD requires the use of SSL and HTTPS to make it entirely secure.

2) The installation of the component automatically installs Update Rollup 5 on the server which cannot be avoided or reversed. Up until now, customers have been advised not to install Rollups unless they address a specific issue that the customer is experiencing. Unfortunately, Microsoft have given no choice in the matter and Update Rollup 5 is a pre-requisit of the installation procedure.

3) I have found no way of removing the component from a server. The component does not appear in the Add/Remove programs or anywhere else. So, this is a one way procedure. Once Mobile Express has been installed on the server, it cannot be reversed. The only option I have found so far is to physically hide the link to the configuration pages in SiteMap. Whilst this doesn’t remove the component, it does at least remove it from view.

With all that in mind, I have been asked to investigate the viability of the Mobile Express component with a view to offering it commercially to customers in the near future. Here is a step by step tutorial on how to install it.

Step By Step

For the purposes of demonstration, I am installing this in a VPC image. The VPC is a standard installation of CRM4 with configuration options that will common to most installations. In other words, I have tried to make the VPC as ‘vanilla’ as possible to avoid conflicts.

1) Download the component from the following URL (http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=f592ec6c-f412-4fd5-9a80-cd3bcbd26d8b#tm)

2) Move the 13mb file you have just downloaded onto the VPC image. It doesn’t matter where you put this, the desktop is fine.

3) Double click the file and run the installation process. Once it has been completed, you will be asked to reboot the server so advanced warning for the customer might be useful.

Walk through the wizard and reboot the server

5) Now the component has been installed, you can customise it. By default, only those fields that are ‘required’ by CRM will be shown in the Mobile Express Client, but generally, you would require many more fields to be shown. Navigate to the ‘Customise Mobile Express’ screen under the ‘Customisation’ menu in CRM.

6) Choose which entities will be visible to users of the Mobile Express client

7) Double click on each entity to view the attributes that will be available to users of the Mobile Express client

Notice that only ‘Business Required’ attributes are shown. Add as many more as you like

9) Publish the newly defined selected entities from the main screen

That’s it. You have successfully configured the Mobile Express Client . However, the users wont be able to access the server to view this information without a few additional set up steps. We need to make the installation IFD or Internet Facing. This procedure usually involves the setting up of SSL and server certificates, as well as the configuration of the CRM, but for the purposes of this demonstration, I am going to access the server via WiFi on my mobile device from within the network.

IFD Deployment

Usually, the company who is setting up the CRM knows in advance that the installation needs to be IFD, but sometimes, you need to do this after the fact. In order to manage this, you can download the IFDTool from here (http://support.microsoft.com/kb/948779)

1) Download the zip file from the link above

2) Copy the zip to the VPC (or the server that is r unning CRM)

3) Extract the contents of the zip file to the following destination : drive:\Program Files\Microsoft Dynamics CRM\Tools

4) Double click the CRM4IFDTool.exe

5) Make the ‘Authentication Strategy’ box display IFD+OnPremise

ref="http://wookiecrm.typepad.com/.a/6a00e5522eea818834011571137464970c-pi">

6) Complete the IFD Internal Network Address and Subnet Mask boxes

7) Click the ‘Add Button’

Complete the AD and IFD Domain Scheme values

9) Apply changes

10) Finally, ensure that the users who have access to the mobile client have the appropriate ‘Go Mobile’ security privilege.

Accessing the Mobile Express client from your phone

Once the IFD deployment has been completed, you will be able to use your usual internet client on your phone to access the CRM.

1) Open up your browser and enter a URL similar to the one below

http://192.168.100.50:5555/<YourOrganisationName>/m

Note that the above URL uses the IP address of your CRM server and the Organisation Name of the installation, not the Friendly name. For the curious, you can find some of the installation files for this component under the directory called ‘m’ in the CRMWeb site.

2) If you IFD installation is correct, you will be presented with a login screen, complete the details as you would normally

3) If your login was successful, you will see a list of available entities

I have tried this on various browsers and so far I have established that only Safari and IE seem to be sophisticated enough to handle this application. My Iphone has a lightweight browser that I use often and this just isn’t good enough. Another obvious point to note is that if you have any Javascript customisations, these will not be executed by the Mobile Express client.

This can present something of a problem if you have a heavily customised installation where data integrity is governed by Javascript in the OnLoad and OnSave events. Unfortunately, the mobile client doesn’t seem to understand the concept of read-only fields so any field that is read-only in full client, will be open to edits on the mobile client. This could cause issues with data integrity.

Another major draw back of the mobile client is the lack of support for Lookup fields. Most entities have related entity fields and unless you know the exact value that should go into this field, the Mobile Express client will throw an error if you enter a value that it cannot resolve.

The good news is that plugins are executed as normal. In this installation, I have a plugin that assigns the value of a particular field on Create of the Opportunity. The field that is usually read-only and is populated by the plugin is of course, open for editing in the mobile client, but even though I enter some rubbish that could cause a breach of data integrity, the plugin fires when the Opportunity is posted to the server and the spurious value is overridden.

Further things to note should be :

1) Not all entities are available to the Mobile Express client. Although you can add all the entities in the list, only the ones in Bold Black font will actually be viewable on your phone. So, competitors, Addresses, Contract Lines, Discounts, Campaign Activities etc are not available.

2) Likewise, not all related entities are available even to the entities that are included. So, Competitors from Opportunities are not shown. In fact, even though I have added all available entities to the installation, only those shown below are actually available to an Opportunity.

3) You can write notes, they are actually available to the entity as long as you include the Note entity.

4) Views are also available but unfortunately, you only get the first two columns. In the Iphone this is handled slightly better by the interface as you get a jog wheel to choose the drop down

5) Picklists are also handled in a similar fa
shion

6) You can also view user information but not edit it

Conclusion

After spending a couple of hours with the Mobile Express client, I have to conclude that it is very limited in it’s application. Although the ability to add records to each entity is available, it would be highly inadvisable if data integrity is of any interest to the customer. The opportunity for corrupting or entering spurious information is just too great. The only way to limit access is by using the native security roles of the user, but this presents it’s own problems. You do not have a choice of levels of access based on client, so it is not possible to restrict access from Mobile Express whilst allowing access from OnPremise. The only way to achieve this is to have two security roles for each person or a system where ‘outdoor sales people’ download the accumulated information from the day and people with appropriate security roles upload the information. Both options cause more cost than would be necessary if Microsoft were to add another level of security which identified the client as well as the user.

If we exclude the ability to add new records to the database for reasons of data integrity, then we are left with only the ability to view existing information. While this is undoubtedly useful, there are other more efficient ways to accomplish most of what is required in most cases. For instance, contact information, addresses, phone numbers are far more efficiently handled by Outlook and if the mobile client is synced with Exchange and using Push, then all this information is already to hand for the user rather than having to access it using the mobile client. In a real world situation, it is most likely that the mobile client would be used by Outdoor sales people so the lack of a Google maps integration is disappointing to say the least.

Unfortunately, there is another major limitation. As many users would perhaps be engineers etc, the inability to review service activities is an issue. The user can read them, but not write to them, presumably because of the multiple lookups and complexity of the scheduling engine. Inexplicably, Competitors are omitted from the list of available entities as are additional addresses. Likewise, History views are not available along with accompanying activities so there is no way to see what has been done on a case for instance. Most scarily of all, it is entirely possible to delete accounts from the CRM using the mobile client! The lack of customisation for the grid is terrible. I know that phone screens are very narrow, but the ability to scroll left or right is an absolute must. By limiting the user to only the first 2 columns in the grid, a whole plethora of problems could arise if the data is not rigourously controlled and there are multiple entries with the same information in those two columns.

I hope you’ve enjoyed this walk through.

Sometimes, interesting things drop into my inbox and I am very pleased to announce this one.

My colleague David Jennaway, Technical Director of Excitation Ltd has been publically named as one of the top 100 most influential people in the Dynamics World. There are only 3 listed from the UK, so this makes the inclusion of David on this list quite impressive. I hope to have many more years working with and learning from David and I am very proud to be associated with the company that he helped to establish.

You can read the list HERE

Ok, this isnt strictly Microsoft Dynamics, well, actually, it has nothing to do with Dynamics, but well, heck, I really want one of these. Apparently, you can pre-order them HERE or HERE

I’ll be the first in line but I hear a rumour that CoolGadget have bought several thousand.

Recently, I received an interesting problem from a customer. My customer told me that whenever he creates a new custom view for an entity and uses the AND/OR group clause, he receives a stack overflow error. For a few horrified minutes, I thought this might have to do with the SQL server but after some mooching around, I managed to find an appropriate Microsoft Knowledgebase Article describing the problem. Further testing confirmed that the error (shown below) was also present in the Advanced Find which is not terribly suprising since considering that the Advanced Find is utilised by the View engine.

Error :

Microsoft Dynamics CRM Error Report Contents

<CrmScriptErrorReport>
<ReportVersion>1.0</ReportVersion>
<ScriptErrorDetails>
<Message>Out of stack space</Message>
<Line>165</Line>
<URL>/RuleFinancial&#47;AdvancedFind&#47;AdvFind.aspx</URL>
<PageURL>/RuleFinancial&#47;AdvancedFind&#47;AdvFind.aspx</PageURL>
<Function>&#41;</Function>
<CallStack>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
<Function>&#41;</Function>
</CallStack>
</ScriptErrorDetails>
<ClientInformation>
<BrowserUserAgent>Mozilla&#47;4.0 &#40;compatible&#59; MSIE 7.0&#59; Windows NT 5.2&#59; .NET CLR 1.1.4322&#59; .NET CLR 2.0.50727&#59; .NET CLR 3.0.04506.30&#41;</BrowserUserAgent>
<BrowserLanguage>en-us</BrowserLanguage>
<SystemLanguage>en-gb</SystemLanguage>
<UserLanguage>en-gb</UserLanguage>
<ScreenResolution>1280x800</ScreenResolution>
<ClientName>Web</ClientName>
</ClientInformation>
<ServerInformation>
<OrgLanguage>1033</OrgLanguage>
<OrgCulture>2057</OrgCulture>
<UserLanguage>1033</UserLanguage>
<UserCulture>2057</UserCulture>
<OrgID>&#123;A0476863-44E0-440B-98C5-9041A4B40DE8&#125;</OrgID>
<UserID>&#123;B742828E-60F7-DD11-BA51-001E0BDBDAD8&#125;</UserID>
<CRMVersion>4.0.7333.3</CRMVersion>
</ServerInformation>
</CrmScriptErrorReport>

 I downloaded the HotFix from the KB Article (You receive an error message or you cannot select the lookup value in the Advanced Find page in Microsoft Dynamics CRM 4.0 ) and applied it to the server.

You can also request the Hotfix from HERE if it has been removed from the KB article in favour of Rollup 4

Warning : This file is 256mb!

Result : No Stack Overflow problems any longer. This fix is also included in Update Rollup 4 for those who are interested, but I have never advised applying a whole Rollup when a single Hotfix will do.

An oldie but a goodie. How to open a centered window from an ISV button. Just add the following to the JavaScript element in ISV.config.

Code

var width  = 400;
 var height = 200;
 var left   = (screen.width  - width)/2;
 var top    = (screen.height - height)/2;
 var params = 'width='+width+', height='+height;
 params += ', top='+top+', left='+left;
 params += ', directories=no';
 params += ', location=no';
 params += ', menubar=no';
 params += ', resizable=no';
 params += ', scrollbars=no';
 params += ', status=no';
 params += ', toolbar=no';
window.open('../../ISV/ContractLineStatusChange.aspx?id=' + crmForm.ObjectId

var width  = 400;
 var height = 200;
 var left   = (screen.width  - width)/2;
 var top    = (screen.height - height)/2;
 var params = 'width='+width+', height='+height;
 params += ', top='+top+', left='+left;
 params += ', directories=no';
 params += ', location=no';
 params += ', menubar=no';
 params += ', resizable=no';
 params += ', scrollbars=no';
 params += ', status=no';
 params += ', toolbar=no';
window.open('../../ISV/ContractLineStatusChange.aspx?id=' + crmForm.ObjectId + '&Status=' + crmForm.all.foc_paymentstatus.DataValue ,"ChangeContract", params);

Further to previous posts HERE on how to hide ISV buttons, I recently came across a new wrinkle in the problem, essentially, I needed to be able to access the properties of that ISV button in order to create a toggle button. Knowing as we do that CRM4 adds some random number to the definition of a button, I really needed to find a way around this.

The Problem

If you create a new ISV button for yourself (on accounts in this case), and you use the Developer Toolbar to access the information about it, you will notice the following :

This number ‘increments’ every time the page is loaded so it you dont have a static name any longer. This presents something of a problem when it comes to creating a toggle button and changing the styles. So, how can we do something about it?

The first question is….what doesnt change? Well, the tooltip definition doesn’t change for a start. When you create the ISV button in ISV.config, you add your tooltip variable right into the XML, like so.

The Solution

As well as the ToolTip variable, we also know that the menu bar is always designated as mnuBar1. We can iterate through a collection to find our particular button.

function OnCrmPageLoad()

{

  //Configure Display when the form loads.

  ConfigureToolbarDisplay();

  //Configure the display each time a user manually changes the window width size.

  attachEvent("onresize",ConfigureToolbarDisplay);

}


function ConfigureToolbarDisplay()

{


    //Toolbar buttons that are to be affected - referenced by the ToolTip value

   //assigned in the ISV.config

   ShowHideToolbarButton( "ISV Button" );

}


function ShowHideToolbarButton( btnTitle )

{


    //Get all toolbar buttons

    var toolBarButtons = document.all.mnuBar1.rows[0].cells[0].childNodes[0].childNodes;


    for (var i = 0 ; i < toolBarButtons.length ; i++)

    {



        var button = toolBarButtons[i];


	//Loop through the collection and find the btnTitle we are interested in

        if( button.title.match(btnTitle) != null )

        {


	//Assign our button to the ButtonID variable

	var ButtonID = button.id;



         }

    }


}


OnCrmPageLoad();

Lovely, we have a basic structure for defining our custom ISV button.

Now, lets have some fun.

I want my button font colour to be RED

Now we have the definition for our ISV button, we can do almost anything else we want with it despite CRM shenanigans with spurious numbers. However, there is one more fundamental problem and that is ‘how’ CRM creates the pages at run time. If you were to use the following code under the ‘var ButtonID….’ line, you would be able to change the text as expected, but your icon would disappear.

document.all[ButtonID].children[0].innerHTML = "Do Something";

Not really very helpful. You can also change the colour quite happily by using the following additional line :

document.all[ButtonID].children[0].style.color = "red";

Lets take a look at the developer toolbar and see what code is actually being generated when a normal ISV button is displayed.

As you can see, there is a SPAN class, then an A class, then the IMG and another SPAN which actually holds the text property which we want to influence.

By looking at the code generated when we use the lines above, we see the following :

Where did our IMG class go? Good question, and the short answer is, I dont know. However, it still leaves us with the problem of how to overcome this little limitation and the answer is to cheat. We know that the text is contained in the SPAN element, but we also want the IMG element to display. Luckily, there is an element called innerHTML which will do very well for our purposes. So, we create a new function called, ModifyButton with some additional parameters.

function ModifyButton (btnName, btnTitle, btnIcon){


try

	 {

	 //find the button we have specified

	 var navigationBar = document.getElementById(btnName);


	 //find the correct element of the menubar/button we have specified

	 var textArray = navigationBar.getElementsByTagName("SPAN");


	 //read our variables

	 var buttonIcon = "http://crm:5555/_imgs/" + btnIcon; //change this reference

	 var buttonText = btnTitle;


	 for (i = 0; i < textArray.length; i++) {


            textArray[i].innerText = buttonText;


	   textArray[i].innerHTML = "<A class=ms-crm-Menu-Label><IMG class=ms-crm-Menu-ButtonFirst src='" + buttonIcon + "'><SPAN class=ms-crm-MenuItem-TextRTL>" + textArray[i].innerText + "</A></></SPAN>";


          }

	}


 catch(ex){

 }

}

This function can be invoked from within the original ShowHideToolBarButton function like so

function FindToolBarButton( btnTitle )

{


    //Get all toolbar buttons

    var toolBarButtons = document.all.mnuBar1.rows[0].cells[0].childNodes[0].childNodes;


    for (var i = 0 ; i < toolBarButtons.length ; i++)

    {



        var button = toolBarButtons[i];


	//Loop through the collection and find the btnTitle we are interested in

        if( button.title.match(btnTitle) != null )

        {


	//Assign our button to the ButtonID variable

	var ButtonID = button.id;




        }


    }




   //Invoke ModifyButton function....

  ModifyButton(ButtonID,"My New Button","ico_16_4200.gif");




}

So, what do we get when we put all of this together?

My New Button now has the text that we specified above as well as the IMG tag containing the correct icon, but it still isnt red. Well, that is now fairly easy to handle. Just add the following line just above the ‘innerHTML’ element within the ModifyButton function.

textArray[i].style.color = "red";

In fact, you can now add any DOM style you want to your new button

textArray[i].style.backgroundColor = "Navy";

Practical Applications

Now that we know we can influence the title of the ISV button and it’s style, it’s really a question of how to use this. The original premise that took me off down this path was that I required a toggle button. I wanted to be able to change the caption of an ISV button and it’s function based on the value in another attribute on the form. This lends itself very nicely to setting account flags, changing addresses, updating records etc. Coupled with the power of SQL stored procedures, you could update the database directly although this is highly unsupported.

First posted by David Jennaway

Additional information by this author

A frequent request we come across is from companies who want to know which users are using CRM and when. The CRM platform provides the facility to gather detailed usage information by writing plug-ins, but a simpler and more general mechanism is to use the Internet Information Services (IIS) logging mechanism.

This article will explain how to derive useful usage metrics from the information provided by IIS logging. The main steps are:

1. Configure IIS logging to allow easy querying of the log data

2. Filter the raw log data to help identify usage patterns

3. Specify time periods to help categorize when people access CRM

4. Present the usage information in a graphical format through reports

Configure IIS logging

IIS logging allow the capture of information about every web request submitted to the server, and it is configured at the web site level. By default the log information is written to text files, but IIS can also write the information directly to a relational database, which allows easier analysis. For the purposes of this post, the simplest setup is to configure IIS 6.0 to write the log data to a SQL Server ODBC data source

1. Create a SQL Database. In this database, create a SQL table to hold the log data. IIS has a SQL script that will create the table with appropriate columns – this script will be in %windir%\system32\inetsrv\logtemp.sql

Download this file

Open the script within SQL Server Management Studio, swap to the correct database and execute the script

2. Create an ODBC data source on the IIS server with the connection details to the database in step 1. The data source must be a system DSN, and I’d recommend using Integrated authentication to SQL Server

 

3. In IIS Manager, open the Properties of the Microsoft Dynamics CRM web site. On the Web Site tab, select ODBC Logging in the Active log format dropdown, and set the associated properties to reflect the SQL table and ODBC DSN created in steps 1 and 2. If you specified Integrated authentication in the DSN, then the User name and Password will be ignored, though oddly, you cannot leave the password field blank

Filtering the log data

The IIS log will have a record for every request submitted to the web site. This includes requests for images, stylesheets and other supporting files, and can result in more data than you need . For instance, opening a CRM record can result in 30 records in the log table. There are also entries for CRM processes (such as the CRM Asynchronous Service) accessing the CRM platform.

This extraneous data can be filtered out with a SQL view. We use the following view definition:

CREATE view [dbo].[IISLogFiltered]
as
select ClientHost, username, LogTime, processingtime, bytesrecvd, bytessent, servicestatus, operation, target, parameters
from inetlog
where username not in (‘-’, ‘CRMTEST\CRM4DEV$’)
and parameters <> ‘-’ and parameters not like ‘lcid=%’

– Replace CRMTEST\CRM4DEV$ with the account used by the CRM services

This assumes the SQL table has the default name (‘inetlog’), and filters out anonymous requests, and those from the CRM services. It also filters out requests with no query string parameters (or where the only parameter is ‘lcid’), which excludes the requests for the supporting files. You can explore the log data in more detail, and adjust the filters as appropriate.

Specifying time periods to categorize access

Even with the SQL view described above, you are still a step away from getting useful information about when users access CRM. The raw data stores the date and time of each request in one field, but it will help to process this into time and date periods. The approach we use is to build a SQL table that contains definable time periods , and a SQL function to match the log time to a time period. The SQL table can be created with the following script:

CREATE TABLE [dbo].[TimePeriod](
[PeriodText] [nvarchar](20) NULL,
[HourStart] [int] NULL,
[MinuteStart] [int] NU
LL,
[TotalMinuteStart] AS ((60)*[HourStart]+[MinuteStart]),
[TotalMinuteEnd] [int] NULL)

You should end up with something that looks like this :

You can then populate it with data for the time periods you want, for example:

This splits the day into hour-long periods between 8:00 and 18:00, with one period before and after the hour-long periods. The table can be used to find the PeriodText from a datetime field using a SQL function:

CREATE function GetTimePeriodText (@DateTime datetime) returns nvarchar(20)
as
begin
declare @tm int, @ret nvarchar(20)
set @tm = 60 * datepart(hh, @DateTime) + datepart(mi, @DateTime)
select @ret = max(PeriodText) from TimePeriod where @tm >= TotalMinuteStart and @tm < TotalMinuteEnd
return @ret
end

It will also help to extract the date component, this can be done using the SQL Convert function. The following SQL view applies these functions to the above SQL view used for filtering. It also extracts the CRM organization name from the target (url), and removes the domain part of the username.

Create view IISLogFilteredWithPeriods
as
select ClientHost, username, LogTime, processingtime, bytesrecvd, bytessent, servicestatus, operation, target, parameters
, dbo.GetTimePeriodText(LogTime) as TimePeriod
, convert(nchar(10), LogTime, 103) as DateText — The last parameter defines the format
, convert(nchar(8), LogTime, 112) as DateYYYYMMDD — Useful for sorting dates
, case when charindex(‘/’, target, 2) > 2 then substring(target, 2, charindex(‘/’, target, 2) – 2) else ” end as Organization — Get organization name from target
, rtrim(substring(username, 1 + charindex(‘\’, username), len(username) – 1 + charindex(‘\’, username))) as usernameonly — Remove Domain part of user name
from IISLogFiltered

Note that the IIS log will store data in Universal Time (UTC). You can modify either the data in the TmePeriod table, or the logic in the GetTimePeriodText function, to apply timezone information. Another, though officially unsupported approach, would be to use the fn_UTCToLocalTime SQL function in the MSCRM database.

The whole structure should look like this :

Creating reports on the log data

Now we’ve got the structure to process the log data, we can present in to users via a SQL Server Reporting Services report. The layout is up to you, but we find an effective way to present the information is in a matrix, with the time periods along the columns, with conditional formatting to highlight periods of light or heavy use. The following is an example, using the report definition below:

The numbers are the count of requests, with the background colours indicating how heavy the use is (as it’s a test system I’m considering 5 or more requests per hour to be heavy usage).

The SQL statement that produced this report is:

select usernameonly as username
, TimePeriod, DateText, DateYYYYMMDD
, sum(processingtime) as ProcessingTime, count(*) as Requests
from IISLogFilteredWithPeriods
where logtime > dateadd(d, -7, getdate())
group by usernameonly, TimePeriod, DateText, DateYYYYMMDD

Further thoughts – more analysis

The report provided in this post is limited to displaying the number of request per user. Further ideas for analysis are:

• Using the processingtime data to get an idea of the processing on the IIS server. This is a measure of the elapsed time from starting processing the request in IIS, to submitting the end of the response, and will be affected by the load on the IIS server. This can be useful for identifying when and if requests take a long time to complete
• Identifying the types of entities accessed. The target can help identify which entity has been requested. This takes a bit of parsing, but the topic ‘URL Addressable Forms and Views’ (http://msdn.microsoft.com/en-gb/library/cc150850.aspx) in the CRM SDK will help
• IIS logs are not useful for identifying the type of data operation (e.g. create, update, delete, assign) has been performed. If you need this information, you would be advised to use a plugin/callout mechanism for the auditing – see http://blogs.msdn.com/crm/archive/2006/12/05/creating-solutions-a-custom-crm-usage-log.aspx for an example of this
• The techniques described in this post were developed for CRM 4, but also work on CRM 3. The only difference is that there is no need to extract the Organization name from the target on CRM 3