Get Skype for Business Status via SQL

status.sql

	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

Thanks!!
I've been trying to use the DiagShowPublisherPresence SP and the PublishedCategorysInstanceView to get a list of all users and their Presence.
This script did it perfectly with everything I needed. Thanks!!

Hello,

Could you tell me how to group by states?

Thank you