locked
Number of outlook users RRS feed

  • Question

  • Hi,

    I am using crm 3.0.

    How to know, how many users are using outlookclient for crm access and how many using webclient?

    Can we restrict users in outlook of using Sync funtion by entites or by role ? For example, I want
    user to sync outlook crm folder on certain entities...(Basically, I don't want user to use sync facility that often to increase speed)
    Thanks in advanced.

    Hugo
    Monday, August 3, 2009 11:03 PM

Answers

  • You can get some information about the clients used by querying the subscription table in the MSCRM database. The main fields you want are:
    systemuserid - links to systemuser table
    subscriptiontype - (0 = Offline client, 1 = Outlook client, 2 = Address Book provider)
    lastsyncstartedon - date/time of last sync.

    If a user has the offline client they'll typically have 3 records; one for each subscriptiontype, whereas the online Outlook client typicaly has 2 (subscriptiontypes 1 and 2), and there will be no entry if a user only users the web client.

    The following query will tell you what client has been used

    select

    systemuserid, fullname
    , case when exists (select * from Subscription s where subscriptiontype = 0 and s.SystemUserId = su.systemuserid) then 'Offline Outlook Client' when exists (select * from Subscription s where subscriptiontype in (1,2) and s.SystemUserId = su.systemuserid) then 'Outlook Client'
    else 'Web Client' end as Client
    from FilteredSystemUser su

    Note that there is no filtered view on the subscription table, so you'll need to be dbo in the database to query it
    Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
    Friday, August 14, 2009 12:00 PM
    Moderator
  • Many Thanks David for this solution.
    • Marked as answer by him912 Monday, September 7, 2009 12:26 PM
    Sunday, August 16, 2009 2:32 AM

All replies

  • There isn't a way to determine usage of the web vs. outlook clients from within CRM.

    In the user security role - turn off the sync to Outlook feature to prevent the users from synching or the 'go offline' privilege to prevent them from creating off-line copies of the database. - but it's all or nothing - there isn't a way to limit to just 1 or 2 entities. -
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, August 4, 2009 12:43 AM
    Moderator
  • Thanks Scott..

    There is no way i can count number of users using outlook to access CRM ?
    I don't have deployment manager access in my org. I thought there must be some
    info in there... so how do we decide about how user access crm thru outlook or webclient ?

    Also one more question...In my knowledge offline outlook client user SQL serever express edition to store
    data ...Does this vesion of SQL is only to cater CRM or it is there to consume by other apps as well? ( Like...MS office,ERP etc..). I ask this to confirm that if one has to restrict outlook client by not going offline, then no need to install SQL server Express ??? or is still requires ??


    Anyway, thanks for your prompt reply..

    Cheers,

    Hugo
    Tuesday, August 4, 2009 1:44 AM
  • That's correct Hugo - there is no way to determine which client (crm or outlook) a user uses. - and in my case I use both, so there's not a clear picture.

    If your company deploys software using SMS (microsoft product) - it may be possible to see how many users have the outlook client installed - but not to determine the amount of usage.
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, August 4, 2009 3:15 AM
    Moderator
  • You can get some information about the clients used by querying the subscription table in the MSCRM database. The main fields you want are:
    systemuserid - links to systemuser table
    subscriptiontype - (0 = Offline client, 1 = Outlook client, 2 = Address Book provider)
    lastsyncstartedon - date/time of last sync.

    If a user has the offline client they'll typically have 3 records; one for each subscriptiontype, whereas the online Outlook client typicaly has 2 (subscriptiontypes 1 and 2), and there will be no entry if a user only users the web client.

    The following query will tell you what client has been used

    select

    systemuserid, fullname
    , case when exists (select * from Subscription s where subscriptiontype = 0 and s.SystemUserId = su.systemuserid) then 'Offline Outlook Client' when exists (select * from Subscription s where subscriptiontype in (1,2) and s.SystemUserId = su.systemuserid) then 'Outlook Client'
    else 'Web Client' end as Client
    from FilteredSystemUser su

    Note that there is no filtered view on the subscription table, so you'll need to be dbo in the database to query it
    Microsoft CRM MVP - http://mscrmuk.blogspot.com http://www.excitation.co.uk
    Friday, August 14, 2009 12:00 PM
    Moderator
  • Many Thanks David for this solution.
    • Marked as answer by him912 Monday, September 7, 2009 12:26 PM
    Sunday, August 16, 2009 2:32 AM