How can I take AWS iam report through email

I want to use AWS lambda to create an AWS IAM report as an excel file and send it email to me.
So I made this code and it worked. but When I opened this excel file, I noticed that several topics were blank.

last_login, password_age

I want to know why it is blank.

this is my code. Thanks

import boto3
import pandas as pd
import os
from io import BytesIO
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart

def lambda_handler(event, context):
    client = boto3.client('iam')
    response = client.list_users()
    users_info = []
    for user in response['Users']:
        username = user['UserName']
        groups = client.list_groups_for_user(UserName=username)['Groups']
        last_login = client.get_login_profile(UserName=username).get('PasswordLastUsed')
        mfa_enabled = client.list_mfa_devices(UserName=username).get('MFADevices') != []
        password_age = (pd.Timestamp.now(tz='utc') - pd.Timestamp(last_login)).days if last_login else None
        access_keys = client.list_access_keys(UserName=username).get('AccessKeyMetadata')
        active_key_age = None
        for access_key in access_keys:
            if access_key['Status'] == 'Active':
                active_key_age = (pd.Timestamp.now(tz='utc') - pd.Timestamp(access_key['CreateDate'])).days
                break
        user_info = {
            'Username': username,
            'Groups': ', '.join([group['GroupName'] for group in groups]),
            'Last Login': str(last_login) if last_login else '',
            'MFA Enabled': mfa_enabled,
            'Password Age (Days)': password_age,
            'Active Key Age (Days)': active_key_age
        }
        users_info.append(user_info)
    df = pd.DataFrame(users_info)
    output = BytesIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    df.to_excel(writer, sheet_name='IAM Users Info', index=False, startrow=1)
    workbook = writer.book
    worksheet = writer.sheets['IAM Users Info']
    header_format = workbook.add_format({
        'bold': True,
        'text_wrap': True,
        'valign': 'top',
        'fg_color': '#D7E4BC',
        'border': 1
    })
    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)
        column_len = df[value].astype(str).str.len().max()
        column_len = max(column_len, len(value)) + 3
        worksheet.set_column(col_num, col_num, column_len)
    writer.save()
    from_address = 'my-mail'
    to_address = 'my-mail'
    subject = 'IAM Users Info'
    body = 'Please see the attached file for IAM Users Info.'  
    client = boto3.client('ses')   
    attachment = MIMEApplication(output.getvalue(), _subtype='vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    attachment.add_header('Content-Disposition', 'attachment', filename='iam_users_info.xlsx')
    message = MIMEMultipart

You are using client.get_login_profile For last_login but it return informations about the login profile of the user not the login activity, use client.get_user() instead, also password_age calculate the difference between the current timestamp and last_login but last_login is not retrieved as we want so password_age is incorrect as well.

I updated your code like that:

import boto3
import pandas as pd
import os
from io import BytesIO
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
def lambda_handler(event, context):
client = boto3.client('iam')
response = client.list_users()
users_info = []
for user in response['Users']:
username = user['UserName']
groups = client.list_groups_for_user(UserName=username)['Groups']
user_data = client.get_user(UserName=username)
last_login = user_data['User'].get('PasswordLastUsed')
mfa_enabled = client.list_mfa_devices(UserName=username).get('MFADevices') != []
password_age = (pd.Timestamp.now(tz='utc') - pd.Timestamp(last_login)).days if last_login else None
access_keys = client.list_access_keys(UserName=username).get('AccessKeyMetadata')
active_key_age = None
for access_key in access_keys:
if access_key['Status'] == 'Active':
active_key_age = (pd.Timestamp.now(tz='utc') - pd.Timestamp(access_key['CreateDate'])).days
break
user_info = {
'Username': username,
'Groups': ', '.join([group['GroupName'] for group in groups]),
'Last Login': str(last_login) if last_login else '',
'MFA Enabled': mfa_enabled,
'Password Age (Days)': password_age,
'Active Key Age (Days)': active_key_age
}
users_info.append(user_info)
df = pd.DataFrame(users_info)
output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
df.to_excel(writer, sheet_name='IAM Users Info', index=False, startrow=1)
workbook = writer.book
worksheet = writer.sheets['IAM Users Info']
header_format = workbook.add_format({
'bold': True,
'text_wrap': True,
'valign': 'top',
'fg_color': '#D7E4BC',
'border': 1
})
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num, value, header_format)
column_len = df[value].astype(str).str.len().max()
column_len = max(column_len, len(value)) + 3
worksheet.set_column(col_num, col_num, column_len)
writer.save()
from_address = 'my-mail'
to_address = 'my-mail'
subject = 'IAM Users Info'
body = 'Please see the attached file for IAM Users Info.'  
client = boto3.client('ses')   
attachment = MIMEApplication(output.getvalue(), _subtype='vnd.openxmlformats-officedocument.spreadsheetml.sheet')
attachment.add_header('Content-Disposition', 'attachment', filename='iam_users_info.xlsx')
message = MIMEMultipart