Sam Jones CRM Corner

Configure IIS Logging : Report Traffic within CRM

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] NULL,
[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

 

You can download the complete report XML HERE

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

GoogleLookup: do things you never thought possible with spreadsheets

 

A couple of weeks ago, I unwittingly stumbled across a serious contender for coolest Google product feature: GoogleLookup. GoogleLookup is a Google Spreadsheets function that attempts to return the value of any attribute for any entity that you specify. Here’s the syntax: =GoogleLookup(”entity”; “attribute”).

For those of you not familiar with functions in Google Spreadsheets (the function-savvy may skip to paragraph 3), here’s a really simple example of a more conventional function: sum. In any spreadsheet cell, if I enter =SUM(3+5) and hit the “return” button, the content of the cell will display 8. Functions can also refer to values already in your spreadsheet, which is what makes them particularly useful. For instance, the function =SUM(A2:A20) will instantly sum all of the values in column A, rows 2 through 20. If you want to try this yourself, go to http://docs.google.com/ and open a new spreadsheet. You can also learn more about functions in the Google Docs Help Center.

So what makes GoogleLookup really cool is that it references data not just from your spreadsheet (as with functions like =SUM(A2:A20)), but searches that incredibly expansive body of published information: the world wide web. By performing a targeted websearch, GoogleLookup attempts to return some fact about the entity you specify. City populations are a nice example. Entering =GoogleLookup(”Philadelphia”; “population”) in any spreadsheet cell…

screenshot: GoogleLookup function

screenshot: GoogleLookUp loading

returns the value 1,449,634, which is in fact accurate. This may sound like magic, but go ahead and try it in a spreadsheet of your own. Now try swapping out “Philadelphia” for other city names: New York, Boston, San Francisco, Bangkok, London…

Radical, no? Perhaps you are saying to yourself, “this seemingly magical feature is indeed rad, but what real world scenario would actually warrant use of this function? How can GoogleLookup help me?” Let’s look at one example.

My good friend Caroline recently asked me and Dan to recommend some awesome dance songs for her upcoming wedding. Needless to say, Dan and I had lots of suggestions. Rather than rattle them off while she took notes on a scrap of paper, I told Caroline that I would send her a spreadsheet. When I got home, I opened a new spreadsheet and typed out 26 dancable song titles. But I wanted to give Caroline a little more information — I wanted to include the artist of each song. I didn’t know the artists for some of the songs on the list (especially for some of the more questionable tracks, all suggested by Dan) but I could have looked each one up and then typed out the artists name. This might have taken 20 minutes. Instead, I used GoogleLookup to automate that process.

Take a look at the spreadsheet. In cell B2 (under “artist”) I typed =GoogleLookup($A2, “artist”). (ignore the $ symbol; it just means that I want the function to use column A even if I copy the formula into another column). I then copied this function into all of the cells in column B (just drag the lower right-hand corner and drop). Within moments, all of those cells were populated with likely values for the attribute “artist” given the entity in column A, the song title.

Now, as you will observe, the values are not perfect. Gloria Trevi is NOT the artist I had in mind when I suggested the classic rock tune, “Gloria.” And while UB40 did do a version of “Can’t Help Falling in Love With You,” I would urge Caroline to stick with the Elvis original. But there were also a lot of accurate values. And one cool subfeature of GoogleLookup is that clicking into the cell will trigger a pop-up citing the source (url) of the value and offering a “more options…” link.

screenshot: click into cell for more info

Clicking “More options…” will give you up to three alternative values (and the sources from which these values come).

screenshot: more options for GoogleLookup

You can easily change the displayed value by selecting one of these alternatives.

As you can see on my Good Dance Songs spreadsheet, I used GoogleLookup to populate two more columns: album and year, both of which will be helpful to Caroline and her DJ (the functions, respectively: =GoogleLookup($A2, “album”), =GoogleLookup($A2, “year”)).

So next time your boss hands you a list of US states and asks you to fill in the capital of each state by EOD; or a list of years for which she would like the Academy Award winner for “Best Film”; or a 200 row list of NBA players, each of which needs a “height”… don’t fret, use GoogleLookup to quickly get the data you need.

First posted by Beah HERE

Productive JavaScript development in Microsoft Dynamics CRM 4.0

1)  Use an external JavaScript file while doing development.  It will save you a lot of time.  Regularly you make a code change and then you need to save and publish every time you want to retest.  Yes, the preview feature is helpful some of the time, but it is still very time consuming.   Using an external file will also allow you to leverage Visual Studio which offers IntelliSense and syntax checking.

Add this code to your onload.  It loads a JS file from within your ISV folder.   It uses a query string parameter to prevent caching and it waits until the dom is loaded before it executes the code.

var head = document.getElementsByTagName('head')[0];
var script = document.createElement('script');
script.type= 'text/javascript';
script.src= '/ISV/Entity/accounttesting.js?noCache=' +  Math.random();
script.onreadystatechange= function () {
if (this.readyState == 'complete' || this.readyState == 'loaded')
{
document.OnLoadCode()
}
}
head.appendChild(script);

Second, you can add calls in your OnSave and OnChange events to call code in the JavaScript file.  This will allow you to do all development in the one file.
OnSave:  document.OnSaveCode();
OnChange of account name:  document.OnChangeCode_AccountName();

Third, add a file name accounttesting.js to your /ISV/Entity folder under the website folder.  It will contain the following code.
document.OnLoadCode = function()
{
   alert("OnLoad code");
}

document.OnSaveCode = function()
{
   alert("OnSave code");
}

document.OnChangeCode_AccountName = function()
{
    alert("OnChange of Account Code");
}

// other functions
Last, start testing.  Any time you make a code change to the file you can refresh the account screen (F5) and the newest code will be executed.  Instant results!!!

Disclaimer:  You should only use this for development.  After you have tested your changes using this method you should copy the code back into the OnLoad and comment out the code that loads the JavaScript file.  Keeping the file in your OnLoad will save you time and headaches in deployment.  Plus, this is not supported.

2)  Use the debugger statement in your code if you want to walk through code.  You are able to leverage Visual Studio to analyze variable values as you execute your code.  Very useful!

First, within Internet Explorer, uncheck “Disable script debugging  (Internet Explorer)” and “Disable script debugger (Other)”

Next, add following code before any code you would like to debug.

debugger;

3) Use a helper library for calling the CRM web service through AJAX.    Calling the CRM web service requires you to work with the XML request using string concatenation which is very error-prone.  The libraries provide an object-oriented API to return data from CRM.  They also provide better error handling to help you troubleshoot errors

You can use my helper objects Click Here.  Ascentium has one as well Click Here.  Their library uses fetchxml while mine uses RetrieveMultiple.

4) Use Intellisense to help prevent mistakes as you code.  The book CRM as a Rapid Development Platform comes with a excellent Intellisense generator for Visual Studio.  http://www.thecrmbook.com/Video/Customization/EnableCRMJavascriptIntellisense/viewvideo.aspx.  It is well worth the 50 dollar investment.

You point the generator application against your instance of CRM and it generates JavaScript files for you to reference in your JS file.  Now you have Intellisense.  No more copy and pasting the field name from the forms window to make sure you don’t mis-spell it.  No more bugs because you spelled the word crmForm as crmform.

5)  Follow the DRY principle.  Don’t repeat yourself.  If you need to hide a field both onload and when a specific dropdown is selected, don’t copy and paste the code around.  Add functions to OnLoad and call the function wherever you need to.  Also, as you find useful code, such as hiding and showing fields, create functions for them and re-use them from project to project.

6)  Use try catch statements where possible.  Depending on your browser setings, it may be hard to determine what error is actually being thrown, especially in OnLoad.  Catching the error message and displaying an alert will help save time while troubleshooting errors.

try
{ 
   // Implementation here
}
catch (er)
{
alert(er.message);
}

 

First posted by Andrew Zimmer of Inetium

Kill Frozen Programs in Windows With a Shortcut

If you're a Windows user, it pretty much goes without saying that you've encountered a frozen program before. Often these jammed apps get labeled with the dreaded "Not Responding" message and simply refuse to do anything, even close. Usually, the only solution is to open the task manager, find the appropriate process, and choose to close it. Fortunately, a quicker and easier way exists.

As the good people at Lifehacker have pointed out (with the aid of HaxAttack), you can create a desktop shortcut that will automatically close any "Not Responding" applications whenever you double-click the shortcut. Here's how to set it up -- it's really easy:

1. Right click while on your desktop and select "create a new shortcut."
2. Quotes included, enter the following as the location: taskkill.exe /f /fi "status eq not responding"

That's it. From here, you can change the icon to make it prettier, or even set a shortcut key if double-clicking is too much work for you. When you launch the shortcut, your computer will automatically identify and terminate any programs that the computer deems "Not Responding." This useful little trick works on Windows XP Pro (but not XP Home), all versions of Vista, and Windows 7,