title | description |
---|---|
Steampipe Table: servicenow_sys_user - Query ServiceNow Users using SQL |
Allows users to query Users in ServiceNow, specifically user details and properties, providing insights into user management and operations. |
ServiceNow is a cloud-based platform designed to help businesses manage digital workflows for enterprise operations. It provides a centralized system to manage, coordinate, and develop effective workflows for business processes. ServiceNow includes a wide range of products and services, including IT service management, IT operations management, and IT business management.
The servicenow_sys_user
table provides insights into user profiles within ServiceNow. As an IT manager or system administrator, explore user-specific details through this table, including roles, groups, and associated metadata. Utilize it to manage and monitor user activities, such as user roles and group memberships, and to verify user properties.
Discover the segments that include users who haven't been active for more than 90 days. This can be useful in identifying inactive accounts for potential clean-up or outreach efforts.
select
user_name,
last_login_time
from
servicenow.servicenow_sys_user
where
last_login_time < now() - interval '90 DAYS';
select
user_name,
last_login_time
from
servicenow_servicenow_sys_user
where
last_login_time < datetime('now', '-90 day');
Explore which users have not enabled multifactor authentication. This can be useful in identifying potential security risks within your system.
select
count(*) as users_with_mfa_enabled
from
servicenow.servicenow_sys_user
where
enable_multifactor_authn = false;
select
count(*) as users_with_mfa_enabled
from
servicenow_servicenow_sys_user
where
enable_multifactor_authn = 0;
Discover the distribution of users across various departments. This query aids in understanding the user allocation in each department, providing valuable insights for resource planning and management.
select
department,
count(*) as num_users
from
servicenow.servicenow_sys_user
group by
department;
select
department,
count(*) as num_users
from
servicenow_sys_user
group by
department;
Discover the segments that have experienced unsuccessful login attempts to gain insights into the average number of failed attempts per user. This information can aid in identifying potential security issues or areas for user experience improvement.
select
count(*) as users_with_failed_attempts,
avg(failed_attempts) as avg_failed_attempts
from
servicenow.servicenow_sys_user
where
failed_attempts > 0;
select
count(*) as users_with_failed_attempts,
avg(failed_attempts) as avg_failed_attempts
from
servicenow_sys_user
where
failed_attempts > 0;
Discover the number of users who have uploaded a profile photo on ServiceNow. This can be useful in identifying user engagement levels or in designing user-centric features.
select
count(*) as users_with_photo
from
servicenow.servicenow_sys_user
where
photo is not null;
select
count(*) as users_with_photo
from
servicenow_sys_user
where
photo is not null;
Explore which countries have the most users to understand the global distribution of your user base. This can help in tailoring your services to cater to these regions more effectively.
select
country,
count(*) as num_users
from
servicenow.servicenow_sys_user
group by
country;
select
country,
count(*) as num_users
from
servicenow_sys_user
group by
country;
Discover the segments that have users without a manager assigned. This can be useful in identifying potential gaps in your team structure or areas that may require additional management oversight.
select
count(*) as users_with_manager
from
servicenow.servicenow_sys_user
where
manager is null;
select
count(*) as users_with_manager
from
servicenow_sys_user
where
manager is null;
Understand the average duration since the last login for all active users. This can be useful for gauging user activity and identifying potential periods of inactivity.
select
avg(extract(epoch
from
(
now() - last_login_time
)
)) as avg_time_since_last_login_in_milliseconds
from
servicenow.servicenow_sys_user
where
active = true;
select
avg(strftime('%s', 'now') - strftime('%s', last_login_time)) as avg_time_since_last_login_in_milliseconds
from
servicenow.servicenow_sys_user
where
active = 1;
Explore the distribution of users based on their preferred language. This helps in understanding user preferences and tailoring language-specific services for better user experience.
select
preferred_language,
count(*) as num_users
from
servicenow.servicenow_sys_user
group by
preferred_language;
select
preferred_language,
count(*) as num_users
from
servicenow_sys_user
group by
preferred_language;