I had a Service Manager coming to me and asking a simple demand like: I want a list over all my groups that I mange and its members. It sounded like a easy thing but all I knew was that I could go the the "groups" list and then I hit the wall trying to figure out how to filter/config layout to give me the result I wanted.
And after searching quite some time since I didn't believe I was the only one with this problem I found the solution. There are a few tables that combines other tables by default. You just need to know them.
For the example above. I just typed "Sys_user_grmember.list" in the navigator filter and there it was! There was the list of all groups and its members. Now I just configured the filter for what groups I wanted to see and its member and I got it.
Another one I found was this: "Sys_user_has_role". If you want to have a summary over who has a certain role and don't want to dig deep into the user-table with filter.. just "Sys_user_has_role.list" and you have it.
Next up is Watermarks. I was up to make some inbound actions and need to do some Query to the records an incoming mail was attached to. Now I knew that the watermark had it, but no idea where to find the information. Here there wasn't any strange combo name. the table is just simply "Sys_watermark".
If you have edited an OOB-record it will not be updated when you patch/upgrade your instance. All of these records can be found in the table sys_update_xml. More about this has been covered in a previous blog post.
If you wonder how all variables to each RITM is stored. Well, looking in sc_item_option_mtom will give you a list over all variables to each RITM.
Sys_email gives you a list over all emails that ServiceNow has handled, both incoming and outgoing. I have sometimes found emails here that isnt anywhere in the normal "email boxes" like received or sent.
And the final thing that I found helpful was when I was trying to learn AngularJS and doing that with UI Pages. If you want a UI Page to be public(not requiring a login) your page need to be in the "sys_public" table.
So to sum it up:
- sys_user_grmember: Nice table to see which member is in which groups without hoping inside each group
- sys_user_has_role: Same function as above, but with roles instead of groups
- sys_watermark: Here you can find all watermarks and see what record has generated the message
- sys_update_xml: Table that contains all records that won't be overwritten when you patch/upgrade.
- sc_item_option_mtom: Table that contains the relationship with RITM and its variables.
- sys_public: The magic list that you need to add your UI Page if you want it to be reachable without an login.
If you got any more tables that are nice to know about, don't hesitate to comment and I'll throw them in the list here.