SCSM Email Integration Part 3.2.3 – Runbook Design – SMMail_Send-WiCommentEmail

Navigation:

Introduction

Alright! We’re ready to actually generate and send comment update emails. This will be a long post filled with child runbooks. Brace yourself.

Overview

We want a runbook that does the following:

  1. Accepts two parameters:
    1. a work item ID such as “IR123”.
    2. a comment GUID.
  2. Generates an email.
  3. Sends the email.

Branch Overview

We will need two branches, one for incidents, and one for service requests.

  • Main
    • Branch 1 – Incidents
    • Branch 2 – Service Requests

Global Variables

  1. SCSM Scripts Account Username
    1. Username of AD account which can run SCSM SMLets on your SCSM server. It should have local admin rights on the SCSM server.
  2. SCSM Scripts Account Password
    1. Password to the account username given in the global variable ‘SCSM Scripts Account Username’.
    2. Make sure to check the ‘Encrypted Variable’ checkbox.
  3. SCSM Email Notification BCC Addresses
    1. semi-colon delimited list of email address to bcc all messages to.

Activities Overview

Main Branch

  1. Initialize Data
    Parameter 1 Name: workItemId
    Parameter 2 Name: commentGuid

Branch 1 – Incidents

  1. Smart Link
    From: Initialize Data
    Include: workItemId from Initialize Data starts with IR
  2. Get IR
    Type: SC 2012 Service Manager -> Get Object
    Filter: ID equals {workItemId from Initialize Data}
  3. (IR) Get Work Item Affected User Email
    Type: System -> Run .Net Script
    Language: PowerShell
    Script: see Script section below.
    Published Data Parameter 1 Name: affectedUserEmailAddr
    Published Data Parameter 1 Variable: retval
  4. (IR) Generate – Email Subject Line
    Type: System -> Run .Net Script
    Language: PowerShell
    Script: see Script section below.
    Published Data Parameter 1 Name: affectedUserEmailAddr
    Published Data Parameter 1 Variable: retval
  5. (IR) Generate Email Message
    Type: System -> Run .Net Script
    Language: PowerShell
    Script: see Script section below.
    Published Data Parameter 1 Name: htmlEmailBody
    Published Data Parameter 1 Variable: retval
  6. (IR) Create and Send E-Mail
    Type: Exchange User -> Create and Send E-Mail
    To: {affectedUserEmailAddr from (IR) Get Work Item Affected User Email}
    Subject: {subject from (IR) Generate – Email Subject Line}
    Body: {htmlEmailBody from (IR) Generate Email Message}
    Body Type: HTML
    Bcc: {SCSM Email Notification BCC Addresses}
  7. (IR) Return Data
    Type: Runbook Control -> Return Data

Branch 2 – Service Requests

Identical to Branch 1, replacing all (IR) with (SR).

Scripts

(IR) Get Work Item Affected User Email

$ErrorActionPreference = "Stop"
try
{
    $username = "{SCSM Scripts Account Username}"
    $password = "{SCSM Scripts Account Password}"
    $scObjGuid = "{scObjectGuid from Get IR}"
    $scObjType = "incident"
    $targetServer = "Your-SCSM-Server"
    $securePassword = $password | ConvertTo-SecureString -AsPlainText -Force
    $creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username,$securePassword
    $retval = Invoke-Command -Credential $creds -ComputerName $targetServer  -ScriptBlock {
        $scObjGuid = $args[0]
        $scObjType = $args[1]
        & C:\Scripts\Get-ScsmWiAffectedUserEmailAddr.ps1 "$scObjGuid" "$scObjType"
    } -ArgumentList $scObjGuid, $scObjType
}
Catch
{
    Throw $_.Exception
}

Get-ScsmWiAffectedUserEmailAddr.ps1

Import-Module SMLets
$scObjGuid = $args[0]
$scObjType = $args[1]

Switch($scObjType) {
	"incident" {
		$wiClassName = "System.WorkItem.Incident$"
		$relationshipClassName = "System.WorkItemAffectedUser$"
	}
	"service request" {
		$wiClassName = "System.WorkItem.ServiceRequest$"
		$relationshipClassName = "System.WorkItemAffectedUser$"
	}
}

If($wiClassName -ne $null) {
	$wiClass = Get-SCSMClass $wiClassName
	$relationshipClass = Get-SCSMRelationshipClass $relationshipClassName
}

If($wiClass -ne $null -and $relationshipClassName -ne $null) {
	$wiFilter = "Id -eq '" + $scObjGuid + "'"
	$scObj = Get-SCSMObject -Class $wiClass -filter $wiFilter
	If ($scObj -ne $null) {
		$affectedUser = Get-SCSMRelatedObject -SMObject $scObj -Relationship $relationshipClass
	}
}

$emailAddr = ""
#ref: http://blogs.litware.se/?p=796
If($affectedUser -ne $null) {
	$userGUID = $affectedUser.Get_ID()
	$userPref = Get-SCSMRelationshipClass "System.UserHasPreference$"
	$endPoint = Get-SCSMRelatedObject -SMObject $affectedUser -Relationship $userPref|?{$_.DisplayName -like "*SMTP"}
	$readEmailaddr = $endPoint.TargetAddress
	If($readEmailaddr.length -ge 0) {
		$emailAddr = $readEmailAddr
	}
	Else {}
}

$emailAddr

(IR) Generate Email Subject Line

$subject = "IT Support [" + "{ID from Get IR}" + "]: " + "{Title from Get IR}"

(IR) Generate Email Message

$ErrorActionPreference = "Stop"
try
{
    $username = "{SCSM Scripts Account Username}"
    $password = "{SCSM Scripts Account Password}"
    $scObjGuid = "{scObjectGuid from Get IR}"
    $scObjType = "incident"
    $targetServer = "Your-SCSM-Server"
    $commentGuid = "{CommentGuid from Initialize Data}"
    $securePassword = $password | ConvertTo-SecureString -AsPlainText -Force
    $creds = New-Object System.Management.Automation.PSCredential -ArgumentList $username,$securePassword
    $retval = Invoke-Command -Credential $creds -ComputerName $targetServer -ScriptBlock {
        $scObjGuid = $args[0]
        $scObjType = $args[1]
        $commentGuid = $args[2]
        & C:\Scripts\generate-WorkItemCommentNotification.ps1 "$scObjGuid" "$scObjType" "$commentGuid"
    } -ArgumentList $scObjGuid, $scObjType, $commentGuid
}
Catch
{
    Throw $_.Exception
}

Generate-WorkItemCommentNotification.ps1

Note, you will need to heavily modify this script to suit your needs, and to strip out my information. I’ll have a templated version up as soon as I can, but am providing this in the meantime.

Here is what the final output looks like. I created the template in MS Word, then saved it as HTML, changed the formatting, and inserted it into the PowerShell script below. I got the idea for the template from another blog post which I cannot find. I try to always credit folks, so if you notice something familiar please let me know. Cross-linking and credit benefits everyone!

2.2.4-2

$scObjGuid = $args[0]
$scObjType = $args[1]
$commentGuid = $args[2]
$emailBodyText = ""

Import-Module SMLets

#get the classes all lined up
Switch($scObjType) {
	"incident" {
		$wiClassName = "System.WorkItem.Incident$"
		$commentRelationshipClass = "System.WorkItem.TroubleTicketHasAnalystComment$"
		$queueProperty = "Impact"
	}
	"service request" {
		$wiClassName = "System.WorkItem.ServiceRequest$"
		$commentRelationshipClass = "System.WorkItemHasCommentLog$"
		$queueProperty = "Priority"
	}
}

#get the scsm work item (wi) object
If($wiClassName -ne $null) {
	$wiClass = Get-SCSMClass $wiClassName
	$wiFilter = "ID -eq '" + $scObjGuid + "'"
	$scObj = Get-SCSMObject -Class $wiClass -Filter $wiFilter
}

#get the WI info and body text
If($scObj -ne $null) {
	$commentRelationship = Get-SCSMRelationshipClass $commentRelationshipClass
	$commentObj = Get-SCSMRelatedObject -SMObject $scObj -Relationship $commentRelationship | ?{$_.ID -eq $commentGuid}
	$commentStr = $commentObj.Comment
	$commentAuthor = $commentObj.EnteredBy
	$commentDate = $commentObj.EnteredDate
	$emailBodyText = $null
	If($commentStr -ne $null -and $commentStr -ne "") {
		#sanitize a little
		While($commentStr -like "*>*") {$commentStr = $commentStr.Replace(">","")}
		While($commentStr -like "*<*") {$commentStr = $commentStr.Replace("<","")}
		While($commentStr -like "*""*") {$commentStr = $commentStr.Replace("""","")}
		While($commentStr -like "*'*") {$commentStr = $commentStr.Replace("'","")}
		$commentStr = $commentStr.Replace("`n","<br>")
		$emailBodyText = $commentStr
	}
	$wiTitle = $scObj.Title
	$wiUrgency = $scObj.Urgency.DisplayName
	$wiQueue = $scObj.$queueProperty.DisplayName
	$wiStatus = $scObj.Status.DisplayName
	$wiId = $scObj.ID
	$wiCreatedDate = $scObj.CreatedDate

	#$affectedUserClassName = "System.Domain.User$"
	#$affectedUserClassObj = Get-SCSMClass $affectedUserClassName
	$affectedUserRelationshipClassName = "System.WorkItemAffectedUser$"
	$affectedUserRelationshipClassObj = Get-SCSMRelationshipClass $affectedUserRelationshipClassName
	$affectedUserObj = Get-SCSMRelatedObject -SMObject $scObj -Relationship $affectedUserRelationshipClassObj
	If($affectedUserObj -ne $null) {
		$affectedUserDisplayName = $affectedUserObj.DisplayName
	}
	Else {
		$affectedUserDisplayName = "ESL Member"
	}
}

$htmlBody = ""
$htmlBody += "<html>

<head>
<meta http-equiv=Content-Type content=""text/html; charset=windows-1252"">
<meta name=Generator content=""Microsoft Word 15 (filtered)"">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:""Cambria Math"";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:8.0pt;
	margin-left:0in;
	line-height:105%;
	font-size:11.0pt;
	font-family:""Calibri"",sans-serif;}
a:link, span.MsoHyperlink
	{color:#0563C1;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:#954F72;
	text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{margin-top:0in;
	margin-right:0in;
	margin-bottom:8.0pt;
	margin-left:.5in;
	line-height:105%;
	font-size:11.0pt;
	font-family:""Calibri"",sans-serif;}
p.msolistparagraphcxspfirst, li.msolistparagraphcxspfirst, div.msolistparagraphcxspfirst
	{mso-style-name:msolistparagraphcxspfirst;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	margin-bottom:.0001pt;
	line-height:105%;
	font-size:11.0pt;
	font-family:""Calibri"",sans-serif;}
p.msolistparagraphcxspmiddle, li.msolistparagraphcxspmiddle, div.msolistparagraphcxspmiddle
	{mso-style-name:msolistparagraphcxspmiddle;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	margin-bottom:.0001pt;
	line-height:105%;
	font-size:11.0pt;
	font-family:""Calibri"",sans-serif;}
p.msolistparagraphcxsplast, li.msolistparagraphcxsplast, div.msolistparagraphcxsplast
	{mso-style-name:msolistparagraphcxsplast;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:8.0pt;
	margin-left:.5in;
	line-height:105%;
	font-size:11.0pt;
	font-family:""Calibri"",sans-serif;}
p.msochpdefault, li.msochpdefault, div.msochpdefault
	{mso-style-name:msochpdefault;
	margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:""Calibri"",sans-serif;}
p.msopapdefault, li.msopapdefault, div.msopapdefault
	{mso-style-name:msopapdefault;
	margin-right:0in;
	margin-bottom:8.0pt;
	margin-left:0in;
	line-height:105%;
	font-size:12.0pt;
	font-family:""Times New Roman"",serif;}
span.apple-converted-space
	{mso-style-name:apple-converted-space;}
.MsoChpDefault
	{font-size:10.0pt;
	font-family:""Calibri"",sans-serif;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
-->
</style>

</head>

<body lang=EN-US link=""#0563C1"" vlink=""#954F72"">

<div class=WordSection1>

<p class=MsoNormal>Dear "

$htmlBody += $affectedUserDisplayName
$htmlbody += ",</p>

<p class=MsoNormal>The comment (below) has been added to your work item. Please feel free to reply
to this message; it will be immediately forwarded to the ESL IT support team.</p>

<p class=MsoNormal><b>Written by "

$htmlBody += $commentAuthor
$htmlBody += " at "
$htmlBody += $commentDate
$htmlBody += ", comment reads:</b></p>

<table class=MsoTableGrid border=1 cellspacing=0 cellpadding=0
 style='border-collapse:collapse;border:none'>
 <tr>
  <td width=935 valign=top style='width:467.5pt;border:solid windowtext 1.0pt;
  padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><span style='color:#0070C0'>"

$htmlBody += $emailBodyText
$htmlBody += "</span></p>
  </td>
 </tr>
</table>

<p class=MsoNormal> </p>

<p class=MsoNormal>Quick Notes:</p>

<p class=MsoListParagraph style='margin-bottom:0in;margin-bottom:.0001pt;
text-indent:-.25in;line-height:106%'><span style='font-family:Symbol'>·</span><span
style='font-size:7.0pt;line-height:106%;font-family:""Times New Roman"",serif'>      
</span>You can track the status of this work item at the <a
href=""https://scsm-esl-wc1.osuesl.net/SMPortal/SitePages/My%20Requests.aspx"">ESL
Service Manager Self-Service Web Portal</a>. (Note: requires Internet Explorer and an ESL LAN or VPN connection).</p>

<p class=MsoListParagraph style='margin-bottom:0in;margin-bottom:.0001pt;
text-indent:-.25in;line-height:106%'><span style='font-family:Symbol'>·</span><span
style='font-size:7.0pt;line-height:106%;font-family:""Times New Roman"",serif'>      
</span>The full work list of the ESL IT Support Team is available on the <a
href=""http://sharepoint.osuesl.net"">ESL SharePoint Helpdesk Website</a>. Choose 'IT Request Billboard'
from the left navigation list. (Note: requires ESL LAN or VPN connection).</p>

<p class=MsoListParagraph style='margin-bottom:0in;margin-bottom:.0001pt;
text-indent:-.25in;line-height:106%'><span style='font-family:Symbol'>·</span><span
style='font-size:7.0pt;line-height:106%;font-family:""Times New Roman"",serif'>      
</span>Make sure to place all new text for a reply at the top of the message
or it will not be read.</p>

<p class=MsoNormal> </p>

<p class=MsoNormal>More Information on the Work Item:</p>

<table class=MsoTable15List1LightAccent1 border=0 cellspacing=0 cellpadding=0
 style='border-collapse:collapse'>
 <tr>
  <td width=189 valign=top style='width:94.25pt;border:none;border-bottom:solid #9CC2E5 1.0pt;
  padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>Title </b></p>
  </td>
  <td width=585 valign=top style='width:292.5pt;border:none;border-bottom:solid #9CC2E5 1.0pt;
  padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>"
$htmlBody += $wiTitle
$htmlBody += "</b></p>
  </td>
 </tr>
 <tr>
  <td width=189 valign=top style='width:94.25pt;background:#DEEAF6;padding:
  0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>ID</b></p>
  </td>
  <td width=585 valign=top style='width:292.5pt;background:#DEEAF6;padding:
  0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'>"
$htmlBody += $wiID
$htmlBody += "</p>
  </td>
 </tr>
 <tr>
  <td width=189 valign=top style='width:94.25pt;padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>Status</b></p>
  </td>
  <td width=585 valign=top style='width:292.5pt;padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'>"
$htmlBody += $wiStatus
$htmlBody += "</p>
  </td>
 </tr>
 <tr>
  <td width=189 valign=top style='width:94.25pt;background:#DEEAF6;padding:
  0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>Urgency</b></p>
  </td>
  <td width=585 valign=top style='width:292.5pt;background:#DEEAF6;padding:
  0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'>"
$htmlBody += $wiUrgency
$htmlBody += "</p>
  </td>
 </tr>
 <tr>
  <td width=189 valign=top style='width:94.25pt;padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'><b>Created Date</b></p>
  </td>
  <td width=585 valign=top style='width:292.5pt;padding:0in 5.4pt 0in 5.4pt'>
  <p class=MsoNormal style='margin-bottom:0in;margin-bottom:.0001pt;line-height:
  normal'>"
$htmlBody += $wiCreatedDate
$htmlBody += "</p>
  </td>
 </tr>
</table>

<p class=MsoNormal> </p>

<p class=MsoNormal>Thank you,</p>

<p class=MsoNormal><img border=0 width=336 height=67 id=""Picture 1""
src=""http://osu.edu/assets/site/images/logos/osu-emailsig.png""
alt=""The Ohio State University""><span style='font-size:9.0pt;line-height:107%;
font-family:""Arial"",sans-serif;color:#333333'><br>
</span><b><span style='font-size:9.0pt;line-height:107%;font-family:""Arial"",sans-serif;
color:#BB0000;background:white'><span style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'>John Puskar, MCP</span></span></b><span style='font-size:
9.0pt;line-height:107%;font-family:""Arial"",sans-serif;color:#333333'><br
style='-webkit-user-select: none;orphans: auto;text-align:start;widows: auto;
-webkit-text-stroke-width: 0px;word-spacing:0px'>
<span style='background:white'><span style='orphans: auto;text-align:start;
widows: auto;-webkit-text-stroke-width: 0px;float:none;word-spacing:0px'>Sr.
Systems Manager</span></span><br style='-webkit-user-select: none;orphans: auto;
text-align:start;widows: auto;-webkit-text-stroke-width: 0px;word-spacing:0px'>
</span><span style='font-size:9.0pt;line-height:107%;font-family:""Arial"",sans-serif;
color:#BB0000;background:white'><span style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'>College of Engineering</span></span><span
class=apple-converted-space><span style='font-size:9.0pt;line-height:107%;
font-family:""Arial"",sans-serif;color:#333333;background:white'><span
style='orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
float:none;word-spacing:0px'> </span></span><span style='font-size:9.0pt;
line-height:107%;font-family:""Arial"",sans-serif;color:#333333;background:white'>Electroscience
Lab</span></span><span style='font-size:9.0pt;line-height:107%;font-family:
""Arial"",sans-serif;color:#333333'><br style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'>
<span style='background:white'><span style='orphans: auto;text-align:start;
widows: auto;-webkit-text-stroke-width: 0px;float:none;word-spacing:0px'>Room
240, 1330 Kinnear Road, Columbus, OH 43212</span></span><br style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'>
<span style='background:white'><span style='orphans: auto;text-align:start;
widows: auto;-webkit-text-stroke-width: 0px;float:none;word-spacing:0px'>614-292-5545
Office / 614-769-1231 Mobile</span></span><br style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'>
</span><a href=""mailto:puskar.4nokspam@osu.edu"" style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'><span style='font-size:9.0pt;line-height:107%;font-family:
""Arial"",sans-serif;background:white'>puskar.4nokspam@osu.edu</span></a><span
class=apple-converted-space><span style='font-size:9.0pt;line-height:107%;
font-family:""Arial"",sans-serif;color:#333333;background:white'><span
style='orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
float:none;word-spacing:0px'> </span></span></span><a
href=""http://electroscience.osu.edu/"" style='-webkit-user-select: none;
orphans: auto;text-align:start;widows: auto;-webkit-text-stroke-width: 0px;
word-spacing:0px'><span style='font-size:9.0pt;line-height:107%;font-family:
""Arial"",sans-serif;background:white'>electroscience.osu.edu</span></a></p>
</div>

</body>

</html>"

$htmlBody

Awesome! Your should now be getting pretty sweet emails every time a non-private comment is entered. Here’s a screenshot of the final runbook:

3.2.3-1

The exported runbooks will be available upon completion of this series.

Advertisements

3 thoughts on “SCSM Email Integration Part 3.2.3 – Runbook Design – SMMail_Send-WiCommentEmail

  1. Pingback: How I Handle SCSM Email Integration | windowsmasher

  2. Pingback: SCSM Email Integration Part 4.1.1 – Receiving Comments | windowsmasher

  3. Pingback: How I Handle SCSM Email Integration – Navigation | windowsmasher

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s