Skip to content
AWS for Query.Farm

aws.pinpoint.get_user_endpoints

Example SQL Queries

SELECT * FROM
aws.pinpoint.get_user_endpoints
WHERE
"application_id" = 'VALUE'
AND "user_id" = 'VALUE';

Description

Retrieves information about all the endpoints that are associated with a specific user ID.

Table Definition

Column NameColumn Data Type
application_id Required Input Column

The unique identifier for the application. This identifier is displayed as the Project ID on the Amazon Pinpoint console.

VARCHAR
user_id Required Input Column

The unique identifier for the user.

VARCHAR
_aws_profile Input Column

The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT(
    "type" VARCHAR,
    "name" VARCHAR,
    "account_id" VARCHAR,
    "via_profile_name" VARCHAR,
    "assumed_role_arn" VARCHAR,
    "organization" STRUCT(
        "account_name" VARCHAR,
        "id" VARCHAR,
        "tags" STRUCT(
            "key" VARCHAR,
            "value" VARCHAR
        )[],
        "master_account" STRUCT(
            "id" VARCHAR,
            "email" VARCHAR
        ),
        "parents" STRUCT(
            "type" VARCHAR,
            "id" VARCHAR,
            "name" VARCHAR,
            "tags" STRUCT(
                "key" VARCHAR,
                "value" VARCHAR
            )[]
        )[]
    )
)
Show child fields
_aws_profile.account_id

The AWS account id

_aws_profile.assumed_role_arn

The ARN of the assumed role

_aws_profile.name

The unique name of the profile.

_aws_profile.organization

Information about this profile's membership in the AWS organization.

Show child fields
_aws_profile.organization.account_name

The name of account speciifed by the organization

_aws_profile.organization.id

The organization id

_aws_profile.organization.master_account
Show child fields
_aws_profile.organization.master_account.email

The organization master account email address

_aws_profile.organization.master_account.id

The organization master account id

_aws_profile.organization.parents[]
Show child fields
_aws_profile.organization.parents[].id

The id of the parent

_aws_profile.organization.parents[].name

The name of the parent

_aws_profile.organization.parents[].tags[]
Show child fields
_aws_profile.organization.parents[].tags[].key
_aws_profile.organization.parents[].tags[].value
_aws_profile.organization.parents[].type

The type of parent can be an organization unit or a root

_aws_profile.organization.tags[]
Show child fields
_aws_profile.organization.tags[].key
_aws_profile.organization.tags[].value
_aws_profile.type

The type of profile, either 'credentials' or 'assumed_role'

_aws_profile.via_profile_name

This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.

_aws_region Input Column

The AWS region to use.

VARCHAR
endpoints_response

Provides information about all the endpoints that are associated with a user ID.

STRUCT(
    "item" STRUCT(
        "address" VARCHAR,
        "application_id" VARCHAR,
        "attributes" MAP(VARCHAR, VARCHAR[]),
        "channel_type" VARCHAR,
        "cohort_id" VARCHAR,
        "creation_date" VARCHAR,
        "demographic" STRUCT(
            "app_version" VARCHAR,
            "locale" VARCHAR,
            "make" VARCHAR,
            "model" VARCHAR,
            "model_version" VARCHAR,
            "platform" VARCHAR,
            "platform_version" VARCHAR,
            "timezone" VARCHAR
        ),
        "effective_date" VARCHAR,
        "endpoint_status" VARCHAR,
        "id" VARCHAR,
        "location" STRUCT(
            "city" VARCHAR,
            "country" VARCHAR,
            "latitude" DOUBLE,
            "longitude" DOUBLE,
            "postal_code" VARCHAR,
            "region" VARCHAR
        ),
        "metrics" MAP(VARCHAR, DOUBLE),
        "opt_out" VARCHAR,
        "request_id" VARCHAR,
        "user" STRUCT(
            "user_attributes" MAP(VARCHAR, VARCHAR[]),
            "user_id" VARCHAR
        )
    )[]
)
Show child fields
endpoints_response.item[]
Show child fields
endpoints_response.item[].address

The destination address for messages or push notifications that you send to the endpoint. The address varies by channel. For example, the address for a push-notification channel is typically the token provided by a push notification service, such as an Apple Push Notification service (APNs) device token or a Firebase Cloud Messaging (FCM) registration token. The address for the SMS channel is a phone number in E.164 format, such as +12065550100. The address for the email channel is an email address.

endpoints_response.item[].application_id

The unique identifier for the application that's associated with the endpoint.

endpoints_response.item[].attributes

One or more custom attributes that describe the endpoint by associating a name with an array of values. For example, the value of a custom attribute named Interests might be: ["Science", "Music", "Travel"]. You can use these attributes as filter criteria when you create segments.

endpoints_response.item[].channel_type

The channel that's used when sending messages or push notifications to the endpoint.

endpoints_response.item[].cohort_id

A number from 0-99 that represents the cohort that the endpoint is assigned to. Endpoints are grouped into cohorts randomly, and each cohort contains approximately 1 percent of the endpoints for an application. Amazon Pinpoint assigns cohorts to the holdout or treatment allocations for campaigns.

endpoints_response.item[].creation_date

The date and time, in ISO 8601 format, when the endpoint was created.

endpoints_response.item[].demographic

The demographic information for the endpoint, such as the time zone and platform.

Show child fields
endpoints_response.item[].demographic.app_version

The version of the app that's associated with the endpoint.

endpoints_response.item[].demographic.locale

The locale of the endpoint, in the following format: the ISO 639-1 alpha-2 code, followed by an underscore (_), followed by an ISO 3166-1 alpha-2 value.

endpoints_response.item[].demographic.make

The manufacturer of the endpoint device, such as apple or samsung.

endpoints_response.item[].demographic.model

The model name or number of the endpoint device, such as iPhone or SM-G900F.

endpoints_response.item[].demographic.model_version

The model version of the endpoint device.

endpoints_response.item[].demographic.platform

The platform of the endpoint device, such as ios.

endpoints_response.item[].demographic.platform_version

The platform version of the endpoint device.

endpoints_response.item[].demographic.timezone

The time zone of the endpoint, specified as a tz database name value, such as America/Los_Angeles.

endpoints_response.item[].effective_date

The date and time, in ISO 8601 format, when the endpoint was last updated.

endpoints_response.item[].endpoint_status

Specifies whether messages or push notifications are sent to the endpoint. Possible values are: ACTIVE, messages are sent to the endpoint; and, INACTIVE, messages aren’t sent to the endpoint.

Amazon Pinpoint automatically sets this value to ACTIVE when you create an endpoint or update an existing endpoint. Amazon Pinpoint automatically sets this value to INACTIVE if you update another endpoint that has the same address specified by the Address property.

endpoints_response.item[].id

The unique identifier that you assigned to the endpoint. The identifier should be a globally unique identifier (GUID) to ensure that it doesn't conflict with other endpoint identifiers that are associated with the application.

endpoints_response.item[].location

The geographic information for the endpoint.

Show child fields
endpoints_response.item[].location.city

The name of the city where the endpoint is located.

endpoints_response.item[].location.country

The two-character code, in ISO 3166-1 alpha-2 format, for the country or region where the endpoint is located. For example, US for the United States.

endpoints_response.item[].location.latitude

The latitude coordinate of the endpoint location, rounded to one decimal place.

endpoints_response.item[].location.longitude

The longitude coordinate of the endpoint location, rounded to one decimal place.

endpoints_response.item[].location.postal_code

The postal or ZIP code for the area where the endpoint is located.

endpoints_response.item[].location.region

The name of the region where the endpoint is located. For locations in the United States, this value is the name of a state.

endpoints_response.item[].metrics

One or more custom metrics that your app reports to Amazon Pinpoint for the endpoint.

endpoints_response.item[].opt_out

Specifies whether the user who's associated with the endpoint has opted out of receiving messages and push notifications from you. Possible values are: ALL, the user has opted out and doesn't want to receive any messages or push notifications; and, NONE, the user hasn't opted out and wants to receive all messages and push notifications.

endpoints_response.item[].request_id

The unique identifier for the most recent request to update the endpoint.

endpoints_response.item[].user

One or more custom user attributes that your app reports to Amazon Pinpoint for the user who's associated with the endpoint.

Show child fields
endpoints_response.item[].user.user_attributes

One or more custom attributes that describe the user by associating a name with an array of values. For example, the value of an attribute named Interests might be: ["Science", "Music", "Travel"]. You can use these attributes as filter criteria when you create segments. Attribute names are case sensitive.

An attribute name can contain up to 50 characters. An attribute value can contain up to 100 characters. When you define the name of a custom attribute, avoid using the following characters: number sign (#), colon (:), question mark (?), backslash (\), and slash (/). The Amazon Pinpoint console can't display attribute names that contain these characters. This restriction doesn't apply to attribute values.

endpoints_response.item[].user.user_id

The unique identifier for the user.