OCS 2007 R2 Monitoring Server Custom Query

A common question I always get is around custom queries for the Monitoring Server. The most requested is for used voice minutes by users. I thought I would post the query below in hope of saving it some where handy and giving others the chance to try it out. The query should produce a report that looks like this: Callee,Caller,StartTime,EndTime,Length

select

    convert (varchar(10), a.SessionIdTime - 0.125,101)  as 'Start Date',

    convert (varchar(10), a.SessionIdTime - 0.125,108)  as 'Start Time',

    convert (varchar(10), d.SessionEndTime - 0.125,101)  as 'End Date',

    convert (varchar(10), d.SessionEndTime - 0.125,108)  as 'End Time',

   

    convert(varchar(10),d.SessionEndTime -      a.SessionIdTime,108) as 'Time',

       b.PhoneUri as 'From N.',

      f.UserUri  as 'From Uri',

       c.PhoneUri as 'To N.',

      g.UserUri  as 'To Uri'

from

   VoipDetails a inner join

      SessionDetails d on d.SessionIdTime = a.SessionIdTime inner join

      Phones b on a.FromNumberId = b.PhoneId inner join

      Phones c on a.ConnectedNumberId = c.PhoneId left join

    Users f on d.User1Id = f.UserId left join

     Users g on d.User2Id = g.UserId

                                  

WHERE

   d.SessionEndTime - a.SessionIdTime is not null

      ORDER BY

      a.SessionIdTime DESC

 

Let me know if you have any other custom queries you would like to share. I am only to happy to post or talk about them. Thanks to Mike D for the info above.

VoIPNorm