本文將介紹通過在SQL Server中結合AD信息與Lync存檔數據庫查詢用戶注冊會話等信息。
【正文】
一、 在SQL Server中查詢AD信息
1.1 要通過SQL Server查詢AD信息，需要先創建鏈接服務器，服務器對象—鏈接服務器—新建鏈接服務器
1.2 選擇訪問接口和填寫相關信息
1.3 在安全性頁面，選擇“使用此安全上下文建立連接”，輸入具有相應域權限賬戶與密碼；
1.4 當然以上的創建操作步驟也可以通過T-SQL語句去創建，語句如下：
--新建鏈接服務器ADSI
EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
--添加登陸憑據
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = '{domain}\{user}', @rmtpassword = '{passowrd}'
1.5 創建了鏈接服務器後，就可以查詢AD的信息，下面的實例查詢出具有SIP地址的AD用戶的顯示名稱、部門、SIP地址信息；
--查詢AD信息
SELECT displayName AS 顯示名稱, department AS 部門, [msRTCSIP-PrimaryUserAddress] COLLATE Latin1_General_CI_AI AS SIP地址
FROM OPENQUERY(ADSI,
'SELECT displayName, msRTCSIP-PrimaryUserAddress, department FROM ''LDAP://DC=canway,DC=net''WHERE objectClass = ''user'' AND msRTCSIP-PrimaryUserAddress = ''*'' ')
1.6 寫這麼長的語句去查詢是不太方便的，所以一般情況下都是不會通過這種方式去查詢，都是通過創建一個視圖，通過查詢視圖來查詢AD信息，以下語句將創建一個視圖：
--創建視圖
CREATE VIEW LyncUsers
AS
SELECT displayName AS 顯示名稱, department AS 部門, [msRTCSIP-PrimaryUserAddress] COLLATE Latin1_General_CI_AI AS SIP地址
FROM OPENQUERY(ADSI,
'SELECT displayName, msRTCSIP-PrimaryUserAddress, department FROM ''LDAP://DC=canway,DC=net''WHERE objectClass = ''user'' AND msRTCSIP-PrimaryUserAddress = ''*'' ')
1.7 在實際環境中，可能會由於組織中的AD用戶較多導致出現以下提示“無法從鏈接服務器 "ADSI" 的 OLE DB 訪問接口"ADSDSOObject"提取行。” 如下圖:
1.8 出現此問題的原因是，LDAPAdminLimits 屬性的 MaxPageSize 值用於控制執行 LDAP 查詢時可以返回的記錄數。默認值為 1000 個記錄。如果要返回多於 1000 個的項，則 Active Directory 將檢測到該最大值，並且不返回任何內容。如果是SqlServer2005的話會顯示查詢結果中的前1000條，如果是2008的話是901條；
1.9 要解決此問題有2種解決方式:
1. 修改MaxPageSize 值
a) 在 Ntdsutil.exe 命令提示符處，鍵入 LDAP policies
b) 在 LDAP 策略命令提示符處，鍵入 connections
c) 在服務器連接命令提示符處，鍵入 connect to server <服務器的 DNS 名稱>，連接到當前使用的服務器。
d) 在服務器連接命令提示符處，鍵入 q
e) 在 LDAP 策略命令提示符處，鍵入 Show Values，查看當前MaxPageSize 值
f) 在 Ntdsutil.exe 命令提示符處，鍵入 LDAP policies
g) 在 LDAP 策略命令提示符處，鍵入 Set MaxPageSize to 1000
h) 可以使用 Show Values 命令來驗證更改。
i) 要保存更改，請使用 Commit Changes。
2. 創建多個視圖
a) 可以根據OU或者部門屬性等，為每個部門創建一個視圖，這樣返回的結果就不會達到LDAPAdminLimits 屬性的 MaxPageSize 值限制；
二、 與AD信息結合查詢LYNC數據
1、以下的實例，將查詢視圖[LcsCDR].[dbo].[RegistrationView]、[LcsCDR].[dbo].[LyncUsers_ZOH_04]來獲取用戶注冊信息，視圖[LcsCDR].[dbo].[RegistrationView]為CDR默認的視圖存儲有關用戶注冊的信息；[LcsCDR].[dbo].[LyncUsers_ZOH_04]視圖是筆者創建的AD信息視圖；
-- 查詢用戶注冊信息
SELECT TOP 1000
[UserUri] AS 注冊用戶URL
,Displayname AS 注冊用戶顯示名稱
,[RegisterTime] AS 注冊時間
,[DeRegisterTime] AS 取消注冊時間
,[ClientVersion] AS 客戶端版本
,[IpAddress] AS IP地址
,[Registrar] AS 注冊器的FQDN
,[Pool] AS 池
,[IsInternal] AS 是否內部登陸
FROM [LcsCDR].[dbo].[RegistrationView]
INNER JOIN [LcsCDR].[dbo].[LyncUsers_ZOH_04]
ON 'sip:'+ [LcsCDR].[dbo].[RegistrationView].UserUri =[LcsCDR].[dbo].[LyncUsers_ZOH_04].SipAddress
2、下面的實例將結合多個表和視圖查詢LYNC用戶撥打電話的記錄:
--查詢LYNC用戶撥打電話記錄
SELECT TOP (100) PERCENT SessionDetails.FromUri, SessionDetails.ToUri, SessionDetails.InviteTime, SessionDetails.ResponseTime, SessionDetails.EndTime,
LcsCDR.dbo.Gateways.Gateway AS 網關, [LcsCDR].[dbo].[LyncUsers_ZOH_04].Displayname AS 顯示名稱, [LcsCDR].[dbo].[LyncUsers_ZOH_04].Department AS 部門, DATEDIFF(ss,
SessionDetails.ResponseTime, SessionDetails.EndTime) AS 時間_秒
FROM LcsCDR.dbo.SessionDetailsView AS SessionDetails INNER JOIN
LcsCDR.dbo.VoipDetails ON SessionDetails.SessionIdTime = LcsCDR.dbo.VoipDetails.SessionIdTime INNER JOIN
LcsCDR.dbo.Gateways ON LcsCDR.dbo.VoipDetails.ToGatewayId = LcsCDR.dbo.Gateways.GatewayId RIGHT OUTER JOIN
[LcsCDR].[dbo].[LyncUsers_ZOH_04] ON 'sip:' + SessionDetails.FromUri = [LcsCDR].[dbo].[LyncUsers_ZOH_04].SipAddress
WHERE (SessionDetails.MediaTypes = 16) AND (SessionDetails.FromUriType = N'UserUri') AND (SessionDetails.ToUriType = N'PhoneUri') AND
(SessionDetails.ResponseCode = 200)
ORDER BY SessionDetails.InviteTime
