There’s no such thing as a free lunch

I recently visited a site where the local IT Team were having to remember to manually backup a SQL Server 2005 Express Edition database each night, as one of the missing features of the Express Edition is the ability to set up a maintenance plan to do this automatically. What follows is a workaround I provided to automate their database backups:

Solution

First off I created a really simple batch file using Notepad:

@echo off
Set YYYY_MM_DD = %DATE:~6,4%_%DATE:~3,2%_%DATE:~0,2%
Set DBName = SDE
set BackupFile = C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\%DBName%_%YYYY_MM_DD%.bak
osql -S ServerName -E -Q "BACKUP DATABASE %DBName% TO DISK='%BackupFile%'"

I saved the file as C:\Program Files\Batch Jobs\DBBackup.bat

All the batch file is doing is running an SQL command to backup the database to disk appending the current date automatically. All that is then left is to create a Scheduled Task to run the batch file every night.

Clearly there is much more you could do with this and indeed there are a whole raft of applications (many free) that allow you to do this with a GUI etc. but…just in case it helps.

As always any criticism, positive or negative is always welcome.

In the final part of this series I am going to finish it off with the business rules that tie everything together. Before I do so I should mention that I have made some changes to the Function GET_STD_CONFIGS detailed in the previous post. So if you downloaded this before please go ahead and download it again and run it against your Service Desk Express database.

The first business rule we need is to create the new starter ticket (incident) when a client is created:

CL01 – Client Insert Create New Starter Ticket

Conditions

when a Client

Actions

Action 1

Not very exciting but it does the job. Now we need a business rule that is going to send an email to the new starter’s supervisor (a new foreign key in the Client module referencing the Client module called Seq.Supervisor) and create a new work order for the SYSTEM ADMINS to create an Active Directory and Exchange account.

HD01 – New Starter Ticket Stage 01

Conditions

when a Incident

Actions

Action 1

Action 2

Notification Business Rules Template HD14 referenced in the above business rule makes use of a two other templates created in my HTML Email Template post. Make sure you create the _HTML_HEAD and _HTML_FOOT templates otherwise this business rule wont work properly.

HD14 – New Starter Email To Supervisor

That’s half of our business process complete. The email that is sent will automatically list all the Standard CI Assemblies available complete with hyperlinks such that when the supervisor clicks on their choice it will create an email configured in a way that we can pick up the inbound email and continue the business process. To pick up the inbound email from the supervisor we need a Mail Listen type business rule.

ML01 – New Starter Approval Received

Conditions

when a Mail Listen

Actions

Action 1

The above business rule simply creates the new workstation work order for our DESKTOPS team advising them of the choice our supervisor has made. The final business rule is the one that assigns the parent Service Request to the HR team for them to complete the new user induction. The trick here though is how do you know which work order is going to be completed first and how to you know when both work orders have been completed. The answer to the first is that you don’t (although you could take a good guess) and as such you need to allow for the fact that either of the work orders could be the last to be closed and should thus trigger this final business rule.

WO01 – New Starter WOs Completed

Conditions

when a Work Orders

Actions

Action 1

So essentially what we are doing here is checking that the parent Incident / Service Request is a New Starter one and that the total number of linked Work Orders is 2 and the total number of open linked Work Orders is 0 i.e. they have all been closed.

Summary

Whilst this business process could have been a lot more complicated (I once implemented one with 6 parallel work orders etc.) this series of posts have shown how it is possible to chain business rules together to deliver a real business process. Hope this helps. As always, any feedback, positive or negative is always welcome. Thanks for reading.

To maintain (or hopefully increase) these numbers I need to keep writing / write more interesting articles and to do this I really need your help. What do you folk want me to write about? Are there any series that you would like to see? Let me know in the comments to this post and next year I will see what I can deliver.

Happy holidays everyone and thanks again.

Out of the box you can’t restrict entries created by one client from another client.

Thankfully, with a little (well quite a lot actually) custom scripting it is possible. I should stress however, that if you are going to do this, MAKE SURE YOU BACK UP EVERYTHING (DB and Self-Service directories) FIRST!!!

Example

So let’s say that you want to create a new custom module to record feedback such that your clients can log Complaints, Compliments and Observations. The design of our custom module is as follows:

We then create a form in for Self-Service, an appropriate popup, and modify the navigation bar such that the new custom module can be accessed as shown below:

Issue

If Client ABARBER (that’s me by the way) submits a feedback record all appears to work perfectly. However, when SBARBER (that’s Sarah my wife) logs in, she can see my feedback and if she were to submit feedback, I would be able to see her’s as shown below:

Solution

First run the following SQL command against your SDE DB and store the results somewhere:

SELECT BVD.TBLSEQ, COLSEQ, VIEWCOLNAME FROM dbo.SMSYSBASEVIEWDATA BVD JOIN dbo.SMSYSTABLES T ON T.TBLSEQ =
BVD.TBLSEQ WHERE T.VIEWNAME = ‘Feedback’ ORDER BY COLSEQ

My result looks like the table shown below but yours will look different depending on what you named your fields and how many custom modules you already have:

Download RestrictingCustomSelfServiceModuleByClient.zip containing the files that need to replace the existing BMC files.

In the recurring_hd.asp and recurring_hd.htm files you need to do a find/replace replacing:

• All occurrences of JOATIT_TBLSEQ with the  TBLSEQ number returned in the table above (so in my case 1002).
• All occurrences of JOATIT_SEQ_CLIENT with the  SEQ_CLIENT number returned in the table above (so in my case 1006).
• All occurrences of JOATIT_STATUS with the  STATUS number returned in the table above (so in my case 1013).

In the 1_common_label_message.js file you need to scroll right down the bottom and replace the “My Whatever Your Custom Module Is Called” with in my case “My Feedback”

Save the files and use them to overwrite the existing files in their appropriate directories.

Run the following script against your SDE DB…

INSERT INTO dbo.NAMSYSCSMESSAGES ([SEQUENCE], [LANGSEQ], [MESGNO], [MESGTYPE], [MESG]) VALUES (3224, 1, 1597, 2, ‘My Feedback’)

…and IISRESET you application server.

Summary

Hope this helps. Remember to backup everything as it is very easy to make a typo. Also remember, as this is custom code you may well have to reapply it after every patch release from BMC. Who knows, perhaps Service Desk Express 10 will make this post obselete…

As always, comments (positive or negative) are always very welcome and very much appreciated. I do try and answer every single one.

What I hope to achieve in this post is to walk you through how you would achieve this. The problem lies in Service Desk Express’s lack of functionality to “iterate” through a dataset and complete an action for each record it finds.

Solution

The solution is courtesy of a little SQL magic – we are going to create a user-defined function that iterates through each of the assessors and builds a string of their Login IDs such that we can give this to a Service Desk Express business rule to send an email:

CREATE FUNCTION [_SMDBA_].[GET_CHANGE_ASSESSORS]
(
@ChangeNo int
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)
SET @ReturnString = ''
DECLARE @LoginID nvarchar(30)
DECLARE cur CURSOR
FOR SELECT DISTINCT P.CODE FROM    _SMDBA_._CHANGEASMT_ CA JOIN _SMDBA_._PERSONNEL_ P ON P.SEQUENCE = CA.ASSESSOR WHERE CA.[_INACTIVE_:] = 0 AND P.[_INACTIVE_:] = 0 AND CA.[CHANGE] = @ChangeNo
OPEN cur
FETCH NEXT FROM cur INTO
@LoginID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @ReturnString = @ReturnString + @LoginID + ';'
END
FETCH NEXT FROM cur INTO
@LoginID
END
CLOSE cur
DEALLOCATE cur
RETURN SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1)
END

It is important that we grant execute permissions to this function:

GRANT EXECUTE ON [_SMDBA_].[GET_CHANGE_ASSESSORS] TO PUBLIC

Then we just need a simple business rule that, on change of status for example, sends and email to all assessors:

Change Request -Notification of Change of Status To Assessors

Conditions

when a Change Request

Actions

Action 1

Summary

User-defined functions can be a goldmine of pieces of functionality that is missing from Service Desk Express. So before you despair…think UDF! As always I hoped this post proved useful. Feedback, positive or negative, is always hugely appreciated.

In this post we are going to lay the foundations to build this process (by populating some necessary data) and then in the final post in the series we will complete the business rules.

Categories/Support Subjects

We need to add a couple of Categories /Support Subjects as shown below:

Now obviously it goes without saying that you don’t have to have the same categories as me. The reason I name Business Rules as zzBusinessRules is to shove them down the bottom of the Category Tree out of the way.

Groups

In my example I am going to have a centralised System Admin Group (SYSADMINS), Desktop Team (DESKTOPS), and HR Team (HR) who will do my induction process. Again, if you are following this you can call your groups whatever you want

CI Types/Inventory Catalogue Items

Whenever I write these posts I am always keen to make the example something that has a reasonable degree of complexity about it. Many technical examples are based around the equivalent of the Hello World application, and from bitter experience this CAN be more frustrating than useful. So in my example here, I am going to assume that we are using the Asset Management modules as well. So we’ll need the following CITypes/Inventory Catalogue Items:

Standard Configurations

We are then going to create two standard configurations as shown below:

and

Standard Configurations User Defined Function

As anyone will tell you, when, as a developer, you hardcode stuff you invariably come to regret it (unless you have already left the organisation that is). I want to be able to send my supervisors a dynamic list of Standard Configurations that he/she can select from such that if that business decides at a later date to have a Non-Standard Mobile Configuration we can cope with it without the need for any code changes.
Alas, out of the box Service Desk Express can’t send a list of anything to anyone. This is where we need a little SQL help in the form of a User-Defined Function (UDF). Below is a link to a function I want to use to send out part of my email which can either be run yourself against the SDE database or given to your SQL DBA to run against the SDE database:

Function GET_STD_CONFIGS

If you haven’t used user-defined functions in SDE then you might find this post helpful.

Summary

So that is all the preparation work done. Now all we have to do is build our business rules to use all this stuff and that is what I will document in the third and final post in this series.

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.

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.

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.

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.