Tuesday, April 30, 2013

Get the last log-in DateTime of CRM user in MS CRM 2011



In Microsoft Dynamics CRM 2011 you can get the last log-in time of a CRM user by using the below query:

SELECT
SU.SystemUserId
,SU.DomainName
,SU.FullName
,SUO.LastAccessTime
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 SUO.LastAccessTime DESC,SU.DomainName

Friday, April 26, 2013

Get FROM, TO, CC, BCC parties in Reports/SQL in MS CRM 2011


It's very common requirement to get the TO and FROM party list  in our report for activities. As we know it doesn't store as part of activity/activitypointer. It get stores separably in activityparty table, and all parties are separate entry in activityparty table.

I wrote a function to get the FROM, TO, CC, BCC or any other party list from CRM in your report. Juse use below function in your report, it will return all parties as comma separated string.

-- To : To Recipient
-- From : Sender
-- TO get "TO" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','To Recipient')
-- TO get "FROM" party list : SELECT [dbo].[fn_GetPartyList] ('B6166B16-9AA2-E211-854C-000C29749A28','Sender')

CREATE FUNCTION [dbo].[fn_GetPartyList](@ActivityId NVARCHAR(100),@PatryAttribute nvarchar(100))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(1000)
DECLARE @Indivisual NVARCHAR(100)
SET @Result=''
DECLARE @Count INT

SET @Count =(SELECT COUNT(partyidname) from FilteredActivityParty
   WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId)
IF(@Count>0)
BEGIN

DECLARE @Table1 TABLE(Row INT IDENTITY(1,1), partyidname nvarchar(100))
INSERT INTO @Table1
SELECT partyidname from FilteredActivityParty
WHERE participationtypemaskname = @PatryAttribute AND activityid = @ActivityId

WHILE(@Count>=1)
BEGIN
SET @Indivisual=(SELECT partyidname FROM @Table1 WHERE Row=@Count)
SET @Result=@Indivisual+','+@Result

SET @Count=@Count-1
END

END

DECLARE @LENGTH INT
SET @LENGTH=LEN(@Result)
RETURN SUBSTRING(@Result,0,@LENGTH)
END

Wednesday, April 3, 2013

Get all Attribute Detail of an Entity in MS CRM 2011

Use below query to get field specification details of any entity in CRM.


SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
LocalizedLabelView_2.Label AS AttributeDisplayName, AttributeView.Name AS AttributeName,
Type = (select XmlType from MetadataSchema.AttributeTypes where AttributeTypeId = AttributeView.AttributeTypeId)
FROM    LocalizedLabelView AS LocalizedLabelView_2
INNER JOIN AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId
RIGHT OUTER JOIN EntityView
INNER JOIN LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId
ON AttributeView.EntityId = EntityView.EntityId
WHERE   LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
 AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
 AND LocalizedLabelView_1.LanguageId = '1033'
 AND LocalizedLabelView_2.LanguageId = '1033'
 AND EntityView.Name IN ('contact')  // Change the entity name
order by AttributeDisplayName