Getting the Skype for business status without any api

If you use an Skype for Business onprem server and you want to get the status of all users easily use this database script on your \RTCLOCAL table.

SELECT LOWER(UserAtHost) AS UserAtHost, Status=
	CASE
		WHEN Availability BETWEEN 0 AND 2999 THEN 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,
	LastPubTime
FROM rtc.dbo.Resource Resource
	RIGHT JOIN (
		SELECT
			Instance.PublisherId,
			SUBSTRING(Instance.Data, CHARINDEX('<availability>', Instance.Data) + 14, CHARINDEX('</availability>', Instance.Data) - CHARINDEX('<availability>', Instance.Data) - 14) AS Availability,
			Instance.LastPubTime
		FROM (
				SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) AS Data, LastPubTime FROM rtcdyn.dbo.PublishedInstance WHERE ContainerNum = 2 AND CategoryId = 4
				UNION ALL
				SELECT PublisherId, cast(substring(Data, 0, 256) AS varchar(256)) AS Data, LastPubTime FROM rtc.dbo.PublishedStaticInstance WHERE ContainerNum = 2 AND CategoryId = 4
			) AS Instance
		WHERE
			CHARINDEX('aggregateState', Data) > 0
) AS UserAndAvailability ON Resource.ResourceId = PublisherId
/*WHERE UserAtHost = 'somebody@example.com' COLLATE SQL_Latin1_General_CP1_CI_AS*/
ORDER BY UserAtHost, LastPubTime DESC

Written by

Tobias Salzmann