Skip to content
AWS for Query.Farm

aws.iot.list_audit_findings

Example SQL Queries

SELECT * FROM
aws.iot.list_audit_findings;

Description

Lists the findings (results) of a Device Defender audit or of the audits performed during a specified time period. (Findings are retained for 90 days.)

Requires permission to access the ListAuditFindings action.

Table Definition

Column NameColumn Data Type
check_name Input Column

The audit check that generated this result.

VARCHAR
end_time Input Column

A filter to limit results to those found before the specified time. You must specify either the startTime and endTime or the taskId, but not both.

TIMESTAMP_S
list_suppressed_findings Input Column

Boolean flag indicating whether only the suppressed findings or the unsuppressed findings should be listed. If this parameter isn't provided, the response will list both suppressed and unsuppressed findings. 

BOOLEAN
resource_identifier Input Column

Information identifying the noncompliant resource.

STRUCT(
    "device_certificate_id" VARCHAR,
    "ca_certificate_id" VARCHAR,
    "cognito_identity_pool_id" VARCHAR,
    "client_id" VARCHAR,
    "policy_version_identifier" STRUCT(
        "policy_name" VARCHAR,
        "policy_version_id" VARCHAR
    ),
    "account" VARCHAR,
    "iam_role_arn" VARCHAR,
    "role_alias_arn" VARCHAR,
    "issuer_certificate_identifier" STRUCT(
        "issuer_certificate_subject" VARCHAR,
        "issuer_id" VARCHAR,
        "issuer_certificate_serial_number" VARCHAR
    ),
    "device_certificate_arn" VARCHAR
)
Show child fields
resource_identifier.account

The account with which the resource is associated.

resource_identifier.ca_certificate_id

The ID of the CA certificate used to authorize the certificate.

resource_identifier.client_id

The client ID.

resource_identifier.cognito_identity_pool_id

The ID of the Amazon Cognito identity pool.

resource_identifier.device_certificate_arn

The ARN of the identified device certificate.

resource_identifier.device_certificate_id

The ID of the certificate attached to the resource.

resource_identifier.iam_role_arn

The ARN of the IAM role that has overly permissive actions.

resource_identifier.issuer_certificate_identifier

The issuer certificate identifier.

Show child fields
resource_identifier.issuer_certificate_identifier.issuer_certificate_serial_number

The issuer certificate serial number.

resource_identifier.issuer_certificate_identifier.issuer_certificate_subject

The subject of the issuer certificate.

resource_identifier.issuer_certificate_identifier.issuer_id

The issuer ID.

resource_identifier.policy_version_identifier

The version of the policy associated with the resource.

Show child fields
resource_identifier.policy_version_identifier.policy_name

The name of the policy.

resource_identifier.policy_version_identifier.policy_version_id

The ID of the version of the policy associated with the resource.

resource_identifier.role_alias_arn

The ARN of the role alias that has overly permissive actions.

start_time Input Column

A filter to limit results to those found after the specified time. You must specify either the startTime and endTime or the taskId, but not both.

TIMESTAMP_S
task_id Input Column

The ID of the audit that generated this result (finding).

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
finding_id

A unique identifier for this set of audit findings. This identifier is used to apply mitigation tasks to one or more sets of findings.

VARCHAR
finding_time

The time the result (finding) was discovered.

TIMESTAMP_S
is_suppressed

Indicates whether the audit finding was suppressed or not during reporting. 

BOOLEAN
non_compliant_resource

The resource that was found to be noncompliant with the audit check.

STRUCT(
    "resource_type" VARCHAR,
    "resource_identifier" STRUCT(
        "device_certificate_id" VARCHAR,
        "ca_certificate_id" VARCHAR,
        "cognito_identity_pool_id" VARCHAR,
        "client_id" VARCHAR,
        "policy_version_identifier" STRUCT(
            "policy_name" VARCHAR,
            "policy_version_id" VARCHAR
        ),
        "account" VARCHAR,
        "iam_role_arn" VARCHAR,
        "role_alias_arn" VARCHAR,
        "issuer_certificate_identifier" STRUCT(
            "issuer_certificate_subject" VARCHAR,
            "issuer_id" VARCHAR,
            "issuer_certificate_serial_number" VARCHAR
        ),
        "device_certificate_arn" VARCHAR
    ),
    "additional_info" MAP(VARCHAR, VARCHAR)
)
Show child fields
non_compliant_resource.additional_info

Other information about the noncompliant resource.

non_compliant_resource.resource_identifier

Information that identifies the noncompliant resource.

Show child fields
non_compliant_resource.resource_identifier.account

The account with which the resource is associated.

non_compliant_resource.resource_identifier.ca_certificate_id

The ID of the CA certificate used to authorize the certificate.

non_compliant_resource.resource_identifier.client_id

The client ID.

non_compliant_resource.resource_identifier.cognito_identity_pool_id

The ID of the Amazon Cognito identity pool.

non_compliant_resource.resource_identifier.device_certificate_arn

The ARN of the identified device certificate.

non_compliant_resource.resource_identifier.device_certificate_id

The ID of the certificate attached to the resource.

non_compliant_resource.resource_identifier.iam_role_arn

The ARN of the IAM role that has overly permissive actions.

non_compliant_resource.resource_identifier.issuer_certificate_identifier

The issuer certificate identifier.

Show child fields
non_compliant_resource.resource_identifier.issuer_certificate_identifier.issuer_certificate_serial_number

The issuer certificate serial number.

non_compliant_resource.resource_identifier.issuer_certificate_identifier.issuer_certificate_subject

The subject of the issuer certificate.

non_compliant_resource.resource_identifier.issuer_certificate_identifier.issuer_id

The issuer ID.

non_compliant_resource.resource_identifier.policy_version_identifier

The version of the policy associated with the resource.

Show child fields
non_compliant_resource.resource_identifier.policy_version_identifier.policy_name

The name of the policy.

non_compliant_resource.resource_identifier.policy_version_identifier.policy_version_id

The ID of the version of the policy associated with the resource.

non_compliant_resource.resource_identifier.role_alias_arn

The ARN of the role alias that has overly permissive actions.

non_compliant_resource.resource_type

The type of the noncompliant resource.

reason_for_non_compliance

The reason the resource was noncompliant.

VARCHAR
reason_for_non_compliance_code

A code that indicates the reason that the resource was noncompliant.

VARCHAR
related_resources

The list of related resources.

STRUCT(
    "resource_type" VARCHAR,
    "resource_identifier" STRUCT(
        "device_certificate_id" VARCHAR,
        "ca_certificate_id" VARCHAR,
        "cognito_identity_pool_id" VARCHAR,
        "client_id" VARCHAR,
        "policy_version_identifier" STRUCT(
            "policy_name" VARCHAR,
            "policy_version_id" VARCHAR
        ),
        "account" VARCHAR,
        "iam_role_arn" VARCHAR,
        "role_alias_arn" VARCHAR,
        "issuer_certificate_identifier" STRUCT(
            "issuer_certificate_subject" VARCHAR,
            "issuer_id" VARCHAR,
            "issuer_certificate_serial_number" VARCHAR
        ),
        "device_certificate_arn" VARCHAR
    ),
    "additional_info" MAP(VARCHAR, VARCHAR)
)[]
Show child fields
related_resources[]
Show child fields
related_resources[].additional_info

Other information about the resource.

related_resources[].resource_identifier

Information that identifies the resource.

Show child fields
related_resources[].resource_identifier.account

The account with which the resource is associated.

related_resources[].resource_identifier.ca_certificate_id

The ID of the CA certificate used to authorize the certificate.

related_resources[].resource_identifier.client_id

The client ID.

related_resources[].resource_identifier.cognito_identity_pool_id

The ID of the Amazon Cognito identity pool.

related_resources[].resource_identifier.device_certificate_arn

The ARN of the identified device certificate.

related_resources[].resource_identifier.device_certificate_id

The ID of the certificate attached to the resource.

related_resources[].resource_identifier.iam_role_arn

The ARN of the IAM role that has overly permissive actions.

related_resources[].resource_identifier.issuer_certificate_identifier

The issuer certificate identifier.

Show child fields
related_resources[].resource_identifier.issuer_certificate_identifier.issuer_certificate_serial_number

The issuer certificate serial number.

related_resources[].resource_identifier.issuer_certificate_identifier.issuer_certificate_subject

The subject of the issuer certificate.

related_resources[].resource_identifier.issuer_certificate_identifier.issuer_id

The issuer ID.

related_resources[].resource_identifier.policy_version_identifier

The version of the policy associated with the resource.

Show child fields
related_resources[].resource_identifier.policy_version_identifier.policy_name

The name of the policy.

related_resources[].resource_identifier.policy_version_identifier.policy_version_id

The ID of the version of the policy associated with the resource.

related_resources[].resource_identifier.role_alias_arn

The ARN of the role alias that has overly permissive actions.

related_resources[].resource_type

The type of resource.

severity

The severity of the result (finding).

VARCHAR
task_start_time

The time the audit started.

TIMESTAMP_S