Services, Organisations, and Service Level Agreements – Part 1

In Service Desk Express 9.0 the new Services module was introduced which, in conjunction with the Organisations and Service Level Agreements modules, allows service delivery companies to manage the delivery of their service catalogue. These new modules are not for everyone – indeed there are a couple of performance issues with these modules that should not be overlooked – but for those looking to implement the functionality, this series of posts are hopefully for you.

I hope to cover all the aspects/features/functionality associated with these modules using examples. As always, I will try and make the examples as realistic as possible.

Services

The easiest module to get your head around is the Services module. The Services module should list the services you, as a service provider, are making available to organisations to consume. As a first pass I would suggest that you start very top-level – for example:

  • Account Support Service
  • Application Support Service
  • Email Support Service
  • Hardware Support Service
  • Mobile Support Service
  • Network/Connectivity Support Service
  • Printing/Scanning Support Service

The decision as to whether you need to go any lower than this depends primarily on whether different applications say, have a different SLA’s. It may also depend on how you have defined your Categories (Support Subjects).

The key fields within Services are:

  • Name – The name of the service.
  • Description – A general description of the service.
  • Activation Date – The date the service is first available.
  • Retirement Date – The date when the service will no longer be available.

Organisations

The way you set up Organisations depends entirely on how you have set up your Companies and Departments. Most Service Desk Express solutions I have seen have been internally focused. As such they will probably have a single Company as this makes reporting much much easier and in my opinion is an excellent idea unless you have a really really good reason not to. They also tend to have a lot of Departments. Service Desk Express allows your Organisations to be Company, Department or Company/Department based. The table below shows which solution is best suited to which criteria:

Situation Solution
1 More than one Company and within each Company Departments can have different SLA’s for a given Service. Organisations should be based on Companies and Departments.
2 More than one Company and within each Company all Departments have the same SLA for a given Service. Organisations should be based on Companies.
3 One Company and all Departments have the same SLA for a given Service. Single Organisation based on the single Company.
4 One Company but different departments can have different SLA’s for a given Service. Organisations should be based on Departments.

If you are in Situation 1 I feel for you as this is the same situation that I have to manage and it can get a little complicated. The more common situation is 3 or 4. So what I will cover in this post is Situation 4 which is the more complex of the two.

OK, so let’s say that we have Scenario 4 with an internal service desk servicing six departments: Sales; IT; Marketing; Facilities; Finance; and HR. Now, let’s say that Finance require a tighter SLA on the Applications Support Service then any of the other departments but only during March each year. We can create six appropriate Organisations each linked to their respective Department as per the screenshot below:

Organisation

Service Level Agreements (SLA’s)

SLA’s allow you to specify conditions under which they are applied, resulting in a given Due Date and Time, and appropriate milestones. They are actually quite straightforward but there are a couple of little gotchas that you just need to be aware of. Let’s walk through the creation of a SLA that would deliver a one-hour response, 24 hour recommended fix and a 48 hour absolute fix.

Urgencies…Priorities…ITIL…Argh!!!!

The first thing you need to be aware is that the SLA module does not contain the fix durations – another module does that. In days of old (i.e. pre Service Desk Express 9.6) this was the Urgency module. So if you are using Service Desk Express 9.2 or below then, to create the SLA mentioned above, you would need to create a new Urgency form that included the Duration, Recommended Fix Duration, and Response Duration fields similar to the one shown below and create the appropriate record as shown:

48HR Urgency

Now notice here that the duration fields are WORKING hours not literal hours. This is important. So, in the example above, our 48HR literal fix time equates to 20 working hours because our work schedule is 10 hours long (0800-1800).

When BMC released Service Desk Express 9.6 they bowed to significant customer pressure that the Priority should drive the fix durations and that Priority is derived based on the combination of Urgency and Impact. What this means is that if you are using Service Desk Express 9.6 or above then you would need to create a new Impact, Urgency, and Priority record to achieve the same effect. By the way, this is not a bad thing it is just that it makes life more complicated when trying to explain all this in a blog post! Anyway, piccy below:

48HR Priority

So now that we have our Urgency (or Priority depending on what version you are using), we can now go ahead and create our SLA as shown below:

48HR SLA 01 48HR SLA 02 48HR SLA 03

So notice a couple of things. In the first piccy we have the following fields:

  • SLA ID – A unique name for the SLA.
  • Description – A freetext description of the SLA.
  • Start Date – The first date this SLA can be selected. You will NOT get the option of selecting this SLA unless the current date and time is between the Start Date and End Date fields.
  • End Date – The date this SLA can be selected.
  • Type – You can select either Service Level Agreement, Operational Level Agreement, or Underpinning Contract. What matters is that this field is purely for reporting purposes.
  • Status – You can select either Active, Draft or Disabled. The record MUST be Active to allow you to select it for NEW tickets. Existing tickets CAN have an SLA with a status of Disabled and these will still process as normal.
  • IT Owner – The Support Staff member who owns this SLA. Again for reporting purposes only.
  • Module – You can select either Incident, Problem Management or Change Request. The SLA will only work in the module selected here.

The second piccy shows the Criteria. This SLA has no criteria which means it will be available for all circumstances. We will use this screen later when we want to check if the current month is March!  The third piccy shows the Goals. This is simply the selection of the appropriate Priority or Urgency that we created above. The final piccy shows the Milestones screen:

Milestones

Milestones is an interesting choice of title here. Normally, we strive to achieve milestones. In the case of BMC’s SLA Milestones these are events that you hope you DON’T achieve! Essentially they are how you monitor your tickets in real-time. A single SLA can have multiple milestones which once hit perform an specific action (usually sending an email or reassignment). One thing to bear in mind when creating these is that no two rules can have the same name and as such it is worth prefixing the rulename with the SLA ID. Below are screenshots of two you might create:

1) 48HR – Ticket Within 90% Due Date

48HR Milestone 01

2) 48HR – Ticket Passed Due Date

48HR Milestone 02

*** DO NOT GET CARRIED AWAY ***

It is really easy to create loads of milestones to monitor everything and depending on just how carried away you get one or two things are likely to occur:

  • Your staff will get information overload and receive so many emails that they will create rules in Outlook to ignore them!
  • Your system will come to a grinding halt as there are performance issues with creating too many milestones!

Next Post

OK, so enough for now – this post is more than long enough. Hopefully this will get you started on how to set up your Service Catalogue, Organisation structure and Service Level Agreements. In my next post in this series, I will cover SLA Criteria (such that we can finish our example); linking Services, Organisations and SLAs;  and of course how to turn SLA Management on in the first place including the biggest gotcha of all!

Service Desk Express QuickViews Don’t Return Any Data

Recently I came across an unusual experience with Service Desk Express 9.8 where my QuickViews suddenly stopped returning any records. So following typical fault finding principles I considered what I had changed and all I had done was install a web application at the root of the same website containing the SDE virtual directory. The rest of Service Desk Express (and for that matter, everything else on the server ) all worked absolutely fine but the QuickViews simply returned “Retrieving data, please wait…”

I would love to say that I identified the root cause and the resolution but alas a gifted colleague did all that. Suffice to say, the issue was caused by the fact that the Service Desk Express web.config file failed to contain a default section that specifies which version of which compiler the application should use. As my new application did provide this information, Service Desk Express tried to inherit it and, as a result of .NET Framework version issues, promptly fell over.

Solution

So to the solution. If you want to have an ASP.NET web application at the root of the same website where Service Desk Express is installed, open the Service Desk Express web.config file (found in C:Program FilesBMCService Desk ExpressApplication Server by default) in Notepad and add the following section just above the </configuration> tag as shown below:

...
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v2.0"/>
<providerOption name="WarnAsError" value="false"/>
</compiler>
</compilers>
</system.codedom>
</configuration>

Summary

Whilst this may be a somewhat isolated situation, hopefully it will save someone pulling their hair out as we did for a morning! As always, comments, positive or negative, are always welcome.

More Traffic Lights

In previous posts (http://www.joatit.com/wordpress/?p=96) and (http://www.joatit.com/wordpress/?p=69) I showed how to colour code the quick views in a traffic light style using the following query:

(CASE WHEN [CLK_STOPTIME:] IS NOT NULL THEN 'On Hold' WHEN GETDATE() > [RECOMMENDEDFIX_DATE:] AND GETDATE() < [DUE_DATE:] THEN 'Warning' WHEN GETDATE() > [DUE_DATE:] THEN 'Critical' ELSE 'Normal' END)

Recently I was talking to one of our users who, when using Service Desk Express 9.8, rather liked the ability to view closed incidents in the quick views. She commented however, that when you viewed closed incidents with the above query everything went red eventually. It would be much better if you could see if the call was inside or outside SLA as defined by Close Date vs. Due Date.

So without further ado; creating a calculated field with the query below will not only achieve the same results as the previous posts but additionally will show if the incident was closed in time (Normal) or not (Critical) when looking at closed incidents:

(CASE WHEN BASE.[CLK_STOPTIME:] IS NOT NULL AND BASE.[STATUS] = 'O' THEN 'On Hold' WHEN GETDATE() > BASE.[RECOMMENDEDFIX_DATE:] AND GETDATE() < BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Warning' WHEN GETDATE() > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Critical' WHEN BASE.[CLOSED ON] > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'C' THEN 'Critical' ELSE 'Normal' END)

Hope it helps. As always, please keep the feedback coming. It makes sure that what I am posting is of relevance and interest to the people reading.

Understanding the Purchase Request fields and how they are calculated

Before using the Purchase Request module it is important to understand some of the more obscure fields and how the totals are calculated. So the purpose of this post is to do just that:

Sub Total

The Sub Total is surprisingly enough quite straightforward. The code for it is:

(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."_INACTIVE_:" = 0)

So essentially, all that is happening here is that we are summing all the prices of the active Purchasing Items for this Purchase Request.

Total

The simplicity of the Sub Total field is not however mirrored in the Total field. The code for it is:

(((select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0)  from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG  where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE"  and CATALOG."TAXABLE" = 1) * (1+(ISNULL(BASE."TAX_%",0)*0.01) ))+(select ISNULL(SUM(CHILD."PRICE"*CHILD."QUANTITY"),0) from "_SMDBA_"."_PI_" as CHILD , "_SMDBA_"."_CATALOG_" as CATALOG where CHILD."SEQ_PR" = BASE."SEQUENCE" and CHILD."CATALOG#" = CATALOG."SEQUENCE" and CATALOG."TAXABLE" = 0)+ISNULL(BASE."SHIPPING_COST",0))

So what is going on here? Well the key lies in the CI Type module (formerly known as Inventory Catalog). Each Purchasing Item (that we are summing) is of a certain CI Type and each CI Type has a flag that determines if that CI Type is Taxable or not. Now depending on whether the Purchasing Item is of a taxable CI Type or not determines whether the Tax field (in the Purchase Request module) gets added on or not. So let’s say you have a Purchase Request with two line items each costing $100. One of the line items is taxable (as determined by the CI Type) and the other is not, and you have set a tax value of 9%. The Total would be:

(100 * 1.09) + (100) = $209 + Shipping (Not Taxable)

This is rather clever but if you don’t know about it you can have a lot of head scratching trying to understand why your Totals don’t match what you think they should.

There is a significant gotcha with this system. Namely, this field is Nullable which means that if you happen to remove the field from the CI Type form then when you save a CI Type, that field is left as Null. Now, when the Purchase Request Total field attempts to sum values and returns a Null for Taxable, the entire Purchase Total becomes 0 – so whoever made that decision should be shot!

Summary

With an understanding of how these fields are calculated and the little gotchas that go along the way you should hopefully be in a better position when you are implementing/understanding Purchasing in Service Desk Express. I have included so links to other relevant articles on Purchasing below. As always, hope this has been helpful. Any comments, positive or negative, are incredibly welcome.

Related Posts:

Forward Schedule of Change V2.0

Version 2.0 of the Forward Schedule of Change application is nearing completion and should be available before the end of the month. Some of the changes that have been incorporated include:

  • Full screen change calendar with change details on separate page.
  • Change details now include Change History, Change Assessments and Change Approvals (visibility of which can be toggled on and off by the application administrator).
  • Links to update changes (visibility of which can be toggled on and off by the application administrator).
  • New search functionality allowing the searching of selected fields.

The new layout, removing the change details panel on the right hand side of the application, will not be to everyone’s liking and as such V1.0 will still be available if organisations prefer the old style.

As always, feedback (positive or negative) is always welcome.

HTML Emails in SDE

A lot of people ask about sending email notifications from SDE in HTML format.  This is a great way to make your notifications consistent and easy to read for your users.  It also works very well for including links in the email for your users to click.  There aren’t too many email clients that won’t display HTML emails these days, although I have in the past had slight problems with Lotus Notes where tables don’t display exactly how you expect.  It is not a show-stopper and usually can be remedied with some minor tweaking specific to the way Notes handles HTML.  Also, certain versions of BlackBerry devices do not display HTML at all, or not very well – especially HTML links.

Business Rules Templates used in conjunction with some simple HTML and Cascading Style Sheets will make the notifications look very professional and consistent.   At the end of this post I will provide some downloadable Business Rules Templates that you can use in your own rules and tweak as needed.  Let’s dive right in and get started.

The basic requirement for having emails sent by SDE display in HTML format is to include the <HTML> and </html> tags around the body of the notification.  HTML formatting in email subjects is not supported by email clients, however.  Here is an example of a simple Incident notification in HTML format:

<HTML>
Incident #{TR,Incident #} has been created for you.<BR>
<BR>
<B>Open Date & Time:</b> {TR,Open Date & Time}<BR>
<B>Category:</b> {TR,Subject Description}<BR>
<B>Urgency:</b> {TR,Urgency ID:}
</html>

Remember to always use the HTML line break tag <BR> rather than just pressing enter in the notification.

The above is a very basic example of how to use HTML in notifications.  However, what if you have multiple Incident notifications and want to use the same format in all of them?  You could put the HTML into each notification body, but then if you ever want to make changes you are stuck modifying multiple rules.   This is where Business Rules Templates come into play.  Here is the template I use at the beginning of all of my notifications – I name it _HTML_HEAD:

<HTML>
<HEAD>
<STYLE type=”text/css”>
body,table,td,th{
font-family:arial,helvetica;
font-size:10pt;
}
table,td,th{
border:1px solid #000;
border-collapse:collapse;
margin:0;
padding:0;
text-align:left;
}
td,th{
padding:.2em .5em;
vertical-align:top;
font-weight:normal;
}
tbody td{
background:#ccc;
}
tbody th{
background:#aaa;
font-size:9pt;
font-weight:bold;
}
pre{
font-family:arial,helvetica;
}
</style>
</head>
<BODY>

This provides very nice formatting for including information in a table format.  I create an _HTML_FOOT template also:

<BR><CENTER><HR width=”80%” size=”1″ noshade>
<FONT color=”silver” size=”1″>{EVENTMODULE} {EVENTMETHOD} / {RULENAME}</font>
</body>
</html>

Putting this at the end of every notification will provide you with information for troubleshooting purposes, including information about the event and Business Rule that generated the notification.  Note that the module you choose for the HTML templates does not matter.  Since no actual fields from any module are used in them, you can use them for notifications based on Incidents, Work Orders, Change Requests, and any other module.

Here is the standard Incident template I use for notifications, which I name _INCIDENT:

<TABLE width=”80%”>
<TR>
<TH width=”150″>Incident #</th>
<TD>{TR,Incident #}</td>
</tr>
<TR>
<TH>Opened</th>
<TD>{TR,Open Date & Time}</td>
</tr>
<TR>
<TH>Client</th>
<TD>{TR,Client ID} – {TR,First Name} {TR,Last Name}</td>
</tr>
<TR>
<TH>Phone</th>
<TD>{TR,Cl.Phone #}</td>
</tr>
<TR>
<TH>Email</th>
<TD>{EXTRACT,{},{TR,Client Email}}</td>
</tr>
<TR>
<TH>Company</th>
<TD>{TR,Company Name}</td>
</tr>
<TR>
<TH>Department</th>
<TD>{TR,Department Name}</td>
</tr>
<TR>
<TH>Category</th>
<TD>{TR,Subject Description}</td>
</tr>
<TR>
<TH>Urgency</th>
<TD>{TR,Urgency ID:}</td>
</tr>
<TR>
<TH>Assigned Staff</th>
<TD>{TR,Login ID Assigned To} – {TR,First Name Assigned To} {TR,Last Name Assigned To}</td>
</tr>
<TR>
<TH>Assigned Group</th>
<TD>{TR,Group Name}</td>
</tr>
<TR>
<TH colspan=”2″>Description</th>
</tr>
<TR>
<TD colspan=”2″><PRE>{TR,Incident Description}</pre></td>
</tr>
</table>

Notice I use the <TH> and <TD> table tags in conjunction with the style sheet to control the formatting.  Putting these templates together into a notification is very simple.  I usually include descriptive text unique to each rule so that the person receiving the email will understand why it was sent to them:

Incident Notification with HTML and templates

Here is how the notification looks to the person receiving it:

Incident Notification Result

With SDE’s ability to generate a link that takes a Staff member to the application and opens a particular record, it is common to include the links in email notifications.  However, you don’t want the Clients seeing these links because they won’t work.  Because of this, I create separate templates for the links.  Here is the template I use for Incident links, appropriately named _INCIDENT_LINKS:

<A HREF=”{LINK,Incident,{TR,Incident #},}”>Click here</a> to view this Incident in SDE.

This is so simple it seems like using a template for this is overkill.  Again, remember that you may include this template in many rules and if you ever want to make a change to it you’ll only have to make that change to the template.  My template _WORKORDER_LINKS also includes a link to the parent Incident (if one exists).  This is an example of the fact that MATH statements, and of course DB Lookups, can be used in templates as well.

<A HREF=”{LINK,Work Orders,{TR,Work Order #},}”>Click here</a> to view the Work Order in SDE.<BR>
{MATH,(CASE WHEN ‘{TR,Seq.HD #}’ <> ” THEN ‘<A HREF=”{LINK,Incident,{TR,Seq.HD #},}”>Click here</a> to view the parent Incident in SDE.’ ELSE ” END)}

The above template will always show a link to the Work Order, but the MATH statement will only display the link to the parent Incident if there is one.

The last aspect of templates I want to discuss is the context of the Business Rule and template.  This is very important.  As an example, suppose you want to trigger a Business Rule from the creation of an Incident Detail with a particular Action ID.  If you want to include the Incident template in a notification from this rule the problem is that you can’t just use {TR} fields from the Incident – the context of the rule is Incident Details, not Incidents.  For this reason, I create a template named _INCIDENT_LOOKUPS:

<TABLE width=”80%”>
<TR>
<TH width=”150″>Incident #</th>
<TD>{TR,Incident #}</td>
</tr>
<TR>
<TH>Opened</th>
<TD>{DB,Incident,Open Date & Time,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Client</th>
<TD>{DB,Incident,Client ID,”Incident #” = {TR,Incident #}} – {DB,Incident,First Name,”Incident #” = {TR,Incident #}} {DB,Incident,Last Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Phone</th>
<TD>{DB,Incident,Cl.Phone #,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Email</th>
<TD>{EXTRACT,{},{DB,Incident,Client Email,”Incident #” = {TR,Incident #}}}</td>
</tr>
<TR>
<TH>Company</th>
<TD>{DB,Incident,Company Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Department</th>
<TD>{DB,Incident,Department Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Category</th>
<TD>{DB,Incident,Subject Description,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Urgency</th>
<TD>{DB,Incident,Urgency ID:,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Assigned Staff</th>
<TD>{DB,Incident,Login ID Assigned To,”Incident #” = {TR,Incident #}} – {DB,Incident,First Name Assigned To,”Incident #” = {TR,Incident #}} {DB,Incident,Last Name Assigned To,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH>Assigned Group</th>
<TD>{DB,Incident,Group Name,”Incident #” = {TR,Incident #}}</td>
</tr>
<TR>
<TH colspan=”2″>Description</th>
</tr>
<TR>
<TD colspan=”2″><PRE>{DB,Incident,Incident Description,”Incident #” = {TR,Incident #}}</pre></td>
</tr>
</table>

As you can see, this template uses the Incident # to lookup all the Incident fields.  It is simply a coincidence that the foreign key in the Incident Details module is called Incident #.  This is, however, a useful coincidence.  If you are doing time-based rules where the notification is delayed, you always want to use DB Lookups to get the most current data when the notification is sent.  Since {TR,Incident #} is a valid field in the Incident module, you can use this lookup template in those notifications.

Finally, here is a link to a set of text files that contain the basic templates I like to use.   You can modify them to suit your needs, of course.  Keep in mind that in the Change Approval template I have used some fields that aren’t available in the module by default.  You’ll either have to remove them from the template, change them to DB Lookups, or add them as virtual fields to the Change Approval module (which is what I do).

Downloadable Business Rules Templates

CI Assemblies/Configurations – How To Use Them – Part 2

In part 1 of this series (http://www.joatit.com/wordpress/?p=539) I described the relationships and use of the modules associated with the CI Assemblies/Configurations module. I also described how I would implement a real-world example using these modules and eluded to the need to automate some functionality through business rules and be aware of certain issues when it comes to reporting.

In this post, I want to cover off those points and hopefully provide some additional resources to help you get the most out of the product

Business Process Automation

In the first post in this series I suggested it would be a sensible idea to have a CI Assembly/Configuration for each client to manage their mobile assets. Fine, but what a pain in the proverbial if for every client you create you have to manually create a CI Assembly/Configuration and then link it to that client! At this point I would normally suggest a business rule but unfortunately we have a problem – namely Service Desk Express does not allow you to create business rules based on the Assembly Clients module which would mean we could create the CI Assembly/Configuration but couldn’t link it to the client. So we need an alternative – enter the Integration Console:

CC – Create and Link CI Assemblies to Clients (click here to download).

What we need to do to achieve this is create a package that contains a step and a substep:

cc-create-and-link-ci-assemblies-to-clients-01

Initiator:

We will set the Initiator to be a schedule running every 15 minutes (as shown below) such that within 15 minutes of creating a new account your CI Assembly/Configuration will be created and linked.

cc-create-and-link-ci-assemblies-to-clients-02cc-create-and-link-ci-assemblies-to-clients-03

Source:

The key to this package is the source for the Create CI Assemblies Step. We need to run a SQL query to only return the clients that don’t have a matching CI Assembly/Configuration so we need to use the ODBC adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-031cc-create-and-link-ci-assemblies-to-clients-04

using the SQL query:

SELECT C1.[CLIENT], C1.[COMPANY], C1.[DEPT], C1.[BILL TO], C1.[BLDNG], C1.[OFFICE] FROM [_SMBDA_].[_CUSTOMER_] C1 LEFT JOIN [_SMDBA_].[_CONFIG_] C2 ON C1.[CLIENT] = C2.[CONFIG] WHERE C2.[CONFIG] IS NULL AND C1.[_INACTIVE_:] = 0

Target:

We then map the target to the CI Assembly view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-05cc-create-and-link-ci-assemblies-to-clients-06cc-create-and-link-ci-assemblies-to-clients-07

So that creates the CI Assemblies/Configurations for the Clients – now we need the sub step to link them to each other.

Initiator:

As can be seen below we only want the ub step to run if the parent succeeds:

cc-create-and-link-ci-assemblies-to-clients-08

Source:

None/Not used.

Target:

Finally we map the target to the Assembly Clients view using the SDE adapter type as shown below:

cc-create-and-link-ci-assemblies-to-clients-09cc-create-and-link-ci-assemblies-to-clients-10cc-create-and-link-ci-assemblies-to-clients-11

This will create and link a CI Assembly for every client that does not already have one based on matching the Client ID with CI Assembly ID  as mentioned in my previous post.

Orphaned Configurations and CSBRs

I wrote a post a while back on how to warn users if the CI Assembly/Configuration they are saving is NOT linked to a client. I would also suggest that you implement this business rule as well.

Reporting

Now if you have implemented something similar to what I suggested in the previous post there are a couple of issues relating to reporting that you need to be aware of. Specifically, if management ask for a list of all assets and their users you need to be very careful how you handle the many to many relationship between Clients and CI Assemblies otherwise you could end up with duplicate assets in the cases when an asset is used by multiple clients. So what are your options:

Exclude duplicates

Your first option is to exclude these circumstances altogether. Let me explain:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description], CC1.[Client ID], CC1.[Configuration ID]
FROM [_SMDBA_].[Inventory Items] II LEFT JOIN [_SMDBA_].[Configurations] C ON C.[Sequence] = II.[Seq.Configuration]
LEFT JOIN [_SMDBA_].[Configurations Clients] CC1 ON CC1.[Seq.Configuration] = C.[Sequence]
WHERE II.[InActive:] = 0 AND (SELECT COUNT(*) FROM  [_SMDBA_].[Configurations Clients] CC2 WHERE CC2.[Seq.Configuration] = CC1.[Seq.Configuration]) < 2

The advantage of this is that your report is accurate in terms of assets as long as you appreciate that your assets do not include shared assets. The disadvantages are pretty obvious in that gaining a holistic view of your assets using this method is almost impossible and as such I would not recommend it.

Show first user only

An alternative solution is to only show the asset once and assume that the first person sharing an asset is the person responsible for it. This is done using an embedded SELECT statement in your report:

SELECT II.[Sequence], II.[Asset/Tag #], II.[Serial #], II.[Category ID], II.[Part Description],
(SELECT TOP 1 CC.[Client ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Client ID',
(SELECT TOP 1 CC.[Configuration ID] FROM [_SMDBA_].[Configurations Clients] CC WHERE II.[Seq.Configuration] = CC.[Seq.Configuration]) As 'Configuration ID'
FROM [_SMDBA_].[Inventory Items] II
WHERE II.[InActive:] = 0

The big advantages here are that there is no customisation required and critically the correct number of assets are shown on report (including shared assets). The big disadvantage is that it makes a very large and often incorrect assumption – i.e. that the first person sharing an asset is the person responsible for it. This simple isn’t true in many circumstances and if you are using this information for billing then you’re potentially opening up a whole can of worms. Again, not my preferred option.

Add Person Responsible field and CSBR

My preferred option is to add a new field (Seq.Responsible) to the Inventory Item/Configuration Item module – a foreign key to the Client module (obviously with its relevant virtual fields) that is used to store the Person Responsible for the asset as shown below:

responsibleforeignkey

Add this field on to your Inventory Item/Configuration Item form and you can then create a simple CSBR that on change of Configuration/CI Assembly, prefill the Person Responsible with the first person linked to that Configuration/CI Assembly. The massive advantage here is that although the person is prefilled you have the ability to overrule the suggestion. What is more you can even select a departmental manager who does not use the shared asset in question. Now you have the ability to report directly off the Inventory Item/Configuration Item module giving you accurate asset information that CAN be used for billing without arguement.

CSBR: CI – Prefill Responsible Client On Change Of CI Assembly

When On Data Change occurs for field CI Assembly ID where {TR,CI Assembly ID} NotEqual <Blank>
Populate Current Form - JQS - Configuration Item (the new form with the Seq.Responsible field added) setting Seq.Responsible = {DB,Assembly Clients,Seq.Client,"Seq.CIAssembly" = {TR,Seq.CIAssembly} DB}
Assigned To JQS - Configuration Item

Summary

CI Assemblies provide a highly flexible solution to the management of assets in Service Desk Express but it is essential that robust business processes are put in place to combat the risk of poor data. As always comments, positive or negative, are always welcome.

Restoring Service Desk Express from one server to another

I can’t imagine there are many organisations that use BMC Service Desk Express but don’t have a development / test environment. Personally, I always prefer to have three environments:

  • Development: Where I am trying out new concepts, customisations, business rules etc.
  • Test/Training: A permanant training environment for new staff and a staging area where new customisations can be tested and approved by the business.
  • Production

Now in an ideal world your Production and Test/Training environment would be either identical or, in the case of just prior to a new release, slightly out of sync. This is normally achieved by restoring a backup of the production database over the test/training database. Whilst this is relatively easy if the Production and Test/Training environments are on the same physical network, this can become slightly more involved if this is not the case. What I want to do with this post is provide a simple backup and restore procedure that works in any SQL Server environment.

Backup

This is the easy bit – the only catch is that frequently your Test/Training environment doesn’t have the disk capacity of your production SQL server and, due to the way that Service Desk Express polls the database, the transaction logs of an SDE database can be huge! So assuming that your DB is called SDE and you are using SQL Server 2005 your code would look something like:

USE SDE
BACKUP LOG SDE WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(SDE_log,2)
GO
BACKUP DATABASE SDE TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x.bak'
GO

where YYYY is the year, MM is the month, DD is the day and x is simply an incrementing number.

Restore

Once we get our backup from one server to the other we then need to restore it. Restoring a normal database is easy – restoring an Service Desk Express database is also easy but unfortunately requires a couple of preparation steps:

Services

Service Desk Express uses a number of Windows Services to perform essential background tasks and these services connect to the database to perform their function. You can’t restore a database without first closing the connections to it. For this reason, and only on a Test/Training or Development Service Desk Express application server, I deliberately configure these services slightly differently:

Warning: Do NOT do this on a production system!

From the desktop click Start > Run and type Services.msc and press Return. From the Services window scroll down to the SDE Indexing Service and select it. Select Actions > Properties from the Services window menu and from the Properties window change the Startup Type to Manual and click Stop. Once the service has successfully stopped click OK to return to the Services window. Repeat this process for the following services: SDE Integration Engine; SDE Job Processor – SDEBR; SDE Mail Processor – SDEBR; and SDE Push Service – SDEPPS. Close the Services window.

Now if we click Start > Run and type IISRESET and press Return we can completely restart IIS flushing out all the metadata without the services reconnecting to the database. Now we can restore our database:

RESTORE DATABASE SDE FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x.bak'
GO

If you have issues with this statement the chances are that your data and log files in your production environment are on a different drive and consequently you need to use the WITH MOVE command to move them to the correct place:

RESTORE DATABASE SDE FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupSDE_YYYY_MM_DD_x' WITH MOVE 'SDE_data' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData', MOVE 'SDE_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData'
GO

Fixing Everything Else

If only it was that simple and then you could just log in and all would be perfect! Unfortunately it is far from the case:

Owners, Logins and Users

Your first problem is that the _SMSYSADMIN_ account needs to be the owner of the SDE database unless it is a SQL Server sysadmin. It’s your choice but the former is more secure than the latter:

EXEC sp_ChangeDBOwner '_SMSYSADMIN_'
or
EXEC sp_AddSrvRoleMember '_SMSYSADMIN_', 'sysadmin'

Your next problem is that when you restore a db from one server to another the roles and users don’t automatically sort themselves out. As such you need to match them up using the following SQL:

EXEC sp_Change_Users_Login 'Auto_Fix', '_SMDBA_'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SMSYSEXEC'
EXEC sp_Change_Users_Login 'Auto_Fix', 'MGUSER20xx'
EXEC sp_Change_Users_Login 'Auto_Fix', '_SMSYSADMIN_'

If you are using Service Desk Express 9.6 or above then you also need to run:

EXEC sp_Change_Users_Login 'Auto_Fix', 'SYSTEM ADMINISTRATION'
EXEC sp_Change_Users_Login 'Auto_Fix', 'ADMINISTRATOR'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SELFSERVICE'
EXEC sp_Change_Users_Login 'Auto_Fix', 'SYSTEMACCOUNT'
EXEC sp_Change_Users_Login 'Auto_Fix', 'HELP DESK'

If you are using Service Desk Express 9.2 or below then you also need to run:

EXEC sp_Change_Users_Login 'Auto_Fix', 'MAGIC'
EXEC sp_Change_Users_Login 'Auto_Fix', 'EXTERNAL_SUPPORT'

Service Desk Express obtains its SYSADMIN password by decrypting the result from the NAMSYSWORK table (< v9.60) or by decrypting the result from the registry (v.9.60 and above). So unless your SYSADMIN password is the same in Production as it is in the Test/Training you will need to update this value. The tool to update this value is the “Set System Admin Password” utility available by clicking Start > All Programs > BMC Service Desk Express > Set System Admin Password. The problem is that this application wont work unless all the Service Desk Express logins are migrated from Production! So how do you do that?

Well the easiest route is to use the DBLogins.exe available from …. This does the hard work for you BUT only works if the Production and Test/Training systems are on the same physical network. Another option is to use sp_help_revlogin – a stored procedure that is created following this knowledge base article. This works very well even if you servers are not on the same network. The only issue with this is that, by default, it will transfer EVERY login whether used by Service Desk Express or not! So an alternative approach is to use a little bit of SQL code that seems to work rather well:

WARNING: The code below will ONLY work with Service Desk Express 9.00 and above.

DECLARE cur CURSOR
READ_ONLY
FOR SELECT [CODE], 'Staff' FROM _SMDBA_._PERSONNEL_ UNION SELECT [CODE], 'Group' FROM _SMDBA_._GROUPS_
DECLARE @Code NVARCHAR(30), @Type NVARCHAR(30), @SqlStatement NVARCHAR(4000), @SysAdminPwd NVARCHAR(100)
SET @SysAdminPwd = 'Enter Your SysAdmin Password Here'
OPEN cur
FETCH NEXT FROM cur INTO @Code, @Type
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF NOT EXISTS(SELECT loginname from master.sys.syslogins where name = @Code and dbname = 'master')
BEGIN
IF @Type = 'Group'
BEGIN
SET @SqlStatement = 'CREATE LOGIN [' + @Code + '] WITH PASSWORD=N''' + @SysAdminPwd + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_ExecuteSQL @SqlStatement
END
ELSE
BEGIN
SET @SqlStatement = 'CREATE LOGIN [' + @Code + '] WITH PASSWORD=N''welcome'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_ExecuteSQL @SqlStatement
END
END
-- Once the login exists we just fix it up with the user in the db
EXEC sp_Change_Users_Login 'Auto_Fix', @Code
END
FETCH NEXT FROM cur INTO @Code, @Type
END
CLOSE cur
DEALLOCATE cur
GO

The above code is a lot simpler than it looks. Essentially all it does is gets all the users/groups from the _PERSONNEL_ and _GROUPS_ tables and then checks to see if a SQL login exists for each of them. If it doesn’t then it creates it whilst if it does then it simply fixes it up. Couple of things to point out though:

  • You need to enter your SYSADMIN password as all groups passwords are the same as the SYSADMIN password.
  • Any staff logins it creates will be created with a password of welcome

Once this is complete you can run the Set System Admin Password utility.

Are we there yet?

Surely we must be there now? I’m afraid not quite. So what is left. Well unless you took a backup whilst all of the Windows Services were turned off and no one had logged into Service Desk Express since a restart (sounding unlikely isn’t it) , then you will have backed up the process/meta data that Service Desk Express uses which we need to get rid of:

DELETE FROM SMSYSPROCESS

Theoretically, you can now log in (assuming everything above went according to plan) but…your business rules wont work properly, your Integration Engine packages will fail and if you are using Web Services then they will fail too! A couple more housekeeping tasks plus their explainations:

DELETE FROM dbo.NAMSYSSERVERS
DECLARE @OldAppServerName NVARCHAR(100), @NewAppServerName NVARCHAR(100), @NewIPAddress NVARCHAR(100)
SET @OldAppServerName = 'Enter your OLD app server name here'
SET @NewAppServerName = 'Enter your NEW app server name here'
SET @NewIPAddress = 'Enter your NEW app server IP address here'
DELETE FROM dbo.NAMSYSPROPERTIES WHERE [NAME] LIKE 'namMBLQParams|' + @OldAppServerName + '%'
UPDATE dbo.INTEGRATION_ENGINES SET SERVER_NAME = @NewAppServerName
UPDATE dbo.WSSERVERS SET NAME = @NewAppServerName, IPADDRESS= @NewIPAddress WHERE NAME = @OldAppServerName

After running this lot you will need to manually start the Service Desk Express Windows Services described at the top of this post and that will automatically fix up your business rules and stop your Integration Engine and Web Services packages failing. You should now be good to go.

Summary

So as you can see it is somewhat convoluted transfering a database from one server to another and hopefully this post has gone someway to providing a solution to it. Hope it helps. As always any comments, positive or negative, are always welcome.