T/SQL LDAP查询如何遍历所有DC获取真实lastLoginTimestamp值

核心思路 #

• 先获取当前域下所有域控制器的完整地址
• 为每台DC生成独立的LDAP查询语句，LDAP路径直接绑定对应DC主机名，避免域名解析自动路由到随机DC
• 合并所有DC的查询结果后，按用户维度取lastLogonTimestamp的最大值，即为真实的最新登录时间

完整实现代码 #

已修正你原代码中S DATETIME的笔误，完整可运行代码如下：

-----------------------------------------
-- 1. 初始化变量 & 临时表存储DC列表
-----------------------------------------
DECLARE @strSQL NVARCHAR(MAX) = N''
DECLARE @DCList TABLE (DCName NVARCHAR(255))

-- 从AD中查询当前域所有域控制器
INSERT INTO @DCList(DCName)
SELECT name FROM OPENQUERY([MyLinkedServer], '
SELECT name FROM ''LDAP://OU=Domain Controllers,DC=MyDomain,DC=com''
WHERE objectClass = ''computer''
')

-----------------------------------------
-- 2. 动态拼接所有DC的查询语句
-----------------------------------------
SELECT @strSQL = @strSQL + N'
UNION ALL
SELECT sAMAccountName AS UserName,
       Mail AS EMail,
       whenCreated AS CreatedOnDate,
       CASE
         WHEN lastLogonTimestamp IS NULL THEN NULL
         WHEN CAST(lastLogonTimestamp AS BIGINT) = 0 THEN NULL
         ELSE CAST((CAST(lastLogonTimestamp AS BIGINT) / 864000000000.0 - 109207) AS DATETIME)
       END AS LastLogon,
       CASE
         WHEN pwdLastSet IS NULL THEN NULL
         WHEN CAST(pwdLastSet AS BIGINT) = 0 THEN NULL
         ELSE CAST((CAST(pwdLastSet AS BIGINT) / 864000000000.0 - 109207) AS DATETIME)
       END AS LastPWReset
FROM OPENQUERY([MyLinkedServer], ''
SELECT sAMAccountName, Mail, DistinguishedName, whenCreated, lastLogonTimestamp, pwdLastSet
FROM ''''LDAP://' + DCName + '.MyDomain.com/OU=Users,OU=MyGroup,DC=MyDomain,DC=com''''
WHERE objectClass = ''''User''''
'')
' FROM @DCList

-- 去掉开头多余的UNION ALL
SET @strSQL = STUFF(@strSQL, 1, 11, N'')

-----------------------------------------
-- 3. 合并结果取最新登录时间
-----------------------------------------
SET @strSQL = N'
SELECT UserName, EMail, CreatedOnDate, 
       MAX(LastLogon) AS RealLastLogon, 
       MAX(LastPWReset) AS LastPWReset
FROM (' + @strSQL + N') AS AllDCResults
GROUP BY UserName, EMail, CreatedOnDate
'

-----------------------------------------
-- 4. 执行查询
-----------------------------------------
EXEC SP_EXECUTESQL @strSQL

注意事项 #

• 如果你需要更精准的实时登录时间，可以将代码中的lastLogonTimestamp替换为lastLogon，该属性为DC本地私有属性，完全不会跨DC同步，更适合获取最新登录时间
• 执行账号需要具备所有DC的AD读取权限，否则部分DC的查询会报错
• 如果有DC长期离线，可以在@DCList的查询中添加过滤条件排除离线DC，提升查询效率

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