How to JOIN sys_user & sys_user_grmember tables

[stevezadroga]

I am working with ServiceNow data with a third party reporting tool and trying to JOIN the sys_user & sys_user_grmember tables.  I want to return the group name, member name, member email, etc.  I was thinking of using the [name] field, but that is not unique.  Are there other keys I should be using?

[Prateek kumar]

Try this using a database view??

[stevezadroga]

I cant see the WHERE clause working because the grmem.user field represents an actual full name of a person AND the usr.sys_id field is an alphanumeric string.  They will not be equal to each other, no?

[Prateek kumar · Accepted solution]

user field on the sys_user_grmember table is a reference field to sys_user table. It will return sys_id in the result.

[DirkRedeker]

Hi How does the 3rd party reporting tool access to ServiceNow? Are you using the table API, or do you go by scripted REST API, etc.? BR Dirk