Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Which Users are on Which Teams (or not)

  • 1.  Which Users are on Which Teams (or not)

    TOP CONTRIBUTOR
    Posted Jan 09, 2019 02:22 PM
    Can someone please help?  I'm trying to get a list of Users that are not on the "All Active Users" Team.  I've tried using advanced find and fetch xml builder, i can get all of the of the Users that are on the team but not the Users that are not.

    Any help is appreciated,

    ------------------------------
    Jim Corriveau
    A.W. Chesterton Company
    Groveland MA
    ------------------------------
    CRMUG Summit - Post


  • 2.  RE: Which Users are on Which Teams (or not)

    Posted Jan 09, 2019 06:54 PM
    Hi Jim,

    Is "All Active Users" a team name or view of users?

    If it's a team name you should be able to use something similar to the below Screenshot.
    nLe9FcoEQSCb6ArqZBuB_Team Name Advanced Find.PNG


    If its a view you should be able to create a view based on User status as shown in the Below Screenshot.

    PAI1bjdgSiGydB5Gndrm_User Status Advanced Find.PNG
    Hope this is helpful!

    ------------------------------
    Bailey Moss
    Account Manager
    mscrm-addons.com
    Atlanta, GA
    ------------------------------

    CRMUG Summit - Post


  • 3.  RE: Which Users are on Which Teams (or not)

    TOP CONTRIBUTOR
    Posted Jan 10, 2019 05:11 AM
    Bailey,

    Thanks for the help.  "All Active Users" is a team.  The filter below will return all Enabled Users that are on the team when I set Team Name Equals All Active Users, this works, it gives me only Users on the team.

    When I set the filter to Team Name Does Not Equal All Active Users I get all Enabled Users because Teams is joined to Users where Teams Contains Data and since every User is by default on at least one other team (BU), which does not equal All Active Users, they all get returned.

    If this is even possible through advanced find, i'm guessing there would need to be another join back to Users or Teams somehow, but i have not been able to figure it out.




    ------------------------------
    Jim Corriveau
    A.W. Chesterton Company
    Groveland MA
    ------------------------------

    CRMUG Summit - Post


  • 4.  RE: Which Users are on Which Teams (or not)

    TOP CONTRIBUTOR
    Posted Jan 10, 2019 04:53 AM
    Hi @Jim Corriveau, following on from @Bailey Moss​​'s message its worth mentioning the following...

    • All users (Enabled and Disabled) will be attached a business unit
    • Each business unit has a default team (lets call it the default business unit team)
    • Each user will be added to the default business unit team and its not possible to remove them from this team
    I'm a System Administrator and not in a Dynamics team because we don't use queues and my System Administrator security role is applied directly to my user profile, therefore my teams list appears as follows...


    Depending on your requirement you may want to exclude users that are in a default business unit team if they might be more back office support staff and not using Dynamics on a day-to-day basis.  You can run the query as per below...


    Hope this helps.

    ------------------------------
    Mark Aston
    Data Analyst
    Ordnance Survey
    Southampton, UK
    ------------------------------

    CRMUG Summit - Post


  • 5.  RE: Which Users are on Which Teams (or not)

    TOP CONTRIBUTOR
    Posted Jan 10, 2019 08:20 AM
    Hello, Jim

    I hope you are doing well.  I was intrigued by this problem.  Although, Mark's idea will work if everyone is in a different team, you will lose Users if they are in multiple teams.  Also, as in our case, we have 145 Teams, trying to set up that Advanced Find would be almost impossible.  The actual SQL Query to do your Fetch is

    select U.fullname, U.lastname, U.firstname, U.systemuserid, U.businessunitidname
    from SystemUser U
    left join (
       select U.systemuserid
       from FilteredSystemUser U
       join FilteredTeamMembership UT
          on UT.systemuserid = U.systemuserid
       join FilteredTeam T on T.teamid = UT.teamid
       where T.name = 'CRM') TT
          on TT.systemuserid = U.SystemUserId
    where  U.IsDisabled = 0 and  TT.systemuserid is null
    order by U.BusinessUnitIdName, U.fullname

    The problem then is to convert this to FetchXML.  I tried using FetchXML Builder but it was taking too long.  I tried using an online SQL to FetchXML converter but it was not able to translate it.  My only suggestion is to export the entire list of enabled Users to an Excel Worksheet and then search for the Users in the Team using VLookup and deleting them from the List.













    ------------------------------
    Gerry Yurko
    CRM Developer
    Lightower Fiber Networks
    Boxborough MA
    ------------------------------

    CRMUG Summit - Post


  • 6.  RE: Which Users are on Which Teams (or not)

    TOP CONTRIBUTOR
    Posted Jan 10, 2019 09:49 AM
    Hi again @Jim Corriveau, you may find it easier to create a report which lists all users and the teams they are attached to.  You can then export it to Excel to analyse.  Here's how I configured our report to see what users are attached to what teams.  If a user is in 3 teams, they'll have 3 rows in the report...


    We exclude a couple of system users which we don't need to know about.  You could exclude the default business unit teams if you wanted to...


    Include the required fields...


    Hope this helps.

    ------------------------------
    Mark Aston
    Data Analyst
    Ordnance Survey
    Southampton, UK
    ------------------------------

    CRMUG Summit - Post


  • 7.  RE: Which Users are on Which Teams (or not)

    SILVER CONTRIBUTOR
    Posted Jan 11, 2019 08:15 AM
    I've found these charts by @Ulrik Carlsson extremely helpful in the past.  It can be a little messy if you have a large number of users but if you have views set up for specific sets of users, say by region, then run this chart it's a very nice visual.​

    https://crmchartguy.com/2018/01/26/more-user-based-charts-in-dynamics-365/

    ------------------------------
    Eric Veneziano
    Sales System Analyst
    Minitab, Inc.
    State College PA
    ------------------------------

    CRMUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role