Synchronising with Active Directory is NOT the best approach

This is a deliberately argumentative statement that, throughout this series of posts, I want to explore and explain my reasoning behind. The other aim of this series is to provide a real-world example of a new starter/leaver process that can be implemented in Service Desk Express.

For many years architects have been pushing for Active Directory synchronisation from every enterprise application vendor with, in most cases, very good reasons. AD synchonisation provides the business with a single directory source that can be used for everything from authentication/authorisation services to location and reporting structure data depending on how well it is populated. No one, not even argumentative me, would argue that a single directory source is a bad thing, nor that single sign on wouldn’t be welcomed by every enterprise user. My argument is NOT with AD. My objection to AD/Service Desk synchronisation is from a business process perspective – namely I believe that the source of HR data (which is what is being synchronised here) is better suited to the HR/Payroll system within an enterprise rather than AD. I can’t imagine that there are many organisations who add their new employees to AD before they are added to payroll and, in my opinion, the system admins should be tasked properly with creating an AD/Exchange account such that OLA/SLAs can be managed and customer satisfaction monitored.  So what, in my humble opinion, is the correct solution:

Data Flow Solution

As you can see from the diagram above, my solution would be to put the Service Desk in the centre acting as the hub through which human business processes are transacted. The initial employee record is created in the HR/Payroll system which then creates a client/customer record in Service Desk Express (using the Integration Engine). This in turn kicks off the New Starter business process that includes, amongst other tasks, an action to create an AD/Exchange account for the new employee. By updating the WinUserID field in the Client record with the user’s AD domain\username, the link between AD and Service Desk Express is then made and Self-Service Single Sign On can be provided. When employee records are updated (either through a request through the Service Desk or directly with the HR/Payroll system) the employee details are updated in Service Desk Express and then in AD.

New Starter Business Process

I am not going to discuss the initial synchronisation with the HR/Payroll system as this was discussed in a previous post (Using the Integration Engine to Manage Clients) and there is an appropriate Integration Engine package that can be downloaded and tweaked for your requirements. The key point to mention is that of “datakeys:”

As you can see Service Desk Express holds the datakey for both the HR/Payroll system (EmployeeID –> CLIENT) and AD (sAMAccountName –> WINUSERID). This is crucial to the solution.

In my example business process above, the employee is going to be provided with the following:

• Workstation (either static or mobile) with a standard operating system/applications build.
• Active Directory and Exchange account for the domain.
• Induction.

The important point to note about my example process is that activities happen both in parallel and sequentially and I want to demonstrate how this can be accommodated within business rules in Service Desk Express.

In the next post I will walk through the creation of the New Starter Process in Service Desk Express based on the output of the Using the Integration Engine to Manage Clients post I mentioned earlier.

Popularity: 22% [?]

A post in the MagicSolutions forum posed a challenge that I thought would be fun to try and solve, namely how to filter the Assign To grids in Service Desk Express. Unlike other popups that are accessed from a popup icon on the form these popups are accessed by clicking on the Assign To menu item. As such there is no out of the box facility within the application to filter this list as there is with other popups (see How to dynamically filter a popup list in Service Desk Express).

Solution

This is an unsupported hack of the ASP.NET code

The first thing to do is determine how you want to filter the popup as there is usually little point hardcoding something if it can be dynamic. For my example, I created a new field in the Support Staff module called Hide In Popups as shown below:

Now this field will be granted a unique number by Service Desk Express and it is important that you find out what this number is using the SQL statement below:

SELECT COLSEQ FROM dbo.SMSYSBASEVIEWDATA WHERE TBLSEQ = 18 AND VIEWCOLNAME = 'Hide In Popups'

In my case this returned a value of 1001.

To filter the popup we need to add some code in assignto_grid.aspx (located in C:\Program Files\BMC\Service Desk Express\Application Server by default) just before the opening <HTML> tag:

<%
if (ViewName == "18" && sMode == "STAFF")
{
AddWhereClause += " + ||1001|| = 0";
}
else if (ViewName == "18" && sMode == "MYGROUP")
{
AddWhereClause += " AND ||1001|| = 0";
}
%>

Save the file and you should be good to go.

So how does this code work. Well the ViewName == “18″ bit means that I only want to filter the Support Staff not the Groups (which incidentally would be ViewName == “13″). The sMode bit refers to which menu item this popup was called from e.g. Member of My Groups. The AddWhereClause bit simply says check if field 1001 (my new Hide in Popups field) is equal to 0. As to why the syntax is different for the two modes – PASS!

Summary

Anyway, seems to work. As always any feedback (positive or negative) is always welcome.

Popularity: 13% [?]

In Part 1 of this series I discussed how to create the Services, Organisations and Service Level Agreements (SLAs) necessary for our example of an internal service desk servicing six departments with the Finance department requiring a tighter SLA on one of the services then any of the other departments during March each year.

In this post, 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.

SLA Criteria

We said that we wanted to provide our Finance department with a tighter SLA during March each year. How do we go about doing that? Well the first thing we need to do is create the new Urgency/Priority and SLA that we want to offer as shown below:

Another Gotcha

We need to add a SLA Criteria that checks if the month the call was opened was March. Problem – SLA Criteria can only check the values of fields within the module they are configured for (i.e. in our case Incidents) and don’t allow for any expressions. Solution – Create a field to hold the month the call was opened and populate it on save using a CSBR as shown below:

You MUST add the field to the forms used otherwise you will get an error – Input string not in correct format. It CAN be hidden though.

Now in order that this SLA is only offered during March we need to configure an SLA Criteria as shown below:

Notice that this criteria only considers the March clause NOT the Finance department clause. That comes later when we link Services, Organisations and SLAs. You could include the Finance department clause but then you would need to keep changing you SLA if, for example, another department wanted the same SLA.

In this example, given all the hassle of creating a field and CSBR, you could choose an alternative solution of selecting the 24HR_MARCH SLA and configuring the Start and End dates to only include March. The problem here of course is that you need to create one for every year which is a little painful. What is more, it wouldn’t have given me the opportunity to explain SLA Criteria

Service Organisation SLA Links

Now there’s a mouthful! Service Organisation SLA Links (LINK) are what they say on the tin – the way to assign a SLA to a service consumed by an organisation. They are accessible from the Service, Organization, and SLA forms via their respective tabs. In our example above we want each department to use our Standard 48HR SLA for the service of Application Support. Now we could create a separate LINK for each Organisation but we don’t need to. If we open the Service form, find our Application Support service and click Add on the SLA/Org tab we can create a LINK as shown below:

What the record above says is “Offer this SLA whenever the Application Support Service is selected.” This is fine for what we want at the moment. Now what we need to do is create another LINK for the Finance Organisation to provide the 24HR SLA against the Application Support Service as shown below:

Thus your Application Support service should look like this:

Now, when anyone logs a ticket against the Application Support Service between April and February inclusive, the only option will be the 48HR SLA as shown below:

However, when a member of the Finance department log a ticket against the Application Support Service during March, a choice of either the 24HR SLA or the 48HR SLA will appear as shown below:

…assuming you have turned the functionality on!

Service Level Agreement Rules

Yes that’s right – out of the box the SLA functionality is NOT turned on. To enable Service Level Agreements you need to click on the Service Level Agreement Rules item in the Service Level Management tab. Then from the Enable SLAs window that appears check the box to enable SLAs as shown below:

So here is the kicker and this is absolutely WOEFUL!

You have to manually enable the SLA functionality for each group by logging in as that group and turning it on by checking the box as described above! Imagine what that is like on 126 Groups! Scripts to the rescue…

SDE_SetDefaultSLARulesForAllGroups.sql

The above script will turn on SLA Management for every group!

One Final Issue!

SLA’s are NOT mandatory out of the box. What this means is that if one of your Service Desk Agents decides to close the Select From SLA popup that appears to select the SLA, no SLA is populated at all. If you want to enforce SLAs you MUST make your SLA ID field selectable (i.e. NOT readonly) and write a Client Side Business Rule (CSBR) as shown below:
When an Incident is Saved

Summary

Hopefully, throughout these two posts I have given you an idea of what the art of the possible might be using the SLA modules available with Service Desk Express. As always, comments, positive or negative, are always welcome.

Popularity: 14% [?]

As some of you might know, I am a BIG BIG fan of Microsoft’s SQL Reporting Services 2005 and STILL (Arggh!) haven’t had a chance to play with SQL Reporting Services 2008! For awhile now, I have been wanting to take my reports to a new level (mainly targetted at producing an enhanced suits of the Service Desk Express reports) and as such I have been looking into some of the 3rd party add-ons that are available.

Recently I have been doing some experimentation with the demo version of the Dundas Map for SQL Reporting Services 2005 – specifically attempting to dynamically populate symbols (in my case Cities) based on dynamic latitudes and longitudes retrieved from a database. Whilst the Dundas Map Control is very cool, this simple task is NOT at all straightforward and consequently I thought explaining it would make a good post. So here goes.

The Basic Stuff

Once you have installed the Dundas Map Control you can drag it onto any report using the SQL Server Business Intelligence Development Studio. A nice Wizard appears…KILL IT!! Seriously, the Wizard is fine if you want to colour sections of the map based on the sum of values for that area (e.g. sales in Wales vs Scotland). For dynamic symbols it is completely useless as you MUST populate dynamic symbols in code (thankfully either C# or VB.NET)! So you should end up with something that looks like this:

Right-click on the Map and select Properties from the popup menu and this is where you can configure what part of the world the map displays. So in my case I have selected the UK and dragged my Dundas Map Control to fill the area of my report as shown below:

Now my database structure looks like this:

In order to make this post realistic, I want my report to call a stored procedure to return the Cities for a given region (or all regions) and as such I need a stored procedure, a dataset of regions and a parameter that uses these regions as shown below:

dsRegions:

SELECT -1 As [RegionID], 'All Regions' As [RegionName] UNION SELECT RegionID, RegionName FROM dbo.Regions ORDER BY 2

RegionID Parameter:

StoredProcedure [dbo].[up_Cities_Select]:

CREATE PROC [dbo].[up_Cities_Select]
@RegionID int
AS
SELECT R.RegionName,
C.CityName,
C.Latitude,
C.Longitude,
C.Population
FROM dbo.Cities C
JOIN dbo.Regions R
ON R.RegionID = C.RegionID
WHERE (R.RegionID = @RegionID OR @RegionID = -1)
ORDER BY C.Population DESC

Code

So far so good. Now here is where things get a little crazy. Right-click on the Map and select View Code from the popup menu. This bring up the Code Editor Window where you choose your choice of language and select the Event you are interested in coding for. For the purpose of this demo I will select VB.NET and the event I am interested in is PostInitialize as shown below:

There are couple of gotchas that you REALLY need to be aware of before you start using this control:

• You cannot access the DataSources from the code and as such everything (including the connectionstring to the database) needs to be provided in code!
• You cannot access the DataSets from the code which means that you need to write all the SQL in the code. For this reason I cannot imagine a realistic example when you wouldn’t use a stored procedure as it makes the code SO much more legible as you will see.

Let me step you through my simple code:

The first thing we need to do is add a SymbolDataBindingRule to tell the MapControl which fields to use for the Latitude, Longitude and Name.

Dim _sbr As New SymbolDataBindingRule
With _sbr
.Name = "sbr"
.BindingField = "CityName"
.XCoordinateField = "Longitude"
.YCoordinateField = "Latitude"
End With
mapObj.DataBindingRules.Add(_sbr)

Now we need to set up a database connection which requires a connectionString. I can’t be doing with hardcoding a connectionString in .NET code as it makes deployment from test to production a nightmare. So, what you can do is create an additional parameter (hidden this time) that holds the connectionString as shown below:

Now whilst we are on the subject of Parameters we need to make these Report Parameters available to the Code by clicking on the Code Parameters tab and creating our two new Code Parameters as shown below:

OK, so now we can carry on with our code creating our database connection:

Dim _objConn As New System.Data.SqlClient.SqlConnection(codeParams("paramConnectionString"))

We then create a SQLCommand object that uses the above connection, and appropriate stored procedure from the database:

Dim _objCmd As New System.Data.SqlClient.SqlCommand
With _objCmd
.CommandText = "dbo.up_Cities_Select"
.CommandType = System.Data.CommandType.StoredProcedure
.Connection = _objConn
End With

And we pass our parameters to the stored procedure as appropriate:

_objCmd.Parameters.Add("@RegionID", System.Data.SqlDbType.Int)
_objCmd.Parameters("@RegionID").Value = codeParams("paramRegionID").ToString

We then fire up our connection:

_objConn.Open

And create a SQLDataAdapter to fill a datatable with the results of the stored procedure:

Dim _objDA As New System.Data.SqlClient.SqlDataAdapter(_objCmd)
Dim _dt As New System.Data.DataTable
_objDA.Fill(_dt)

We then bind the map object (mapObj) to the datatable.

mapObj.DataSource = _dt
mapObj.DataBind

And then finally we iterate through each of the symbols we have created setting their name:

Dim _i As Integer = 0
For Each _symbol As Symbol In mapObj.Symbols
_symbol.Text = _dt.Rows(_i).Item("CityName")
_i = _i + 1
Next

Before closing the connection:

_objConn.Close

Compile the code and then run it and with a bit of luck it should end up looking like this (obviously subject to some data):

Debugging

Another tip (stolen from a post in Dundas Support Forums) is to wrap all your code in a Try Catch such that your errors write out to a file on the local machine. This makes debugging ALOT easier:

Try
...code above...
Catch ex As Exception
System.IO.File.WriteAllText("C:\MapRSExceptionText.txt", ex.Message & " - " & ex.StackTrace)
End Try

Summary

You can download a copy of my database and the example report here.

So, as I said at the beginning of the post – not straightforward at all! But with a little work this control adds a valuable component to anyone’s SQL Reporting Services toolbox. As always, any feedback (positive or negative) is always welcome.

Popularity: 12% [?]

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:

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:

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:

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:

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:

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

2) 48HR – Ticket Passed Due Date

*** 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!

Popularity: 19% [?]

Whilst there are always arguments for being able to delete records from a database, I have always preferred to InActivate my records as opposed to deleting them thus maintaining the referential integrity, history etc. With that in mind I thought I would pen a really quick post about how to do this using LINQ and VB.NET hopefully writing as little code as possible.

Solution

Consider the following (incredibly simplistic) Countries database table:

Using Visual Studio 2008 we create a standard ASP.NET 3.5 web project called Example and add a Linq To SQL class as shown below:

Using the Server Explorer we simply drag the Countries table onto the design surface of the Example.dbml file as shown below:

We save that file and modify the Default.aspx file to include a simple Gridview and accompanying LinqDataSource:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>JOATIT - Example</title>
</head>
<body>
<form id="frm" runat="server">
<div>
<asp:GridView ID="grvRecords" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" DataSourceID="ldsRecords">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:CheckBoxField DataField="Active" HeaderText="Active"
SortExpression="Active" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
ShowHeader="True" />
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="ldsRecords" runat="server"
ContextTypeName="ExampleDataContext" EnableDelete="True" OrderBy="Name"
TableName="Countries">
</asp:LinqDataSource>
</div>
</form>
</body>
</html>

If we run this as it is the GridView will bind as shown below, but the Delete link will ACTUALLY delete the records from the database which is not what we want:

Instead what we want to is InActivate them when the Delete event is fired. To do this we simply open the Default.aspx.vb code-behind file and add the following custom event as shown below:

Protected Sub ldsRecords_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceDeleteEventArgs) Handles ldsRecords.Deleting
Dim _recordToBeDeleted As Country = CType(e.OriginalObject, Country)
Dim _db As New ExampleDataContext
Dim _specificRecord = (From _allRecords In _db.Countries _
Where _allRecords.ID = _recordToBeDeleted.ID _
Select _allRecords).Single
_specificRecord.Active = False
_db.SubmitChanges()
e.Cancel = True
End Sub

Now when we click the Delete link, our custom event fire and updates the record in question setting Active = 0, and cancels the Delete event.

Summary

More than happy to learn of a better way of doing this other than with a database trigger (which has its own advantages and disadvantages) so please, feedback (positive or negative) is always welcome.

Popularity: 18% [?]