Table: servicenow_sys_user_grmember - Query ServiceNow User Group Members using SQL
ServiceNow is a cloud-based software platform that provides IT service management (ITSM) and helps automate IT Business Management (ITBM). It specializes in IT services management (ITSM), IT operations management (ITOM), and IT business management (ITBM). One of the key aspects of ServiceNow is its ability to manage User Group Members, providing a structured way to organize and manage users in a ServiceNow instance.
Table Usage Guide
The servicenow_sys_user_grmember
table provides insights into User Group Members within ServiceNow. As an IT administrator, you can explore details through this table, including the user's ID, the group's ID they belong to, and other related metadata. Utilize it to manage and organize users in your ServiceNow instance, such as identifying which groups a user belongs to, and verifying user-group relationships.
Examples
Which users belong to a particular group?
Determine the members of a specific group in your ServiceNow system. This can be useful for managing permissions, delegating tasks, or understanding the composition of your team.
select g.user ->> 'value' as user_idfrom servicenow_sys_user_grmember gwhere g.group ->> 'value' = 'ff0370019f22120047a2d126c42e702b';
select json_extract(g.user, '$.value') as user_idfrom servicenow_sys_user_grmember gwhere json_extract(g.group, '$.value') = 'ff0370019f22120047a2d126c42e702b';
Which users were added to any group in the last 24 hours?
Explore the recent changes in user group memberships to identify any new additions within the last day. This can help monitor and manage group access rights, ensuring only authorized users are included.
select distinct g.user ->> 'value' as user_idfrom servicenow_sys_user_grmember gwhere g.sys_created_on >= now() - interval '24 hours';
select distinct json_extract(g.user, '$.value') as user_idfrom servicenow_sys_user_grmember gwhere g.sys_created_on >= datetime('now', '-24 hours');
How many users are in each group?
Determine the distribution of users across various groups to understand user management and group allocation. This can be useful in identifying areas of high user concentration and potential need for group restructuring.
select g.name as group_name, count(distinct m.user ->> 'value') as user_countfrom servicenow_sys_user_grmember m join servicenow_sys_user_group g on m.group ->> 'value' = g.sys_idgroup by g.name;
select g.name as group_name, count(distinct json_extract(m.user, '$.value')) as user_countfrom servicenow_sys_user_grmember m join servicenow_sys_user_group g on json_extract(m.group, '$.value') = g.sys_idgroup by g.name;
Which users are not members of any group?
Discover the segments that consist of users who are not associated with any group. This can be beneficial in identifying potential risks or gaps in user management and ensuring that all users are properly categorized for access control and auditing purposes.
select u.name as user_namefrom servicenow_sys_user u left join servicenow_sys_user_grmember m on u.sys_id = m.user ->> 'value'where m.sys_id is null;
select u.name as user_namefrom servicenow_sys_user u left join servicenow_sys_user_grmember m on u.sys_id = json_extract(m.user, '$.value')where m.sys_id is null;
Which groups have no members?
Discover the segments that have no members, which can help in identifying unused or redundant groups in your system for better resource management.
select g.name as group_namefrom servicenow_sys_user_group g left join servicenow_sys_user_grmember m on g.sys_id = m.group ->> 'value'where m.sys_id is null;
select g.name as group_namefrom servicenow_sys_user_group g left join servicenow_sys_user_grmember m on g.sys_id = json_extract(m.group, '$.value')where m.sys_id is null;
Which users are members of a specific group?
Determine the members of a specific group to better understand the distribution of roles and responsibilities within your organization. This is particularly useful in managing access controls and ensuring proper security protocols are followed.
select u.name as user_name, g.name as group_namefrom servicenow_sys_user_grmember m join servicenow_sys_user u on m.user ->> 'value' = u.sys_id join servicenow_sys_user_group g on m.group ->> 'value' = g.sys_idwhere g.name = 'it securities';
select u.name as user_name, g.name as group_namefrom servicenow_sys_user_grmember m join servicenow_sys_user u on json_extract(m.user, '$.value') = u.sys_id join servicenow_sys_user_group g on json_extract(m.group, '$.value') = g.sys_idwhere g.name = 'it securities';
Schema for servicenow_sys_user_grmember
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
group | jsonb | User group to which the user is a member. | |
sys_created_by | text | User who created the record. | |
sys_created_on | timestamp with time zone | Date and time when the record was created. | |
sys_id | text | Unique system identifier for the record. | |
sys_mod_count | bigint | Number of times the record was modified. | |
sys_updated_by | text | User who last updated the record. | |
sys_updated_on | timestamp with time zone | Date and time when the record was last updated. | |
user | jsonb | User who is a member of the user group. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- servicenow
You can pass the configuration to the command with the --config
argument:
steampipe_export_servicenow --config '<your_config>' servicenow_sys_user_grmember