SQL
How to get the System User last accessed CRM Application info using SQL?
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
How to query CRM Global OptionSet Values in SQL?
Use the below select query to get the Global OptionSet values in SQL,
SELECT OS.NAME, L.LABEL, AV.VALUE FROM ATTRIBUTEPICKLISTVALUEASIFPUBLISHEDLOGICALVIEW AV JOIN OPTIONSETASIFPUBLISHEDLOGICALVIEW OS ON AV.OPTIONSETID = OS.OPTIONSETID JOIN LOCALIZEDLABELASIFPUBLISHEDLOGICALVIEW L ON L.OBJECTID = AV.ATTRIBUTEPICKLISTVALUEID WHERE OS.ISGLOBAL = 1 AND OS.ISCUSTOMOPTIONSET = 1 AND L.OBJECTCOLUMNNAME = 'DISPLAYNAME' AND OS.NAME ='<Provide Global OptionSet Schema Name>'