Skip to content
AWS for Query.Farm

aws.connectcases.search_related_items

Example SQL Queries

SELECT * FROM
aws.connectcases.search_related_items
WHERE
"case_id" = 'VALUE'
AND "domain_id" = 'VALUE';

Description

Searches for related items that are associated with a case.

If no filters are provided, this returns all related items associated with a case.

Table Definition

Column NameColumn Data Type
case_id Required Input Column

A unique identifier of the case.

VARCHAR
domain_id Required Input Column

The unique identifier of the Cases domain. 

VARCHAR
filters Input Column

The list of types of related items and their parameters to use for filtering.

STRUCT(
    "comment" BOOLEAN,
    "contact" STRUCT(
        "channel" VARCHAR[],
        "contact_arn" VARCHAR
    ),
    "file" STRUCT(
        "file_arn" VARCHAR
    )
)[]
Show child fields
filters[]
Show child fields
filters[].comment

A filter for related items of type Comment.

filters[].contact

A filter for related items of type Contact.

Show child fields
filters[].contact.channel[]
filters[].contact.contact_arn

A unique identifier of a contact in Amazon Connect.

filters[].file

A filter for related items of this type of File.

Show child fields
filters[].file.file_arn

The Amazon Resource Name (ARN) of the file.

_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.

association_time

Time at which a related item was associated with a case.

TIMESTAMP_S
content

Represents the content of a particular type of related item.

STRUCT(
    "comment" STRUCT(
        "body" VARCHAR,
        "content_type" VARCHAR
    ),
    "contact" STRUCT(
        "channel" VARCHAR,
        "connected_to_system_time" TIMESTAMP_S,
        "contact_arn" VARCHAR
    ),
    "file" STRUCT(
        "file_arn" VARCHAR
    )
)
Show child fields
content.comment

Represents the content of a comment to be returned to agents.

Show child fields
content.comment.body

Text in the body of a Comment on a case.

content.comment.content_type

Type of the text in the box of a Comment on a case.

content.contact

Represents the content of a contact to be returned to agents.

Show child fields
content.contact.channel

A list of channels to filter on for related items of type Contact.

content.contact.connected_to_system_time

The difference between the InitiationTimestamp and the DisconnectTimestamp of the contact.

content.contact.contact_arn

A unique identifier of a contact in Amazon Connect.

content.file

Represents the content of a File to be returned to agents.

Show child fields
content.file.file_arn

The Amazon Resource Name (ARN) of a File in Amazon Connect.

performed_by

Represents the creator of the related item.

STRUCT(
    "user_arn" VARCHAR
)
Show child fields
performed_by.user_arn

Represents the Amazon Connect ARN of the user.

related_item_id

Unique identifier of a related item.

VARCHAR
tags

A map of of key-value pairs that represent tags on a resource. Tags are used to organize, track, or control access for this resource.

MAP(VARCHAR, VARCHAR)
type

Type of a related item.

VARCHAR