Authoring Friday

Every friday, adventures in System Center authoring

Service Request Report The Whole Shebang!–part 1

One of the “missing” features concerning reporting in SCSM is there are no reports for Service Requests. I think it was a year ago I wrote a need bundle with some reports.

Now since this came up with another customer and to be honest there are some parts (very big understatement!)missing here I decided to write the whole sjizzle shebang.

and when I mean the whole sjizzle shebang I mean the whole nine yards! Laughing out loud

Thanks MS I can now write C#, SQL Queries, XML, Management Packs, Bundles, DW Extensions, DW views, tables, temp tables and my lord can I join tables and views endlessly!

I can honestly say Developer makes more sense these days than consultant but don’t understand me wrong I love writing code and all the rest! Whoop, whoop!

If that is the new popular term “Devops” than I have been a Devops man since MOM 2005 Hot smile

I won’t go into detail in how you can get everything into packs etc. but will quickly point out some nice things which might be handy and why you want these extensions  in your DW

I know there are the cubes which are great for long term reporting but to be honest although they like it at first they want to be able to go into details, including dates and times something which simply isn’t in the cubes. So back to reporting since writing and extending DW I can now do in my dreams. Yeehaaa

People who know me I tend not to take the easy way but only go the whole nine yards! So prepare for a journey into how I got these reports!

Action logs – Analyst/User Comments logs in Datawarehouse

First of all there are no action logs / comment logs in the Data Warehouse…yes that makes 0,0 sense when your manager wants to know what happened and when.Eye rolling smile

To get these logs into the dw a simple extension is sufficient to re-use over and over again.

http://gallery.technet.microsoft.com/SCSM-Action-Logs-user-e2c87c1f

Once you have imported these extensions and synchronized the dw you are ready to start writing reports including comments, actionlogs!

After importing in SCSM run the MPsync job:

image

Right after this you could wait endlessly for the other jobs to kick in but Travis to the rescue, he wrote a script which kicks of the jobs in sequence for you with a little PowerShell magic:

http://gallery.technet.microsoft.com/PowerShell-Script-to-Run-a4a2081c

Don’t forget to remove the cube jobs since this will not improve the running of the sync as we all now. Winking smile

Simply run the script after the MPSync job finishes and there you go, although now I think of it they might kick in right after MPSync job has run??? Well I want to be sure, so use the script

Woohoo Actionlogs in the DW how nice!

Now for a little back ground there are also logs for activities in these extensions! Why?

Take a look at a runbook activity and there they are and we want these as well!

image

Result in report:

image

Actual Start Date – Actual End Date

Nice there is an actual start and end date property on every activity, that’s cool I can use these!

So when a manager or customer asks you can you report on how long it took for an activity to complete you all yell “Yes off course actual start and end date!”’

Below a quick table what is actually logged in the DB:

  Activity Actual Start Date Actual End Date
Service Request      
  Manual No No
  Review No Yes
  Parallel No No
  Sequential No No
  Runbook Yes Yes

Yes this is it!

pretty scary there is an actual end date on review activities but never a start……

Nice another challenge, you could use ActivityStatusDurationFactvw but this is more complicated for the normal user and even worse where exactly get I get these in a view? Or use SLO on the activities?

Yes you cannot until now.

I have written a couple of workflows which actually populates the actual Start Date and Actual end Date!

http://gallery.technet.microsoft.com/SCSM-Actual-Start-and-End-2c6d40e2

Simply import the management pack and you can now use the actual start and end date in your views as well, pretty cool!

 

What about Lists or enumerations in reports?

If you have joined three tables you will be able to view the area of a service request by display name in your report. Nice! But what if you have multiple area’s named the same but under a different branch?

Let me explain by example:

image

In a report you cannot tell if it is a Linux or Windows Account since it will only show “User Account”.

A solution for this is to create a custom view in the Data Warehouse which stores the entire name:

image

CREATE VIEW dbo.ServiceRequestAreaHierarchy AS
WITH _Items as
 (
 SELECT ServiceRequestAreaId Id, ParentId, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(ServiceRequestAreaId as varchar) + ':' as varchar (100)) AS Struc
 , cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName
 FROM dbo.ServiceRequestArea AS t
 INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = 'ENU' AND d.ElementName = t.Id
 WHERE ParentId = 1
 UNION ALL
 SELECT ServiceRequestAreaId Id, t.ParentId, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(ServiceRequestAreaId as varchar)+ ':' as varchar(100)) AS Struc
 , cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName
 FROM dbo.ServiceRequestArea AS t
 INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = 'ENU' AND d.ElementName = t.Id
 JOIN _Items i on i.Id = t.ParentId
 )
 SELECT ISNULL(Parent.Id,Child.Id) ParentId, ISNULL(Parent.DisplayName,Child.DisplayName) ParentClassification, Child.ID ChildId, Child.DisplayName Classification, Child.Ordinal
 , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) FullName
 FROM _Items Child
 LEFT OUTER JOIN _Items AS Parent ON Parent.ID = Child.ParentId

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }This will create the view and now you can report on the full name which would be Linux\User Account

instead of User Account!

I cannot remember exactly where I got this solution it was somewhere on the TechNet Forums I think, if you recognize the solution please let me know and I will add your credits!

How about Runbook Activities?

By default there are no runbook activities in the Data Warehouse. When you want to report on these you can use the ActivityDimVw but this will not have the runbook specific settings like which runbook and more runbook activity specific information.

Since we are interested in these properties as well I have written an extension to report on these as well!

http://gallery.technet.microsoft.com/SCSM-Data-Warehouse-e8e9bb25

Just Import the management pack and you will extend your datawarehouse with runbook activities:

 

image

Once you start writing your queries there is one little catch in here.

 

if you want to join the tables you need to join on

runbookitemdimvw.id on RunbookItemAutomationActivityDimvw.Runbookid

you can try but his is not going to work because in runbookitemdimvw this property is a string and in the RunbookItemAutomationActivityDimvw this is a guid. Sad smile

LEFT OUTER JOIN runbookitemdimvw rbitem
ON CAST(
        SUBSTRING(rbitem.id, 1, 8) + '-' + SUBSTRING(rbitem.id, 9, 4) + '-' + SUBSTRING(rbitem.id, 13, 4) + '-' +
        SUBSTRING(rbitem.id, 17, 4) + '-' + SUBSTRING(rbitem.id, 21, 12)
        AS UNIQUEIDENTIFIER)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }Which actually converts the string to ID, really Cool !

 

Wrap up

With this post and the solutions you will prepare your environment to do some really cool reporting sjizzle! I promised you the whole shebang so wait for part two which will go into the reports!

 

Happy Authoring,

Oskar Landman

Dates and Time zones in SCSM Explained

First of all there the way SCSM handles dates is not really new but something you need to consider and be aware of.

Since this question comes along a lot especially when you start working with reporting I decided to write an article explaining the differences and pitfalls when dealing with SCSM and dates.

Time zone and understanding what is happening

Before you start diving into the databases be aware that the data in the SCSM databases is always UTC!

Data in the SCSM Databases is UTC

Although to an end user he couldn’t care less this is very crucial for troubleshooting and understanding SCSM!

This will become more and more crucial when you start designing reports or even more start using the SDK where you query directly to the databases.

Do not deviate from UTC or your life will become a living hell! Devil

Why?

The reason for adding data into a database in one universal way is you are always sure you are in the “correct” Time zone.

image

So when a user logs in with the console and creates a request in Europe the user will see the request being created, CreatedDate, in his own time zone but in the background the data is actually translated to UTC and after this written to the Database.

Now when a user from the US opens the same request the data is being pulled from the DB to the console and translated from UTC into the US time zone.

This way you are sure if you start measuring requests for like created to resolved date you are measuring the actual time between createddate and resolveddate.

Running into this issue

Now if you would create your own solution which will automatically add the first response date and you forget to do this in UTC time in the DB. This will not be noticed until you start working with this date.

image

Let’s say you are creating a SLO based on CreatedDate and First Response date, now CreateDate will be in UTC and First Response date( where you created your custom solution for) will be in your local time zone.

Now depending on what time zone you are in this can lead to never reaching you SLA because your time zone deviate from UTC with a plus like +2 or leaving you at ease because you live in the minus zones like -4 which could even result in first response being prior to the create date! Unless you are wearing a cape with a big S on your shirt try explaining this to your manager Winking smile

image

image

For reference:

Type Result in DB Result in Console
CreateDate 2014-04-10 08:09:50.033 2014-04-10 11:09:50.033
First Response 2014-04-10 11:16:50.033 2014-04-10 11:16:50.033

Notice the behavior, to the user this all looks OK since he is in the same time zone.

The incident is created at 11:09 and first responded 7 minutes later on 11:16!

A custom PowerShell script took the current time, [DateTime]$Now = get-date,  and wrote it to the database. (no translation to UTC).

Now in the database it looks like this:

The incident is created at 08:09 and first responded 3 hours and 7 minutes later on 11:16!!!!

Because when creating SLO it will measure against the dates from the DB imagine how your SLA score will look like.

Because time difference from UTC can be from –12 to + 14 you can imagine how this will affect your measurements.

 

Running into this issue part 2

Another way even without having any custom solution writing date time to the databases directly would be when you start writing reports.

When you are reporting against the Data Warehouse be sure you are translating your date time data into the time zone of where the report is actually running.

For reference you can check the default reports.

There is “code behind” the reports which will make the translation for you if you are writing a new report based on an already existing one.

image image
Open the reporting website(default http://localhost/reports) Navigate to the incident list report
SystemCenter\ServiceManager\

ServiceManager.Console.Reporting.IncidentManagement

image image
Right Click the incident details report and select edit in report builder. Now report builder should open and select the cell for the create date as above and right click select expression.
image image
Notice the expression is using code behind to do the translation!
You can build your custom report based on this version and use the same code behind.
If you want the details of this code just click on the report itself, right click and select report properties and select the code pane!

Now when you want to add a column to this report be sure to use the same expression to translate from UTC into the time zone of the report to avoid time translation issues.

If you are building a report from scratch I would recommend to still use the default ones as base and delete all other stuff in the report to re-use the code.

Or if you don’t want to translate from UTC at least let your manager know you are running these reports in UTC! Winking smile

 

Lessons learned

Data in SCSM Databases is in UTC

When writing custom solutions for SCSM (or any other product) be sure you are translating the data into UTC in order to avoid “Strange” behavior.

Remember this rule or you will find out the hard way Winking smile

When reporting you are running into the same challenges always remember data in the DB is in UTC!

SCSM Incident Change Status task the correct way! “Evil Other Status Task”

When you start with incident management there will always be a discussion about incident status.

The discussion will be about who will close the incidents?

If want to go straight to the MP – http://gallery.technet.microsoft.com/SCSM-Incident-Change-04594d9b

Auto Close Incidents Task

The preferred way should be to have the Service Desk set the incidents to resolved and have closing of the incidents automated after x days. This way you leave x days for the end user to respond, if not close the incident. Once it is closed it stays closed and if the end user still requires assistance he needs to log a new incident.

For auto close there are several solutions to be found on the internet.

This is it, hmm no need to blog about this right?

Well there is more to it.

Remove Close Task

The second question would be “Can you remove the close task?” Sure although changing access to a task is a hassle in SCSM it can be done. So you create new roles based on the existing ones and you unselect the close task.

image

image

Cool! Well customer here you are, enjoy yourself with incident management change status in SCSM!

You cannot remove close task!

Why ? would one ask well you don’t really have to be a rocket scientist to discover that you actually can close the incident. Just select the “evil” Other Status which let you change to ANY state you want.

image

You remove the “other status” but how would you set the incident to pending or a custom status?

You can’t!

Evil Other status task – NO WAY change status without comments!! Confused smile

To be honest the other status task is plain evil from a incident manager perspective. Let me explain:

When I started in IT as a helpdesk employee I had a manager who would slap you silly if you changed the status of an incident without telling the reason WHY you changed the status. This is because using the status in an incorrect way is basically a sophisticated way to cover your *ss.

If you wanted to play games all day you could set the status of your incidents to pending and have a nice day off Winking smile 

Another one which was used was closing and incident or resolving it because the end user was a real pain and his issue was in general more a flame on the help desk employee’s. By the time he logged the incident again you made sure you where at some “customer” doing your thing.

Believe me I have seen many other versions of this as well Smile

The key in here is always have progress in you incidents, and give a reason why you changed the status because especially the last one affects the End User in question. Which is a business employee making  money for the business and in the end pays your check every month!!!

In the end the incident manager should have an operational process in place to catch these little rascals in their act.

But if you can provide some assistance here you should.

Changing status in SCSM the correct way – Friendly Other Status

Because another customer asked to remove the close task I figured I should be ahead of things to come and pointed them out although I can remove the close task this will leave the “evil” one for anybody to use.

Since I have some skills in building extensions I figured I would write the code to change status in a correct way.

image image
   

Cool Solution right Smile 

The whole idea was you cannot see any difference at first and the cool stuff will be once you open the Other status, which is the friendly one Smile

image

When you click the “other Status” a new form will open where you can select the other status.

And need to fill in comments.

These comments are nicely placed in the action log:

image

And off course “custom Statuses” will also be available!

image

image

What About the close task?

If you would still require the close task to be available but want to benefit from the comments added to the other status?

The Solution – http://gallery.technet.microsoft.com/SCSM-Incident-Change-04594d9b

You need to edit the XML accompanied with the solution. There are comments in the XML what to uncomment to get the close task!

If you want to separate the tasks from the grouped version please take a look at – http://gallery.technet.microsoft.com/Service-Manager-2012-b558f3ac

Other resources:

Auto Close Cireson – http://cireson.com/apps/auto-close/

Hiding Console Tasks – http://blogs.technet.com/b/servicemanager/archive/2011/06/24/hiding-console-tasks.aspx

 

Regards,

Oskar Landman

VMware Components in System Center Service Manager “The Good, The Bad and the Ugly”

One of the key selling points in SCSM is the way it integrates with other systems and how it populates and updates the SCSM CMDB automatically!

By configuring connectors for System Center Configuration Manager, System Center Operations Manager and Active Directory you have a dynamic CMDB in just a few mouse clicks and yes it is that easy Winking smile

You can be and feel like:

image

Believe me this is a “Good” position to be in!

This post will not go into detail on setting up the connectors for SCCM and AD but will focus on SCOM and how you can use SCOM to automatically populate your CMDB.

 

Customer request – The Good, the Bad and the Ugly

A common question I got from every customer is I want a Dynamic CMDB but what about the non standard objects?

Wait let me picture the setting.

In nearly every SCSM project you end up in a sort of stand off.

Most times it is just like the scene from The good, the bad and the ugly.

There is always a person which has has waited the whole session on SCSM and how it integrates, with his question. He has thought thoroughly about his question and from his experience and knowledge on “other” products. He is waiting to pull the trigger with his question.

And when he pulls the trigger on his question:

image

“Yeah, Microsoft but what about the non Microsoft products? We have “tons” of other stuff like VMware, Oracle and Linux?” A nasty smile followed by “You cannot use Microsoft for these so how are you going to populate these?” Angry smile

The room is filled with silence and all eyes are on you. From my experience you can wait for the ugly to stand by him, he is the person who has been working with the “old” system and don’t want to migrate.

image

“Yeah without these we cannot use a dynamic CMDB how are YOU going to cope with that?”

Now this is exactly the position I want to be in Hot smile I know these guys and if you watched “The Good, The Bad and the ugly” you know you need to change this into your favor or remove the bullets from one of them, which leaves you with one left. The one which is slow on the trigger, because he is using old ammo Winking smile

image

Now you need some help to remove the guys’ bullets right?

 

The solution

First the Linux systems, this is not really an issue ever since 2012 we can monitor these with SCOM and once monitored you can pull them with the SCOM CI connector to SCSM.

The next parts are harder, this is where third party solutions come into play.

This particular case was solved by Opslogix who provide both VMware as well as Oracle monitoring in SCOM.

So done deal, right?

Well one would think so, so I installed the VMware pack in SCOM and waited for all VMware components to be discovered. There is a great manual accompanied to easily install the components in SCOM and additional information can be found at the site’s of

Dieter Wijckmans – Jedi and MVP – Test drive Opslogix VMware management pack

Stanislav Zhelyazkov – MVP – First Look at OpsLogix’s VMware Management Pack

Arjan Vroege – System Center Dashboarding Guru – OpsLogix VMware Management Pack and Dashboarding capabilities

Prepare SCSM for SCOM CI Connector

You first need to install the SCOM management packs, just import them in SCSM.

These packs can be found in the install directory of SCSM:

C:\Program Files\Microsoft System Center 2012 R2\Service Manager\Operations Manager 2012 R2 Management Packs\

image

After Importing these packs you can import the OpsLogix management packs:

Before the actual import you need to add two depended management packs which are required by the OpsLogix packs.

image

NOTE*** The Microsoft.SystemCenter.Visualization.Library.mpb file is a Bundle and NOT the MP!

You can find the management packs on the SCOM installation media in the folder “ManagementPacks” Again be sure you use the MPB of the Microsoft SystemCenter Visualization Library !

After this you can import the OpsLogix Management packs in SCSM:

image

After adding the management packs you can add the OpsLogix VMware classes to the SCSMAllowlist

You can use the PowerShell Script below in a PowerShell Session: (Be sure to change the drive if you installed service manager on an alternative drive)

Import-Module 'C:\Program Files\Microsoft System Center\Service Manager 2012\Powershell\System.Center.Service.Manager.psd1'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.Cluster'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.Datacenter'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.Datastore'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.ESXServer'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.Network'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.vCenter'
Add-SCSMAllowListClass -ClassName 'OpsLogix.IMP.VMWare.VirtualMachine'

The last thing to do is configure the SCOM CI Connector, in order to do so you require a Service Account with SCOM Administrator rights.

In SCSM navigate to Administration\Connectors

image

And Add the Operations Manager CI Connector.

Make sure you select all management packs for synchronization:

image

Now the last step is Synchronize the connector to synchronize your VMWare components into SCSM.

Views

The last step will be you create views in your Configuration Item pane to show the VMware components in SCSM.

image

 

image

Recap

If you want to walk away alive in the session or show down on SCSM Dynamic CMDB, you need to know how to remove the Bullets from the bad Smile

Although I like to think of walking away like Clint Eastwood smoking a cigar and spitting in the sand, I still require the bullets to shoot back.

Opslogix packs provide me the a perfect way to remove the bullets from the bad and silence the Ugly once and for all !

and not only this they have an excellent support team which is open to suggestions and improvements on their products!

Credits all go to OpsLogix Support!

During testing this solution I discovered the original VMWare pack had an issue, since SCSM would like to look at things from a Configuration Item perspective it is required classes are underneath the Configuration Item Abstract class, Which they weren’t!

Since this is not a minor change I was really impressed by the support delivered, they redesigned (replaced) the VMWare classes below the configuration item class which makes them inherit the correct properties to be in SCSM!  All within one week.

So be sure to check for the latest version from the VMWare pack by OpsLogix and how you can fill your dynamic CMDB.

http://www.opslogix.com/products/vmware-intelligent-management-pack

FYI.  I requested for a management pack to use in SCSM for easily viewing the VMware classes and it is been created as we speak,

Thanks Guys!

Regards,

Oskar Landman

Create a PowerShell Activity in Service Manager

Hello,

PowerShell is becoming more and more important in service automation. Now you have the single scripts that you can execute against SCSM, you can integrate them in SCSM workflows via Authoring tool or integrate them in Orchestrator runbooks. SMA is another workflow engine from where you can execute PowerShell automation.

Service Manager is the tool to integrate processes in your automation. I have created a procedure to create your own PowerShell Activity in Service Manager to run SMA runbooks or update the request.

The idea:

Why can’t we simply add an activity in the SCSM activity flow that includes the PowerShell script and this script gets executed by Service Manager. This can have two advantages:

  • - You don’t need to create a workflow for every PowerShell script that is required to enable your process.
  • - You can directly call SMA or Orchestrator runbooks from your Service Manager Activity flow via PowerShell.
  • The flow of the idea is illustrated in the figure below.

    image

  •  

How you can build this with standard Service Manager functionality:

A Class and a form

An additional Activity work item class need to be created. Together with this new class you need a form where you can specify the PowerShell script and set the activity properties.

SCSM workflow

To execute the PowerShell script you specify on the custom activity, a workflow is needed. This workflow gets the script information from the activity, executes the script and updates the custom activity in Service Manager.

The procedure to build the custom PowerShell activity:

Creating a class and form in Service Manager is not a difficult task. This is explained in many blogs (here and here). For the form you have the option to create one in the Authoring tool or in Visual Studio.  An overview of the elements you need to create is illustrated below.

image

The PowerShell Activity class and form.

The Procedure

Class: PowerShellActivity – Base class: System.WorkItem.Activity

Class properties: (In this example, you can define the properties as you like)

  • - PSScriptEnabled: Boolean, default value = enabled
  • - PSScriptBody: String, check maxlength value (make sure you have enough lines for your PS script)
  • - PSScriptMessage: String

PowerShell Activity Form:

  • - From the Authoring tool you can make it working;
  •              Textbox for PS script can be added and a checkbox to manage the PSScriptEnabled property.
  • - From Visual Studio you can make it all working Winking smile:
  •         -   ID can be set with the prefix you want
  •         -   Different items on the activity work item can be controlled in code behind the form. (Like in the example, I have added the “CreatedDate”^property)
  •         – Start point to create your custom form can be the blog of Patrik Sundqvisthttp://blogs.litware.se/?p=1402 – Another blog post that can help.
  •         – From there you can build the form, add images and include code to set properties. Besides the additional references and getting the EnterpriseManagementGroup session, I have added the following Event code (UserControl DataContextChanged) in the example form to set my required properties on the PS activity.
  •    1: private void UserControl_DataContextChanged(object sender, DependencyPropertyChangedEventArgs e)

       2: {

       3:     // wait binding

       4:     if (this.DataContext is IDataItem)

       5:     {

       6:         instance = (this.DataContext as IDataItem);

       7:         // If this is new incident, set some default properties

       8:         if ((bool)instance["$IsNew$"])

       9:         {

      10:             instance["CreatedDate"] = DateTime.Now.ToUniversalTime();

      11:             instance["Id"] = "PS" + instance["Id"].ToString();

      12:         }

      13:     }

      14: }

PowerShell Activity form example:

image

Note:

You need to define one variable ($ItemID) that is also used in the Service Manager workflow to execute the script. I have added a note on the form to specify this variable.

Script output is added below the note on the form. In this way you can have some output of the script in the form

The Service Manager workflow to execute the PS script:

This one starts from the Authoring tool, open the Authoring tool and create a workflow (existing or new management pack. In the example MP I have all info in one management pack).

Give the workflow a name and specify to run the workflow when a PowerShell activity is updated. Criteria trigger for filtering the updates is the change from “Is not equal” to “Equals” the “In Progress” status. Additional criteria is the script body is not empty.

image 

image

image

In the workflow configuration in the Authoring tool, drag-and-drop a Windows PowerShell Script activity in the workflow designer.

The configuration of the activity in the workflow:

Script parameters:

ItemID: Value bound to the ID of the PowerShellActivity class.

PSScript: Value bound to the PSScriptBody property of the PowerShellActivity class.

Script Body:

   1: try

   2: {

   3:        Import-Module -Force "C:\Program Files\Common Files\SMLets\SMLets.psd1"

   4:        $result = Invoke-Expression -Command $PSScript -ErrorVariable errorOUT

   5:        #set activity to completed

   6:        $PScriptClass = Get-SCSMClass -name System.WorkItem$

   7:        $WIFound = Get-SCSMObject -Class $PScriptClass -Filter "Name -eq $ItemID"

   8:        $PropertyHash = @{"Status"="Completed";"PSScriptMessage"="Script executed"}

   9:        Set-SCSMObject -SMObject $WIFound -PropertyHashtable $PropertyHash

  10: }

  11: catch

  12: {   

  13:        #set Activity to failed       

  14:        $PScriptClass = Get-SCSMClass -name System.WorkItem$

  15:        $WIFound = Get-SCSMObject -Class $PScriptClass -Filter "Name -eq $ItemID"

  16:        $PropertyHash = @{"Status"="Completed";"PSScriptMessage"="errorOUT"}

  17:        Set-SCSMObject -SMObject $WIFound -PropertyHashtable $PropertyHash

  18: }

Creation of the workflow to execute the script is created in the Authoring tool.

One item that we miss in the overall solution is an activity template for your PowerShell Activity. This is a task that can be done xml management pack editing. Add the XML <Templates> information below the </Monitoring> part of the MP. Example illustrated below.

Don’t forget to specify the <DisplayString ElementID="DefaultPSActivityTemplate"> for the template also in the management pack.

   1: <Templates>

   2:     <ObjectTemplate ID="DefaultPSActivityTemplate" TypeID="PowerShellActivity.Projection" />

   3: </Templates>

All done! Create the management pack bundle and import the solution in Service Manager. The dll from your workflow creation in the Authoring tool needs to be copied to the Service Manager program folder.

Now you can add the PowerShell activity in the activity flows of templates.

After importing the mpb file in Service Manager (and restart of the consol), create a SR template that includes a PowerShell activity and the script (can be added when work item is created).

An example PS script to update the Service Request can be found below. It’s just an example to give you an idea what you can do with your solution.

In this way you can execute SMA runbooks from Service Manager or directly launch Orchestrator runbooks (using the default runbook activity is preferred for this last one Winking smile ). Example will be added later.

Update Service request via the PS activity:

   1: set-executionpolicy -executionPolicy ByPass 

   2: import-module smlets

   3: $PSA_Object=Get-SCSMObject (get-SCSMClass PowerShellActivity$) -Filter "Name -eq '$ItemID'"

   4: $ParentRequestRelationshipID = "2da498be-0485-b2b2-d520-6ebd1698e61b"

   5: $RelatedParentRequest = (Get-SCSMRelationshipObject –ComputerName <Your ComputerName -ByTarget $PSA_Object  | ?{$_.RelationshipID -eq $ParentRequestRelationshipID -and $_.IsDeleted -eq $False})

   6: $ParentClass = get-SCSMClass $RelatedParentRequest.SourceObject.ClassName

   7: if ($ParentClass = "System.WorkItem.ServiceRequest")

   8: {

   9:     $SRID = $RelatedParentRequest.SourceObject.Name

  10:     $srClass = Get-SCSMClass -name System.WorkItem.ServiceRequest$

  11:     $SRFound = Get-SCSMObject -Class $srClass -Filter "Name -eq $SRID"

  12:     Set-SCSMObject -SMObject $SRFound -Property 'Description' -Value "Updated by PS Script Activity"

  13: }

Run SMA runbooks via the PS activity:

   1: will follow....

The example created for this blog post will be posted on TechNet Gallery. Post will be updated when the bits upload is done.

Have fun!! Now you can do everything with Service Manager, SMA integration in Service Manager is reality.

Kurt Van Hoecke

SCSM portal user input to description of the work item

In Service Manager you can create request offerings to publish on the portal. Information from the portal can be mapped to a property of the work item, this is one on one mapping. This can be sometimes difficult, for example if you want all information (questions and answers) from the portal in the description field of the Incident. You can do create this functionality with Orchestrator runbooks that monitor the object creation event, in the solution described in this post a PowerShell script and SCSM workflow are used to add this functionality in the environment.

This blog post provides information around the PowerShell scripts and describes the procedure to create the workflow. The goal of this post is to document the logic so you can adjust it to your needs. The files created in this blog post can be downloaded from TechNet Gallery. The complete flow of the example scenario is illustrated below. Incident or Service Request is created via portal, workflow kicks-off a PS script that updates the new created work item.

image

The PowerShell script

This part of the blog post provides an overview of the PS script. Glue all script information together and you have the complete script. (PS script files are in the download). Let’s start…first we need to initialize the variables in the PS script and get the work item.

For Incidents:

#initialize all variables
[Array]$Array = $null
$builder = [string]::Empty
[Array]$ListArray = $null
$listBuilder = [string]::Empty
$IR_ID=Get-SCSMObject (get-SCSMClass System.WorkItem.Incident$) -Filter "Name -eq $IRId"

For Service Requests:

set-executionpolicy -executionPolicy ByPass
import-module smlets
#initialize all variables
[Array]$Array = $null
$builder = [string]::Empty
[Array]$ListArray = $null
$listBuilder = [string]::Empty
$SR_ID=Get-SCSMObject (get-SCSMClass System.WorkItem.ServiceRequest$) -Filter "Name -eq $SRId"

Once we have the work item object we can define the input (UserInput xml), format it to xml and start filling the array. This is the same for both process, PS info is an example for Service Requests.

#Define input
$UserInput= $SR_ID.UserInput
$content=[XML]$UserInput
#Format input as XML
$inputs = $content.UserInputs.UserInput
$Array += "************ Service Request information **********"
$Array += ""

Now we can loop through the UserInput xml information. Important in this execution part of the script is to capture the case where multiple answers can be provided to the question on the portal. The script detects the multivalue answer (check value "<value*") and enumerates the values selected. All enumerated information is added to the array.

#loop thru all the inputs
foreach ($input in $inputs)
{

#check for multivalues
if($($input.Answer) -like "<value*")
{      
    #format the content of the answer as XML
    [xml]$answer = $input.answer
    foreach($value in $($($answer.values)))
    {
            foreach($item in $value)
            {                  
                foreach ($txt in $($item.value))
                {                    
                    #Add the Property displayname to an array
                    $ListArray += $($txt.DisplayName)                 
                }#End Foreach list value items
                #flat the array using ; as separator
                $Array += $input.Question + " = " + [string]::Join(" ; ",$ListArray)
                $ListArray = $null
            }#End Foreach list              
    }#End foreach multiple answers      
}#End IF

In case when no multiple answers are provided, there are 2 possible input types: Enumeration values or string values. For the enum values the Get-SCSMEnumeration is used to get the displayname of the selected enumeration value. All enumerated information is added to the array.

else
{
     if ($input.type -eq "enum")
    {
        $ListGuid = Get-SCSMEnumeration -Id $input.Answer
        $Array += $($input.Question + " = " + $ListGuid.displayname)
    }
    else
    {
    $Array += $($input.Question + " = " + $input.Answer)
    }#End ELSE
}#End ELSE

The array holds now all information and this information can be set as the description property of the work item. For Incidents we can simple glue this to the Incident work item property, for Service Request it is also important to get this information in the Activity’s related to the request. 

For Incidents:

The Set-SCSMObject PS cmdlet is used to update the description property of the Incident. Before we can set this information the output (array) must be formatted (to string).

$Builder = [string]::Join("`r`n",$Array)
Set-SCSMObject -SMObject (Get-SCSMObject (get-SCSMClass System.WorkItem.Incident$) -Filter "Name -eq $IRID") -Property ‘Description’ -Value $Builder

For Service Requests:

For Service Request the same approach can be used to update the description property on the work item. Additionally, we need to do this also for the Activity’s related to the work item. The Get-SCSMRelatedObject cmdlet get’s the object, The output is filtered based on Service Request ID and the relationshipclass (System.WorkItemContainsActivity). In this example script all activity’s are updated, if you want to limit the update to only Review Activity’s you can extend the script with a filter on the Activity class.

$Builder = [string]::Join("`r`n",$Array)
Set-SCSMObject -SMObject (Get-SCSMObject (get-SCSMClass System.WorkItem.ServiceRequest$) -Filter "Name -eq $SRId") -Property ‘Description’ -Value $Builder
# Get the activities in the SR
$Activities = Get-SCSMRelatedObject -SMObject (get-scsmobject (get-scsmclass System.workitem.ServiceRequest$)|?{$_.Name -eq $SRId}) -Relationship (Get-SCSMRelationshipClass System.WorkItemContainsActivity)
foreach($activity in $activities)
{
    Set-SCSMObject -SMObject $activity -Property ‘Description’ -Value $Builder
}

All done on the scripting part! You can easily extend this script with other updates on work item properties. The best way to do this is to use PropertyHashtable method to update multiple properties on the work item. More information can be found on the cmdlet description – Set-SCSMObject.

Creation of the SCSM workflow via Authoring tool

The procedure to start the Authoring tool and create your management pack is already well documented. The steps to create the management pack for this workflow are simple : create the workflow, add a PowerShell Activity and save the MP.

Create the workflow

Right click on Workflows –> Click Create and follow the wizard. Specify a name for the workflow, optionally a description of the WF can be specified. On the Trigger Condition page, select “Run only when a database object meets specified conditions”.

image

On the Trigger Criteria page, specify the class name. For the event criteria the object created event is selected. Optionally you can specify additional criteria for this workflow. In the example WF an additional criteria is added to only run for portal created work items (Source=Portal).

image

Review Summary and click create. On the Completion page click Close to finalize the workflow creation wizard.

image

Add PowerShell Activity

This procedure requires to create 2 workflows, one for Incidents and one for Service Requests. For both workflows, drag-end-drop the Windows PowerShell Script Activity in the workflow. Navigate to the PS script Activity details and open the Script Body property from the Activity Inputs. This opens the “Configure a Script Activity” wizard. On the Script Body, expand the View and Edit Script view and Import or paste your script information in the script body.

image

On the Script Properties page, make sure that you configure the ID as input for the workflow. In this example the Incident ID is set to the IRId script parameter.

image

Depending on your needs you can do this for both classes or only for one class (if you only need this functionality for Service Requests for example). Save your management pack, copy the dll file(s) to the SCSM program folder of the mgmt server that executes the workflows and import the management pack. All done, ready for testing. The result of this solution is that you have nice formatted description fields of the work items that are created via the portal. In case of a Review activity, the Approver has all information to answer the request for approval. Example output below.

image

The output dll files and management pack created for this blog post can be downloaded via the TechNet Gallery. (http://gallery.technet.microsoft.com/SCSM-Portal-Userinput-to-4b9acee4)

Have fun!

Kurt Van Hoecke

A “little” SCSM DW journey from the “basics” al the way onto the “lastmodified by” property. Part 3 – “Stored Procedure”

This third part in this series will show how the “queries” are defined in SCSM by using stored procedures.

After this part you should be able to “translate” or adjust already existing stored procedures or at least “read” and understand how a stored procedure is set up in SCSM!.

I know this part will look like real hard core but again this part is only to understand a stored procedure!

Nerd smile

YOU Don’t have to fully write them from scratch!

Thumbs up

 

End goal

Remember our end goal is delivering a need report to present to the managers crying for reports! Winking smile

The list of the end goal report:

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Duration of the work items being in one state?
  4. Which people are responsible?
  5. When was the last time the work item was modified?
  6. Who did the last modification?

With part 1 “ Basic Queries” we are able to deliver 2 out of the 6 fields to present in our report.

  1. What work items are open?
  2. Date/time when the work items where logged

Part 2 “Getting those Relationships!” delivers you all related objects to and incident like:

Which people are responsible?

Part 3 will show you how to write a stored procedure to deliver the data for the report.

 

Stored Procedure

In SCSM reporting stored procedures are used to deliver data for reports. The reason for following this procedure is because the queries are performance wise a pain because of the many joins.

So delivering the query in a Stored Procedure (SP) will be far more efficient.

Explaining the Stored procedure and how it is been setup in SCSM.

Translate the SP “Servicemanager_Report_IncidentManagement_SP_GetListOfIncidents”

For the example I am going to use the default SP for a list of incidents which already has allot of information in them on the open incidents.

First you need to edit the already existing SP simply right click the SP and select modify to show the full SP:

image_thumb1_thumb

We are going to edit the SP.

Below is the standard stored procedure

Servicemanager_Report_IncidentManagement_SP_GetListOfIncidents

I have added extra comments to clarify the SP.

This is just for explanation on how the SP is setup.

 

USE [DWDataMart]

GO

/****** Object:  StoredProcedure [dbo].[ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents]    Script Date: 7/31/2013 8:28:54 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

ALTER PROCEDURE [dbo].[ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents]

    

/***** BEGIN Section Defining Parameters *****/

    

    @DateFilter    nvarchar(256) = null,

    @StartDate    datetime,

    @EndDate    datetime,

    @Source        nvarchar(max) = '-1',

    @Status        nvarchar(max) = '-1', 

    @Priority    nvarchar(max) = '-1',

    @Urgency    nvarchar(max) = '-1',

    @Impact        nvarchar(max) = '-1',

    @Classification nvarchar(max) = '-1',

    @SupportGroup nvarchar(max) = '-1',

    @ResolutionCategory    nvarchar(max) = '-1',

    @AssignedTo    int = null,

    @ContactMethod nvarchar(max) = null,

    @Description    nvarchar(max) = null,

    @ResolutionDescription nvarchar(max) = null,

    @RelatedCIs  nvarchar(max) = null,

    @ID  nvarchar(max) = null,

    @IncludeDeleted bit = 0,

    @LanguageCode nvarchar(max)= 'ENU'

 

/***** END Section Defining Parameters *****/

 

AS

BEGIN

  SET NOCOUNT ON

 

  DECLARE @Error int

  DECLARE @ExecError int

 

  SET @EndDate = DateAdd(Day, 1, @EndDate)

 

/***** BEGIN Section Creating Temp Tables *****/

 

  DECLARE @tableID TABLE(value nvarchar(256))

  INSERT @tableID (value)

  Select * FROM dbo.fn_CSVToTableString(ISNULL(@ID, ''))

 

  DECLARE @tableSource TABLE (value nvarchar(256))  

  INSERT @tableSource (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Source) 

 

  DECLARE @tableStatus TABLE (value nvarchar(256))  

  INSERT @tableStatus (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Status)

  

  DECLARE @tablePriority TABLE (value nvarchar(256))  

  INSERT @tablePriority (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)

  

  DECLARE @tableUrgency TABLE (value nvarchar(256))  

  INSERT @tableUrgency (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Urgency)

  

  DECLARE @tableImpact TABLE (value nvarchar(256))  

  INSERT @tableImpact (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Impact)

 

  DECLARE @tableClassification TABLE (value nvarchar(256))  

  INSERT @tableClassification (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)

 

  DECLARE @tableSupportGroup TABLE (value nvarchar(256))  

  INSERT @tableSupportGroup (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@SupportGroup)

  

  DECLARE @tableResCategory TABLE (value nvarchar(256))  

  INSERT @tableResCategory (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@ResolutionCategory)

  

  DECLARE @tableRelatedCIs TABLE (value nvarchar(256))  

  INSERT @tableRelatedCIs (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)

 

/***** END Section Creating Temp Tables *****/

 

/**** BEGIN Actual SQL Query *****/

 

/***** BEGIN Select Statement *****/

  

SELECT DISTINCT

I.IncidentDimKey,

I.CreatedDate,

Description=NULL, 

I.ResolvedDate, 

I.Priority, 

I.Id, 

I.Title,

 

Source = ISNULL(SourceDS.DisplayName, SourceEnum.IncidentSourceValue) ,

SourceEnum.IncidentSourceId AS SourceId,

 

Status = ISNULL(StatusDS.DisplayName, StatusEnum.IncidentStatusValue) , 

StatusEnum.IncidentStatusId AS StatusId, 

 

Impact = ISNULL(ImpactDS.DisplayName, ImpactEnum.IncidentImpactValue),

ImpactEnum.IncidentImpactId AS ImpactId,

 

AssignedTo.UserDimKey AssignedToUserId,

AssignedTo.DisplayName AssignedToUserName

 

/***** END Select Statement *****/

 

FROM         

    

/***** Incident View ******/

 

    dbo.IncidentDimvw I 

 

/***** Incident View ******/

 

/***** JOIN INCIDENTDIMVW ON WORKITEMDIMVW in order to retrieve WorkItem relations:

     - WorkItem Assigned to User

     - About Config Item

    *****/

    

    INNER JOIN dbo.WorkItemDimvw WI

    ON I.EntityDimKey = WI.EntityDimKey

    

/***** JOIN INCIDENTDIMVW ON WORKITEMDIMVW ******/

 

/***** RETRIEVE ASSIGNED TO USER *****/

 

    /***** join WorkItemDimvw on WorkItemAssignedUserFactVw to Retrieve Assigned User Dimkey *****/

 

    LEFT OUTER JOIN    

    dbo.WorkItemAssignedToUserFactvw

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (@IncludeDeleted = 1 OR dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)

    

    /***** Join WorkItemAssignedToUserFactvw on UserDimvw to retrieve Assigned to User DisplayName *****/

 

    LEFT OUTER JOIN

    dbo.UserDimvw AS AssignedTo 

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 

    

/***** RETRIEVE ASSIGNED TO USER *****/

 

/***** RETRIEVE ABOUT CONFIG ITEMS *****/

 

    LEFT OUTER JOIN 

    dbo.WorkItemAboutConfigItemFactvw ON 

    dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (@IncludeDeleted = 1 OR dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)

    

    LEFT OUTER JOIN 

    dbo.WorkItemAboutConfigItemFactvw CIFctForFilter ON 

    CIFctForFilter.WorkItemDimKey = WI.WorkItemDimKey 

    AND (@IncludeDeleted = 1 OR CIFctForFilter.DeletedDate IS NULL)

 

/***** RETRIEVE ABOUT CONFIG ITEMS *****/

 

/***** RETRIEVE INCIDENT SOURCE *****/

 

    LEFT OUTER JOIN

    dbo.IncidentSourcevw AS SourceEnum 

    ON SourceEnum.IncidentSourceId = I.Source_IncidentSourceId  

    

    /**** join on displaystringvw to get the displayname of the source enum *****/

 

    LEFT OUTER JOIN 

    dbo.DisplayStringDimvw SourceDS

    ON SourceEnum.EnumTypeId=SourceDS.BaseManagedEntityId

    AND SourceDS.LanguageCode = @LanguageCode

 

/***** RETRIEVE INCIDENT SOURCE *****/

 

/***** RETRIEVE INCIDENT STATUS *****/

    

    LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId 

    

    /**** join on displaystringvw to get the displayname of the Status enum *****/ 

    

    LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = @LanguageCode

 

/***** RETRIEVE INCIDENT STATUS *****/

 

/*****RETRIEVE INCIDENT IMPACT *****/

    

    LEFT OUTER JOIN

    dbo.IncidentImpactvw AS ImpactEnum 

    ON ImpactEnum.IncidentImpactId = I.Impact_IncidentImpactId

 

    /**** join on displaystringvw to get the displayname of the Impact enum *****/ 

    

    LEFT OUTER JOIN 

    dbo.DisplayStringDimvw ImpactDS

    ON ImpactEnum.EnumTypeId=ImpactDS.BaseManagedEntityId

    AND ImpactDS.LanguageCode = @LanguageCode

 

/*****RETRIEVE INCIDENT IMPACT *****/

 

WHERE

 

/**** FILTERS FOR THE SQL QUERY all @..... values are the parameters of the report *****/

 

(

    (@DateFilter = 'ResolvedOn' AND ((I.ResolvedDate >= @StartDate) AND (I.ResolvedDate < @EndDate))) OR

    (@DateFilter = 'ClosedOn' AND ((I.ClosedDate >= @StartDate) AND (I.ClosedDate < @EndDate)))  OR

    (@DateFilter = 'CreatedOn' AND ((I.CreatedDate >= @StartDate) AND (I.CreatedDate < @EndDate))) OR

    (@DateFilter = 'All')

) AND 

(@StartDate <= @EndDate) AND

((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource))) AND

((-1 IN (Select value from @tableStatus)) OR (I.Status_IncidentStatusId IN (Select value from @tableStatus))) AND

((-1 IN (Select value from @tableImpact)) OR (I.Impact_IncidentImpactId IN (Select value from @tableImpact))) AND

((-1 IN (Select value from @tableUrgency)) OR (I.Urgency_IncidentUrgencyId IN (Select value from @tableUrgency))) AND

((-1 IN (Select value from @tableClassification)) OR (I.Classification_IncidentClassificationId IN (Select value from @tableClassification))) AND

((-1 IN (Select value from @tableSupportGroup)) OR (I.TierQueue_IncidentTierQueuesId IN (Select value from @tableSupportGroup))) AND

((-1 IN (Select value from @tablePriority)) OR (I.Priority IN (Select value from @tablePriority))) AND 

(

    (-1 in (Select value from @tableResCategory)) 

    OR 

    (

        I.ResolutionCategory_IncidentResolutionCategoryId IN (Select value from @tableResCategory) 

        OR 

        (    

            (I.ResolutionCategory_IncidentResolutionCategoryId IS NULL) 

            AND 

            ('' IN (Select value from @tableResCategory))

        )

    )

) AND

((@AssignedTo = 0) OR AssignedTo.UserDimKey = @AssignedTo) AND

((@ID IS NULL) OR (I.Id IN (Select value from @tableID)))

AND ((0 IN (select value from @tableRelatedCIs)) OR (CIFctForFilter.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)))

 

/**** END Actual SQL Query *****/

 

  SET @Error = @@ERROR

 

QuitError:

  

  RETURN @Error

END

This is the standard SP which exists in SCSM DWDatamart.

 

Just a little background on the comments:

/***** BEGIN Section Defining Parameters *****/

This section defines the parameters in the report:

image

 

/**** BEGIN Actual SQL Query *****/

This section defines the SQL query to retrieve the data from the database.

/**** FILTERS FOR THE SQL QUERY all @….. values are the parameters of the report *****/

This section covers the “Filters” of the SQL query.

which is described in the next section.

Filters

Up until now I have shown how to query for data from the Data Warehouse. The query just pulled ALL data from the database. Although this works you would normally just get a subset of the data like data from a particular time frame or only data with a specific priority etc. in other words filter the data.

The filters are defined after the WHERE clause  let’s take a simple query to explain:

Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

This will retrieve ALL data from the IncidentDimvw.

image

Now let’s say we ONLY want the incidents with the status Closed?

We need to add  the following line:

Where StatusEnum.IncidentStatusValue = 'Active'

This will retrieve all Incidents from IncidentDimvw WHERE status is Active!

You can use this same approach to get any filtered results you want.

Another option is to get only data after a specifc create date:

Where StatusEnum.IncidentStatusValue = 'Active'

    AND I.CreatedDate > '2013-07-25'

This means you can filter the results any way you want.

Going the Extra Mile on filters / Parameters and Temp Tables (OPTIONAL!)

Now in the default query we are also filtering the results but the results are filtered based on the parameters given when you run the report. (These are all parameters with @ in front of them).

So basically you would say :

where status = @IncidentStatus.

Now there is a little catch here. In the stored procedures provided by SCSM the results of the parameters are stored in temp tables (which are tables used temporarily when you run the report they are store in memory on the SQL server when you run the report).

You don’t need to know the details but let’s quickly look at how the temp tables are defined:

DECLARE @tableSource TABLE (value nvarchar(256))  

  INSERT @tableSource (value)

  SELECT * FROM dbo.fn_CSVToTableInt(@Source) 

This tells SQL to create a temp table @TableSource and place the results from the selected parameter(s) @Source into this table.

Now if you look at the filter in place when you want to filter the results on Source:

((-1 IN (Select value from @tableSource)) OR (I.Source_IncidentSourceId IN (Select value from @tableSource)))

Which the first statement is when you select nothing and leave the parameters default. (before the OR)

The second statement is used when you select one or more Sources in the @Source paramter.

I know this all looks really complex but in reality it isn’t just try to read these. This is only required if you want to create a report with extra parameters instead of the only provided ones.

Since we will edit the already installed stored procedure this isn’t required so you are safe for now Smile

recap

Now you understand how to read the stored procedures presented in SCSM DWDatamart.

You can use this knowledge in combination with the post from Kurt – http://www.authoringfriday.com/2013/05/10/creating-custom-reports-in-scsm/ to come up with a little custom report.

For our requirement as stated in the beginning of this series you need to be able to “read” the SP no need to fully write them!

The next part 4 of this series I will explain how to get the other required properties and how to combine them in the SP!

 

Cheers,

Oskar Landman

A “little” SCSM DW journey from the “basics” al the way onto the “lastmodified by” property. Part 2 – “Getting those Relationships!”

 

The  first part of this series showed you how to write simple queries to get data from you DWDatamart database (or any other database). And why you need to join 3 views to be able to get the displayname of the lists (enumerations) in SCSM!

This part will show you the more advanced queries to get the different relationships from a incident in the Datawarehouse.

End goal

Remember our end goal is delivering a need report to present to the managers crying for reports! Winking smile

The list of the end goal report:

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Duration of the work items being in one state?
  4. Which people are responsible?
  5. When was the last time the work item was modified?
  6. Who did the last modification?

With part 1 “ Basic Queries” we are able to deliver 2 out of the 6 fields to present in our report.

  1. What work items are open?
  2. Date/time when the work items where logged

And we are able to deliver much more “standard” information from an incident to get already a real reporting experience.

This part will focus on how you can add the responsible people of the incident and a little “extra” how to get the related CI’s.

Understanding the data model

Before we can start writing the Query you need to familiarize yourself with the data model used in SCSM.

Here a version of the 2010 data model For the Service Manager Database and DWDatamart (which is mostly the same for 2012) Have fun! Smile

Service Manager Database

image

DW Datamart (Incident)

image

This makes sense doesn’t it? Nerd smile

Now let’s start writing the query.Nyah-Nyah

 

Hmm maybe some more details would be nice Winking smile

First of all the data model is based on 2010 so all new classes / relationships created since 2012 are not in the Visio diagrams!!!

You can download the Visio Diagrams from :

 http://blogs.technet.com/b/servicemanager/archive/2011/02/01/model-visio-diagram-released-job-aids-package-update-update-on-resource-kit.aspx

You need to have the DWDatamart Visio which has the model for each workitem / config item.

But before we dive into the Visio’s let’s first take a step back and look at the data model.

Data Model

Now SCSM is build on the same framework as SCOM so any information / knowledge on the SCOM Data Model will greatly benefit!

All classes in SCSM are derived from the base class named “Object” which is the highest base class.

Mainly there are 2 main abstract classes beneath this class in SCSM for “Work Items” and “Configuration Items”.

Let’s take a look at how it all ties together when looking at an incident:

image image
  • As stated an object is the base class in SCSM and all objects are derived from this class.
    Work Item class is created with a base class of Object class.
    Incident Class is created with a base class of Work Item.
In SCSM all work Items like Incident, Change request, Service Request are created from the base class Work Item.

What does this mean?

This means an Incident = Work Item = Object

Change Request = Work Item = Object

Service Request = Work Item = Object

Now when you look at properties and relationships on a class it is crucial you understand how a class gets it’s properties.

When you look at an incident class it has the properties and relationships defined specific for the Incident class AND all Work Item class properties and relationships AND all Object Class properties  and relationships .

This means every object in SCSM which has a base class of work item class (like Change Request, Service Request etc.) has beside it’s own properties also all properties from object class and Work Item Class.

Below is an overview of all class properties defined for the specific class.

image

When you open the Incident class in the authoring tool and look at the properties and relationships you will notice all properties in above sheet are shown on the Incident Class properties (Except the internal ones from the object class).

The information of where these relationships are defined (on which class) is crucial to be able to pull the information!!!

Let’s take a look the properties of the Incident class to get the relationship we want:

image

When you select the relationship in the authoring console you need to take a look at the details of the relationship and specifically the SOURCE this will tell you the source class of the relationship, Work Item.

So this relationship we want is not defined on the Incident class but on the work item class!

You don’t have to learn this stuff by hard but you should be aware of the fact Relationships are defined at the specific class and how the high level of the data model is designed

Incident = Work Item = Object

If you understand this concept you can find anything in the DWDatamart and you can really understand the impact of a class extension or DW extension.

If you want to know more on the data model Chapter 16 and 18 of System Center Service Manager 2010 Unleashed explains in detail the data model of SCSM.

For SCOM reference System Center Operations Manager 2012 Unleashed which is basically the same data model as for SCSM.

Getting the relationships

When you look at the DWDatamart Visio this is the diagram of how the relationships are defined in the DWDatamart database views. When you look at the incident class you will notice ONLY the relationships for the Incident class :

image image
These are all Relationships defined on the Incident class. I have marked the ones which are in the DWDatamart. Here are the views you will need to be able to pull the information from the DWDatamart Database

Let’s take a better look at the Incident class in the DWDatamart Visio:

So basically if we want the Primary Owner we need to start joining views again:

image

We need to join the IncidentDimvw with IncidentHasPrimaryOwnerFactvw by IncidentDimkey = IncidentDimkey

and we need to join IncidentHasPrimaryOwnerFactvw with UserDimvw by IncidentPrimaryOwner_UserDimkey = UserDimkey

Now you should be able to pull the Primary owner with a query:

The Joins:

Left Outer JOIN

    dbo.IncidentHasPrimaryOwnerFactvw 

    ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey

    AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)

 

 

    LEFT OUTER JOIN

    dbo.UserDimvw PrimaryOwner

    ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey

This will get the primary owner of an incident.

Notice the “AND deletedate IS Null” the reason for adding this line is we only want the current owner! When you change the owner of an incident this table is updated with the new owner and the old owner will be “flagged” as deleted.

 

Now combined with the query we earlier created:

Select 

I.Id,

I.Title,

I.CreatedDate,

StatusDS.DisplayName as Status,

PrimaryOwner.DisplayName as PrimaryOwner

 

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = 'ENU'

 

    Left Outer JOIN

    dbo.IncidentHasPrimaryOwnerFactvw 

    ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey

    AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)

 

 

    LEFT OUTER JOIN

    dbo.UserDimvw PrimaryOwner

    ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey

 

 

Order by I.CreatedDate desc

 

 

Will result in a query delivering the Incident Owner as well (if there is one):

image

 

This actually looks easy….so why did I explain the whole data model part….well now you can only get two relationships:

Primary Owner

Resolved By User

These are nice but you want the related CI’s as well or any other relationship of an incident.

Getting the others

To get the other relationships you need to be aware of where those relationships are targeted. In the example shown Incident = Work Item = Object we have seen these relationships life at the Work Item.

We need to travel one level up in our query to be able to get to these relationships.

To “travel” one level up we are going to join the view IncidentDimvw with WorkItemDimvw:

INNER JOIN dbo.WorkItemDimvw WI

ON I.EntityDimKey = WI.EntityDimKey 

With this join we are now able to get to those other relationships because we can now use the WorkItemDimvw to get to these by using WI.

Now you can use the same approach as with the incidentDimvw and IncidentFact views with WorkItemDimvw.

To get the assigned to user:

LEFT OUTER JOIN    

dbo.WorkItemAssignedToUserFactvw

ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 

AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)

 

LEFT OUTER JOIN

dbo.UserDimvw AS AssignedTo 

ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 

The first join is on the WorkItemAssignedTouserFactvw, these kind of fact views hold the relationships.

So you first need to get the relationship and after this you are able to join this with the UserDimvw to get to the Display Name of the user!

Now let’s join the other joins with the existing joins in our query:

Select 

I.Id,

I.Title,

I.CreatedDate,

StatusDS.DisplayName as Status,

PrimaryOwner.DisplayName as PrimaryOwner,

AssignedTo.DisplayName as AssignedUser

 

From IncidentDimvw I

    

    INNER JOIN dbo.WorkItemDimvw WI

    ON I.EntityDimKey = WI.EntityDimKey

    

    LEFT OUTER JOIN    

    dbo.WorkItemAssignedToUserFactvw

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)

    

    LEFT OUTER JOIN

    dbo.UserDimvw AS AssignedTo 

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 

    

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = 'ENU'

 

    Left Outer JOIN

    dbo.IncidentHasPrimaryOwnerFactvw 

    ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey

    AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)

 

 

    LEFT OUTER JOIN

    dbo.UserDimvw PrimaryOwner

    ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey

 

 

Order by I.CreatedDate desc

 

 

The results:

image

 

You are now also able to get the affected CI’s:

LEFT OUTER JOIN 

    dbo.WorkItemAboutConfigItemFactvw

    ON dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)

    

    LEFT OUTER JOIN

    dbo.ConfigItemDimvw AboutCon

    ON AboutCon.ConfigItemDimKey = dbo.WorkItemAboutConfigItemFactvw.WorkItemAboutConfigItem_ConfigItemDimKey

 

Now let’s write the Query containing this Joining extravaganza! Smile with tongue out

Select 

I.Id,

I.Title,

I.CreatedDate,

StatusDS.DisplayName as Status,

PrimaryOwner.DisplayName as PrimaryOwner,

AssignedTo.DisplayName as AssignedUser,

aboutcon.DisplayName as AboutConfigItem

 

From IncidentDimvw I

    

    INNER JOIN dbo.WorkItemDimvw WI

    ON I.EntityDimKey = WI.EntityDimKey

    

    LEFT OUTER JOIN    

    dbo.WorkItemAssignedToUserFactvw

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)

 

    

    LEFT OUTER JOIN

    dbo.UserDimvw AS AssignedTo 

    ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey 

    

    LEFT OUTER JOIN 

    dbo.WorkItemAboutConfigItemFactvw

    ON dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey 

    AND (dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)

    

    LEFT OUTER JOIN

    dbo.ConfigItemDimvw AboutCon

    ON AboutCon.ConfigItemDimKey = dbo.WorkItemAboutConfigItemFactvw.WorkItemAboutConfigItem_ConfigItemDimKey

 

    LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = 'ENU'

 

    Left Outer JOIN

    dbo.IncidentHasPrimaryOwnerFactvw 

    ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey

    AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)

 

 

    LEFT OUTER JOIN

    dbo.UserDimvw PrimaryOwner

    ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey

 

 

Order by I.CreatedDate desc

 

 

The result:

image

You are now able to get the other relationships as well!

The initial question was:

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Duration of the work items being in one state?
  4. Which people are responsible?
  5. When was the last time the work item was modified?
  6. Who did the last modification?

Now we are able to deliver:

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Which people are responsible?

The next part will describe how to setup the stored procedure and explain what is in there. After the stored procedure I will explain the duration.

Recap

Part 2 of this series showed how the data model is defined and how you can get the relationships into your query.

Now we are able to pull any relationship and property from the DWDatamart!

The next part will explain the stored procedures used in SCSM reporting.

Stay tuned for part 3 – “ Stored Procedures”

Regards,

Oskar Landman

A “little” SCSM DW journey from the “basics” al the way onto the “lastmodified by” property. Part 1 – “Basic Queries”

A really common requirement for SCSM reporting is the ability to report on a daily or weekly basis on the “state” of the total open incidents, changes and service requests.

IT managers want to be in control to be able to “manage” the different processes which are one of the  core reasons for their existence. Winking smile

To keep it simple all they wanted to know is :

  1. What work items are open?
  2. Date/time when the work items where logged?
  3. Duration of the work items being in one state?
  4. Which people are responsible?
  5. When was the last time the work item was modified?
  6. Who did the last modification?

and lastly “Why are these work items still open?” which is an open question they will ask the process managers after they receive the report.

They don’t want to run these reports themselves so we need to design a report which does the job and can be scheduled.

Well this shouldn’t be to hard, right?

Introduction

Let me point out the Little in the title is a big understatement! Angry smile

You need to combine / join allot of views in the DW to come up with the information they request but you will eventually get all of them except “LastModified by”.

And in the end they will ask for more fields in the report instead of 6 we pointed out.

To fully explain the issue I will write the entire Stored procedure, step by step.

The procedure will be part of a series of Blog Posts on reporting in SCSM! Hot smile

After these series you are able to update the DW with extra dimensions and write advanced stored procedures and reports! Stay Tuned!

Not only this but since allot of people struggle with writing a report in general I will go from the basics to in the end the hard stuff!

 

Explanation: Retrieving the correct information

First I will explain how to retrieve data from the SCSM DWDataMart Database.

Views

When it concerns SCSM reporting you need to use the views, these are ideal to report against and a best practice is to use the views.

To start you can review the information and different views to give and understanding of the information in there:

image image
You can navigate the views of the DWDataMart database to understand all the different views. You can expand the view to show the columns in the views.
image image
If you want to show the records in the views simply right click and select “Select Top 100 Rows”. Here you can view the actual rows and information in the view. You can see most information is very cryptic.
   

 

You can now “browse” the information in the DWDataMart database and look at all the information in there.

Next I will show how to write a simple query to retrieve simple information.

Retrieve Simple Incident Information

First start a new query and make sure you select the correct database:

image

We need to retrieve information on incidents so we start with the view IncidentDimvw

Start of with a simple query to select All columns from the IncidentDimvw:

Select *

From IncidentDimvw

This will result in all information from the IncidentDimvw:

image

Let’s be more specific and select only the following common columns :

  • Incident ID
  • Incident Title
  • Incident Create Date

Notice the query editor will automatically select “possible” columns:

image

The query:

  1. Select Id, Title, CreatedDate

    From IncidentDimvw 

  2. The result:
  3. image
  4. You can select any of the columns in your select statement!
  5. Easy right?
  6. You can now already write a simple query to collect any information in a view!
  7. Try a couple of different queries and views to make yourself familiar with this concept.

Soon you will discover information for lists is stored by it’s internal ID instead of a readable string.

Like Status below:

Now let’s take a look at the Status of an incident.

image

Now this is where it gets tricky the status is shown as the Internal ID, this is not the wanted result!

You need to “translate” the internal ID to a readable string. The next section will exactly show you how.

Retrieve Incident Status

Basically all data in the DWDatamart is stored in the same way. So once you understand how to retrieve the actual displayname and why you need to join 2 tables you can retrieve any data in the DWDataMart Database.

Let’s take a closer look at the incident views in the DWDataMart and in particular IncidentStatusvw:

IncidentDimvw IncidentStatusVw
image image

Notice the IncidentStatusvw which has the same columns as the IncidentDimvw.

IncidentDimvw

image

IncidentStatusvw

image

Now because this information is in two views we want to join these 2 views in the end result.

In order to combine views you need to have a common identifier in both views.

For the IncidentDimvw and IncidentStatusvw this would be these columns. We are going to use Status_IncidentStatusId and IncidentStatusId.

Now how to combine the views and retrieve the information:

The query until now:

Select I.Id, I.Title, I.CreatedDate, I.Status

From IncidentDimvw I

Notice I have added a “I” after the view IncidentDimvw!

By placing the I after IncidentDimvw you simply tell SQL to use “I” as a replacement for the IncidentDimv. You can use any character or multiple characters for identifying the view. Use something which makes sense since you need to re-use these when the query becomes more complex.

So now we can refer to IncidentDimvw by I.

Joining the 2 views in the Query:

Select I.Id, I.Title, I.CreatedDate, I.Status

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

This translates to:

Join view IncidentsStatusvw and name the IncidentStatusvw StatusEnum. (As StatusEnum).

Join the view ON IncidentStatusvw column IncidentStatusId is similar to InicdentDimvw column Status_IncidentStatusId.

Now you can use the combination of the two views:

image

Smile Cool this is the result we wanted! Smile

Well that’s it right?

Not really we need to do one more join in order to get the “Display Name” of the enumeration!

Why? Well Since SCSM is bilingual you want to be able to translate the report as well. When you run the solution in a non English environment they need to be translated.

Also not every Enumeration has a value as the status if you review the classification of an incident IncidentClassificationvw you will notice the value column is not the wanted result:

image

In order to get the displayname of an enumeration (list) object in DWDataMart you will need the view DisplayStringdimvw.

The results of the DisplayStringvw contains the translation in all supported languages:

image

But how to join?

Well every object in SCSM has a BaseManagedEntityId or Guid to identify an object.

We need to join the StatusDimvw with the DisplayStringvw on this column to join the two views!

There is a little catch here since the BaseManagedEnityId in the DisplayStringvw is the same as EnumTypeId in the IncidentStatusvw.

The query including the join on DisplayStringvw:

Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

Notice we name the DisplayStringvw to StatusDS and do a similar join on the two identical columns.

When you now use the new join in your select:

image

hmmm now we get the result in every language! This is not the result we want Sad smile

There is one more step to retrieve only the display strings of the language we are interested in let’s use Dutch as an example.

Remember there is a language column in the DisplayStringvw view? We are going to add an extra expression on the DisplayStringvw join.

Since we tell to do the join of the DisplayStringvw when BaseManagedEnityId equals EnumTypeId we simply add an AND to include the Language column as well:

Select I.Id, I.Title, I.CreatedDate, StatusEnum.IncidentStatusValue, StatusDS.DisplayName

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = 'NLD'

Translate: Join DisplayStringvw on EnumTypeID equals BaseManagedEntityID and column LanguageCode equals NLD which is the language code for dutch.

Result only the dutch displaystrings are shown! :

image

You can change the language code to your prefered lanuage. If you want to know the codes? Simply take a look at the DisplayStringvw results:

image

Now let’s pimp the query some more to give the column names a meaningfull name and order them by createdate (desc or asc):

Select 

I.Id,

I.Title,

I.CreatedDate,

StatusDS.DisplayName as Status

 

From IncidentDimvw I

 

LEFT OUTER JOIN

    dbo.IncidentStatusvw AS StatusEnum 

    ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId

 

        LEFT OUTER JOIN 

    dbo.DisplayStringDimvw StatusDS

    ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId

    AND StatusDS.LanguageCode = 'NLD'

 

Order by I.CreatedDate desc

image

Now you should be able to follow the similar procedure to retrieve other values from the IncidentDimvw in the same way:

  • IncidentClassificationvw – Classification of an incident
  • IncidentImpactvw – Impact of an incident
  • IncidentResolutionCategoryvw – Resolution Category of an incident
  • IncidentSourcevw – Source of an incident
  • IncidenTierQueuesvw – Supportgroup of an incident
  • IncidentUrgencyvw – Urgency of an incident

Recap

Part 1 showed you how to create simple queries to retrieve data from the DWDatamart database. You are now able to pull already allot of information from the Datawarehouse and these are the basics for creating and understanding SCSM reports.

Although this is specific to SCSM you can use the same knowledge to query data from any database!

The next part will explain how to get to the other objects like related CI’s etc.

Stay tuned for part 2 – “Getting those Relationships!”

 

Regards,

Oskar Landman

Creating custom reports in SCSM

Hello,

Creating reports is not an easy task; in System Center Service Manager this task is not simplified, you need to have some SQL reporting knowledge to be able to build them. If we take the deployment of the custom reports via management pack into account it is even more difficult to go through the procedure and successfully deploy the reports.

This blog will explain how you can build custom reports that are based on an existing report of Service Manager. The following steps need to be taken in order to get the custom reports working in the SCSM console, without deploying them via mgmt pack:

  • - SQL stored procedure (SP) will need to be adjusted to include the necessary values.
  • - Report needs to be adjusted to have the additional columns of information.
  • - SQL Stored Procedure and reports need to be deployed.

Next procedure will guide you through the steps to create a custom report. The “list of changes” report will be used as base report for the example report that will be build in this procedure. This report will be adjusted to include all Review Activities that are part of the Change. Let’s start…

Creating/updating the SQL stored procedure

Navigate in the SQL Management Studio to the DWDataMart Database, expand Programmability folder, expand Stored Procedures folder

Right click on the “ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install” SP, select Script Stored Procedure as, select CREATE To and select New Query Editor Window.

 image

The query will be opened in a new pane, you need to adjust the name of the SP before you can execute the creation and start modifying the SP.  For this procedure the name is adjusted to:

CREATE PROCEDURE [dbo].[ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities]

image

After changing the name you can start modifying the SP. To get the needed information in the SP you need to update the “SELECT”, “FROM” and “WHERE” sections in the Query. Right click on the new created SP and select Modify. For the example report that will be created we need to have to include review activity information. This code is added in the FROM of the query to get this additional information.

image

The SELECT section in the query is updated to include the additional values.

image

In the WHERE section of the query you can filter the information. A filter on the ActivityType can be added to exclude these kind of activity entries in the report. Following code is added in the example query:

ActivityType.DisplayName NOT LIKE (‘%Extension%’)

image

All changes for the example SP are made and the query can be executed. This will make the changes in the SP, the only thing that need to be done is to make sure that reportuser role in SQL can execute the SP. click on the new created SP, and select Properties.

  • - Select Permissions, in the Permissions pane click on Search button
  • - Add “reportuser” in the selected objects (check names), click OK
  • - Grant Execute rights for the report user

image

All done for this part.

As you can read, the information you need to add in the query is depended on what you need to present in the report. This example is getting the review activities, another could be to get all Incidents that are related to the Change. The same procedure can be applied to other mgmt function reports. Copy the SP, modify the query and update permissions.

To test your SP you need to remove the parameter information from the query. Click Modify on the SP you have created. In the begin of the SP you need to remove All “@” and DECLARE lines. Your SP should look like this:

image

In the WHERE section of the SP you can remove all parameter information, you own filters can stay in the SP.

image

Execute the query in order to update the SP. After this update you should be able to execute and test your SP. Once you have finished testing, you can copy and paste the parameter information back in your SP. Click Modify on the SP, copy the parameter information from your “source” SP and execute the query. In this way your SP includes the needed information for SCSM reporting.

SCSM custom report creation

To create the custom report you can use the same approach. Start from an existing report, adjust the SP and update the column definition to include the needed information. Run through next procedure to create a custom report.

Start Report Builder and open an existing report. Navigate to your reportserver website (http://<YourServer>/ReportServer) and browse to the “source” report. In this example  the following reporting is used as starting point for the custom report: ServiceManager.Report.ChangeManagement.ListOfRFCs

In the Report Data pane (left of the Report Builder console), expand Datasets and right click on the Table_RFC dataset, select Dataset Properties.

On the Query page of the Dataset properties update the dataset to point to the new created SP. Select the new SP in the Select or Enter stored procedure name dropdown (ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities)

image

Click on the Refresh Fields button and click Ok.

The fields in the dataset should be updated with the additional fields defined in the selected SP.

image

If you haven’t done it yet, save your report with another name on the report server. (The example report is saved as “Changes with Activities list”)

Now you can update the columns in the report. For this report we don’t need all time information, these columns can be deleted. The Activity Id, status and activity type columns need to added. To do this you can right click and select Delete Columns to remove the unneeded columns. Right click on the last column, select Insert Column and click Right to add columns.

image

In the new column, hover over the white space of the column (an icon should appear in the upper right of the white area) and click on the icon. The list of dataset properties pops-up from where you can select the property to bind in to the report column. Same steps need to be taken to include the other properties from the dataset. Final result for the example report is illustrated below.

image

Save the report to the SQL report server. At this point we have all SQL reporting configuration done if we want to deploy the report with a management pack. The detailed procedure how to do this is explained here.

The purpose of this blog is to illustrate how you can deploy the custom reports without management pack. The missing part in this solution is the Report Parameter Definition Language  (.rpdl) file for the custom report. This is not a difficult part; on the SQL reporting server (in this case the ‘”ListOfRFCs” report) download the .rpdl file from the source report  and rename this file to the same name as the new report. Upload the file in the same directory on the reporting server.

image

Both files are now on the report server and the job is done.

image

Report in SCSM console (restart your SCSM console if the report is not directly appearing in the folder)

image

All “default” report parameters are included in the report

image

Report created and working!

image

SQL Stored Procedure and report deployment

If you deploy the reports via management pack then you don’t need any additional steps to get the reports working in another environment. All is done via the MP deployment. In this procedure the SP, report and Report Parameter Definition Language (rpdl) file needs to be downloaded and uploaded in the new environment. For example if you are building your reports in a development environment and need to get them in the production environment.

Stored Procedure

Via SQL management Studio you can create a deployment script. Right click on the SP, select Script Stored Procedure as, Select CREATE TO and click on File… Provide a name for the query and click on Save.

image

This file can be used to deploy the SP in another environment.

Report and Report Parameter Definition Language file

You can download the files from the reporting server. The report file needs to be adjusted to point to the correct reporting server (all this is done if you deploy via MP. I need to check if we can avoid this step…). Open the report file (rdl) and navigate to the end of the file. Update the ReportServerUrl tag in the file with your reporting server.

image

Create the SP via the file, upload the report files and custom report is deployed!

Hope this helps creating you own report in SCSM. I like the cube functionality, but in a lot of deployments the people want to have it in a standard report. Following the procedure in this blog you can easily create and update custom reports without the need to delete/import a management pack.

Have Fun!!

Kurt Van Hoecke

Older Posts »