1

I would like to assess system usage patterns over time, and would therefore like to be able to extract either a count (or a list) of users who are accessing our Dynamics CRM-based application at a particular time (on demand?) or sampled at intervals.

Is there any way to do this in a straightforward way perhaps by querying some log via MS SQL Management Studio and an SQL script or through the system application itself or maybe even through SDK? Is this sort of query something that should be done through IIS or is it Dynamics itself which holds the necessary information?

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
John
  • 13
  • 5
  • 1
    It is not clear to me what you are trying to accomplish here. Can you try to explain your requirements? – Sean Lange May 23 '17 at 13:38
  • Yes.. for instance say I need to know how many users are accessing our Dynamics CRM based application at this moment. The audit log is not a real-time way to do it as it only lists the time someone accesses the system and does not indicate whether the access is still active. And there is no formal log in and out of the system either that might help. So I was thinking maybe there is some internal log file for user accesses I could tap into using say an SQL query.. – John May 23 '17 at 13:42
  • Not at all clear what you are really trying to do here but perhaps you should look at sp_who. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql – Sean Lange May 23 '17 at 14:06
  • The question is actually quite clear... The solution, on the other hand ... *scratches head* – Alex May 23 '17 at 15:51
  • I believe mixing the Audit User Access data and `modifiedon` fields might lead to a roughly accurate result, but I don't have the means to try at the moment. – Alex May 23 '17 at 16:01
  • This is not CRM specific and also StackOverflow offtopic. CRM is an ordinary web site, so your question should be stated as - [Check who is currently connected to an IIS web server](https://serverfault.com/questions/17862/list-who-is-currently-connected-to-an-iis-web-server). Following this pattern you will find tons of examples using google, this is only an example but to keep this simple - you should look for this information on your IIS server, not CRM application – Pawel Gradecki May 24 '17 at 07:53
  • Yes, I suspected IIS might be the key to this! I will follow up the recommended link accordingly. – John Jun 05 '17 at 12:25
  • 1
    Thanks. Arun Vinoth's approach below is really neat also and meets my immediate needs, except for the slight problem with the time part of the output from the query. Hopefully someone will help me crack that! – John Jun 06 '17 at 12:04

1 Answers1

0

We used to pull the active users using last logon date. This way we know if users are using the tool or system is stale.

Try parameterizing the below query with your between timeframe & get the result.

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
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • 1
    Arun, This works great thanks.The only problem is that the time part is being reported incorrectly, as in it shows the time as 2 hours earlier than the actual login time. I have checked all servers and my own laptop and all have the correct time and time zone settings. So where could this 2 hour discrepancy be coming from? Any ideas please? – John Jun 06 '17 at 12:01
  • Happy this helps someone. As you aware, the time will be UTC in database. Pls massage the time with timezone as needed.. [Also, kindly mark as answer, this will help others as well] – Arun Vinoth-Precog Tech - MVP Jun 06 '17 at 14:39
  • 1
    Arun, I have upvoted the answer but since I still only have a low "reputation", it is recorded but not reflected in the "publicly displayed post score". Meanwhile I have temporarily solved the time issue by adding 2 hours to the reported time within the SQL script. Unfortunately I have no knowledge of any other ways on how to "massage" the time with the timezone. – John Jun 08 '17 at 08:36
  • you can check this - https://stackoverflow.com/questions/16872007/date-time-conversion-from-timezone-to-timezone-in-sql-server – Arun Vinoth-Precog Tech - MVP Jun 08 '17 at 13:38
  • 1
    The above query does not only return last login; I found that it also returned the names of any user whose account has been updated or modified, for instance if he/she was temporarily disabled and then enabled, on being enabled the name would be caught by the query – John Jan 19 '18 at 09:54