Calls and Meetings Database Schema

Call

A call represents an online conversation between 2 people directly (P2P) or 1 or more person in a group call. The call lasts until there are no more participants in the call. If users join a meeting invite, then all leave, then re-join, this will represent two unique calls.
Session Minutes below are not to be confused with call duration, see the description of session/segment. For a simple explanation however, it is the time users have spent in the call collectively, so a 5 minute call with 3 people, 2 of which were in for the duration, and 1 that joined 2 minutes before the end would result in 5 + 5 + 2 = 12.

Schema

• CallId = Id of the call direct from graph
• _HashOrganiserUserId = Either the User Id or the Phone Id (number e164) of the organiser, hashed.
• OrganiserUserId = Either the User Id or the Phone Id (number e164) of the organiser
• OrganiserUserType = The type of organiser (internal, external or pstn, calculated from tenant-id or phone number)
• Version = Version of the call direct from graph
• Type = Call Type direct from graph, references lookup table lookups.callType
• StartDateTime = Start Date Time of the call direct from graph
• EndDateTime = End Date Time of the call direct from graph
• AudioSessionMinutes = The sum of minutes of the durations (endDateTime – startDateTime) of the sessions in the call that have an audio modality. The sum is rounded up to the nearest whole minute.
• VideoSessionMinutes = The sum of minutes of the durations (endDateTime – startDateTime) of the sessions in the call that have a video modality. The sum is rounded up to the nearest whole minute.
• AppShareSessionMinutes = The sum of minutes of the durations (endDateTime – startDateTime) of the sessions in the call that have an app share modality. The sum is rounded up to the nearest whole minute.
• TotalSessionMinutes = The sum of minutes of the durations (endDateTime – startDateTime) of the sessions in the call, regardless of modality. The sum is rounded up to the nearest whole minute.
• VoipParticipants = The count of Participants with User Ids in the call
• PstnParticipants = The count of Participants with Phone Ids (number e164) in the call
• TotalParticipants = The count of all Participants in the call

Session/Segment

A session and a segment represent identical concepts, although come back from graph as two seperate, identical entities. In TWA we treat them as the same thing and just use segment.
A segment represents a single user join.
A P2P call can only have 2 sessions, 1 for the caller and 1 for the callee.
A Group call can have more 1 or more segments.

• The first user that joins (and starts) the group call will be the first segment. That same user could then leave the call and the call would end and only have one segment.
• Someone could then join what they think is the same call, eg, a meeting invite or group conversation, but this would result in a new call record being generated. A call ends as soon as the last person leaves, and cant be reused.
• 2 or more users could join, ensure they are both in the call at the same time, then leave and the call would have as many segments as users.
• If a group call already has someone in it, another user could join, then leave, then join, then leave (providing the call doesn’t ever have fewer than 1 participant in it at anyone time). This would result in 2 segments for that user for that call, one with the start and end time of the first join, and one with the start and end time of the second join.
• 2 users could join a call, 1 leave, then a different user join. This would result in 3 segments, representing each unique user in the call.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• Version = Version of the call direct from graph
• StartDateTime = Start Date Time of the segment direct from graph
• EndDateTime = End Date Time of the segment direct from graph
• _HashCallerId = Either the User Id or the Phone Id (number e164) of the caller, hashed.
• CallerId = Either the User Id or the Phone Id (number e164) of the caller
• CallerUserType = The type of caller (internal, external or pstn, calculated from tenant-id or phone number)
• _HashCalleeId = Either the User Id or the Phone Id (number e164) of the callee, hashed.
• CalleeId = Either the User Id or the Phone Id (number e164) of the callee
• CalleeUserType = The type of callee (internal, external or pstn, calculated from tenant-id or phone number)

Media Stream

A stream represents a flow of data in a direction for a media type for a segment between two endpoints in a call.
A stream can flow from callerToCallee and calleeToCaller. Basically, an upstream and a downstream.

• In a P2P call, the streams are direct between the 2 users (endpoints).
  • The caller could have audio and video, but the callee could just have audio. This would result in 2 audio streams and 1 video stream. There isn’t a 2nd video stream because the callee isn’t upstreaming their video. They do however downstream the callers video upstream. The callee’s downstream is the same stream as the callers upstream. (only in a P2P call)
• In a Group call, the streams are mediated via a conference server.
  • Each user in the group call establishes an upstream if they have that modality enabled to send media to the group (user endpoint -> service endpoint), as well as a downstream to receive media from the other participants in the call (service endpoint -> user endpoint).
  • The upstream of one user does not equal the downstream of another user. The stream is mediated by a central server. So if 5 people are on audio, each user will have 1 audio upstream (their mic feed) and 1 audio downstream (the other users audio streams merged). This is different to a P2P or star network. Video differs slightly, in that the MCU can decide to mix and send up to 10 video streams to each user for quality reasons.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• MediaLabel = Label of the media direct from graph
• StreamDirection = Direction of the stream direct from graph
• Version = Version of the call direct from graph
• StreamId = Id of the stream direct from graph
• StreamStart = Start Date Time of the stream direct from graph
• StreamEnd = End Date Time of the stream direct from graph
• AverageAudioDegradation = Average Audio Degradation of the stream direct from graph
• AverageAudioNetworkJitter = Average Audio Network Jitter of the stream direct from graph
• AverageBandwidthEstimate = Average Bandwidth Estimate of the stream direct from graph
• AverageJitter = Average Jitter of the stream direct from graph
• AveragePacketLossRate = Average Packet Loss Rate of the stream direct from graph
• AverageRatioOfConcealedSamples = Average Ratio Of Concealed Samples of the stream direct from graph
• AverageReceivedFrameRate = Average Received Frame Rate of the stream direct from graph
• AverageRoundTripTime = Average RoundTrip Time of the stream direct from graph
• AverageVideoFrameLossPercentage = Average Video Frame Loss Percentage of the stream direct from graph
• AverageVideoFrameRate = Average Video Frame Rate of the stream direct from graph
• AverageVideoPacketLossRate = Average Video Packet Loss Rate of the stream direct from graph
• LowFrameRateRatio = Low Frame Rate Ratio of the stream direct from graph
• LowVideoProcessingCapabilityRatio = Low Video Processing Capability Ratio of the stream direct from graph
• MaxAudioNetworkJitter = Max Audio Network Jitter of the stream direct from graph
• MaxJitter = Max Jitter of the stream direct from graph
• MaxPacketLossRate = Max Packet Loss Rate of the stream direct from graph
• MaxRatioOfConcealedSamples = Max Ratio Of Concealed Samples of the stream direct from graph
• MaxRoundTripTime = Max Round Trip Time of the stream direct from graph
• PacketUtilization = Packet Utilization of the stream direct from graph
• PostForwardErrorCorrectionPacketLossRate = Post Forward Error Correction Packet Loss Rate of the stream direct from graph
• WasMediaBypassed = Was Media Bypassed of the stream direct from graph

Media Network

A network represents information about an endpoints network, it is related to the sender of the stream (for a media type of a segment of a call).

In some instances, graph sends us network data without stream data and vice versa. therefore it is not possible to gurantee a 1:1 ratio between network and stream.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• MediaLabel = Label of the media direct from graph
• StreamDirection = Direction of the stream direct from graph
• Version = Version of the call direct from graph
• BandwidthLowEventRatio = Bandwidth Low Event Ratio of the sender’s network of the stream direct from graph
• BasicServiceSetIdentifier = Basic Service Set Identifier of the sender’s network of the stream direct from graph
• ConnectionType = Connection Type of the sender’s network of the stream direct from graph
• DelayEventRatio = Delay Event Ratio of the sender’s network of the stream direct from graph
• DnsSuffix = Dns Suffix of the sender’s network of the stream direct from graph
• IpAddress = Ip Address of the sender’s network of the stream direct from graph
• LinkSpeed = Link Speed of the sender’s network of the stream direct from graph
• MacAddress = Mac Address of the sender’s network of the stream direct from graph
• Port = Port of the sender’s network of the stream direct from graph
• ReceivedQualityEventRatio = Received Quality Event Ratio of the sender’s network of the stream direct from graph
• ReflexiveIPAddress = Reflexive IP Address of the sender’s network of the stream direct from graph
• RelayIPAddress = Relay IP Address of the sender’s network of the stream direct from graph
• RelayPort = Relay Port of the sender’s network of the stream direct from graph
• SentQualityEventRatio = Sent Quality Event Ratio of the sender’s network of the stream direct from graph
• Subnet = Subnet of the sender’s network of the stream direct from graph
• WifiBand = Wifi Band of the sender’s network of the stream direct from graph
• WifiBatteryCharge = Wifi Battery Charge of the sender’s network of the stream direct from graph
• WifiChannel = Wifi Channel of the sender’s network of the stream direct from graph
• WifiMicrosoftDriver = Wifi Microsoft Driver of the sender’s network of the stream direct from graph
• WifiMicrosoftDriverVersion = Wifi Microsoft Driver Version of the sender’s network of the stream direct from graph
• WifiRadioType = Wifi Radio Type of the sender’s network of the stream direct from graph
• WifiSignalStrength = Wifi Signal Strength of the sender’s network of the stream direct from graph
• WifiVendorDriver = Wifi Vendor Driver of the sender’s network of the stream direct from graph
• WifiVendorDriverVersion = Wifi Vendor Driver Version of the sender’s network of the stream direct from graph

Media Device

A device represents information about an endpoints device, it is related to the sender of the stream (for a media type of a segment of a call).

In some instances, graph sends us device data without stream data and vice versa. therefore it is not possible to gurantee a 1:1 ratio between device and stream.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• MediaLabel = Label of the media direct from graph
• StreamDirection = Direction of the stream direct from graph
• Version = Version of the call direct from graph
• CaptureDeviceName = Capture Device Name of the sender’s device of the stream direct from graph
• CaptureDeviceDriver = Capture Device Driver of the sender’s device of the stream direct from graph
• RenderDeviceName = Render Device Name of the sender’s device of the stream direct from graph
• RenderDeviceDriver = Render Device Driver of the sender’s device of the stream direct from graph
• SentSignalLevel = Sent Signal Level of the sender’s device of the stream direct from graph
• ReceivedSignalLevel = Received Signal Level of the sender’s device of the stream direct from graph
• SentNoiseLevel = Sent Noise Level of the sender’s device of the stream direct from graph
• ReceivedNoiseLevel = Received Noise Level of the sender’s device of the stream direct from graph
• InitialSignalLevelRootMeanSquare = Initial Signal Level Root Mean Square of the sender’s device of the stream direct from graph
• CpuInsufficentEventRatio = Cpu Insufficent Event Ratio of the sender’s device of the stream direct from graph
• RenderNotFunctioningEventRatio = Render Not Functioning Event Ratio of the sender’s device of the stream direct from graph
• CaptureNotFunctioningEventRatio = Capture Not Functioning Event Ratio of the sender’s device of the stream direct from graph
• DeviceGlitchEventRatio = Device Glitch Event Ratio of the sender’s device of the stream direct from graph
• LowSpeechToNoiseEventRatio = Low Speech To Noise Event Ratio of the sender’s device of the stream direct from graph
• LowSpeechLevelEventRatio = Low Speech Level Event Ratio of the sender’s device of the stream direct from graph
• DeviceClippingEventRatio = Device Clipping Event Ratio of the sender’s device of the stream direct from graph
• HowlingEventCount = Howling Event Count of the sender’s device of the stream direct from graph
• RenderZeroVolumeEventRatio = Render Zero Volume Event Ratio of the sender’s device of the stream direct from graph
• RenderMuteEventRatio = Render Mute Event Ratio of the sender’s device of the stream direct from graph
• MicGlitchRate = Mic Glitch Rate of the sender’s device of the stream direct from graph
• SpeakerGitchRate = Speaker Gitch Rate of the sender’s device of the stream direct from graph

Feedback

A Users feedback of call quality, found in both the sessions and segments of caller/callee entities. In most cases this feedback object is rather null or does not exists. We omit saving this to the db. So when joining a segment onto the feedback table, if no rows are returned then assume no feedback was given.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• StreamDirection = Direction of the stream direct from graph. While feedback aren’t actually a part of the streams. They follow the same principle where only 2 can exist for the same segment, one CallerToCallee the other CalleeToCaller.
• Version = Version of the call direct from graph.
• Rating = A rating value from 0 – 5, Unknown, Bad to Excellent.
• Text = Any notes given by the user to support their feedback.
• Tokens = An open object type of inconsistent size and field names. This contains more information on what happened during the call using boolean fields. E.g. NoSound, Echo, Noisy, etc. We save this as a json serialized text field which we can get dynamic props out in either sql or powerBi.
• _LastUpdatedDateTime = The date and time the row was added to the database.

FailureInfo

When a call or a portion of a call fails, Microsoft will provide us with details to where and why the call failed. This is done at a per segment level. In most cases this failureInfo object does not exists in the graph response and so we omit saving this to the db. So when joining a segment onto the failureInfo table, if no rows are returned then assume there was no failure.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• Version = Version of the call direct from graph
• Stage = At what stage during the call did it fail. Can either be “CallSetup”, “MidCall”, “Unknown” or “UnknownFutureValue”. In most cases it will be either of the first 2.
• Reason = The reason why the call failed, provided by Microsoft. In most cases this will be “Other”.
• _LastUpdatedDateTime = The date and time the row was added to the database.

Endpoints

Each segment connects two participants aka endpoints. An endpoint can have an AD User identity, a phone indentity, a bot/service/MCU identity etc. Data relating to each endpoint is stored in the [dbo].[Endpoints] table. There are exactly two endpoints per segment, one for the caller and one for the callee.

Schema

• CallId = Id of the call direct from graph
• SegmentId = Id of the segment direct from graph
• Version = Version of the call direct from graph
• IsCallee = True if the endpoint is the segment Callee, false if it’s the Caller
• UserAgentType = A Microsoft value that denotes whether the endpoint used client or service software. This is stored as a number (enum) with a value of either 1 or 2 to represent #microsoft.graph.callRecords.clientUserAgent and #microsoft.graph.callRecords.serviceUserAgent respectively. The [lookup].[UserAgentType] view provides a number-to-name mapping for this field.
• ProductFamily = The family of sotware products used, e.g. Teams or skypeForBusiness.
• Platform = The operating system used by the endpoint, e.g. Windows, macOS or web.
• HeaderValue = A string that shows how the endpoint’s software identified itself.
• ApplicationVersion = The version of the software used by the endpoint.
• Role = Typically used to indicate the type of a service/bot endpoint, e.g. mediationServer or Voicemail.
• _LastUpdatedDateTime = The date and time the row was added to the database.