Skip to content

aws.detective.list_investigations

Example SQL Queries

SELECT * FROM
aws.detective.list_investigations
WHERE
"graph_arn" = 'VALUE';

Description

Detective investigations lets you investigate IAM users and IAM roles using indicators of compromise. An indicator of compromise (IOC) is an artifact observed in or on a network, system, or environment that can (with a high level of confidence) identify malicious activity or a security incident. ListInvestigations lists all active Detective investigations.

Table Definition

Column NameColumn Data Type
graph_arn Required Input Column

The Amazon Resource Name (ARN) of the behavior graph.

VARCHAR
filter_criteria Input Column

Filters the investigation results based on a criteria.

STRUCT(
"severity" STRUCT(
"value" VARCHAR
),
"status" STRUCT(
"value" VARCHAR
),
"state" STRUCT(
"value" VARCHAR
),
"entity_arn" STRUCT(
"value" VARCHAR
),
"created_time" STRUCT(
"start_inclusive" TIMESTAMP_S,
"end_inclusive" TIMESTAMP_S
)
)
Show child fields
filter_criteria.created_time

Filter the investigation results based on when the investigation was created.

Show child fields
filter_criteria.created_time.end_inclusive

A timestamp representing the end date of the time period until when data is filtered, including the end date.

filter_criteria.created_time.start_inclusive

A timestamp representing the start of the time period from when data is filtered, including the start date.

filter_criteria.entity_arn

Filter the investigation results based on the Amazon Resource Name (ARN) of the entity.

Show child fields
filter_criteria.entity_arn.value

The string filter value.

filter_criteria.severity

Filter the investigation results based on the severity.

Show child fields
filter_criteria.severity.value

The string filter value.

filter_criteria.state

Filter the investigation results based on the state.

Show child fields
filter_criteria.state.value

The string filter value.

filter_criteria.status

Filter the investigation results based on the status.

Show child fields
filter_criteria.status.value

The string filter value.

max_results Input Column

Lists the maximum number of investigations in a page.

BIGINT
next_token Input Column

Lists if there are more results available. The value of nextToken is a unique pagination token for each page. Repeat the call using the returned token to retrieve the next page. Keep all other arguments unchanged.

Each pagination token expires after 24 hours.

VARCHAR
sort_criteria Input Column

Sorts the investigation results based on a criteria.

STRUCT(
"field" VARCHAR,
"sort_order" VARCHAR
)
Show child fields
sort_criteria.field

Represents the Field attribute to sort investigations.

sort_criteria.sort_order

The order by which the sorted findings are displayed.

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

investigation_details

Lists the summary of uncommon behavior or malicious activity which indicates a compromise.

STRUCT(
"investigation_id" VARCHAR,
"severity" VARCHAR,
"status" VARCHAR,
"state" VARCHAR,
"created_time" TIMESTAMP_S,
"entity_arn" VARCHAR,
"entity_type" VARCHAR
)[]
Show child fields
investigation_details[]
Show child fields
investigation_details[].created_time

The time stamp of the creation time of the investigation report. The value is an UTC ISO8601 formatted string. For example, 2021-08-18T16:35:56.284Z.

investigation_details[].entity_arn

The unique Amazon Resource Name (ARN) of the IAM user and IAM role.

investigation_details[].entity_type

Type of entity. For example, Amazon Web Services accounts, such as IAM user and role.

investigation_details[].investigation_id

The investigation ID of the investigation report.

investigation_details[].severity

Severity based on the likelihood and impact of the indicators of compromise discovered in the investigation.

investigation_details[].state

The current state of the investigation. An archived investigation indicates you have completed reviewing the investigation.

investigation_details[].status

Status based on the completion status of the investigation.