Skip to content
AWS for Query.Farm

aws.iam.get_organizations_access_report

Example SQL Queries

SELECT * FROM
aws.iam.get_organizations_access_report
WHERE
"job_id" = 'VALUE';

Description

Retrieves the service last accessed data report for Organizations that was previously generated using the GenerateOrganizationsAccessReport operation. This operation retrieves the status of your report job and the report contents.

Depending on the parameters that you passed when you generated the report, the data returned could include different information. For details, see GenerateOrganizationsAccessReport.

To call this operation, you must be signed in to the management account in your organization. SCPs must be enabled for your organization root. You must have permissions to perform this operation. For more information, see Refining permissions using service last accessed data in the IAM User Guide.

For each service that principals in an account (root user, IAM users, or IAM roles) could access using SCPs, the operation returns details about the most recent access attempt. If there was no attempt, the service is listed without details about the most recent attempt to access the service. If the operation fails, it returns the reason that it failed.

By default, the list is sorted by service namespace.

Table Definition

Column NameColumn Data Type
job_id Required Input Column

The identifier of the request generated by the GenerateOrganizationsAccessReport operation.

VARCHAR
marker Input Column

When IsTruncated is true, this element is present and contains the value to use for the Marker parameter in a subsequent pagination request.

VARCHAR
max_items Input Column

Use this only when paginating results to indicate the maximum number of items you want in the response. If additional items exist beyond the maximum you specify, the IsTruncated response element is true.

If you do not include this parameter, the number of items defaults to 100. Note that IAM might return fewer results, even when there are more results available. In that case, the IsTruncated response element returns true, and Marker contains a value to include in the subsequent call that tells the service where to continue from.

BIGINT
sort_key Input Column

The key that is used to sort the results. If you choose the namespace key, the results are returned in alphabetical order. If you choose the time key, the results are sorted numerically by the date and time.

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.

access_details

An object that contains details about the most recent attempt to access the service.

STRUCT(
    "service_name" VARCHAR,
    "service_namespace" VARCHAR,
    "region" VARCHAR,
    "entity_path" VARCHAR,
    "last_authenticated_time" TIMESTAMP_S,
    "total_authenticated_entities" BIGINT
)[]
Show child fields
access_details[]
Show child fields
access_details[].entity_path

The path of the Organizations entity (root, organizational unit, or account) from which an authenticated principal last attempted to access the service. Amazon Web Services does not report unauthenticated requests.

This field is null if no principals (IAM users, IAM roles, or root user) in the reported Organizations entity attempted to access the service within the tracking period.

access_details[].last_authenticated_time

The date and time, in ISO 8601 date-time format, when an authenticated principal most recently attempted to access the service. Amazon Web Services does not report unauthenticated requests.

This field is null if no principals in the reported Organizations entity attempted to access the service within the tracking period.

access_details[].region

The Region where the last service access attempt occurred.

This field is null if no principals in the reported Organizations entity attempted to access the service within the tracking period.

access_details[].service_name

The name of the service in which access was attempted.

access_details[].service_namespace

The namespace of the service in which access was attempted.

To learn the service namespace of a service, see Actions, resources, and condition keys for Amazon Web Services services in the Service Authorization Reference. Choose the name of the service to view details for that service. In the first paragraph, find the service prefix. For example, (service prefix: a4b). For more information about service namespaces, see Amazon Web Services service namespaces in the Amazon Web Services General Reference.

access_details[].total_authenticated_entities

The number of accounts with authenticated principals (root user, IAM users, and IAM roles) that attempted to access the service in the tracking period.

error_details

Contains information about the reason that the operation failed.

This data type is used as a response element in the GetOrganizationsAccessReport, GetServiceLastAccessedDetails, and GetServiceLastAccessedDetailsWithEntities operations.

STRUCT(
    "message" VARCHAR,
    "code" VARCHAR
)
Show child fields
error_details.code

The error code associated with the operation failure.

error_details.message

Detailed information about the reason that the operation failed.

is_truncated

A flag that indicates whether there are more items to return. If your results were truncated, you can make a subsequent pagination request using the Marker request parameter to retrieve more items. Note that IAM might return fewer than the MaxItems number of results even when there are more results available. We recommend that you check IsTruncated after every call to ensure that you receive all your results.

BOOLEAN
job_completion_date

The date and time, in ISO 8601 date-time format, when the generated report job was completed or failed.

This field is null if the job is still in progress, as indicated by a job status value of IN_PROGRESS.

TIMESTAMP_S
job_creation_date

The date and time, in ISO 8601 date-time format, when the report job was created.

TIMESTAMP_S
job_status

The status of the job.

VARCHAR
number_of_services_accessible

The number of services that the applicable SCPs allow account principals to access.

BIGINT
number_of_services_not_accessed

The number of services that account principals are allowed but did not attempt to access.

BIGINT