turbot/servicenow
steampipe plugin install servicenow

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_contacts
from
servicenow_now_contact;
select
count(*) as num_contacts
from
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 source
from
servicenow_now_contact;
select
distinct source
from
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_days
from
servicenow_now_contact
where
sys_created_on >= now() - interval '7 days';
select
count(*) as num_contacts_last_7_days
from
servicenow_now_contact
where
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_contacts
from
servicenow_now_contact
group by
country
order by
num_contacts desc
limit
1;
select
country,
count(*) as num_contacts
from
servicenow_now_contact
group by
country
order by
num_contacts desc
limit
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_days
from
servicenow_now_contact
where
sys_created_on >= now() - interval '30 days'
group by
source;
select
source,
count(*) as num_contacts_last_30_days
from
servicenow_now_contact
where
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_days
from
(
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_days
from
(
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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accounttextSys_id of the account record to which the contact is associated; Account [customer_account] table.
activebooleanFlag that indicates whether the contact is active within the system.
agent_statustextStatus of the agent.
buildingtextSys_id of the record that describes the building in which the contact resides; Building [cmn_building] table.
calendar_integrationbigintCalendar application that the contact uses.
citytextCity in which the contact resides.
companytextSys_id of the company record to which the contact is associated; Company [core_company] table.
cost_centertextSys_id of the cost center associated with the contact; Cost Center [cmn_cost_center] table.
countrytextCountry code of the country in which the contact resides.
date_formattextFormat in which to display dates to contacts.
default_perspectivetextSys_id of the default perspective for the contact. Located in the Menu List [sys_perspective] table.
departmenttextSys_id of the department associated with the contact. Located in the Department [cmn_department] table.
edu_statustextEducation status of the associated contact.
emailtextContact email address.
employee_numbertextContact employee number.
enable_multifactor_authnbooleanFlag that indicates whether multifactor authorization is required for the contact to log in to the service portal.
failed_attemptsbigintNumber of failed log in attempts.
first_nametextContact first name.
gendertextContact gender.
geolocation_trackedbooleanFlag that indicates whether the contact location is obtained through geotracking.
home_phonetextContact home phone number.
internal_integration_userbooleanFlag that indicates whether the contact is an internal integration user.
introductiontextIntroduction
last_logintimestamp with time zoneDate on which the contact last logged into the system.
last_login_devicetextDate on which the contact last logged into the system.
last_login_timetextDate and time the contact logged in to the system.
last_nametextContact last name.
last_position_updatetextDate and time the last position was updated.
latitudedouble precisionLatitude coordinate of the contact.
ldap_servertextSys_id of the LDAP server used by the contact to last log in to the system; LDAP Server [ldap_server_config] table.
locationtextSys_id of the record that describes the location of the contact; Location [cmn_location] table.
locked_outbooleanFlag that indicates if the contact is locked-out.
longitudedouble precisionLongitude coordinate of the contact.
managertextSys_id of the record that describes the direct supervisor of the contact; User [sys_user] table.
middle_nametextContact middle name.
mobile_phonetextContact mobile phone number.
nametextContact full name.
notificationbigintIndicates whether the contact should receive notifications.
on_scheduletextIndicates the timeliness of dispatched service personnel.
phonetextContact business phone number.
phototextPhoto image of the contact.
preferred_languagetextCountry code of the contact primary language.
rolestextList of user roles associated with the contact.
scheduletextSys_id of the record that describes the work schedule for the associated contact; Schedule [cmn_schedule] table.
sourcetextSource of the contact.
statetextState in which the contact resides.
streettextContact street address.
sys_class_nametextTable that contains the contact record.
sys_created_bytextUser that originally created the associated contact record.
sys_created_ontimestamp with time zoneData and time the associated contact was originally created.
sys_domaintextServiceNow instance domain of the associated contact record.
sys_domain_pathtextContact record domain path.
sys_idtextUnique identifier for the associated contact record.
sys_mod_countbigintNumber of times that the associated contact record has been modified.
sys_tagstextSystem tags.
sys_updated_bytextUser that last updated the associated contact information.
sys_updated_ontimestamp with time zoneData and time the associated contact information was updated.
time_formattextFormat in which to display time.
time_sheet_policytextSys_id of the record that contains the time sheet policy for the associated contact; Time Sheet Policy [time_sheet_policy] table.
time_zonetextTime zone in which the contact resides, such as Canada/Central or US/Eastern.
titletextContact business title such as Manager, Software Developer, or Contractor.
user_nametextContact user ID.
vipbooleanFlag that indicates whether the associated contact has VIP status.
web_service_access_onlybooleanFlag that indicates whether the contact can only access services through the web.
ziptextContact 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