Table: servicenow_sys_user - Query ServiceNow Users using SQL

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.

Table Usage Guide

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.

Examples

List users who haven't logged in for more than 90 days

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');

Users with Multifactor Authentication Disabled

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;

Distribution of Users by Department

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;

Users with Failed Login Attempts

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;

Users with Profile Photo

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;

Distribution of Users by Country

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;

Users with No Manager Assigned

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;

Average Time Since Last Login

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;

Distribution of Users by Preferred Language

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;

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