Table: servicenow_now_consumer - Query ServiceNow Consumers using SQL

ServiceNow is a cloud-based platform designed to improve business efficiency and productivity. It supports various IT service management tasks and is typically used by businesses to handle incident reports or IT service requests. A ServiceNow Consumer is an entity that can make requests to and consume responses from an API.

Table Usage Guide

The servicenow_now_consumer table provides insights into ServiceNow Consumers within the ServiceNow platform. As an IT manager or developer, explore consumer-specific details through this table, including the client ID, client secret, and redirect URL. Utilize it to manage and monitor your API consumers, ensuring they are correctly configured and operating as expected.

Examples

What are the first and last names and email of all active consumers?

Explore the active consumers by identifying their first and last names along with their email addresses. This is useful for maintaining up-to-date records or for reaching out to active consumers for feedback or promotional campaigns.

select
  first_name,
  last_name,
  email
from
  servicenow_now_consumer
where
  active = true;

select
  first_name,
  last_name,
  email
from
  servicenow_now_consumer
where
  active = 1;

How many consumers are there in each state?

Determine the distribution of consumers across various states to understand regional demographics and market penetration. This query can help guide decisions on resource allocation, targeted marketing, and expansion strategies.

select
  state,
  count(*) as num_consumers
from
  servicenow_now_consumer
group by
  state
order by
  state desc;

select
  state,
  count(*) as num_consumers
from
  servicenow_now_consumer
group by
  state
order by
  state desc;

What is the total number of consumers who doesn't have a mobile phone number listed?

Discover the segments that consist of consumers without a listed mobile phone number. This can help in identifying potential gaps in your customer communication channels.

select
  count(*)
from
  servicenow_now_consumer
where
  mobile_phone is null;

select
  count(*)
from
  servicenow_now_consumer
where
  mobile_phone is null;

What is the most common prefix for consumer names?

Explore which prefix is most frequently used among consumer names, helping to identify common naming conventions and patterns. This could be particularly useful for data organization and customer segmentation strategies.

select
  prefix,
  count(*) as num_consumers
from
  servicenow_now_consumer
group by
  prefix
order by
  num_consumers desc
limit
  1;

select
  prefix,
  count(*) as num_consumers
from
  servicenow_now_consumer
group by
  prefix
order by
  num_consumers desc
limit
  1;

How many consumers have an email address that contains the word "gmail"?

Explore how many consumers have registered with an email address that includes the word 'gmail'. This can be useful for understanding the popularity of different email service providers among your consumers.

select
  count(*)
from
  servicenow_now_consumer
where
  email like '%gmail%';

select
  count(*)
from
  servicenow_now_consumer
where
  email like '%gmail%';

Which consumers have a title that contains the word "Manager"?

Discover the segments that include individuals with managerial roles. This query can be used to identify those consumers who hold a title containing the term "Manager", which can be useful in tailoring communication or resources for this specific group.

select
  *
from
  servicenow_now_consumer
where
  title like '%Manager%';

select
  *
from
  servicenow_now_consumer
where
  title like '%Manager%';

How many consumers have a photo attached to their record?

Discover the segments that have consumers with photos attached to their records. This is useful for understanding the extent of user engagement and personalization within your platform.

select
  count(*)
from
  servicenow_now_consumer
where
  photo is not null;

select
  count(*)
from
  servicenow_now_consumer
where
  photo is not null;

Schema for servicenow_now_consumer

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
active	boolean	Flag that indicates whether the consumer is active.
business_phone	text	Business phone number of the consumer.
city	text	City in which the consumer resides.
country	text	Country in which the consumer resides.
date_format	text	Format in which to display dates.
email	text	Email address of the consumer.
fax	text	Fax number of the consumer.
first_name	text	Consumer first name.
gender	text	Gender of the consumer.
home_phone	text	Home phone number of the consumer.
household	text	Sys_id of the record that describes the household characteristics. Located in the Household [servicenow_csm_household] table.
last_name	text	Consumer last name.
middle_name	text	Consumer middle name.
mobile_phone	text	Consumer mobile phone number.
name	text	Consumer full name; first_name+middle_name+last_name.
notes	text	Notes on consumer.
notification	bigint	Indicates whether the consumer should receive notifications.
number	text	Unique number associated with the consumer.
photo	text	Photo of the consumer.
preferred_language	text	Consumer primary language.
prefix	text	Consumer name prefix such as, Dr., Mr., Mrs., or Ms.
primary	boolean	Flag that indicates whether this is the primary consumer.
state	text	State in which the consumer resides.
street	text	Consumer street address.
suffix	text	Consumer name suffix such as Jr., Sr., or II.
sys_created_by	text	User that created the consumer record.
sys_created_on	timestamp with time zone	Date and time the consumer record was originally created.
sys_domain	text	ServiceNow domain in which the consumer information resides.
sys_id	text	Unique identifier for the consumer.
sys_mod_count	bigint	Number of times that the associated consumer information has been modified.
sys_tags	text	System tags.
sys_updated_by	text	User that last updated the consumer information.
sys_updated_on	timestamp with time zone	Date and time when the consumer information was last updated.
time_format	text	Format in which to display time.
time_zone	text	Consumer time zone, such as Canada/Central or US/Eastern.
title	text	Consumer business title such as Manager, Software Developer, or Contractor.
user	text	Sys_id of the consumer user. Located in the Consumer User [csm_consumer_user] table.
zip	text	Consumer 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_consumer