hey everyone welcome back to my channel it is so good to have you here in this episode I am going to go over some of the table names that revolve around users groups roles and the relationships between them the reason you want to know these table names is it's going to make navigating to these tables way more easier than what's provided for you out of the box first up the user table and this table is named CIS underscore user now why is it so handy that you know that table name well if you go to your Navigator bar and type in user you get a stupendous amount of results which one of these links in your Navigator bar that says user in it is actually the list of users that you want to look at so because the Navigator bar is so complex we are going to use the table name and a navigator bar hack to get to the user list we're going to type in sys user dot list we're going to type listed capital letters guys going to open it up in a new tab and here we have the listed users now if you're new to servicenow I want to really encourage you to use your show XML here so I have a user I'm going to right click show XML and this is a list of properties on the user table whether or not they show up on the form if you want to know more about show XML I did a video on it that's going to be coming up in the corner keep your eyes peeled for it would love to see you watch that video another reason you want to know the user table name is if you're ever building a report on the user table and you type in user again you get this gigantic list because there's so many tables that have the word user in it you don't even get the full list it just has to load more results and in fact on a PDI this can literally take you minutes so we're going to show you a little hack here we're gonna go sis user that will radically reduce the number so we have a much more abbreviated list but we can even get better because in reporting it searches for the little square brackets as well so I'm going to put a square bracket at the end of that and we're down to just a couple results so I can click sys user and there I'm recording a title when you're actually building a report that can take seconds instead of lots of seconds it's also critical to know the table name here in case you are dealing with any scripts that have to create modify or delete user records you're going to need to know that table name okay the next obvious topic is groups and why would we want to know the table name for groups well again if you go to your Navigator bar and you type in group you get an enormous scrolling list in which the word group is featured but you don't want a list of options you just want to go to the darn group table so the name of the group table is sys user group and again we'll use our hack to do dot list in 15 years of doing this I think at least 10 000 times I've written it as CIS user groups so a nice hack to be better than me instantaneously is to remember that table names are almost always the singular of the noun group instead of groups I execute our little navbar hack table name dot list and capital letters I'm now on a new tab and now I see groups let's go into one and once again if you're new to servicenow really pays to do your show XML and take a good look at that table structure again these are all the fields in the group table whether or not they appear on the form okay now you're looking at this group and you see in the bottom corner here we have roles and group members and these are many to many relationships because a group can have many roles and a role might have many groups just the same a group might have many members and one person might be a member of many many groups this is called an m to M relationship if you're unfamiliar with that relationship you'll see a video come up in the corner I highly encourage you to watch that that will explain M to M tables on servicenow and the two different ways you can create them so we've talked about users and groups now the next most obvious table is the membership table and why might I want to know the table because if I go to member in my navigator bar I get a short list but none of them are really what I want sure there's a report down here that shows me group memberships but what if I just want to get to the list or what if I want to run this in a script so the table name for group membership is sys user gr member and again follow my advice I've done this for 15 years and I keep on writing gr members because I wasn't taught right at the start that the table names are singular not plural dot list in all caps because we want a new tab and there we have our list of all users and all memberships to the groups there are some cases where maybe via flow designer or via script you'll want to add members to a group and when you do that you're going to need the table name and now you know sys user gr member real quick but super important tangent here the best practice is that you always associate roles to groups you don't associate roles to users unless somebody's got a gun to your head now the reason we do that is because we assume a role will be applicable to many people and the group is the object that collects many people it's also just a lot easier for you because now you can move people in and out of the group and their roles will gain or lose automatically versus trying to remember which individuals got Associated a role so best practices roles go to groups users get put in groups to get their roles and speaking of roles let's go to our Navigator bar and see what it has in store for us again a plethora of options many of the mean different things and I would have to know that I want to go to this roles not the other ones or I can just use the table name of the roles table which is CIS user role you dot list and there are all of the roles in the system again if you're a new servicenow user it pays to investigate this a little bit go to the rules do the show XML but also take a look at the related list because there's some interesting features here you have contains and contained by so you can see that there's a hierarchy of roles and if you give somebody a role that contains rules you're giving them all the roles that are contained as well okay but we were just talking about roles being assigned to groups but how would I ever understand which roles have been given to what groups easy enough the table name here is sis group has role dot list and there are all of the roles and the groups that they have been assigned to so here we see that the it routing group has the ITIL agent workspace user interaction agent and the SN uni Rec routing agent so if I've given the group a role and then I put a user in the group shouldn't I expect that that user gets a role you betcha and it's stored in its own table that table is sys user has role and now I see all the users and all the roles that they have now there are some interesting properties on here that you're going to want to pay attention to okay you see this inherited inheritance counts included in role included in role instance the inherited tells you if the role has come from a group or another role so anything where that's true means it hasn't been manually assigned and that's a good thing the inheritance count tells you from how many places the user got that role so I might be a member of 15 different groups that have the ITIL role and if you remove me from one of them it still doesn't take away my ITIL role because I've inherited it from 14 other locations CIS user has roll really cool table to go exploring with your show XML also warning this table is governed by memberships to groups and it is not a place where you want to play with scripting you are in dangerous territory if you're trying to script anything on this now the last table doesn't really have anything to do with users or roles or memberships or has roles this table is called group type you may have noticed on groups there's a field called type if you've ever wondered what this is for it is for filtering lists of groups on the assign to field it's very likely that the set of groups that you can assign an incident to will be different from the set of groups that you can assign a project task to or a resource plan too and many different process areas may have whole different lists of groups and the primary way servicenow does this is by the group type field the problem is that group type does not feature anywhere in the Navigator bar it is unless you know the table name so your table name is sis User Group type dot list and there we have a list of all the group types that have been used and for you Intrepid servicenow application Builders a a new button so that you may create your own group types because it's very likely if you have a unique application in servicenow a unique process that the existing groups are not going to be the ones you want to you sign this stuff to might be a isolated group of three or four groups and in that case you're definitely going to want to know how to build a group type now you know how to get to that table it's assist User Group type and then all you have to do is give it a name and description all right everybody I really appreciate you guys coming and spending your time with me today I hope you learned something from this all the table names are going to be in the description below so you can copy and paste them into the note-taking tool of your choice this is my email address if your company needs experienced servicenow advisory or if you need to level up your servicenow career just feel free to reach me at Rob the duke.digital thanks for watching