Table: servicenow_now_contact - Query ServiceNow Contacts using SQL
ServiceNow Contacts are individuals or groups associated with incidents, problems, and changes within the ServiceNow system. These contacts may include employees, customers, vendors, or any other entities that interact with the organization's ServiceNow system. They play a crucial role in incident management, problem management, and change management processes within ServiceNow.
Table Usage Guide
The servicenow_now_contact
table provides insights into the contacts within the ServiceNow system. As a system administrator or IT service manager, you can explore contact-specific details through this table, including their roles in incidents, problems, and changes. Utilize it to uncover information about contacts, such as their associations with incidents, problems, and changes, and the details of these associations.
Examples
How many contacts are in the servicenow_now_contact table?
Determine the total number of contacts recorded in your ServiceNow platform to assess the size of your network. This can be useful for understanding your reach and planning communication or marketing strategies.
select count(*) as num_contactsfrom servicenow_now_contact;
select count(*) as num_contactsfrom servicenow_now_contact;
What are the unique sources of the contacts?
Discover the segments that represent unique origins within your contact list, which can help streamline communication strategies and customize outreach efforts.
select distinct sourcefrom servicenow_now_contact;
select distinct sourcefrom servicenow_now_contact;
How many contacts were created in the last 7 days?
Discover the number of new contacts added within the last week. This can be useful for monitoring contact list growth and assessing recent engagement efforts.
select count(*) as num_contacts_last_7_daysfrom servicenow_now_contactwhere sys_created_on >= now() - interval '7 days';
select count(*) as num_contacts_last_7_daysfrom servicenow_now_contactwhere sys_created_on >= datetime('now', '-7 days');
What is the most common country of origin for the contacts?
Explore which country is most frequently listed as the origin for the contacts. This could be useful for understanding the geographical distribution of your contacts and tailoring your services accordingly.
select country, count(*) as num_contactsfrom servicenow_now_contactgroup by countryorder by num_contacts desclimit 1;
select country, count(*) as num_contactsfrom servicenow_now_contactgroup by countryorder by num_contacts desclimit 1;
How many contacts were created by each source in the last 30 days?
Discover the segments that each source has contributed to your contacts list in the past month. This information is useful for understanding which sources are most effective in generating new contacts.
select source, count(*) as num_contacts_last_30_daysfrom servicenow_now_contactwhere sys_created_on >= now() - interval '30 days'group by source;
select source, count(*) as num_contacts_last_30_daysfrom servicenow_now_contactwhere sys_created_on >= datetime('now', '-30 days')group by source;
What is the average number of contacts created per day in the last 90 days?
Analyze the trends in your contact creation by determining the average number of new contacts added each day over the past three months. This can help you understand your team's productivity and identify any significant changes in your contact volume.
select avg(num_contacts_per_day) as avg_contacts_per_day_last_90_daysfrom ( select count(*) as num_contacts_per_day from servicenow_now_contact where sys_created_on >= now() - interval '90 days' group by sys_created_on ) as daily_contacts;
select avg(num_contacts_per_day) as avg_contacts_per_day_last_90_daysfrom ( select count(*) as num_contacts_per_day from servicenow_now_contact where sys_created_on >= datetime('now', '-90 day') group by sys_created_on ) as daily_contacts;
Schema for servicenow_now_contact
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
account | text | Sys_id of the account record to which the contact is associated; Account [customer_account] table. | |
active | boolean | Flag that indicates whether the contact is active within the system. | |
agent_status | text | Status of the agent. | |
building | text | Sys_id of the record that describes the building in which the contact resides; Building [cmn_building] table. | |
calendar_integration | bigint | Calendar application that the contact uses. | |
city | text | City in which the contact resides. | |
company | text | Sys_id of the company record to which the contact is associated; Company [core_company] table. | |
cost_center | text | Sys_id of the cost center associated with the contact; Cost Center [cmn_cost_center] table. | |
country | text | Country code of the country in which the contact resides. | |
date_format | text | Format in which to display dates to contacts. | |
default_perspective | text | Sys_id of the default perspective for the contact. Located in the Menu List [sys_perspective] table. | |
department | text | Sys_id of the department associated with the contact. Located in the Department [cmn_department] table. | |
edu_status | text | Education status of the associated contact. | |
text | Contact email address. | ||
employee_number | text | Contact employee number. | |
enable_multifactor_authn | boolean | Flag that indicates whether multifactor authorization is required for the contact to log in to the service portal. | |
failed_attempts | bigint | Number of failed log in attempts. | |
first_name | text | Contact first name. | |
gender | text | Contact gender. | |
geolocation_tracked | boolean | Flag that indicates whether the contact location is obtained through geotracking. | |
home_phone | text | Contact home phone number. | |
internal_integration_user | boolean | Flag that indicates whether the contact is an internal integration user. | |
introduction | text | Introduction | |
last_login | timestamp with time zone | Date on which the contact last logged into the system. | |
last_login_device | text | Date on which the contact last logged into the system. | |
last_login_time | text | Date and time the contact logged in to the system. | |
last_name | text | Contact last name. | |
last_position_update | text | Date and time the last position was updated. | |
latitude | double precision | Latitude coordinate of the contact. | |
ldap_server | text | Sys_id of the LDAP server used by the contact to last log in to the system; LDAP Server [ldap_server_config] table. | |
location | text | Sys_id of the record that describes the location of the contact; Location [cmn_location] table. | |
locked_out | boolean | Flag that indicates if the contact is locked-out. | |
longitude | double precision | Longitude coordinate of the contact. | |
manager | text | Sys_id of the record that describes the direct supervisor of the contact; User [sys_user] table. | |
middle_name | text | Contact middle name. | |
mobile_phone | text | Contact mobile phone number. | |
name | text | Contact full name. | |
notification | bigint | Indicates whether the contact should receive notifications. | |
on_schedule | text | Indicates the timeliness of dispatched service personnel. | |
phone | text | Contact business phone number. | |
photo | text | Photo image of the contact. | |
preferred_language | text | Country code of the contact primary language. | |
roles | text | List of user roles associated with the contact. | |
schedule | text | Sys_id of the record that describes the work schedule for the associated contact; Schedule [cmn_schedule] table. | |
source | text | Source of the contact. | |
state | text | State in which the contact resides. | |
street | text | Contact street address. | |
sys_class_name | text | Table that contains the contact record. | |
sys_created_by | text | User that originally created the associated contact record. | |
sys_created_on | timestamp with time zone | Data and time the associated contact was originally created. | |
sys_domain | text | ServiceNow instance domain of the associated contact record. | |
sys_domain_path | text | Contact record domain path. | |
sys_id | text | Unique identifier for the associated contact record. | |
sys_mod_count | bigint | Number of times that the associated contact record has been modified. | |
sys_tags | text | System tags. | |
sys_updated_by | text | User that last updated the associated contact information. | |
sys_updated_on | timestamp with time zone | Data and time the associated contact information was updated. | |
time_format | text | Format in which to display time. | |
time_sheet_policy | text | Sys_id of the record that contains the time sheet policy for the associated contact; Time Sheet Policy [time_sheet_policy] table. | |
time_zone | text | Time zone in which the contact resides, such as Canada/Central or US/Eastern. | |
title | text | Contact business title such as Manager, Software Developer, or Contractor. | |
user_name | text | Contact user ID. | |
vip | boolean | Flag that indicates whether the associated contact has VIP status. | |
web_service_access_only | boolean | Flag that indicates whether the contact can only access services through the web. | |
zip | text | Contact zip code. |
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_now_contact