Office Communications Server 2007 R2 Tool: Adoption Reporting

From DrRez blog:

The Adoption Reporting Tool enables administrators to more easily measure both usage and adoption of Microsoft Office Communications Server 2007 R2 in their enterprise. 

More info about it here.

Useful OCS SQL queries (Part II)

Another day, another query. This time, a more important one that can be used for accounting.

This query will show all the completed calls to the PSTN, showing call time, who dialed and where to. Advanced reporting could be built on top of this query to find out how much this calls cost.

Use LcsCDR
SELECT    CONVERT(varchar(10), VoipDetails.SessionIdTime, 103) [Start Date],
        CONVERT(varchar(10), VoipDetails.SessionIdTime, 108) [Start Time],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 103) [End Date],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 108) [End Time],
        CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
        REPLACE(REPLACE(Phones.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [From Number],
        Users1.UserUri [From Sip],

        REPLACE(REPLACE(Phones1.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [Number Dialed],
        Users3.UserUri [On Behalf Of],
        Users4.UserUri [Referred By]

FROM         VoipDetails LEFT OUTER JOIN
                      SessionDetails ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND
                      VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq LEFT OUTER JOIN
                      Phones ON VoipDetails.FromNumberId = Phones.PhoneId LEFT OUTER JOIN
                      Phones Phones1 ON VoipDetails.ConnectedNumberId = Phones1.PhoneId LEFT OUTER JOIN
                      Users Users1 ON SessionDetails.User1Id = Users1.UserId LEFT OUTER JOIN
                      Users Users2 ON SessionDetails.User2Id = Users2.UserId
                      LEFT OUTER JOIN Users Users3 ON SessionDetails.OnBehalfOfId = Users3.UserId
                    LEFT OUTER JOIN Users Users4 ON SessionDetails.ReferredById = Users4.UserId

where SessionDetails.ResponseCode = 200 and
    Users2.UserUri is null and
    SessionDetails.SessionEndTime is not null
order by VoipDetails.SessionIdTime

You need to change the red text to match your profile(s) so it 'cleans' the number.

Outcome:

Useful OCS SQL queries (Part I)

After you get a fully functional OCS environment you want to check some statistics on it's use.
Since all of the info is on the DB it should be easy to get it and even create some reporting (Reporting Services) on top of it.

Next i'll show you some queries i find useful to have at hand.

Client versions

You can check a overall of client versions on your environment using OCS MMC:

But, you might want to get a little more details, as in, who's using what version. You can do that by running this query on the backend.

SELECT     rtc.dbo.Resource.UserAtHost, CAST(rtcdyn.dbo.Endpoint.ClientApp AS varchar(128)) AS ClientApp
FROM         rtcdyn.dbo.Endpoint INNER JOIN
                      rtc.dbo.Resource ON rtcdyn.dbo.Endpoint.OwnerId = rtc.dbo.Resource.ResourceId
order by UserAtHost

This will return a list showing what versions are being used and who's using them. It will even show if the same user is using different devices (as in one user on CWA, MOC, COMO, etc at the same time).

If you need the same query that MMC uses, this is it:

SELECT CAST(ClientApp AS varchar(128)) AS ClientApp, COUNT(*) AS Clients
FROM rtcdyn.dbo.Endpoint
Group by ClientApp

It's not something you will use on a daily base but it's useful to have.