·
14 min read

CRM Usage Reporting Unleashed

Our CRM MVP guest blogger for today is David Jennaway who is the technical director at Excitation.

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 (links to other options are given below).

To configure an IIS 6.0 server to write log information for the CRM web site:

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

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

Related links:

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 can then populate it with data for the time periods you want, for example:

PeriodText

HourStart

MinuteStart

TotalMinuteStart

TotalMinuteEnd

00:00 – 08:00

0

0

0

480

08:00 – 09:00

8

0

480

540

09:00 – 10:00

9

0

540

600

10:00 – 11:00

10

0

600

660

11:00 – 12:00

11

0

660

720

12:00 – 13:00

12

0

720

780

13:00 – 14:00

13

0

780

840

14:00 – 15:00

14

0

840

900

15:00 – 16:00

15

0

900

960

16:00 – 17:00

16

0

960

1020

17:00 – 18:00

17

0

1020

1080

18:00 – 00:00

18

0

1080

1440

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.

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:

      clip_image002

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

For performance reasons, and out of habit, the aggregation is done in the SQL query, rather than in reporting services.

The full report definition follows at the end of this post.

Further thoughts – performance

This post focused on providing an overall process for getting usage information from the IIS logs. There are some performance overheads when using ODBC logging – it is more processor intensive that logging to file or raw formats, and it affects the IIS caching mechanism.

An alternative approach is to log to raw or file formats, and either import the data periodically to SQL Server (see http://support.microsoft.com/kb/296093/ and http://support.microsoft.com/kb/296085/ ), or process it with other tools, such as the LogParser tool (http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en)

You should also consider archiving or deleting old log data. One heavy user of CRM can easily generate over a megabyte of log data per day, so the quantity of data can mount up quickly.

If logging directly to SQL Server, there is a trade-off to consider with respect to SQL indexes. Adding a non-clustered index on the parameters column may help with the analysis performance, but would adversely affect the logging performance. A clustered index on the logtime column is recommended for all circumstances.

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

Source files

The SQL objects, and report RDL described in this post are available for download on the MSDN Code Gallery here (http://code.msdn.microsoft.com/IISLoggingCRM). I’d welcome any comments there, and I can add people as contributors to the code gallery resource if they have further reports or SQL objects they are willing to share.

Full report definition

This is a SQL 2005 Reporting Services report. The Data Source will need to be changed for your environment:

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
   3:   <DataSources>
   4:     <DataSource Name="IISLog">
   5:       <rd:DataSourceID>c04397fa-f432-4983-907e-3533dc2dea9c</rd:DataSourceID>
   6:       <ConnectionProperties>
   7:         <DataProvider>SQL</DataProvider>
   8:         <ConnectString>Data Source=crm4dev;Initial Catalog=IISLog</ConnectString>
   9:         <IntegratedSecurity>true</IntegratedSecurity>
  10:       </ConnectionProperties>
  11:     </DataSource>
  12:   </DataSources>
  13:   <InteractiveHeight>11in</InteractiveHeight>
  14:   <rd:DrawGrid>true</rd:DrawGrid>
  15:   <InteractiveWidth>8.5in</InteractiveWidth>
  16:   <rd:GridSpacing>0.25cm</rd:GridSpacing>
  17:   <rd:SnapToGrid>true</rd:SnapToGrid>
  18:   <RightMargin>2.5cm</RightMargin>
  19:   <LeftMargin>2.5cm</LeftMargin>
  20:   <BottomMargin>2.5cm</BottomMargin>
  21:   <rd:ReportID>c13a6342-b76c-4be0-8d0f-8598eac7fa80</rd:ReportID>
  22:   <PageWidth>21cm</PageWidth>
  23:   <DataSets>
  24:     <DataSet Name="IISLog">
  25:       <Fields>
  26:         <Field Name="username">
  27:           <DataField>username</DataField>
  28:           <rd:TypeName>System.String</rd:TypeName>
  29:         </Field>
  30:         <Field Name="TimePeriod">
  31:           <DataField>TimePeriod</DataField>
  32:           <rd:TypeName>System.String</rd:TypeName>
  33:         </Field>
  34:         <Field Name="DateText">
  35:           <DataField>DateText</DataField>
  36:           <rd:TypeName>System.String</rd:TypeName>
  37:         </Field>
  38:         <Field Name="DateYYYYMMDD">
  39:           <DataField>DateYYYYMMDD</DataField>
  40:           <rd:TypeName>System.String</rd:TypeName>
  41:         </Field>
  42:         <Field Name="ProcessingTime">
  43:           <DataField>ProcessingTime</DataField>
  44:           <rd:TypeName>System.Int32</rd:TypeName>
  45:         </Field>
  46:         <Field Name="Requests">
  47:           <DataField>Requests</DataField>
  48:           <rd:TypeName>System.Int32</rd:TypeName>
  49:         </Field>
  50:       </Fields>
  51:       <Query>
  52:         <DataSourceName>IISLog</DataSourceName>
  53:         <CommandText>select usernameonly as username, TimePeriod, DateText, DateYYYYMMDD
  54: , sum(processingtime) as ProcessingTime, count(*) as Requests
  55: from IISLogFilteredWithPeriods
  56: where logtime &gt; dateadd(d, -7, getdate())
  57: group by usernameonly, TimePeriod, DateText, DateYYYYMMDD</CommandText>
  58:         <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  59:       </Query>
  60:     </DataSet>
  61:   </DataSets>
  62:   <Width>16.5cm</Width>
  63:   <Body>
  64:     <ColumnSpacing>1cm</ColumnSpacing>
  65:     <ReportItems>
  66:       <Textbox Name="textbox1">
  67:         <rd:DefaultName>textbox1</rd:DefaultName>
  68:         <Style>
  69:           <Color>SteelBlue</Color>
  70:           <FontFamily>Tahoma</FontFamily>
  71:           <FontSize>20pt</FontSize>
  72:           <FontWeight>700</FontWeight>
  73:           <PaddingLeft>2pt</PaddingLeft>
  74:           <PaddingRight>2pt</PaddingRight>
  75:           <PaddingTop>2pt</PaddingTop>
  76:           <PaddingBottom>2pt</PaddingBottom>
  77:         </Style>
  78:         <ZIndex>1</ZIndex>
  79:         <CanGrow>true</CanGrow>
  80:         <Height>0.91429cm</Height>
  81:         <Value>Log By Date And User - Last 7 days</Value>
  82:       </Textbox>
  83:       <Matrix Name="matrix1">
  84:         <MatrixColumns>
  85:           <MatrixColumn>
  86:             <Width>1.35cm</Width>
  87:           </MatrixColumn>
  88:         </MatrixColumns>
  89:         <DataSetName>IISLog</DataSetName>
  90:         <RowGroupings>
  91:           <RowGrouping>
  92:             <Width>2.53968cm</Width>
  93:             <DynamicRows>
  94:               <Grouping Name="matrix1_DateText">
  95:                 <GroupExpressions>
  96:                   <GroupExpression>=Fields!DateText.Value</GroupExpression>
  97:                 </GroupExpressions>
  98:               </Grouping>
  99:               <Sorting>
 100:                 <SortBy>
 101:                   <SortExpression>=Fields!DateYYYYMMDD.Value</SortExpression>
 102:                   <Direction>Descending</Direction>
 103:                 </SortBy>
 104:               </Sorting>
 105:               <Subtotal>
 106:                 <ReportItems>
 107:                   <Textbox Name="textbox5">
 108:                     <rd:DefaultName>textbox5</rd:DefaultName>
 109:                     <Style>
 110:                       <Color>White</Color>
 111:                       <BackgroundColor>#6e9eca</BackgroundColor>
 112:                       <BorderColor>
 113:                         <Default>LightGrey</Default>
 114:                       </BorderColor>
 115:                       <BorderStyle>
 116:                         <Default>Solid</Default>
 117:                       </BorderStyle>
 118:                       <FontFamily>Tahoma</FontFamily>
 119:                       <FontWeight>700</FontWeight>
 120:                       <PaddingLeft>2pt</PaddingLeft>
 121:                       <PaddingRight>2pt</PaddingRight>
 122:                       <PaddingTop>2pt</PaddingTop>
 123:                       <PaddingBottom>2pt</PaddingBottom>
 124:                     </Style>
 125:                     <CanGrow>true</CanGrow>
 126:                     <Value>Total</Value>
 127:                   </Textbox>
 128:                 </ReportItems>
 129:               </Subtotal>
 130:               <ReportItems>
 131:                 <Textbox Name="DateText">
 132:                   <rd:DefaultName>DateText</rd:DefaultName>
 133:                   <Style>
 134:                     <Color>White</Color>
 135:                     <BackgroundColor>#6e9eca</BackgroundColor>
 136:                     <BorderColor>
 137:                       <Default>LightGrey</Default>
 138:                     </BorderColor>
 139:                     <BorderStyle>
 140:                       <Default>Solid</Default>
 141:                     </BorderStyle>
 142:                     <FontFamily>Tahoma</FontFamily>
 143:                     <FontWeight>700</FontWeight>
 144:                     <PaddingLeft>2pt</PaddingLeft>
 145:                     <PaddingRight>2pt</PaddingRight>
 146:                     <PaddingTop>2pt</PaddingTop>
 147:                     <PaddingBottom>2pt</PaddingBottom>
 148:                   </Style>
 149:                   <ZIndex>3</ZIndex>
 150:                   <CanGrow>true</CanGrow>
 151:                   <Value>=Fields!DateText.Value</Value>
 152:                 </Textbox>
 153:               </ReportItems>
 154:             </DynamicRows>
 155:           </RowGrouping>
 156:           <RowGrouping>
 157:             <Width>4.5cm</Width>
 158:             <DynamicRows>
 159:               <Grouping Name="matrix1_username">
 160:                 <GroupExpressions>
 161:                   <GroupExpression>=Fields!username.Value</GroupExpression>
 162:                 </GroupExpressions>
 163:               </Grouping>
 164:               <Sorting>
 165:                 <SortBy>
 166:                   <SortExpression>=Fields!username.Value</SortExpression>
 167:                   <Direction>Ascending</Direction>
 168:                 </SortBy>
 169:               </Sorting>
 170:               <ReportItems>
 171:                 <Textbox Name="username">
 172:                   <rd:DefaultName>username</rd:DefaultName>
 173:                   <Style>
 174:                     <Color>White</Color>
 175:                     <BackgroundColor>SlateGray</BackgroundColor>
 176:                     <BorderColor>
 177:                       <Default>LightGrey</Default>
 178:                     </BorderColor>
 179:                     <BorderStyle>
 180:                       <Default>Solid</Default>
 181:                     </BorderStyle>
 182:                     <FontFamily>Tahoma</FontFamily>
 183:                     <FontWeight>700</FontWeight>
 184:                     <PaddingLeft>2pt</PaddingLeft>
 185:                     <PaddingRight>2pt</PaddingRight>
 186:                     <PaddingTop>2pt</PaddingTop>
 187:                     <PaddingBottom>2pt</PaddingBottom>
 188:                   </Style>
 189:                   <ZIndex>2</ZIndex>
 190:                   <CanGrow>true</CanGrow>
 191:                   <Value>=Fields!username.Value</Value>
 192:                 </Textbox>
 193:               </ReportItems>
 194:             </DynamicRows>
 195:           </RowGrouping>
 196:         </RowGroupings>
 197:         <Top>0.91429cm</Top>
 198:         <ColumnGroupings>
 199:           <ColumnGrouping>
 200:             <Height>0.53333cm</Height>
 201:             <DynamicColumns>
 202:               <Grouping Name="matrix1_TimePeriod">
 203:                 <GroupExpressions>
 204:                   <GroupExpression>=Fields!TimePeriod.Value</GroupExpression>
 205:                 </GroupExpressions>
 206:               </Grouping>
 207:               <Sorting>
 208:                 <SortBy>
 209:                   <SortExpression>=Fields!TimePeriod.Value</SortExpression>
 210:                   <Direction>Ascending</Direction>
 211:                 </SortBy>
 212:               </Sorting>
 213:               <Subtotal>
 214:                 <ReportItems>
 215:                   <Textbox Name="textbox4">
 216:                     <rd:DefaultName>textbox4</rd:DefaultName>
 217:                     <Style>
 218:                       <Color>White</Color>
 219:                       <BackgroundColor>#6e9eca</BackgroundColor>
 220:                       <BorderColor>
 221:                         <Default>LightGrey</Default>
 222:                       </BorderColor>
 223:                       <BorderStyle>
 224:                         <Default>Solid</Default>
 225:                       </BorderStyle>
 226:                       <FontFamily>Tahoma</FontFamily>
 227:                       <FontWeight>700</FontWeight>
 228:                       <PaddingLeft>2pt</PaddingLeft>
 229:                       <PaddingRight>2pt</PaddingRight>
 230:                       <PaddingTop>2pt</PaddingTop>
 231:                       <PaddingBottom>2pt</PaddingBottom>
 232:                     </Style>
 233:                     <ZIndex>4</ZIndex>
 234:                     <CanGrow>true</CanGrow>
 235:                     <Value>Total</Value>
 236:                   </Textbox>
 237:                 </ReportItems>
 238:               </Subtotal>
 239:               <ReportItems>
 240:                 <Textbox Name="TimePeriod">
 241:                   <rd:DefaultName>TimePeriod</rd:DefaultName>
 242:                   <Style>
 243:                     <Color>White</Color>
 244:                     <BackgroundColor>#6e9eca</BackgroundColor>
 245:                     <BorderColor>
 246:                       <Default>LightGrey</Default>
 247:                     </BorderColor>
 248:                     <BorderStyle>
 249:                       <Default>Solid</Default>
 250:                     </BorderStyle>
 251:                     <FontFamily>Tahoma</FontFamily>
 252:                     <FontWeight>700</FontWeight>
 253:                     <PaddingLeft>2pt</PaddingLeft>
 254:                     <PaddingRight>2pt</PaddingRight>
 255:                     <PaddingTop>2pt</PaddingTop>
 256:                     <PaddingBottom>2pt</PaddingBottom>
 257:                   </Style>
 258:                   <ZIndex>5</ZIndex>
 259:                   <CanGrow>true</CanGrow>
 260:                   <Value>=Fields!TimePeriod.Value</Value>
 261:                 </Textbox>
 262:               </ReportItems>
 263:             </DynamicColumns>
 264:           </ColumnGrouping>
 265:         </ColumnGroupings>
 266:         <Width>9.73968cm</Width>
 267:         <Corner>
 268:           <ReportItems>
 269:             <Textbox Name="textbox3">
 270:               <rd:DefaultName>textbox3</rd:DefaultName>
 271:               <Style>
 272:                 <BorderColor>
 273:                   <Default>LightGrey</Default>
 274:                 </BorderColor>
 275:                 <BorderStyle>
 276:                   <Default>Solid</Default>
 277:                 </BorderStyle>
 278:                 <FontFamily>Tahoma</FontFamily>
 279:                 <PaddingLeft>2pt</PaddingLeft>
 280:                 <PaddingRight>2pt</PaddingRight>
 281:                 <PaddingTop>2pt</PaddingTop>
 282:                 <PaddingBottom>2pt</PaddingBottom>
 283:               </Style>
 284:               <ZIndex>6</ZIndex>
 285:               <CanGrow>true</CanGrow>
 286:               <Value>
 287:               </Value>
 288:             </Textbox>
 289:           </ReportItems>
 290:         </Corner>
 291:         <MatrixRows>
 292:           <MatrixRow>
 293:             <Height>0.53333cm</Height>
 294:             <MatrixCells>
 295:               <MatrixCell>
 296:                 <ReportItems>
 297:                   <Textbox Name="textbox2">
 298:                     <rd:DefaultName>textbox2</rd:DefaultName>
 299:                     <Style>
 300:                       <BackgroundColor>=iif(Fields!Requests.Value &gt; 5, "LightSteelBlue", iif(Fields!Requests.Value &gt; 0, "AliceBlue", "White"))</BackgroundColor>
 301:                       <BorderColor>
 302:                         <Default>LightGrey</Default>
 303:                       </BorderColor>
 304:                       <BorderStyle>
 305:                         <Default>Solid</Default>
 306:                       </BorderStyle>
 307:                       <FontFamily>Tahoma</FontFamily>
 308:                       <PaddingLeft>2pt</PaddingLeft>
 309:                       <PaddingRight>2pt</PaddingRight>
 310:                       <PaddingTop>2pt</PaddingTop>
 311:                       <PaddingBottom>2pt</PaddingBottom>
 312:                     </Style>
 313:                     <ZIndex>1</ZIndex>
 314:                     <CanGrow>true</CanGrow>
 315:                     <Value>=Sum(Fields!Requests.Value)</Value>
 316:                   </Textbox>
 317:                 </ReportItems>
 318:               </MatrixCell>
 319:             </MatrixCells>
 320:           </MatrixRow>
 321:         </MatrixRows>
 322:       </Matrix>
 323:     </ReportItems>
 324:     <Height>2.51428cm</Height>
 325:   </Body>
 326:   <Language>en-US</Language>
 327:   <TopMargin>2.5cm</TopMargin>
 328:   <PageHeight>29.7cm</PageHeight>
 329: </Report>

Cheers,

David Jennaway