Due to a notification requirement of a customer, I needed to somehow access the SMTP address of a User CI in the CMDB. This is not that easy, because the SMTP address is not directly stored on the user object, but in a related object of another class. In this post I will show how to get the SMTP address out of the CMDB by using Powershell.

One requirement of the customer was to notify the IT analyst of a manual activity (“assigned to”-user) when the activity is overdue, meaning that the activity was not completed within the scheduled time frame. This indeed makes sense, because otherwise the analyst might forget about his activities which may delay the whole Change Request. For my customer, I solved this by using Opalis with my own SCSM Integration Pack in Version 2.0. This is how the workflow called “Send Personal Overdue Manual Activities” looked at the end.

Now let’s take a look at the details.

• Daily 04:00

This is easy, as it’s just the schedule trigger of the workflow. My customer wanted to run this workflow daily, so that IT analysts will already have a mail in their mailbox when Overdue Activities exist.

• Get Overdue Manual Activities

This object comes from my SCSM OIP and checks the Service Manager Database for overdue activities. As you can see, it connects to the Service Manager Server (using an Opalis Variable) and only checks for Overdue Manual Activities that are active and that have a scheduled end date. The activities that do not have a scheduled end date are not returned (can be changed if needed).

• Get Mail Address of IT Analyst

This is the most tricky part that I have done using Powershell based on the smlets beta 3. I added a “Run .NET Script” activity, chose Powershell and added the script.

This is the complete script which uses Powershell Remoting. See comments within the script.

---

Comment: First, Published Data from the previous object is used to get the display name of the “assigned to” user of each active, overdue Incident.

$userdisplayname = “displayname from “assigned to” user of previous object (Pubished Data)

Comment: Now the Session to the SM Server is initiated.

$session = new-pssession –computername scsmserver01.itnetx.ch

$returnarray = invoke-command -session $session -argumentlist $userdisplayname -scriptblock{

Comment: This is the part of the script that will run on the remote system, in this case on the SCSM Management Server.

param($userdisplayname)

import-module smlets

Comment: The User class has a relation called “User has Preference” which we are storing in a variable.

$userpreferenceclass = get-scsmrelationshipclass -name system.userhaspreference$

Comment: Now we will select the User that we got from the previous object. But first we have to select the object class for it.

$class = get-scsmclass -name system.user$

$user = Get-SCSMObject -class $class -filter “displayname -eq $userdisplayname”

Comment: Now we access the SMTP address by using the relationship. The output will be the SMTP adress which is then returned to the Opalis Server.

$mail = (Get-scsmrelatedobject –smobject $user –relationship $userpreferenceclass | where{$_.displayname –match “smtp”}).targetaddress

return $mail

}

Comment: As a last step I output the data so that I can use it as published data afterwards.

if($returnarray)

{$returnarray}

else{exit}

remove-pssession $session

---

To make sure I can use the SMTP Address later to send an eMail, I have to publish the SMTP Address to the Opalis data bus. This is still done in the activity “Get Mail Address of IT Analyst”.

• Compare Values

This object just does what it is named after. It checks the result of the previous object (SMTP Address) and stops the workflow if no Mail Address was returned. When a minimum of one SMTP Address is returned, the workflow continues. Make sure that the Link after the activity is also configured correctly.

• Send Mail

The last step is sending the email to the IT Analyst. Because we now have the SMTP Address as published data, this is easy.

Voilà! The workflow itself is not that special. The only special thing is the Powershell script that gets the SMTP address of the User that the Manual Activity was assigned. But with the help of this blog post you will never have to fear any relationships in SCSM again – as you can just make some simple modifications to the script to traverse all relationships that exist

Update: If you need the reverse approach and find out what CI belongs to a specific mail address, check this blog post –> http://blog.scsmfaq.ch/2011/12/29/to-what-ci-belongs-a-specific-mail-address-2/

bye bye
Marcel