Automated Reporting using SSRS

The SCSM reporting out-of-the-box is a underwhelming, so we augmented them with a series of manual reports, to both manage workload and also report progress to our stakeholders. A couple of months ago we undertook an exercise to automate these manual reports using SQL Server Reporting Services (SSRS) and the Data Warehouse (DW) database.

It required us to develop an intimate knowledge of the table/view relationships of the DW, which was the bulk of the effort. Once the queries were written, the actual development of the reports (achieved using Business Intelligence Development Studio), was relatively painless.

SSRS as a presentation medium is quite good, with an intuitive interface and easy ability to schedule and subscribe to reports.

The end result is the elimination of the manual effort on a daily, weekly and monthly basis to produce the reports…and the capability for users to help themselves to get the information they want.

Listing of reports available on SSRS

Example report that is calculated in real-time

Example of graphs

Solution! Sending logical notifications for Review Activities

One of the most frustrating elements of SCSM is the limited ability to include items from a parent Service Request in Review Activity notifications, and the bug with selecting the incorrect manager as a Reviewer:

1. The name of the Affected User of the parent Service Request not being available to insert in the Review Activity notification
2. The incorrect line manager being selected for Requests created in the Service Manager console – when Line Manager Should Review is set to True, the manager of the Created By user is selected instead of the Affected User‘s manager

Well, my friends at Olikka and I have developed a solution using Orchestrator that addresses these 2 frustrations. Our Runbook Activity captures the Affected User’s name and inserts into the title of the Review Activity, and adds the Affected User’s manager as a Reviewer in the same Review Activity. Now, when a notification is triggered (when the status of the Review Activity changes from Pending to In Progress), the correct manager is notified, and the person can now easily determine who is making the request.

Orchestrator Runbook

1. The Service Request template is created with the Runbook Activity inserted as the first Activity
2. A Service Request is created, either in the SM console or via the Self-Service Portal
3. The Runbook kicks off and updates the subsequent Review Activity:
   1. The name of the Affected User of the parent SR is captured and inserted into the Review Activity Title
   2. The Line Manager of the Affected User is determined and is inserted as a Reviewer
4. After the Runbook Activity completes, the Review Activity status is set to In Progress, and the notification workflow is triggered.
5. The Manager processes the approval or rejection
6. The Review Activity is marked as complete.

Approval Process with Orchestrator

Whilst it is disappointing that we had to use Orchestrator to address what should be standard functionality, but it’s pleasing that a manual workaround has been eliminated.

Here’s the Runbook for you to look at (use in a Dev/Test environment): http://sdrv.ms/Vx2i6K

UPDATE: When you insert the Runbook Activity into a Service Request template, you have to specify the mapping value of the ServiceRequestID (which is the parent work item ID) with the Work Item ID:

Configuring the Runbook Activity

 

View All Activities in a Service Request

Following on from the great code found here to create a view that lists all Activities for a Change Request:

http://blogs.technet.com/b/servicemanager/archive/2010/03/04/create-a-view-of-all-activities-that-are-a-part-of-a-change-request-and-see-the-change-request-information-in-the-view-columns.aspx

I created a similar View, but for Service Requests.

Here is the code:

ActivityManagement.Views.Requests

Service Manager QuickGuides

Hi Everyone,

To assist with training and awareness of the Service Manager tool, we developed a series of one page QuickGuides, aligned to processes, that enable users to quickly get up-to-speed with the basic operations of Service Manager.

• Incident Management
• Change Management
• Request Management

Please feel free to use them, and as always, your feedback is welcome!

Thanks,

Warren

Building the Service Catalog

Link: Service Requests Build Status
The building of Request Offerings in Service Manager is relatively straightforward, given the wizards provided.
But you still need to prepare the Service Requests you want to model. I created this input document to assist in tracking the template and request offering for each type of Service Requesst we wanted in the tool.

Building Notifications

Link: Notifications Input

Service Manager doesn’t come with any notifications configured, so you have to do it all yourself. I chose to use the Notifications module (as opposed to Workflow) for the configuration of all our notifications.  It can quickly become unruly given the number of templates and subscriptions that are required.

I came up with this input document to help keep track of all the notifications required.  The sequentially numbering system is particularly useful to marry up the template with the subscription, and whilst having to maintain a separate document is a pain, it helps with the notification sprawl.

Service Manager Build Document

Link: Service Manager Build Document

Even though we had an “expedited” implementation, I wanted to make sure that every configuration we made was captured, so I created a Build Document which also served as guidance for other interested parties as to how the tool has been configured.

I couldn’t find a similar document although there are some useful blogs available, but for our implementation we didn’t set out to customise, only to configure, and most of the blogs talk about use of the Authoring Tool or PowerShell scripts which is something we wanted to avoid.

Requirements were captured as part of the tool selection process, and this document doesn’t replace the need for formal requirement elicitation.  The Build Document can be used at the start of implementation to guide the modification of various Service Manager elements.

It is provided as is with no warranties attached. I hope you find it useful.

Error – Template with Sequential or Parallel Activities

An annoying quirk when adding Sequential or Parallel Activities to a Service Request of Change…the following errors were received.

On the Self-Service Portal:

In the Service Manager Console:

The resolution details can be found here:

http://social.technet.microsoft.com/Forums/en-US/systemcenterservicemanager/thread/252dbfc4-50d9-465c-9ed0-43324da1b109/

Basically, you can’t use the “Drag Activities” here function.

Self Service Portal Design

Our approach from the very beginning has been to rely on the out-of-the-box functionality as far as practical, and only customise to meet a Must Have business requirement.

This approach extends to our design of the Self-Service Portal – there are third party products available, and extensive customisation is possible through SharePoint and XML coding, but we needed to make do with some minor tweaks, and here is the result:

Error – Creating tickets on the SSP and Mandatory Fields

When trying to create a Generic Incident on the SSP the following error appears:

A mandatory field on the Incident form was not populated in the template attached to the corresponding generic request offering.
Annoying thing is that it lets you get right to the end and provides a less than useful error message to the user.