turbot/servicenow
steampipe plugin install servicenow

Table: servicenow_sys_user_group - Query ServiceNow User Groups using SQL

ServiceNow User Groups is a resource within ServiceNow that allows for the management and organization of users into specific groups. It provides a centralized way to manage and assign roles, responsibilities, and access permissions to specific groups of users. User Groups in ServiceNow helps in streamlining the process of user management, ensuring efficient distribution of tasks, and maintaining security protocols.

Table Usage Guide

The servicenow_sys_user_group table provides insights into User Groups within ServiceNow. As an administrator or IT manager, you can explore group-specific details through this table, including group names, descriptions, and managers. Utilize it to manage user roles and responsibilities, monitor group activities, and enforce access control policies effectively.

Examples

Find all active groups

Discover the segments that are currently active in your ServiceNow user groups. This can be useful for managing user access and permissions in real-time.

select
*
from
servicenow.servicenow_sys_user_group
where
active = true;
select
*
from
servicenow.servicenow_sys_user_group
where
active = 1;

Count the number of groups created by each user

Analyze the distribution of group creation among users to understand who has been most actively involved in group formation. This could be useful for identifying key contributors or potential bottlenecks in your team structure.

select
sys_created_by,
count(*) as group_count
from
servicenow.servicenow_sys_user_group
group by
sys_created_by
order by
group_count desc;
select
sys_created_by,
count(*) as group_count
from
servicenow_sys_user_group
group by
sys_created_by
order by
group_count desc;

Count the number of groups with each type

Determine the distribution of various types within a system's user groups. This can be useful for understanding the structure and organization of your user groups.

select
type,
count(*) as group_count
from
servicenow.servicenow_sys_user_group
group by
type;
select
type,
count(*) as group_count
from
servicenow_servicenow_sys_user_group
group by
type;

Find all groups with name starting with HR

Identify all the user groups within a system that have names beginning with 'HR'. This could be useful for HR departments to quickly locate and manage all relevant groups in their organization.

select
name,
description
from
servicenow.servicenow_sys_user_group
where
name like 'HR%';
select
name,
description
from
servicenow_sys_user_group
where
name like 'HR%';

Find child groups of a parent group

Explore which child groups fall under a specific parent group. This is useful for understanding the organizational structure and hierarchy within a system.

select
*
from
servicenow.servicenow_sys_user_group
where
parent ->> 'value' = 'ff0370019f22120047a2d126c42e702b';
select
*
from
servicenow.servicenow_sys_user_group
where
json_extract(parent, '$.value') = 'ff0370019f22120047a2d126c42e702b';

Find all groups created by a specific user

Discover the groups that have been created by a specific user. This can be useful for auditing purposes or for understanding the user's role and responsibilities within the system.

select
*
from
servicenow.servicenow_sys_user_group
where
sys_created_by = 'admin';
select
*
from
servicenow_servicenow_sys_user_group
where
sys_created_by = 'admin';

Count the number of groups with each source

Discover the distribution of user groups across various sources by counting the number of groups associated with each source. This can help in assessing the diversity of group origins and identifying sources with a high or low number of groups.

select
source,
count(*) as group_count
from
servicenow.servicenow_sys_user_group
group by
source;
select
source,
count(*) as group_count
from
servicenow_servicenow_sys_user_group
group by
source;

Find all groups with a specific role

Identify all user groups that have been assigned a specific role in the ServiceNow system. This could be useful in managing user permissions and ensuring appropriate access rights.

select
*
from
servicenow.servicenow_sys_user_group
where
roles like '%catalog%';
select
*
from
servicenow_servicenow_sys_user_group
where
roles like '%catalog%';

Find all groups with a specific manager who is not excluded from email notifications

Identify all groups managed by a specific individual who isn't excluded from receiving email notifications. This is useful for ensuring that important communications are reaching the correct managerial personnel.

select
*
from
servicenow.servicenow_sys_user_group
where
manager ->> 'value' = 'f298d2d2c611227b0106c6be7f154bc8'
and exclude_manager = false;
select
*
from
servicenow.servicenow_sys_user_group
where
json_extract(manager, '$.value') = 'f298d2d2c611227b0106c6be7f154bc8'
and exclude_manager = 0;

Schema for servicenow_sys_user_group

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
activebooleanIndicates whether the user group is active or inactive.
cost_centerjsonbCost center associated with the user group.
default_assigneejsonbDefault user assigned to tasks assigned to the user group.
descriptiontextDescription or additional information about the user group.
emailtextEmail address associated with the user group.
exclude_managerbooleanDetermines whether the manager is excluded from the user group.
include_membersbooleanDetermines whether members are included in the user group.
managerjsonbManager assigned to the user group.
nametextName of the user group.
parentjsonbParent user group.
rolestextRoles assigned to the user group.
sourcetextSource of the user group.
sys_created_bytextUser who created the record.
sys_created_ontimestamp with time zoneDate and time when the record was created.
sys_idtextUnique system identifier for the record.
sys_mod_countbigintNumber of times the record was modified.
sys_updated_bytextUser who last updated the record.
sys_updated_ontimestamp with time zoneDate and time when the record was last updated.
typetextType 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_group