Add columns access_key_last_used_date, access_key_last_used_region and access_key_last_used_service to aws_iam_access_key table

[ngowraj]

Can we get a table to get the last used details for Access Keys?

Python boto function => https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/iam.html#IAM.Client.get_access_key_last_used

Getting info AccessKeyLastUsed['LastUsedDate'] will be very useful for killing any stale IAM users with inactive access keys.

[ngowraj]

@ParthaI

[misraved]

Hello @ngowraj, we already have a table aws_iam_access_key with a column status which provides information on whether the access key is active or not.

Sample query - https://hub.steampipe.io/plugins/turbot/aws/tables/aws_iam_access_key#list-of-access-keys-which-are-inactive

We also have a detailed query linking the access key to the user, showing how long have the access keys been created which can also provide valuable insights into its usage.

sample query - https://hub.steampipe.io/mods/turbot/aws_insights/queries/aws_iam_access_key_age_table#sql

However, the above queries don't provide data on when the access key was last used. Instead of creating a new table, I think we could add the information in the aws_iam_access_key table by making use of this API - https://docs.aws.amazon.com/IAM/latest/UserGuide/example_iam_GetAccessKeyLastUsed_section.html

This will help us in adding a column access_key_last_used to aws_iam_access_key table.

[ngowraj]

This will help us in adding a column access_key_last_used to aws_iam_access_key table.

This will be amazing ❤️

[ngowraj]

I am currently using role_last_used_date value from table aws_iam_role which has been useful to delete any unused roles. Having something similar to query for IAM Users table aws_iam_user would be amazing.

[ngowraj]

@misraved Is there any plan to implement this feature?

[cbruno10]

Hey @ngowraj , sorry for the silence on this issue. Yes, it's currently in our backlog, but we've had some issues come up this week that required new plugin versions (often with new Steampipe SDK versions). So, it's still planned from our end, but lower priority with no concrete date at the moment.

In the meantime, if anyone is interested in adding columns for this info, I believe there are 3 columns to be added to the aws_iam_access_key table:

• access_key_last_used_date (ColumnType_TIMESTAMP)
• access_key_last_used_service (ColumnType_STRING)
• access_key_last_used_region (ColumnType_STRING)

A separate hydrate function (getIamAccessKeyLastUsed) would probably be required for all 3 columns.

[cbruno10]

Hey @ngowraj , I spoke too soon! Looks like @misraved is already working on this and raised a draft PR - we'll review those in the upcoming days and look to release these new columns next week. If you see anything in the PR that looks awry from your perspective, feel free to add your comments over there.

[ngowraj]

❤️