读取AD用户数据库信息推送飞书密码过期提醒

读取AD用户数据库信息推送飞书密码过期提醒，本质上是通过ldap获取AD的用户信息将其存储到数据库中，通过数据库获取密码即将过期的用户信息，将AD上的用户账号和邮箱去飞书的API接口获取用户id，再通过飞书机器人API接口来推送信息。

环境：mysql、ldap、AD服务器证书，具体可参照前边撰写的scp部署相关、飞书机器人，需要具备私发用户信息、读取用户邮箱、读取用户ID的相关权限、且该机器人所有成员可见。

#!/bin/bash
# 设置严格模式：出错即停止，变量未定义报错，管道错误报错
set -euo pipefail

# ===================== 配置项 =====================
# AD域服务器配置
AD_HOST="AD的IP"
AD_PORT="636"
AD_BIND_DN="CN=管理员账号,OU=GZ,DC=WOORING,DC=CN"
AD_BIND_PW="你的AD绑定密码"
AD_SEARCH_BASE="OU=GZ,DC=WOORING,DC=CN"
AD_FILTER="(objectClass=user)"

# 数据库配置
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASS="你的数据库密码"
DB_NAME="你的数据库名"
DB_TABLE="app_ad_user_adusermodel"

# 临时文件
LOG_FILE="/var/log/ad_sync_to_db.log"
TMP_RAW_LDIF="/tmp/ad_raw.ldif"
TMP_DATA_TSV="/tmp/ad_processed.tsv"

# 业务逻辑配置
TIME_ZONE_OFFSET=8           # 时区偏移小时
MAX_PWD_VALID_DAYS=180       # 密码有效期

# ===================== 工具检查 =====================
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] [$1] $2" | tee -a "${LOG_FILE}"
}

check_env() {
    local deps=("ldapsearch" "mysql" "base64" "awk")
    for dep in "${deps[@]}"; do
        if ! command -v "$dep" &> /dev/null; then
            log "ERROR" "缺少工具: $dep"; exit 1
        fi
    done
}

# ===================== 主逻辑 =====================

fetch_ad_data() {
    log "INFO" "正在从 AD 获取数据 (含分页处理)..."
    
    # -E pr=1000/noprompt: 关键参数，解决1000条限制
    # -o ldif-wrap=no: 关键参数，防止长DN换行导致解析失败
    ldapsearch -x -H ldaps://${AD_HOST}:${AD_PORT} \
        -D "${AD_BIND_DN}" -w "${AD_BIND_PW}" \
        -b "${AD_SEARCH_BASE}" "${AD_FILTER}" \
        -E pr=1000/noprompt -o ldif-wrap=no \
        name sAMAccountName employeeNumber mail mobile distinguishedName userAccountControl pwdLastSet whenCreated memberOf \
        > "${TMP_RAW_LDIF}"

    if [ ! -s "${TMP_RAW_LDIF}" ]; then
        log "ERROR" "未能获取到 AD 数据"; exit 1
    fi
}

process_data() {
    log "INFO" "开始解析解析 LDIF 并处理 Base64 字段..."

    # 使用 AWK 进行流式处理，提高性能
    # 逻辑：识别以 "::" 开头的 Base64 编码并实时解码
    awk -v offset="$TIME_ZONE_OFFSET" -v max_days="$MAX_PWD_VALID_DAYS" '
    function decode_b64(val) {
        cmd = "echo " val " | base64 -d"
        cmd | getline decoded
        close(cmd)
        return decoded
    }
    BEGIN { FS=": "; OFS="\t"; RS="\ndn: " }
    {
        # 初始化变量
        name=""; sam=""; emp=""; mail=""; mob=""; dn=""; uac=""; pwdlast=""; created=""; memberof="";
        
        # 遍历每一行属性
        n = split($0, lines, "\n")
        for (i=1; i<=n; i++) {
            # 处理属性（考虑普通 ":" 和 Base64 "::"）
            split(lines[i], kv, ":")
            key = kv[1]; 
            # 提取值（如果是 :: 则去掉前导空格）
            val = substr(lines[i], length(key) + 3)

            if (key == "name") name = (lines[i] ~ /::/) ? decode_b64(val) : val
            else if (key == "sAMAccountName") sam = val
            else if (key == "employeeNumber") emp = val
            else if (key == "mail") mail = val
            else if (key == "mobile") mob = val
            else if (key == "distinguishedName") dn = val
            else if (key == "userAccountControl") uac = val
            else if (key == "pwdLastSet") pwdlast = val
            else if (key == "whenCreated") created = val
            else if (key == "memberOf") memberof = memberof ";" val
        }

        # 1. 转换 whenCreated (YYYYMMDDHHMMSS.0Z -> 数据库格式)
        if (created != "") {
            gsub(/\..*/, "", created)
            # 使用 date 计算时区偏移
            "date -d \"" substr(created,1,8) " " substr(created,9,2) ":" substr(created,11,2) ":" substr(created,13,2) " UTC " offset " hours\" +\"%Y-%m-%d %H:%M:%S\"" | getline created_fmt
            close("date...")
        } else { created_fmt = "1970-01-01 00:00:00" }

        # 2. 转换 pwdLastSet (AD Timestamp -> Unix Epoch)
        rem_days = -1
        if (pwdlast != "" && pwdlast != "0") {
            epoch = int(pwdlast / 10000000) - 11644473600
            "date -d \"@" epoch " " offset " hours\" +\"%Y-%m-%d %H:%M:%S\"" | getline pwd_fmt
            close("date...")
            
            # 计算剩余天数
            exp_ts = epoch + (max_days * 86400)
            "date +%s" | getline now_ts
            close("date...")
            rem_days = int((exp_ts - now_ts) / 86400)
        } else { pwd_fmt = "1970-01-01 00:00:00" }

        is_haiwai = (memberof ~ /FQ_User/) ? 1 : 0

        if (sam != "") {
            print name, sam, emp, mail, mob, dn, uac, is_haiwai, pwd_fmt, created_fmt, rem_days
        }
    }' "${TMP_RAW_LDIF}" > "${TMP_DATA_TSV}"
}

sync_to_db() {
    log "INFO" "开始批量导入数据库 (LOAD DATA方式)..."
    
    # 注意：需要 MySQL 用户有 FILE 权限且服务端开启 --local-infile
    mysql --local-infile=1 -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASS} ${DB_NAME} <<EOF
SET NAMES utf8mb4;
TRUNCATE TABLE ${DB_TABLE};
LOAD DATA LOCAL INFILE '${TMP_DATA_TSV}'
INTO TABLE ${DB_TABLE}
FIELDS TERMINATED BY '\t'
(name, sAMAccountName, employeeNumber, mail, mobile, distinguishedName, userAccountControl, is_haiwai, pwdLastSet, whenCreated, passwd_active_days)
SET create_time=NOW(), update_time=NOW();
EOF

    log "INFO" "同步完成，共导入 $(wc -l < "${TMP_DATA_TSV}") 条数据。"
}

cleanup() {
    rm -f "${TMP_RAW_LDIF}" "${TMP_DATA_TSV}"
}

# ===================== 执行 =====================
check_env
trap 'log "ERROR" "脚本异常中断"; cleanup; exit 1' ERR

fetch_ad_data
process_data
sync_to_db
cleanup

log "INFO" "全部流程执行完毕。"

代码如下：


#!/bin/bash

##########################################################################
# 1. 数据库配置
DB_HOST="数据库IP"
DB_PORT="3306"
DB_USER="root"
DB_PASS="数据库密码"
DB_NAME="devops"
DB_TABLE="app_ad_user"

# 2. 飞书配置
FEISHU_APP_ID="应用ID"
FEISHU_APP_SECRET="应用密钥"
FEISHU_BASE_URL="https://open.feishu.cn/open-apis"

# 3. 测试配置
# 填入你自己的飞书 User ID（注意：是 user_id，不是 open_id）
MY_FEISHU_USER_ID="飞书ID"
TEST_MODE=false   # 设置为 true，则所有消息都只会发给你自己，该配置为验证信息推送配置

# 4. 提醒配置
REMIND_DAYS=(1 2 3)     #配置需要提醒密码到期前天数的时间，根据想推送的时间自由更改
LOG_FILE="/var/log/feishu_pwd_reminder_test.log"
##########################################################################

log() {
    local LEVEL=$1; local MSG=$2
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] [$LEVEL] $MSG" | tee -a $LOG_FILE
}

# 1. 获取飞书租户Token (使用 jq)
get_feishu_token() {
    log "INFO" "开始获取飞书租户Token..."
    RESP=$(curl -s -X POST "${FEISHU_BASE_URL}/auth/v3/tenant_access_token/internal" \
        -H "Content-Type: application/json" \
        -d "{\"app_id\":\"${FEISHU_APP_ID}\",\"app_secret\":\"${FEISHU_APP_SECRET}\"}")

    TOKEN=$(echo "$RESP" | jq -r '.tenant_access_token // empty')

    if [ -z "$TOKEN" ]; then
        log "ERROR" "获取飞书Token失败：$RESP"
        exit 1
    fi
}

# 2. 从数据库查询需要提醒的用户
get_remind_users() {
    log "INFO" "查询数据库过期用户..."
    TODAY=$(date +'%Y-%m-%d')
    DAYS_STR=$(IFS=,; echo "${REMIND_DAYS[*]}")

    SQL="SELECT CONCAT_WS('|', sAMAccountName, IFNULL(name,'用户'), mail, passwd_active_days)
         FROM ${DB_TABLE}
         WHERE passwd_active_days IN (${DAYS_STR})
         AND DATE(update_datetime) = '${TODAY}'
         AND userAccountControl != '66048';"    ##66048是AD里密码永不过期的典型用户

    USERS=$(mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASS} -D ${DB_NAME} -N -e "${SQL}")
}

# 3. 发送飞书私信 (测试重定向逻辑)
send_feishu_msg() {
    local ACCOUNT=$1; local NAME=$2; local EMAIL=$3; local DAYS=$4
    local EMAIL=$(echo "$3" | tr -d '\r\n ')  # 强行去除可能存在的换行和空格
    local TARGET_ID=$MY_FEISHU_USER_ID

    log "INFO" "准备处理用户: $ACCOUNT ($EMAIL)"

    # 如果是正式模式，需要动态查询对方的 ID
    if [ "$TEST_MODE" = false ]; then
        USER_ID_RESP=$(curl -s -X POST "${FEISHU_BASE_URL}/contact/v3/users/batch_get_id" \
            -H "Authorization: Bearer ${TOKEN}" \
            -H "Content-Type: application/json" \
            -d "{\"emails\":[\"${EMAIL}\"]}")
        TARGET_ID=$(echo "$USER_ID_RESP" | jq -r '.data.user_list[0].user_id // empty')

        if [ -z "$TARGET_ID" ]; then
            log "ERROR" "无法获取 $ACCOUNT 的飞书ID"
            log "DEBUG" "飞书返回内容: $USER_ID_RESP" # 这一行非常重要
            return 1
        fi
    fi

    # 构造消息内容
    local TEXT="【密码过期提醒】\n"
    if [ "$TEST_MODE" = true ]; then
        TEXT="${TEXT}⚠️ 测试模式开启，原接收人应该是: ${NAME}(${ACCOUNT})\n"
    fi
    TEXT="${TEXT}--------------------------------\n"
    TEXT="${TEXT}👤 账号：${NAME}(${ACCOUNT})\n"
    TEXT="${TEXT}⏳ 剩余：${DAYS} 天\n\n"

    TEXT="${TEXT}🛠 【操作指引】: \n"
    TEXT="${TEXT}1️⃣ 请在公司内网访问：************ 密码自助修改。\n"
    TEXT="${TEXT}2️⃣ 修改密码后，请及时重连：WIFI、VPN 或 共享盘。\n\n"

    TEXT="${TEXT}💬 如需帮助，请在飞书中联系 *****。"

    # 发送请求
    MSG_RESP=$(curl -s -X POST "${FEISHU_BASE_URL}/im/v1/messages?receive_id_type=open_id" \
        -H "Authorization: Bearer ${TOKEN}" \
        -H "Content-Type: application/json" \
        -d "{
            \"receive_id\":\"${TARGET_ID}\",
            \"msg_type\":\"text\",
            \"content\": $(echo -n "{\"text\":\"$TEXT\"}" | jq -Rs .)
        }")

    if [ "$(echo "$MSG_RESP" | jq -r '.code')" = "0" ]; then
        log "INFO" "推送成功 (Target: $TARGET_ID)"
    else
        log "ERROR" "发送失败: $MSG_RESP"
    fi
}

main() {
    get_feishu_token
    get_remind_users

    if [ -z "$USERS" ]; then
        log "INFO" "未发现符合条件的过期用户。"
        exit 0
    fi

    echo "$USERS" | while IFS='|' read -r ACCOUNT NAME EMAIL DAYS; do
        send_feishu_msg "$ACCOUNT" "$NAME" "$EMAIL" "$DAYS"
        sleep 0.5
    done
}

main

再编写一个crontab 定时执行推送任务即可。

 00 16 * * 1-5 /bin/bash /root/check_pw.sh >> /var/log/feishu_pwd_reminder_cron.log 2>&1

Our Visitor

发送评论编辑评论

Our Visitor

发送评论 编辑评论

发送评论编辑评论