Found this post [blogs.technet.com] for configuring additional custom reports / SQL queries using the OCS Archiving CDR Reporter Tool (look for the heading ‘Some useful SQL queries’). However, it may be specific to OCS 2007 (not R2). The format of the queries in the XML file require some modification to work in R2 (for me anyway). When the queries are added to the Archiving CDR Reporter Tool’s config file (ArchivingCdrReporter.xml located in the ..\ResKit\ArchivingCdrReporter folder), they gave a “No database alias” error as follows when the tool was next run:

First thing that needs to happen is the queries need to have a database alias specified since their are two logging databases, CDR and Archiving and this tells the tool which database the query applies to. At the end of each query, between the </Value> and </Query> tags, need to add:

<Description>Archiving</Description>

These queries use the LcsLog, i.e. Archiving database. After that was done for each query, you may then got a “NullReferenceException” error:

To fix that, a tag needs to be added between the </Value> and <Database> tags for each query containing CDATA information such as:

<Description>
<![CDATA[ Query Description :   This query returns ... ]]>
</Description>

Once that is done, the tool should open and you should see the new queries available under the ‘More reports’ section in the tree.

Also, you can edit the queries that specify a User URI to make the address a variable so that it can be modified at runtime rather than in the XML file. For example: the “All IMs Sent by User” query can look like this in the XML file (note use of User1Filter(Users.UserId) at the end of the query):

<Query>
      <Name>All IMs Sent by User</Name>
      <Value>Select * from Messages, Users where Users.UserId = Messages.FromId and User1Filter(Users.UserId)</Value>
      <Description>
<![CDATA[
Query Description :
  The query returns ...
]]>
      </Description>
      <Database>Archiving</Database>
    </Query>

That will allow you to enter a value in the tool’s ‘User1 (SIP URI)’ field, much more convenient than setting a value in the XML file and re-running the tool each time.

Popularity: 51% [?]

How to find out the SIP URIs of users connected on one particular OCS Front End server – Ram Ojha’s Blog – Site Home – TechNet Blogs.

To stop the query returning duplicates, just change it to ‘select distinct a.UserAtHost …’

Also, can extend the query slightly to provide a bit more info such as:

Declare @FrontEndServerID int
Set @FrontEndServerID=2

Select Fqdn "FE-Server-Name" from rtcdyn.dbo.FrontEnd where FrontEndId=@FrontEndServerID

Select count(distinct a.UserAtHost) "Active-Users-Count" from rtc.dbo.Resource a,
rtcdyn.dbo.DeliveryContext b where a.ResourceId=b.SubscriberId and b.FrontEndId=@FrontEndServerID

Select distinct a.UserAtHost "Active-Users" from rtc.dbo.Resource a,
rtcdyn.dbo.DeliveryContext b where a.ResourceId=b.SubscriberId and b.FrontEndId=@FrontEndServerID order by a.UserAtHost asc

Popularity: 8% [?]