Analyze This

At Microsoft Ignite we announced our content pack for Power BI. It is very exciting for me to be able to let everyone know that as of today, it is available in preview to be used by any SQL Sentry user using Version 8.4. Now that our users are able to take full advantage of this great new service, I thought it would be a good time to provide some information about how it works and what you can do with Power BI and our content pack.

Prerequisite – Enabling Custom Condition Events and Actions. To get the most of our cloud offerings for understanding and analyzing the data we provide, you should make sure you have the right Custom Conditions installed and enabled. See Greg’s introduction and tutorial for a good starter. If you’re reading this, I’m assuming you already have done this by now.

Next, activate cloud sync in the client software. There are other sources to help you with this, so if you haven’t done it already, you can read about it in my previous security blog post or our online help.

Now that your data is in the cloud, the real fun begins! To use Power BI you must have a Microsoft account. This is generally an Office 365 account. If you’re not sure if you have a valid Microsoft account, you can go to Power BI and sign up for one. Keep in mind that it will be less confusing if you use the same email address to configure cloud sync and to sign up for Power BI. Power BI and SQL Sentry Cloud are both oAuth 2.0 compatible so once you sign in with one, you can be authenticated for both. If you already have a Cloud account before signing up for Power BI, you can link your Cloud account to your Microsoft account from the Cloud settings page.

Next log in to cloud.sentryone.com to get some information you will need to enable the SQL Sentry content pack in Power BI. Remember the credentials you created above, you will need those to authenticate through Power BI. You will also need to know the Database ID for the SQL Sentry environment you want to use to create your dashboard and reports. In the latest publish of our cloud application we have added this ID in 2 locations:

You can find the Database ID on the main overview grid. If you sync more than one environment or have multiple environments shared with you in the cloud application, you can create a dashboard for each of them. Power BI also allows you to create a combined dashboard by pinning reports from any of your data sources. The Database ID column contains the information you need to provide when enabling the content pack.

This ID is also available when you go to the Server Details area of the application and then to the Settings page.

Now it’s time to head on over to Power BI, login and navigate to the Get Data page. Once there, scroll down to the SQL Sentry option in the list of data sources, and click on the icon.

On the page that comes up you will see some instructions and a button to “Connect” to the SQL Sentry Content Pack.
Follow the steps of the wizard and provide your user credentials for cloud.sentryone.com and your Database ID which you found on cloud.sentryone.com.

After you have successfully completed the wizard, there will be a message in the upper right telling you your dashboard is being created. After your data loads you will be able to go back to the main Power BI screen and see a new SQL Sentry Dashboard listed in your account. In addition to that, you will see SQL Sentry Reports and a SQL Sentry Dataset.

Now that your dashboard and associated reports and dataset have been created we can take a look at the information that is available to you. The beauty of the Power BI content pack is you immediately have usable, actionable information that you can use to determine the state of your monitored environment. This information is not only useful to technical staff, but also to IT or corporate management. With a single glance, you can see the overall health of your systems. Between this dashboard and our own Server Health Status you can easily pinpoint where you need to focus resources to attack the problem areas in your environment, and also know immediately how you are doing on SLAs and other important management and provider metrics.

Viewing the dashboard you can quickly get the following information:

1) Server Health – this metric is based upon the number and severity of conditions that are being triggered in your SQL Sentry environment. Relative scores are calculated and your servers are divided into 5 alert levels, 5 being the most severe. Within the alert level, servers are again ranked from highest overall score to lowest overall score. As you view the treemap, your servers that have the highest number and overall severity appear to the upper left in the Level 5 grouping.

2) Server Availability – a 100% stacked bar chart which illustrates the relative amount of downtime to uptime percentage by server for all servers which have had less than the standard SLA amount of 99.999% uptime.

3) CPU Events – similar to the Memory Events chart, this scatter chart displays information for each server as to how the number of CPUs correlates to the number of triggered CPU related events. Again the bigger the circle the higher the Alert Level for the server.

4) Memory Events – the scatter chart shows a circle for each server with the number of Memory related Events that have occurred on the X-axis compared to the amount of available memory on the Y-axis. The size of the circle represents overall Alert Level for that server. From this chart it is easy to see exactly which servers are suffering from events that may be reduced by increasing available memory.

5) Events by Severity – represents another view of the data as pictured in the Server Health treemap chart. This chart provides additional detail to let you know exactly how many events are occurring by server and their relative severity. Servers with many low severity events may not need attention as quickly as a server with a fewer number of critical events.

6) Total Events – This chart shows the number of total events that have occurred in your environment by date for the last 16 days (based on data retention).

7) Metric Cards – The column in the middle of the dashboard represents easy to understand values for many of the related charts which allow you to quickly get an idea in a single glance about your environment and what is being monitored as well as current conditions.

Clicking any of the Dashboard tiles will take you to an associated detail report. I won’t go into all of the details, as I think most of them become relatively easy to understand after you have digested the information that is on the main dashboard.