Archive for: ‘June 2014’

How to add mail addresses to Data Warehouse

June 25, 2014 Posted by Alexander Axberg

In this post I will go through how to add your users mailaddresses to the Data Warehouse, to be able to display them i reports.
Since they are not transferred to the Data Warehouse by default, we have to build a new Data Warehouse Management Pack to be able to sync this information.

But first a quick look how the mailaddresses are stored.
They are not simply stored in a textstring directly on the user object as you might think. They are actually stored as a separate object in the class “System.Notification.Endpoint”. This is makes it able to create several addresses on the same user (SIP and SMTP).
A relation between this object and the user object is then created. The relation is called System.UserHasPreference.

So what we need to do is to define a dimension for the System.Notification.Endpoint, and include the attributes that store the actual mailaddress.
Then we also need a Relationship Fact between the Notification Endpoint dimension and the User dimension.

The code to create that looks like this:

      <Dimension ID="SubscriberAddressDim" Accessibility="Public" InferredDimension="true" Target="Notifications!System.Notification.Endpoint" HierarchySupport="Exact" Reconcile="true">
        <InclusionAttribute ID="TargetAddress" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/TargetAddress$" SlowlyChangingAttribute="false" />
        <InclusionAttribute ID="ChannelName" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/ChannelName$" SlowlyChangingAttribute="false" />
        <InclusionAttribute ID="Id" PropertyPath="$Context/Property&#91;Type='Notifications!System.Notification.Endpoint'&#93;/Id$" SlowlyChangingAttribute="false" />
      <RelationshipFact ID="HasPreferenceFact" Accessibility="Public" Domain="DWBase!Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="System!System.Domain.User" SourceDimension="DWBase!UserDim">
        <Relationships RelationshipType="SupportingItem!System.UserHasPreference" TargetDimension="SubscriberAddressDim" />

So the complete steps to create our new Data Warehouse MP looks like this:

  • Create a new MP with the code above, or download the complete one below
  • Seal it, and import it into Service Manager as usual
  • Wait for the MPSyncjob in Data Warehouse to kick in (every hour) or start it manually. The MP will then be synced into DW.
  • Take a beer while you wait for the deployment in Data Warehouse.
  • When deployment is completed, log into the DWDataMart database in SQL, and look under views and you should have 2 new views there: SubscriberAddressDimvw, HasPreferenceFactvw
  • Now you are all set to start query the database in Reports to display the mailaddresses. You can use the following SQL query to list all your user object in DW with the columns: Username, Domain, Mailaddress

    Take in mind that after the Management Pack deployment is completed, it could take a while to populate the tables with the mail addresses.

    smtp.TargetAddress AS 'E-Mail'
    UserDimvw AS u
    INNER JOIN HasPreferenceFactvw AS hp
    ON u.UserDimKey = hp.UserDimKey
    INNER JOIN SubscriberAddressDimvw AS smtp
    ON hp.UserHasPreference_SubscriberAddressDimKey = smtp.SubscriberAddressDimKey
    smtp.ChannelName = 'SMTP'
    AND hp.DeletedDate IS NULL


    Moving SLOs from one environment to another? (Part 1)

    June 18, 2014 Posted by Anders Asp

    Service Manager is built around storing your configuration in Management Packs. This is a great solution when you’re working with several different environments and would like to move your configuration between these, such as a test and a production environment. Most of the configuration you do is stored in different Management Packs, while data is stored in the database.

    With this in mind, let’s take a closer look at how Service Level Objectives, SLOs, are constructed.


    As the picture above displays, the SLO itself is constructed of a Calendar, a Metric, a Queue and a specified Target Time. When creating a new Calendar or Metric, these will not be stored in a MP, instead they will only be created in the database. However, when you are creating the SLO itself, you are able to specify a MP to store it in, so the SLO itself should be stored in a MP, shouldn’t it? Unfortunately not!

    New SLO

    So what is really stored in the Mangement Pack specified if not the SLO itself?

    – SLO workflow group
    – SLO workflow target
    – SLO workflow: AddRelationship
    – SLO workflow: DeleteRelationship
    – SLO workflow: EndEvent
    – SLO workflow: StartEvent
    – SLO workflow: PauseEvent (disabled by default)
    – SLO workflow: ResumeEvent (disabled by default)

    In other words, parts of the SLO configuration and how it is calculated is stored in the MP (yes, the SLO is based upon a set of workflows), but not the actual SLA Configuration object.

    As a result of this, we are not able to copy SLOs from one environment to another by export/import of an MP since most of the configuration regarding your SLOs is stored in the database itself. If you try to do this, you will end with a number of “ghost workflows” – not visible in the console and related to an SLO that doesn’t exist.

    Here’s an example of that – in this first picture, you can see my existing SLOs in the system and all the workflows following a certain pattern (that applies to SLO workflows). Note how my SLOs is matching these workflows.

    Before MP import

    Below is the picture displaying the exact same thing after an import of a MP containing two other SLOs. Note that these SLOs are not visible in the console and are not functional at all, yet a number of workflows has been created within Service Manager (marked with red). These are the so called “ghost workflows”.

    After MP import

    These “ghost workflows” will not function and will throw errors in the Operations Manager event log on your Management Server, just like this:


    So to summarize: Do not try to export/import the MP containing SLOs to copy SLOs from one environment to another. Doing so will only result in a number of erroneous “ghost workflows” that might affect performance, stability and clog up your event logs with events.

    In the second part of this blogpost I will try to create a script or runbook that you can use to copy SLOs from one environment to another instead – stay tuned!