[Lync/SFB]presence 정보 쿼리

use rtc

declare @sipQuery nvarchar(250)
set @sipQuery = 'sipusername%'

select Publisher, MIN(Status) as Status
from
(select Publisher, Status=
CASE
when Availability BETWEEN 0 AND 2999 then 'Not defined:'+Availability
when Availability BETWEEN 3000 AND 4499 then 'Available'
when Availability BETWEEN 4500 and 5999 then 'Available - Idle'
when Availability BETWEEN 6000 and 7499 then 'Busy'
when Availability BETWEEN 7500 and 8999 then 'Busy - Idle'
when Availability BETWEEN 9000 and 11999 then 'Do not Disturb'
when Availability BETWEEN 12000 and 14999 then 'Be right back'
when Availability BETWEEN 15000 and 17999 then 'Away'
when Availability > 18000 then 'Offline'
end
from
(
select Publisher,
substring(
substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50),0,
patIndex('%<availability>%',substring(PublicationDocument,patIndex('%<availability>%',PublicationDocument)+14,50))
) Availability
from
(select UserAtHost Publisher,ContainerNum,CONVERT(varchar(4000),convert(varbinary(4000),Data)) PublicationDocument
from rtcdyn.dbo.PublishedInstance tblPublishedInstance,
rtc.dbo.Resource tblResource
where tblPublishedInstance.PublisherId = tblResource.ResourceId) as PublishedDocuments
where LEN(replace(PublicationDocument,'aggregateState','')) < LEN(PublicationDocument)
and ContainerNum = 2
) as PublisherAndAvailability
where Publisher like @sipQuery
union
select UserAtHost Publisher, 'Offline-Not Registered Here' Status
from rtc.dbo.Resource
where UserAtHost like @sipQuery) as PublisherAndStatus
group by Publisher

참고

http://mikestacy.typepad.com/mike-stacys-blog/sql/

Container 테이블 설명

https://msdn.microsoft.com/en-us/library/bb879521.aspx

https://msdn.microsoft.com/en-us/library/office/dn454664.aspx

Access Control List Containers

Office Communications Server creates these reserved containers to provide access control functionality.

Container ID	Description
100	Public, Federated subscribers
200	Workplace subscribers
300	Team member subscribers
400	Personal subscribers
32000	Blocked subscribers

Special Containers

Office Communications Server defines special containers for receiving published data.

Container ID	Description
0	A container with an exclusive access scope.
1	Self-presence category data, which includes userProperties, alerts, rccOptions, userInformation, and calendarData.
2	The server aggregates user, machine, phone and calendar states published to this container. The states are published to container 100, 200, or 400.
3	The server aggregates presence states in this container and publishes the aggregated computer and user states to container 300.