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
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.