Skype for Business SQL Reporting - Part I - Call Qualtiy Ratings

In my Bridge Web Dashboard application, I set out to create a set of simple reports based on the Skype for Business SQL data logging that is part of the server configuration.  In today's blog post, we'll examine the code used to report on the call quality survey information users submit after certain Lync/Skype4b calls.

Our goal with this post is show how we take this information to display something like this to the server administrators with the goal of improving the call quality for all users.

So where do we begin?  Let's start with where to find the data we want to query.  See this post to see how to enable the feature on your server.  So once we have the data collection happening it's time to start reporting.

*Note:  This is going to see a little overwhelming to start but bear with me.  The first step is take the data we want to put it into a temporary table we can report on, and then drop.  That will save us doing to many large cpu consuming sql queries on our CDR DB instance.  For that we can use a sql query like this.

    string query= "select * into #Temp from (SELECT s.ConferenceDateTime ,Caller.URI as Caller ,CallerCqf.FeedbackText ,CallerCqf.Rating ,CallerCqfTokenDef.TokenDescription ,CallerCqfToken.TokenValue FROM [Session] s WITH (NOLOCK) INNER JOIN [MediaLine] AS m WITH (NOLOCK) ON m.ConferenceDateTime = s.ConferenceDateTime AND m.SessionSeq = s.SessionSeq INNER JOIN [AudioStream] AS a WITH (NOLOCK) ON  a.MediaLineLabel = m.MediaLineLabel and a.ConferenceDateTime = m.ConferenceDateTime and a.SessionSeq = m.SessionSeq and a.SenderIsCallerPAI = 1 INNER JOIN [CallQualityFeedback] AS CallerCqf WITH (NOLOCK) ON CallerCqf.ConferenceDateTime  = s.ConferenceDateTime and CallerCqf.SessionSeq = s.SessionSeq INNER JOIN [CallQualityFeedbackToken] AS CallerCqfToken WITH (NOLOCK) ON CallerCqfToken.ConferenceDateTime  = s.ConferenceDateTime and CallerCqfToken.SessionSeq = s.SessionSeq and CallerCqfToken.FromURI = CallerCqf.FromURI INNER JOIN [CallQualityFeedbackTokenDef] AS CallerCqfTokenDef WITH (NOLOCK) ON CallerCqfTokenDef.TokenId = CallerCqfToken.TokenId and CallerCqfToken.TokenId < 20 INNER JOIN [User] AS Caller WITH (NOLOCK) ON Caller.UserKey = CallerCqf.FromURI  ) data";

Told you that was going to look a little crazy :-)  If you are confused at all by it I recommend you run in the SQL management studio and study the output a little before moving on.

So let's examine how we get the users have the most trouble, you can see those in a datagrid at the bottom right corner of the larger screenshot.  Here is the code.

private void getWorstGrid()

    {

        SqlConnection cn = new SqlConnection(QOEString);

        List<GridClass> _grid = new List<GridClass>();

        try

        {

            cn.Open();

            SqlCommand mycom = new SqlCommand();

            mycom.Connection = cn;

            string t = "";

            t = query;

            t = t.Replace("#Temp", tname);

            //Response.Write(t + "\r\n");

            mycom.CommandText = t;

            mycom.ExecuteNonQuery();

            mycom.CommandText = "select top 20 Caller, avg(Cast(Rating as decimal)) as Rate from " + tname +" group by Caller order by avg(Cast(Rating as decimal))";

            SqlDataReader tmpreader = default(SqlDataReader);

            tmpreader = mycom.ExecuteReader();

            while (tmpreader.Read())

            {

                try

                {

                    _grid.Add(new GridClass(getUserName(tmpreader.GetString(0).ToString()), Convert.ToDouble(tmpreader.GetDecimal(1))));

                }

                catch { }

            }

            RadGrid1.DataSource = _grid;

            RadGrid1.DataBind();

            cn.Close();

        }

        catch (Exception ex1)

        {

            Response.Write(ex1.Message);

        }

        finally

        {

            if (cn != null)

                cn.Dispose();

        }

    }

You can see we pull and display the 20 users with the worst overall rating, that gives us a place to start from a troubleshooting perspective.  We can examine their network, computer and other infrastructure to see if any of those improvements can help the users experience.  Stay tuned next week and we'll dive in deeper.

Doug Routledge, C# Lync, Skype for Business, SQL, Exchange, UC Developer  BridgeOC
Twitter - @droutledge @ndbridge