Calls and Meetings Aggregate Table Calculations

The TWA Database contains both the [aggregates].[P2PMinsByUsers] and [aggregates].[ConferenceMinsByUsers] tables. These tables are updated by their respective stored procedure which is exucted once per day by the CallAggregating function. The aggregation is done for the previous day as we should already have all the data for that day. 

The tables are split by their call type, groupCall/Conference and peerToPeer. This is done for both ease of use and performance reasons. Both tables consist of the same properties as follows:

• Id: A unique Identifier for this row.
• _HashUserId: The SHA256 hash of the Caller/Callee Id.
• UserId: The Id of the Caller/Callee. This could be their mobile for a pstn user, an azure id for a teams user or null if the user is Unknown or NoIdentity.
• UserType: The type of user, defined by the Identity objects of the call record. Can either be 0 (Unknown), 1 (Internal), 2 (External), 3 (Pstn) or 4 (NoIdentity).
• Date: The aggregation date.
• TotalSegmentMinutes: The sum of minutes in any modality a user has sent/received in calls they’ve been in.
• AudioMinutes: The sum of audio minutes a user has sent/received in calls they’ve been in.
• VideoMinutes: The sum of video minutes a user has sent/received in calls they’ve been in.
• DesktopShareMinutes: The sum of desktop share minutes a user has sent/received in calls they’ve been in.
• TotalConferenceCount / TotalCallCount: The sum of streams in any modality a user has in calls they’ve been in.
• AudioConferenceCount / AudioCallCount: The sum of audio streams a user has in calls they’ve been in.
• VideoConferenceCount / VideoCallCount: The sum of video streams a user has in calls they’ve been in.
• DesktopShareConferenceCount / DesktopShareCallCount: The sum of desktop share streams a user has in calls they’ve been in.

For users that don’t have a UserId/_HashUserId, which would normally be users of UserType 0 (Unknown) or 4 (NoIdentity). Instead of having a row each they are squashed together into a single row for that aggregation date and UserType. You would typically only see these rows in the aggregated Conference table as the Callee of each segment is almost always going to be a Microsoft service endpoint which doesn’t have an Identity.