In my previous post, "Sophisticated Skype: Detailed Usage and Adoption (Part 1)," I kicked off this new series by defining usage versus adoption and detailing how the Skype for Business monitoring service captures meta data related to every communication session in two databases: Lcs_cdr and QoE. I then explored queries that focused on adoption within a specific business unit.
As a reminder, I created a new UserGroups SQL table that maintains a list of SIP URIs (email addresses) for all the users I want to report on:
In this second part of the article, I'll continue to explore adoption metrics and then shift to examine usage trends related to our focus group.
Top Adopters
Understanding which users in a group are the top adopters of a new service can be helpful, as you can often cultivate these top adopters to champion the benefits of the new service.
Here's how we can identify the top Skype for Business conference participants in our target group:
Secret sauce (highlighted phrases): I exclude specific client types because they represent automated conferencing services and not actual users. COLLATE Latin1_General_BIN forces both databases to use the same collation sequence -- a fancy way to say we want both databases to sort uppercase, lowercase, and accented characters in the same way. Without this clause, your SQL statement may return an error.
The output from the above query is a list of users. I choose to format this using Excel into a report similar to...
Perhaps more interesting than top conference participants is the list of top conference organizers. Here's how we generate that list:
Secret sauce (highlighted phrases): As per above, I exclude specific client types because they represent automated conferencing services and not actual users. I then CONVERT() the duration of the specific conference to hour:minutes:seconds format. COLLATE Latin1_General_BIN forces both databases to use the same collation sequence; we want both databases to sort uppercase, lowercase, and accented characters in the same way. Without the COLLATE clause, the SQL statement may return an error.
Again, the output from this query is a list of users that I format using Excel:
Based on this data, we see most users in our target group have not organized a Skype conference. Does this indicate a need for further training or does this meet our expectations? Working with administrative assistants is almost always key to Skype for Business usage and adoption. Winning over executive admins is often key to influencing the leadership's perception of Skype.
We can identify the top audio or video callers in our focus group, with the following query:
Secret sauce (highlighted phrases): MediaType 16 is an audio call, 32 is a video call, and 48 (16 logical or 32) is an audio and video call. I then CONVERT() the duration of the specific conference to hour:minutes:seconds format. COLLATE Latin1_General_BIN forces both databases to use the same collation sequence; we want both databases to sort uppercase, lowercase, and accented characters in the same way. Without the COLLATE clause, the SQL statement may return an error.
This query generates a list of users who had the most number of minutes on a call and tallies the number of calls for each user in our focus group. For any of the "top" queries, you can add in the top x (see green highlight above) clause as part of the main SELECT statement in order to limit the list.
Usage Trends
So far we have analyzed point-in-time adoption metrics. However, adoption and usage trends over time is often a better indicator of whether we're moving in the right direction.
We can capture adoption trends by running the aforementioned queries on a weekly or monthly basis.
For usage trends, we can count the number of peer-to-peer sessions per week for our target group using the SessionDetails table, as follows:
Secret sauce (highlighted phrases): Most people believe SQL Server only locks database rows when rows are being INSERTed or UPDATEd. However, in order to ensure read consistency, SELECT statements can also try to lock database rows and indexes. Normally this is not a problem; however, in an active Skype environment where new rows are being inserted frequently without the WITH (NOLOCK) phrase, as highlighted in yellow above, the query may generate an error. MediaTypes is a bit- level field as per the comments; hence we use the logical and &.COLLATE Latin1_General_BIN to force both databases to use the same collation sequence.
The query above generates a weekly usage summary for IM sessions (Mediatypes 1), something like this:
I then paste these results into Excel and create a line graph:
I also add a trend line (the dotted line in the Excel graph above) to show if overall usage for the specific modality is increasing, decreasing, or remaining constant.
Quantifying Opinions
Before, during, and after a Skype for Business deployment you'll hear many opinions related to what should be done, what was done, and how successful the project was.
Customized queries exploring detailed usage and adoption across multiple groups in your organization can help move from debatable opinion to quantifiable fact. Quantified results allow you to identify real problems, take correct action, and measure the effectiveness of any of the undertaken actions.
If you have deployed Skye for Business, you should be producing detailed usage and adoption metrics.
I spend my time helping organizations and IT Teams succeed implementing communication and collaboration systems, most often Skype for Business, and I am committed to helping you succeed. Quantified metrics through advanced reporting can be a valuable tool to help track adoption and usage. If you have specific questions please comment below, send me a tweet @kkieller, or message me on LinkedIn.
