Most out of the box reports are designed to answer a specific question at a given point in time. However, business requirements change frequently and limit the effectiveness of out of the box reporting. These changes require the constant tuning and even recreation of standard reports. By using IT Analytics for Microsoft System Center, another option is creating a drill down report that shows a high level summary of key data that also allows the user to click on the report to view detailed information.
In this post, we will show you how to quickly and easily create your own custom drill down report using IT Analytics and SQL Server Report Builder.
For this use case, our reporting requirement is to list the number of machines that require a software update and the percent complaint. We also want the ability to click on the required computers count to see a list the computers that require that software update.
To make this drill down report possible, we will follow a three step process: create an overview report, a details report, and finally the necessary parameters.
Using the steps outlined in a previous post, we create a report that includes the software update, the count of required computers, and the count of installed computers. Lastly, we create a calculated field showing overall compliance for a bulletin.
Here is a preview of the report.
For the Details Report, we include the bulletin name, computer name and required computer count:
Here is a preview of the report:
To make the drilldown work, we need to add a parameter on both reports to pass the correct value to present the correct information.
On the Patch_L1 report, in the "Required Computer Count" column, we right click to bring up the "Text Box Properties" dialog box:
For now, we leave the default options and only edit the "Actions" section:
In "Actions," we click "Go to Report" and select the "patchdetails" report.
We also add a parameter called "pSoftwareUpdate" and the following formula:
="[Software Update].[Software Update - Bulletin ID].&[" +Fields!Software_Update___Bulletin_ID.Value + "]"
This formula will contain the same values as presented in the report.
Next, in the "patchdetails" report, we will add a new parameter:
On the first page, we will give the parameter a name, title, data type and make the parameter visible.
Next, we will set the available values. We will get the values from a query to the dataset so the values match. The parameter dataset is the same dataset used for the report, the value field is set to "ParameterValue" and the label field is "ParameterCaption." The reason we recommend these settings is to match the data as it exists within IT Analytics.
To maintain consistency, we set the default value to "All." The formula used below is a direct reference to the IT Analytics cube:
Last, we leave the default settings:
In the overview report, we now see that our cursor changes when we hover over the "Required Computer Count" number.
Here is the resulting details report:
Using the power of Report Builder and the available data within IT Analytics, we were able to build out a detailed drilldown report in a very short time without writing any additional code or SQL queries. Our admins are able to present data quickly and easily, while our management team is informed and can navigate the data at their convenience!
This post is the next in my series on using IT Analytics to analyze the performance of our Private Cloud at Bay Dynamics. In previous posts I've focused on analysis of my VMware infrastructure, but now I'll turn my attention to investigating the performance of our Hyper-V hosts.
We start with opening our SCOM Performance Cube and refine the metrics to data coming from System Center Virtual Machine Manager by choosing the MP named "System Center Virtualization Reports 2008". Adding the Entity Count and Sample Count to the pivot table gives me a high level picture of how much data I'm collecting from different objects within that MP. We have lots to choose from, including information on virtualization candidate servers, Hyper-V hosts, VM guests, and VMware ESX servers. See below:
In this case, I only want to look at my Hyper-V hosts, so I'll multi-select those five objects, right click, and filter my results to that selection.
Next I add the actual host name to the mix, and I can see the six Hyper-V hosts I'm collecting performance metrics for with this MP.
Now that I have a good set of metrics and hosts, I want to change my measures to show the actual minimum, average, and maximum values of each.
Now I've got some real numbers to chew on. To make the analysis easier, I'd like to see this as a chart. Choosing the Chart option at the top and adjusting the options to split that into three charts by Counter, I now have a much more visually informative perspective.
I see that one of the hosts, svsfhypv002, has the most available memory as well as good free space, but also has the highest variance in CPU % total run time. I'd like to drill into that further and see that data over time to help me determine if this host is a good candidate for a new set of VMs I need to provision.
Hmm, looks like things were stable in April with high available space and low CPU utilization, but there was a big change in May. My next thought is to see that % free space broken down by specific day to see the recent trend. Dragging in the date, I see:
Clearly there was additional load added to this server in early May. Fortunately I still have over 75% of the disk free and this is still a good candidate to host new Hyper-V VMs.
Once again, by leveraging my IT Analytics SCOM Performance cube, I was able to analyze trends in a fluid way, asking and answering my own questions on the fly in a matter of minutes. No guessing and no waiting, I have the information I need to make good decisions about loading and capacity of my virtual infrastructure!
This post is the next in my series on using IT Analytics to analyze the performance of our Private Cloud at Bay Dynamics. So far in this series we've explored IT Analytics cubes in many ways, and drag and dropped our way to insightful Pivot Tables and Charts that helped me understand how my VMware infrastructure is performing. We also created Key Performance Indicators and SQL Server Reporting Services reports to help communicate key metrics. Now we're going to tie everything together and create a SharePoint Dashboard to allow others to take advantage of our IT Analytics cubes directly from within our corporate SharePoint portal.
The prerequisite for this scenario is an installation of SharePoint 2010 Enterprise. This gives us the ability to create a new SharePoint Site using the Business Intelligence Center template, and from that site we can take advantage of the recent inclusion of PerformancePoint Services and the Dashboard Designer. The screen below shows the home of a Business Intelligence Center.
Clicking the link labeled "Start using PerformancePoint Service", we arrive at the following page, from which we can launch the PerformancePoint Dashboard Designer.
After launching the Dashboard Designer, we're ready to start creating dashboard content. Our first action is to create a Data Connection to one of our IT Analytics Cubes so we can use it as the source of information for the dashboard. For a high level overview of creating a dashboard with IT Analytics, check out this post which will guide you to a recent Webinar that provides step by step guidance for creating a PerformancePoint Dashboard with IT Analytics cubes.
Once our Data Connection is configured, we're ready to choose from the following types of dashboard content.
Our first dashboard item will be an Analytic Chart. Using the same drag and drop design technique we've highlighted throughout this series, I configured the following chart to show alerts by severity, filtered to only show VMware alerts reported from the nworks MP. Notice the range of chart types and other options we have available to us. For this chart, we want to do a Stacked Bar Chart.
Next we'll create an Analytic Grid using a similar approach. In this grid I want to see the number of VMware alerts by name, and I want to sort that list descending so the most common alert type shows at the top.
Now that we have a couple of items to use in our dashboard, lets go ahead and create one. After clicking the new dashboard button, I chose a 2 column template as shown below.
Now I can arrange my two dashboard bits by dragging them into the zones on the dashboard template.
Before I finish though, I'd like to add another page to this dashboard that hosts the custom SSRS report I created in the previous post in this series. I choose Reporting Services from the content creation ribbon, enter my server name, and choose my custom report from the list.
With that added to my PerformancePoint Content listing, I add a new page to my VMware Alert Dashboard with one zone and drag the custom report into place.
That's everything I want on my dashboard for now. After deployment, I can now navigate to the dashboard on my SharePoint Site and see it live!
But that's not the end of it. Unlike many other dashboards, PerformancePoint dashboards are highly interactive when you use IT Analytics Cubes as a source for the charts and reports. On first glance that VM Balloon Memory Usage alert pops out at me again, and I want to drill in that further. I can right click on that Alert Count cell and choose Decomposition Tree to do further analysis.
After opening the Decomposition Tree I can explore my IT Analytics SCOM Alerts Cube directly from within my SharePoint site and drill down, level by level, "decomposing" the data by adding criteria one at a time. The Decomposition Tree is a great example of the powerful capabilities built into SharePoint specifically to leverage OLAP cubes.
Analytic Grids aren't the only place we can further slice the data. The following screen shows how we can right click on a value in an Analytic Chart and drill down to an attribute such as the Host Name to get further insight without leaving the browser.
Finally, lets see how that custom SSRS report that uses our SCOM Alerts cube looks in our dashboard. Choosing the link at the top of the page to navigate to our report, we can now see the report we designed by dragging and dropping attributes from our SCOM Alerts cube directly within SharePoint!
The combination of IT Analytics, System Center, and SharePoint have proven to be a very flexible and powerful platform to create a Business Intelligence portal to help me manage my Private Cloud. I can create visually informative charts and graphs that I can drill into for additional insight, and share them with others in my organization using the familiar SharePoint portal they're already using. All of this without having to bother my DBAs once!