Run the below query in SQL against the required CRM Organization to get the System User last accessed CRM Application details,
With CRMSystemUser
As
(
select Row_Number() Over(Partition by U.fullname Order By MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) desc, U.fullname desc) As Row_Num,
U.fullname AS [FullName], U.DomainName AS [UserName], U.BusinessUnitIdName AS [BusinessUnitIdName]
,DATEPART(YYYY,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Year]
,MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) [LastAccessAt]
FROM AUDIT A
INNER JOIN SystemUser U ON A.objectid = U.Systemuserid
WHERE Action= 64
GROUP BY
U.fullname, U.DomainName, U.BusinessUnitIdName, U.Systemuserid
,DATEPART(YYYY,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))
,DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))
,DATEPART(DD,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))
)
Select
CRMSystemUser.FullName,
CRMSystemUser.UserName,
CRMSystemUser.LastAccessAt
From CRMSystemUser
Where ROW_NUM = 1
order by
LastAccessAt desc
Output:
Credits to My Friend Krishna Gowtham
Like this:
Like Loading...
