System Center Service Manager 2012 Reports

wolf77
wolf77 used Ask the Experts™
on
In SCCM 2012 R2 is easy to create a custom reports with Report Builder, but in SCSM 2012 R2 i can't find a easy way to create a Report for example, all tickets that have a severity low, or all tickets that so easy to create a custom reports with Report Builder, but in SCSM i can't find a easy way to create a Report for example, all tickets that have a severity low, or all tickets that have a specific selection in a custom list.

Is there a way to easy create this type of reports or any report?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technology Consultant
Commented:
When writing reports in Service Manager, the first thing I strongly suggest is to download the Technical Doc's for Service Manager. Included in this bundle you'll find a file called SM_job_aids.zip.

https://www.microsoft.com/en-us/download/details.aspx?id=27850

In the SM_job_aids.zip file, open up the DWDataMart.vsd file. This outlines the schema of the DWDataMart database. (This is the SCSM Data Warehouse Database where your Incidents, Service Requests, Problems, etc reside, and which you'll be reporting against.)

The first thing to note here is that each of these boxes represents a SQL View in the database. Second thing to note, Service Manager is designed to be extensible, so if your organization has added customizations, which you need to report on, you'll have to do a bit more work to determine the relationships to those database tables/views.

Now to answer your question. Can you use Report Builder to create SCSM reports? Yes, but there are some limitations. You can build the bulk of your query using Report Builders GUI. Start by pulling some fields you want from the IncidentDimvw view.

What you're going to notice very quickly, is that much of the information in this view, isn't going to look good on a report. The columns such has Urgency, TierQueue, Impact, Source, Status, ResolutionCategory, etc, actually contain Enumeration GUID's. Taking Urgency for example, what you likely want to see on your report is the "IncidentUrgencyValue" located in the related "IncidentUrgencyvw". To get this value, you have to create a JOIN.

Now you've looked at the Visio diagram of the DWDataMart Schema, and you're probably wondering how many JOINS you need. To pull back basic information about an incident, you'll easily need as many as 10 JOINS. But don't fret, you can build this out in the Report Builder GUI, at least to start. It's pretty easy to do this for Status, ResolutionCategory, TierQueues, Impact, Classification, Source, and Urgency.

Full Example Code: http://pmibluepapers.blogspot.com/2015/12/BP2015120801.html

Your T-SQL for the JOINS for these values might look something like this:
 LEFT OUTER JOIN IncidentClassificationvw
    ON IncidentDimvw.Classification_IncidentClassificationId = IncidentClassificationvw.IncidentClassificationId
  LEFT OUTER JOIN IncidentSourcevw
    ON IncidentDimvw.Source_IncidentSourceId = IncidentSourcevw.IncidentSourceId
  LEFT OUTER JOIN IncidentImpactvw
    ON IncidentDimvw.Impact_IncidentImpactId = IncidentImpactvw.IncidentImpactId
  LEFT OUTER JOIN IncidentUrgencyvw
    ON  IncidentDimvw.Urgency_IncidentUrgencyId = IncidentUrgencyvw.IncidentUrgencyId
  LEFT OUTER JOIN IncidentTierQueuesvw
    ON IncidentDimvw.TierQueue_IncidentTierQueuesId = IncidentTierQueuesvw.IncidentTierQueuesId
  LEFT OUTER JOIN IncidentStatusvw
    ON IncidentDimvw.Status_IncidentStatusId = IncidentStatusvw.IncidentStatusId
  LEFT OUTER JOIN IncidentResolutionCategoryvw
    ON IncidentDimvw.ResolutionCategory_IncidentResolutionCategoryId = IncidentResolutionCategoryvw.IncidentResolutionCategoryId

Open in new window



Now if you're building this out, you're probably thinking, your making headway. But you're probably also thinking you need to see who the IR is assigned to, and who it affects.  And this is where we run in to a limitation with the Report Builder GUI.

If you only need to pull back one user, then yes, you can define the relationship and complete your report. However most of the time we need to pull back multiple users per WorkItem. Unfortunatly, ReportBuilder's GUI only let's you pull back a column from a view or table one time. We might want to pull back the users DisplayName as many as five times for a single IR. To do this, you will need to drop down to the text editor (and once you do that, you can't go back to the GUI).

To add users, you need to create new temp colmns for your query's returned dataset, which might look something like this:

 
[Assigned To] = AssignedToUser.DisplayName,
                [Affected User] = AffectedUser.DisplayName,
                [Created By] = CreatedByUser.DisplayName,
                [Primary Owner] = PrimaryOwner.DisplayName,
                [Resolved By] = ResolvedByUser.DisplayName

Open in new window


Next you'll need to define the relationship for the users:

/* Joins for User Relationships */
                Left Outer Join dbo.WorkItemAssignedToUserFactvw 
                                ON WorkItemDimvw.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey
                                And WorkItemAssignedToUserFactvw.DeletedDate IS NULL
                Left Outer Join dbo.UserDimvw AssignedToUser
                                ON WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey
 
                Left Outer Join dbo.WorkItemAffectedUserFactvw
                                ON WorkItemAffectedUserFactvw.WorkItemDimKey = WorkItemDimvw.WorkItemDimKey
                                And WorkItemAffectedUserFactvw.DeletedDate IS NULL
                Left Outer Join dbo.UserDimvw AffectedUser
                                ON WorkItemAffectedUserFactvw.WorkItemAffectedUser_UserDimKey = AffectedUser.UserDimKey
 
                Left Outer Join dbo.WorkItemCreatedByUserFactvw
                                ON WorkItemCreatedByUserFactvw.WorkItemDimKey = WorkItemDimvw.WorkItemDimKey
                                And WorkItemCreatedByUserFactvw.DeletedDate IS NULL
                Left Outer Join dbo.UserDimvw CreatedByUser
                                ON WorkItemCreatedByUserFactvw.WorkItemCreatedByUser_UserDimKey = CreatedByUser.UserDimKey
 
                Left Outer Join IncidentHasPrimaryOwnerFactvw
                                ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
                                AND IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL
                Left Outer Join dbo.UserDimvw PrimaryOwner
                                ON IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey = PrimaryOwner.UserDimKey
 
                Left Outer Join dbo.IncidentResolvedByUserFactvw
                                ON IncidentResolvedByUserFactvw.IncidentDimKey = IncidentDimvw.IncidentDimKey
                                AND IncidentResolvedByUserFactvw.DeletedDate IS NULL
                Left Outer Join dbo.UserDimvw ResolvedByUser
                                ON IncidentResolvedByUserFactvw.TroubleTicketResolvedByUser_UserDimKey = ResolvedByUser.UserDimKey

Open in new window


If you open of the WorkItem tab in the DWDataMart.vsd file, you'll be able to see what we're doing with the User Relationship Joins above.

The short of it is that creating Reports based on data in the Service Manager DW, which run effeciently, is a bit more of a chore that what you might be able to get out of SCCM, but once you understand the database schema, a bit, and work through building a couple, the process will become more familiar.

To see a full example of a working T-SQL Query written to be easy to understand, and returns many of the most common components of a Service Manager IR, visit http://pmibluepapers.blogspot.com/2015/12/BP2015120801.html

Author

Commented:
I've requested that this question be deleted for the following reason:

Not resolvet
Matthew EnglandTechnology Consultant

Commented:
Detail provided on how to create a SCSM Report.

Author

Commented:
Sorry for that, i dont see your answer.

Thks for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial