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

Last week we began looking a way to graphically represent the call quality reports gathered by Skype for Business.

These surveys pop up periodically after audio / video calls, and give the end users a way to give feedback to the network administrator about the quality of their calls.

To fully make use of this information we need to be able to query it in a useful manor.

Today we are going to look at how to gather the issues encountered here and report on the most prevalent.

Last week you will remember we reported on the top 20 users with the lowest overall rating.





If you remember the giant query from last week, this weeks will be a nice easy change of pace.


 mycom.CommandText = "select  TokenDescription, count(*) as Cnt from " + tname +" where TokenValue=1 group by TokenDescription order by count(*) desc";

Now let's see how I wrap that into my procedure that produces the yellow bar graphs at the bottom left part of this screen.

private void getRatingIssue()
    {
        SqlConnection cn = new SqlConnection(QOEString);

        string name = "";
        double val = 0;

        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  TokenDescription, count(*) as Cnt from " + tname +" where TokenValue=1 group by TokenDescription order by count(*) desc";

            SqlDataReader tmpreader = default(SqlDataReader);
            tmpreader = mycom.ExecuteReader();
            while (tmpreader.Read())
            {
                try
                {
                    val = Convert.ToDouble(tmpreader.GetInt32(1));
                    name = tmpreader.GetString(0).ToString();


                    _GraphIssue.Add(new GraphClass(name, val, false));
                }
                catch { }

            }


            cn.Close();
        }
        catch (Exception ex1)
        {
            Response.Write(ex1.Message);
        }
        finally
        {
            if (cn != null)
                cn.Dispose();

        }


    }

The GraphClass is a simple class I bind to my telerik RadHTMLGraph control.

public class GraphClass
    {
        public GraphClass(string name, double val, bool isExploded)
        {
            _name = name;
            _val = val;
            _isExploded = isExploded;
        }
        private string _name;
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }
        private double _val;
        public double Val
        {
            get { return _val; }
            set { _val = value; }
        }
        private bool _isExploded;
        public bool IsExploded
        {
            get { return _isExploded; }
            set { _isExploded = value; }
        }
    }

Then just a simple data bind.

RadHtmlChart4.DataSource = _GraphIssue;
RadHtmlChart4.PlotArea.XAxis.LabelsAppearance.RotationAngle = 0;
RadHtmlChart4.DataBind();

That's it, now we can easily see a bar graph of the reported call quality issues.

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