Authoring Friday

Every friday, adventures in System Center authoring

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

SCSM–SCOM Business Service Extension

With the integration of  SCOM and SCSM we can leverage our DA (Distributed Applications) created in SCOM into SCSM. Which is great but how can you extend the properties of a Business Service?

When you first start with this synchronization the first part you get stuck would be how to setup the connection which is a different topic. This post will describe what to do when you have your Services in SCSM and want to add custom properties to these services.

This post is created and dedicated to the famous Anders Asp Hot smile  http://www.scsm.se/ aka. @SCSMSE  (Famous…Yes!….They even named a framework after him Smile)

Understanding the “Business Service” in SCSM When you look at the business services in SCSM it all looks really simple, right?

You can either create the Business Service Manually from within SCSM or synchronize them form SCOM. It is the same, right?

Well for the end users yes they are the same! But for us the guys needing to create extensions add forms, and eating XML…..that’s another story:

To answer this question you need to open up the MP’s and put on your XML gear and dive in!

To understand what exactly happens on the back ground I have added the class structure. The figure below shows the classes the top is the base class “Object” below are all “child” classes al the way to the business service:

image

This is the structure let’s take a look at SCSM and synchronize a Distributed Application from SCOM to SCSM.

After checking the DA was synchronized now create a second “Business Service by clicking the Task “create Service”.

When you review the Business Service View from the SCSM console you see 2 “Business Services”.

This all looks really transparent, right? Or maybe there something more to it? Yes up until now this is what a user would experience. To the user this is all transparent and he will see 2 business services.

This is perfectly correct since to the user a business service is a business service. From our world as authoring freaks there is far more to it.

Let’s take a look how it really is designed in SCSM:

image

Unlike what it looks like to the user above picture shows how all components really live in SCSM. This is where the difference between both components lives, I will explain each component:

Business Service View

The view in SCSM is target at System.Service which makes it possible to show both child classes beneath the System.Service Class. This gives the console user the experience of looking at one “Business Service”. While in reality he is looking at multiple classes.

SCOM Distributed Applications Synchronized

In SCOM a Distributed application “Manually created in SCOM” lives in the class User Created Distributed Application. (Not manually created, really depends on the management pack. Most of the times it will live as a system.service. But this will bring in the same problem.)

SCSM Business Service Manually created

A manually created Business Service in SCSM (Created by the task “Create Service”) will live as a business service in SCSM.

Now why did they create a new class Business Service Class?

Why didn’t they re-used the already existing class and extended this one?

The answer to both these questions lies a little deeper and to understand you need to really examine the classes and their properties.

First remember an abstract class is not extendable nor can you add an abstract class instance manually in SCSM!

The picture below shows you exactly which classes are abstract and which are not. So basically state which classes you can extend and which classes you cannot extend:

image

Now you understand why they needed to create the extra class. All other classes are abstract and therefore you cannot create an instance of this class from SCSM by using the task “Create Service” nor can you extend them.

So basically you cannot extend the Distributed applications which are synchronized from SCOM!

This is a fact which we cannot change but there are solutions to work around this problem.

I will describe the solution to “Extend” the Synchronized DA’s from SCOM!

Extend Synchronized Business Services From SCOM

Since we cannot really extend the abstract class we are going to create a solution which is based on a new class and a type projection.

For extra read on type projections I strongly recommend reading below and just test:

Travis Wright – Creating Views That Use Related Property Criteria (Type Projections) : Software Views Example

Travis Wright – Creating Queues and Groups Using Type Projections

Travis Wright – Having More Fun with Type Projections Using ObjectProjectionCritera

The end goal is again a transparent solution to the user looking at the business services.

Here is the end result shown where the end user or service desk employee using the console experiences a single form with extended properties:

image

 

Create Extended.Business Service Class

Open the Authoring console and create a new Class with the following properties :

image

Make Sure the relationship “ExtendedBusinessRelatesToService” has a Source Max Cardinality of 1.

This is required to be able to use this relationship in a later phase.

image

Create a custom form for this class:

image

You have now created a Management pack with a new class and properties.

Trick the Default Service Form with a Type Projection

Next we are going to edit the default Service Form.

Simply edit the default Service Form in the Authoring Console and add an extra Tab Item containing extra properties, just add them don’t bind them to targets yet:

image

After creating this form in your management pack, save the management pack and close the authoring console.

Now we are going to extend the Type projection target at the customized service form by using a XML editor.

In the XML there are two type projections and two forms:

<TypeProjection ID="Extended.Business.Service.Form_TypeProjection" Accessibility="Public" Type="ExtendedBusinessServiceClass">

 

<TypeProjection ID="CustomForm_8a075e3c_dd8f_4b29_9e63_d73c2c76ee0d_TypeProjection" Accessibility="Public" Type="System!System.Service">

 

<Form ID="Extended.Business.Service.Form" Accessibility="Public" Target="Extended.Business.Service.Form_TypeProjection" Assembly="CustomFormTemplates" TypeName="CustomFormTemplates.HistoryOnlyTemplate">

 

<Form ID="CustomForm_8a075e3c_dd8f_4b29_9e63_d73c2c76ee0d" Accessibility="Public" Target="CustomForm_8a075e3c_dd8f_4b29_9e63_d73c2c76ee0d_TypeProjection" BaseForm="Alias_c47dbffb_8a79_48d7_a8d0_62b94f82d77f!ServiceMainForm" TypeName="Microsoft.EnterpriseManagement.ServiceManager.ServiceMaps.Forms.ServiceForm">

We need to focus on the Type Projection which is used in the ServiceMainForm, the form for viewing Services.

We are going to change this type projection to include our new class, After changing the Type Projection we can use the authoring tool to add the bindings.

<TypeProjection ID="CustomForm_8a075e3c_dd8f_4b29_9e63_d73c2c76ee0d_TypeProjection" Accessibility="Public" Type="System!System.Service">

          <Component Path="$Context/Path[Relationship='Alias_4631e730_74bb_49f3_9a11_d8ab92b69348!Microsoft.SystemCenter.ServiceDesigner.ServiceHasGroups']$" Alias="ServiceHasGroups" />

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemContainsConfigItem' SeedRole='Target']$" Alias="UsedBy">

            <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target' TypeConstraint='Alias_ce17648b_cb08_4401_8e4c_73099262a0ae!System.WorkItem.Incident']$" Alias="AffectedByIncidents" />

            <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target' TypeConstraint='Alias_c5b0afe9_4b97_478b_af44_b878af41a801!System.WorkItem.ChangeRequest']$" Alias="AffectedByChanges" />

          </Component>

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemOwnedByUser']$" Alias="ComponentServiceOwners" />

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemServicedByUser']$" Alias="ComponentServiceContacts" />

          <Component Path="$Context/Path[Relationship='System!System.ServiceImpactsUser']$" Alias="ComponentImpactedByService" />

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemImpactsCustomers']$" Alias="ComponentBusinessCustomers" />

          <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemRelatesToConfigItem' SeedRole='Target']$" Alias="ImpactedWorkItem" />

          <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target']$" Alias="RelatedWorkItem" />

          <Component Path="$Context/Path[Relationship='Alias_e5d6f5cf_62d1_4210_bbdf_f67e3239c646!System.ConfigItemHasFileAttachment']$" Alias="FileAttachment" />

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemRelatesToConfigItem']$" Alias="RelatedConfigItem" />

          <Component Path="$Context/Path[Relationship='System!System.ConfigItemRelatesToConfigItem' SeedRole='Target']$" Alias="RelatedConfigItemSource" />

          <Component Path="$Context/Path[Relationship='Alias_a34c42e2_3c61_4218_bb7f_31888ba93501!System.EntityLinksToKnowledgeDocument']$" Alias="RelatedKnowledgeArticles" />

        </TypeProjection>

Above is the entire type projection. we are going to add our newly created relationship to this type projection:

<Component Path="$Context/Path[Relationship='ExtendedBusinessServiceRelatesToService' SeedRole='Target']$" Alias="ExBusinessServiceRelatesToService">

            <Component Path="$Context/Path[Relationship='Tech1']$" Alias="Tech1RelatesToExBusinessService" />

          </Component>

 

The complete Type Projection will be:

<TypeProjection ID="CustomForm_8a075e3c_dd8f_4b29_9e63_d73c2c76ee0d_TypeProjection" Accessibility="Public" Type="System!System.Service">

         <Component Path="$Context/Path[Relationship='Alias_4631e730_74bb_49f3_9a11_d8ab92b69348!Microsoft.SystemCenter.ServiceDesigner.ServiceHasGroups']$" Alias="ServiceHasGroups" />

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemContainsConfigItem' SeedRole='Target']$" Alias="UsedBy">

           <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target' TypeConstraint='Alias_ce17648b_cb08_4401_8e4c_73099262a0ae!System.WorkItem.Incident']$" Alias="AffectedByIncidents" />

           <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target' TypeConstraint='Alias_c5b0afe9_4b97_478b_af44_b878af41a801!System.WorkItem.ChangeRequest']$" Alias="AffectedByChanges" />

         </Component>

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemOwnedByUser']$" Alias="ComponentServiceOwners" />

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemServicedByUser']$" Alias="ComponentServiceContacts" />

         <Component Path="$Context/Path[Relationship='System!System.ServiceImpactsUser']$" Alias="ComponentImpactedByService" />

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemImpactsCustomers']$" Alias="ComponentBusinessCustomers" />

         <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemRelatesToConfigItem' SeedRole='Target']$" Alias="ImpactedWorkItem" />

         <Component Path="$Context/Path[Relationship='Alias_b97cdc42_d94e_4c0e_ba0c_a0b3af25ff3e!System.WorkItemAboutConfigItem' SeedRole='Target']$" Alias="RelatedWorkItem" />

         <Component Path="$Context/Path[Relationship='Alias_e5d6f5cf_62d1_4210_bbdf_f67e3239c646!System.ConfigItemHasFileAttachment']$" Alias="FileAttachment" />

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemRelatesToConfigItem']$" Alias="RelatedConfigItem" />

         <Component Path="$Context/Path[Relationship='System!System.ConfigItemRelatesToConfigItem' SeedRole='Target']$" Alias="RelatedConfigItemSource" />

         <Component Path="$Context/Path[Relationship='Alias_a34c42e2_3c61_4218_bb7f_31888ba93501!System.EntityLinksToKnowledgeDocument']$" Alias="RelatedKnowledgeArticles" />

         <Component Path="$Context/Path[Relationship='ExtendedBusinessServiceRelatesToService' SeedRole='Target']$" Alias="ExBusinessServiceRelatesToService">

           <Component Path="$Context/Path[Relationship='Tech1']$" Alias="Tech1RelatesToExBusinessService" />

         </Component>

       </TypeProjection>

 

Now save the XML and open the authoring console again.

Now you can add the bindings to the ServiceMainForm, just select the field and select the “…” to browse for the property you want to use:

image

Notice how you can now traverse the relationship “ExtendedBusinessRelatesToService” to select properties of our custom class.

Select the correct binding paths for all fields.

The path to the TECH 1 user you need to add this manually and can’t use the browse function wen using this binding path. This is because this relationship is three layers deep.

Just add the binding path like “ExBusinessServiceRelatesToService.Tech1RelatesToExBusinessService”.

This binding path now travels over the first relationship from Service To Extended Service (ExBusinessServiceRelatesToService) and two the second realtionship Extended Service clas to User (Tech1RelatesToExBusinessService).

After this you can save the management pack and seal it.

Now you can import the management pack in your environment,

Create a view for your new Class.

image

Create a Class and relate it to an existing business service.

image

And now you are finished:

image

Recap

This post describes how to “extend” a business Service in SCSM.

The idea behind it is you grant your CMDB manager rights on the Extended Service View and instruct him how to create this class and link it to an business service.

The other users using the console don’ have access to this view and this solution will be transparent to them. They experience simple editing and adding of custom properties to business services.Open-mouthed smile

If you want this  even fancier you could create a task to add the custom class and link them to a business service or use orchestrator to automatically create these for you !Hot smile

The example used uses a solution to show history tab, required for auditing purposes from Patrick Sundqvist – Part 1: Custom Form Templates for Service Manager 2012

Happy Authoring,

Oskar Landman

SCSM showcase: Incident & Request Intake task

Hello,

This blog describes an example solution to demonstrated the customization capabilities of System Center Service Manager – a customization show case. The creation of the described solution started with a request of a customer and feedback on the out-of-the-box functionality I have received. The following functionality request and feedback was the start point for the creation of the SCSM Intake task:

  • - Cascading dependencies should be available for the intake of an Incident or Request.
  • - It should be possible to display knowledge article information while service desk is completing the intake of a request.
  • - It must be possible to directly resolve the work item.

These functionalities can really have an added value for the intake of a request. In my solution and answer on the desired functionality I enable the following capabilities in the Service Manager console via a custom task:

  • - Service/Incident managers can enforce their process; impact and urgency can be set based on the selections made in the cascading dependencies dropdown list boxes.
  • - Service desk analyst does not always know the Incident routing information. The routing configuration can be set for the selections and based on the selections made in the form the correct support group is set.
  • - Intake configuration of the cascading dependencies is made in the Library workspace. Customers can apply their own settings for the intake of the requests.

 

An overview of my solution:

  • I have created a custom task for the intake of an Incident. The default Incident form is not replaced, after the intake of the request the analyst make use of the default forms in Service Manager. Service, Component and Symptom are custom configuration item classes. Views and forms are there to create and configure the intake settings in the Library workspace. Below a screenshot of the “symptom” form. Some explanation around this configuration:
  • - Display name: displayed selection in the form
  • - ID’s: currently the cascading dependencies configuration is based on ID’s. This will be changed to relationships to ease up the configuration of the functionality.
  • - Impact, Urgency and support group are set on the work item when selected
  • - Knowledge article that is presented when selected.
  • clip_image002
  • In the solution that I have build there is a four level selection, as you can see in figure below. The selection to create an Incident or request can be made in the two checkboxes. Classification Category, Service, Component and Symptom displayed items are depended on the selection you make in the list box above. For example, you will only see components from the service you have selected. 

image

  1. 1. Items in the drop down are enumerated from the custom classes that are created and configured in the library workspace. List boxes with cascading dependencies : each selection defines what will be presented in the next drop down.
  2. 2. Last selection sets the support group, Impact and Urgency on the work item.
  3. 3. Each drop down item can have a related knowledge article. This is presented to the analyst. Can be a questionnaire or simply input for resolution…

The complete intake task I have build includes:

  • - User pickers to specify Affected user and reporting user.
  • - Affected user his devices (primary owner & User uses device) are listed in the “Affected configurations items” drop down. Other can be searched via the buttons next to the list view.
  • - Via the “Assistance” button a remote assistance session can be started to the specified computer.
  • - Files can be added…
  • - Incidents can be submitted and also directly resolved.

image

This is an example solution that is created for a customer. As you can see, if it’s not in the product you can build it your self Smile

Have fun customizing SCSM on AuthoringFriday!

Kurt Van Hoecke

SCOM: Input and Pass user input parameters to Console Task

 

This is a small thing I figured out to request user input and pass it as a parameter to a console task by using PowerShell.

Issue:

The client wanted to be able to create tickets from an alert in the console if they were missed by an operator or notification script. The notifications pass the different parameters to a PowerShell script that generates the ticket. So far so good. But they wanted to call the notification from a Alert Console task. There are different parameters that need to be customized per alert to generate the correct ticket. One option was to create an Alert Console task for ALL the different classifications of alerts which would be a nightmare from manageability perspective + clutter the console.

Solution:

I came up with a small PowerShell script which will ask the user for input and use that input to generate the ticket with the correct info. The user still needs to know what to fill in but still it’s better than creating all the different Alert Console tasks.

This script is reusable in all your scripts you need to make interactive so you can prompt users for input during a console task.

Example:

In this example I’ve created a small Alert Console Task to connect to a Remote server via Remote desktop connection which can be different than the server which is generating the alert you’ve selected.

The PowerShell script I used in this example can be downloaded here

1. Creating the console task:

First things first. We need to create the console task.

Navigate to the Authoring pane > Management pack objects > tasks > create new task.

In the selection window select a Console Task > Alert Command line (This is necessary if we want to pass parameters from the alert to the script we would like to run)

SNAG-0007

Name your task.

Note: This will be the actual name which will appear in your console so keep it short and simple but clear enough so someone will know what the task will do.

SNAG-0009

Note: I’ve created a separate management pack for all my Console Tasks

Specify the command line options:

  • Application: %windir%\system32\windowspowershell\v1.0\Powershell.exe
  • Parameters: C:\scripts\consoletask\remotedesktop.ps1 $ID$ “$Managed Object Name$"
  • Working Directory: c:\scripts\consoletask\

Note: The script must be copied to the local computer where the console is installed. In this case: c:\scripts\consoletask\

Hints:

  • If you want to check what alert parameters you can pass to your script click the arrow behind the parameters field
  • Keep the “Display output when this task is run ticked as this is a great tool to check whether there are issues with your script during execution. If you are convinced after testing that the task is working you can change it to hidden.

SNAG-0013

2. Check the Console Task

When saved, the Task will appear between the Alert Tasks in the task pane when you select an alert.

SNAG-0012

When you click the task the Console Task Output window will pop up together with a window asking the user to put in a servername. If the user clicks cancel the task is terminated.

SNAG-0041

Success! The remote connection is started with the server we have put in!

SNAG-0042

3. Disable the Output window

Now that we have verified everything is working we can disable the task console window so only the prompt for input is shown.

Open the task properties and deselect the box “Display output when this task is run”

SNAG-0010

SNAG-0040

4. PowerShell Script

The PowerShell script I used in this example can be downloaded here

 

The section which is responsible for the input is here so if you have existing scripts this is what you are looking for to make them interactive:

#Get the server to connect to
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.VisualBasic’) | Out-Null
$server =  [Microsoft.VisualBasic.Interaction]::InputBox("Enter a Servername", "Servername", "")

System Center 2012–Extended OIP–Maintenance Mode POWER!! – Self Service portal

This posting is about using the Self Service Portal to set OpsMgr objects in maintenance mode. This simple example shows how you can use the System Center 2012 Extended OIP in combination with SCSM and OpsMgr.

<<<<<———>>>>> Download Latest version <<<<<———->>>>>

The runbook can be used to make adhoc maintenance mode available to application owners.

The Runbook

The first thing which you need to configure is a runbook in Orchestrator.

Start a new runbook and name the runbook AdHoc  Maintenance.

The end result should look like the figure below:

image

First you will need to add the Initialize data activity with the following settings:

Initialize Data Class – Get Class
image image
These are the properties we are going to add to the runbook from SCSM. Because we are going to use a simple list in SCSM we need to query OpsMgr for the group Internal Name. We can do this by using a filter on DisplayName which matches the input from SCSM.
   
SCOM MaintenanceMode – Start Group Get Relationship
image image
Internal Name from previous Class – Get Class Activity and both comment and duration from SCSM. This will query SCSM for the runbook activity related class service request.
   
Get Object Update Object
image image
This will get the Actual Service Request by using the GUID we retrieved from the previous step. This will add the description with the results of the Maintenance mode into the service request.
   

 

The runbook activity template

After creating the runbook you can sync the orchestrator connector to make the runbook available in SCSM. After making sure the runbook is in SCSM you need to set create a runbook activity template

Create Runbook Activity Template – General Tab
image
Fill in the require properties and don’t forget the IS READY FOR AUTOMATION checkbox!
Next fill in the
 
Create Runbook Activity Template – Runbook Tab
image
Set the ActivityGuid to the Object ID you will need this to be bale to update the Service request Description in the Runbook! Leave all other parameters empty.

Service Request Template

Next you will need to create the Service Request Template to include the Runbook Activity.

Service Request Template – General Tab
image
Fill in the default request properties.
 
Service Request Template – Activities Tab
image
Add the Runbook Activity we created earlier. No need to change any settings just add and click on OK to create the service request template.

Create Request Offering

After creating the service request template you can now create the request offering.

General User Prompts
image image
Fill in the properties, add a nice icon and make sure you are using the service request template we created in the previous step. Create the 3 user prompts:
Group – Simple List
Duration – String
Comment – String
   
Configure User Prompts Map Prompts
image image
You need to configure the Group Simple list with the exact display names of the groups you want to expose in the Self Service Portal. Make sure you map the user inputs to the correct parameters in the runbook activity.
   

After setting these settings you can publish the request offering.

Service Offering

The last step is to create a service offering and add the request offering to the service request and publish the content on the se;f service portal.

General Request Offering
image image
Fill in the properties and give it a nice icon. You can skip the rest of the steps for this example and just add the request offering, Add the request offering we created earlier and publish the service offering on the website.
   

THE END RESULT

After following the steps the end result should look similar to the pictures below:

The request offering on the SSP.
image
 
image
 
image
 
image
 
Service Request
image
 
OpsMgr
image

Wrap up

Here is one simple example on how to really benefit from the System Center 2012 Extended OIP and make Maintenance Mode available to Application owners since they are the people responsible for the application or services! Real easy to set maintenance mode and log everything directly in SCSM user who raised maintenance mode and when everything nicely logged at the place where it should be logged!

You can setup scoping on the Self Service portal this way to make these kind of tasks only available to a delegated group of users. You can add other activities as well to deliver full self service and the best part about it everything is logged in SCSM and no extra privileges required on the systems everything being taken care of by Orchestrator!

You can add scheduled runbooks in orchestrator itself for the scheduled maintenance tasks this extra SCSM request offering is just for when they want to place objects in maintenance NOW! Smile

As a side note my main goal was to sync all groups from SCOM to SCSM which worked like a charm with the sync properties in the OIP!

BUT there is one really nasty thing when setting up the user prompts to query based. I had no way to filter the query on groups!!!! Which made this kind of useless and needed me to step away to a simple list.

Although I am assuming this is restricted because the UI for Query based is filtered and doesn’t make it possible to filter based on groups I will try if XML only works.

Because the end result when you select a group which lives in SCSM makes the solution more dynamic and less error sensitive you could add the group as affected item to the service request.

Next up

Will try a Exchange or Sharepoint Calendar solution where application owners can add schedules by using the Self Service Portal and using an orchestrator runbook to pull the schedule and set maintenance mode automated. This will really deliver scheduling and setting maintenance mode to the application owners  / administrator leaving the OpsMgr admin to do nice things instead of scheduling maintenance for everything.

Cheers,

Oskar Landman

Older Posts »