Tuesday, October 4, 2011

When was the last time a user logged in?

Recently, I was asked to look into which users where using our production MSCRM 2011 environment. Prior to MSCRM 2011, you could get a rough idea of which users where accessing CRM by looking through IIS logs. However, this was tedious and it was not very accurate, since the Outlook Client traffic is included in the IIS logs. Luckily, Microsoft made life easier with CRM 2011 and included a column named LastAccessTime in the SystemUserOrganizations table of the MSCRM_CONFIG database. To get the LastAccessTime for your users, you can run the query below from your Organization database, #ORGNAME#_MSCRM.
FROM SystemUser SU
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] SUO ON SUO.CrmUserId = SU.SystemUserId
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserAuthentication] SUA ON SUA.UserId = SUO.UserId
ORDER BY SU.DomainName
I want to credit Results on Demand who posted the query that inspired mine. You can see their version of the query that runs from the MSCRM_CONFIG database at http://www.resultondemand.nl/support/blog/post/How-to-get-the-last-login-date-and-time-of-a-CRM-user-(MSCRM-2011).aspx

Sunday, October 2, 2011

When was the last time a report ran in MSRM?

I was recently asked if it was possible to determine the last time a MSCRM report was run. To get this information you will need read access to the report server db and read access to the Reports table of your CRM instance. You can then run the this query
--NOTE!!! This query is designed to run on SSRS 2008 R2
--Replace #REPORT SERVER DB# with the name of the your SSRS DB 
--Replace #CRM DB NAME# with the name of the your CRM DB

;WITH theExecution AS (
    C.Name as reportid,
  FROM Executionlog E  
  INNER JOIN Catalog C on C.ItemID = E.ReportID AND C.Type = 2 AND C.Path LIKE '#CRM DB NAME#%'  
  AND E.Format = 'RPL'
), numberTimesRun AS (
  COUNT(*) NumberTimesRan
  FROM theExecution TE
  GROUP BY te.reportid
R.ModifiedOn LastModifiedOn,
TE.TimeEnd LastTimeGenerated,
FROM [RGA_MSCRM].[dbo].Report R
  LEFT OUTER JOIN theExecution TE ON TE.reportid = r.ReportId AND TE.rowNumber = 1
  LEFT OUTER JOIN numberTimesRun NTR ON NTR.reportid = r.ReportId
R.ispersonal = 0 --Exclude Personal reports 
Running this query will return a result set that looks like the below report but with more data.

ReportId ReportName FileNameLastModifiedOn LastTimeGenerated NumberTimesRan
7AD95C7E-737A-DF11-AB37-00145EED82FA Account Distribution Account Distribution.rdl2011-07-24 02:11:35.000 NULL 80
AAA85E84-737A-DF11-AB37-00145EED82FA Account Distribution Detail Account Distribution Detail.rdl 2011-07-24 02:11:35.000 NULL 80
82D95C7E-737A-DF11-AB37-00145EED82FA Account Overview Account_Overview.rdl 2011-09-30 20:06:08.000 2011-09-30 15:15:07.290 221

On thing to keep in mind when you report is that by default, SSRS log entries are kept 60 days. Entries that exceed this date are removed at 2:00 A.M. every day. On a mature installation, only 60 days of information will be available at any given time. You can read more about the SSRS execution log at http://msdn.microsoft.com/en-us/library/ms159110.aspx.

Saturday, October 1, 2011

What are the valid RootComponent types in the solution.xml file?

Recently, I needed to add a new RootComponent node to the soltuion.xml file for a Plugin Assembly, but I did not know the correct value for the type attribute. Unfortunately, a look at MSDN and Technet for the SDK didn't turn up any anything for the schema of the solution.xml file and a Google search turned up limited information for a few of the valid values. Well, I was sure that this information was stored somewhere in the CRM database. The query below is the result of my efforts.
SM.AttributeValue [type],
SM.Value [Description]
FROM StringMap SM
SM.AttributeName = 'componenttype'
SM.ObjectTypeCode = 7103
Running this query against the CRM database will give you a list of all the valid values for the type attribute of RootComponent node. For reference, I have included the results I got when I ran the above query.
  • <RootComponent type="1" is for a Entity
  • <RootComponent type="2" is for a Attribute
  • <RootComponent type="3" is for a Relationship
  • <RootComponent type="4" is for a Attribute Picklist Value
  • <RootComponent type="5" is for a Attribute Lookup Value
  • <RootComponent type="6" is for a View Attribute
  • <RootComponent type="7" is for a Localized Label
  • <RootComponent type="8" is for a Relationship Extra Condition
  • <RootComponent type="9" is for a Option Set
  • <RootComponent type="10" is for a Entity Relationship
  • <RootComponent type="11" is for a Entity Relationship Role
  • <RootComponent type="12" is for a Entity Relationship Relationships
  • <RootComponent type="13" is for a Managed Property
  • <RootComponent type="20" is for a Role
  • <RootComponent type="21" is for a Role Privilege
  • <RootComponent type="22" is for a Display String
  • <RootComponent type="23" is for a Display String Map
  • <RootComponent type="24" is for a Form
  • <RootComponent type="25" is for a Organization
  • <RootComponent type="26" is for a Saved Query
  • <RootComponent type="29" is for a Workflow
  • <RootComponent type="31" is for a Report
  • <RootComponent type="32" is for a Report Entity
  • <RootComponent type="33" is for a Report Category
  • <RootComponent type="34" is for a Report Visibility
  • <RootComponent type="35" is for a Attachment
  • <RootComponent type="36" is for a Email Template
  • <RootComponent type="37" is for a Contract Template
  • <RootComponent type="38" is for a KB Article Template
  • <RootComponent type="39" is for a Mail Merge Template
  • <RootComponent type="44" is for a Duplicate Rule
  • <RootComponent type="45" is for a Duplicate Rule Condition
  • <RootComponent type="46" is for a Entity Map
  • <RootComponent type="47" is for a Attribute Map
  • <RootComponent type="48" is for a Ribbon Command
  • <RootComponent type="49" is for a Ribbon Context Group
  • <RootComponent type="50" is for a Ribbon Customization
  • <RootComponent type="52" is for a Ribbon Rule
  • <RootComponent type="53" is for a Ribbon Tab To Command Map
  • <RootComponent type="55" is for a Ribbon Diff
  • <RootComponent type="59" is for a Saved Query Visualization
  • <RootComponent type="60" is for a System Form
  • <RootComponent type="61" is for a Web Resource
  • <RootComponent type="62" is for a Site Map
  • <RootComponent type="63" is for a Connection Role
  • <RootComponent type="70" is for a Field Security Profile
  • <RootComponent type="71" is for a Field Permission
  • <RootComponent type="90" is for a Plugin Type
  • <RootComponent type="91" is for a Plugin Assembly
  • <RootComponent type="92" is for a SDK Message Processing Step
  • <RootComponent type="93" is for a SDK Message Processing Step Image
  • <RootComponent type="95" is for a Service Endpoint