如何通过Linux命令将AWS CloudTrail输出转换为表格格式

我想用Linux命令把以下命令的输出转换成表格格式：

aws --region us-east-1 cloudtrail lookup-events --lookup-attributes AttributeKey=EventName,AttributeValue=ConsoleLogin --start-time $START_TIME | \
jq -r .Events[].CloudTrailEvent | \
jq '.userIdentity.arn, .eventTime, .responseElements.ConsoleLogin'

当前输出是：

"arn:aws:sts::accountnum:assumed-role/role/user@com"

"2024-11-29T06:48:05Z"

"Success"

"arn:aws:sts::accountnum:assumed-role/role/user@com"

"2024-11-29T03:12:14Z"

"Success"

"arn:aws:sts::accountnum:assumed-role/role/user@com"

"2024-11-29T02:42:48Z"

"Success"

解决方案 #

方法1：用jq生成CSV后转表格 #

修改原命令，让jq直接输出结构化的CSV格式，再通过column工具生成对齐表格：

aws --region us-east-1 cloudtrail lookup-events --lookup-attributes AttributeKey=EventName,AttributeValue=ConsoleLogin --start-time $START_TIME | \
jq -r '.Events[] | .CloudTrailEvent | fromjson | [.userIdentity.arn, .eventTime, .responseElements.ConsoleLogin] | @csv' | \
sed 's/"//g' | column -t -s ','

输出示例：

arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T06:48:05Z  Success
arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T03:12:14Z  Success
arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T02:42:48Z  Success

方法2：带表头的格式化输出 #

如果需要添加表头，用制表符分隔字段后自动对齐：

echo -e "ARN\t事件时间\t登录状态" && \
aws --region us-east-1 cloudtrail lookup-events --lookup-attributes AttributeKey=EventName,AttributeValue=ConsoleLogin --start-time $START_TIME | \
jq -r '.Events[] | .CloudTrailEvent | fromjson | "\(.userIdentity.arn)\t\(.eventTime)\t\(.responseElements.ConsoleLogin)"' | column -t

输出示例：

ARN                                                事件时间           登录状态
arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T06:48:05Z  Success
arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T03:12:14Z  Success
arn:aws:sts::accountnum:assumed-role/role/user@com  2024-11-29T02:42:48Z  Success

方法3：用awk处理原始换行输出 #

如果不想修改原有jq逻辑，直接处理当前的逐行输出，用awk每3行合并为一条记录：

aws --region us-east-1 cloudtrail lookup-events --lookup-attributes AttributeKey=EventName,AttributeValue=ConsoleLogin --start-time $START_TIME | \
jq -r '.Events[].CloudTrailEvent | .userIdentity.arn, .eventTime, .responseElements.ConsoleLogin' | \
awk 'BEGIN{print "ARN\t事件时间\t登录状态"} {gsub(/"/,""); if (NR%3==1) arn=$0; else if (NR%3==2) time=$0; else {print arn"\t"time"\t"$0}}' | column -t

内容的提问来源于stack exchange，提问作者Ajith Jesudasan