Introduction

If you’re a seasoned write of SRSS reports (or any type of RDBMS reporting system), you’ll be used to trawling through databases, tables, fields and indexes to deliver that right combination of headings and columns which present the underlying data as your users want to see it. Well, here’s a report that doesn’t involve any of that. OK, there’s a database, but only as a place to store the code and the only table is a temporary one.

The Problem

The problem we’ve had is with advising users of IT equipment on what to do if they need to travel to one of our other offices and want to have access to their data and emails, etc. As we have offices in Africa, including some more remote places, this can become quite an issue.

So first off, someone wrote down all you needed to know in an 18-page document which we spent ages trying to agree. Would someone really read through this before they went? Also it contained a lot of duplication as some scenarios were very similar. So I went through and reduced it to 4 concise pages which explained all they needed to know. Were they satisfied? No. “No-one’s going to read this Jon; they just want to know what they have to do, not the reasons why everything works the way it does!” Back to the drawing board…

Does this sound familiar? In my experience, IT departments are great at producing information solutions for other departments, but pretty bad at managing knowledge in their own sphere.

Lastly, I remember back in the late 80’s learning about expert systems. It was a specific branch of AI which yielded good results because the scope of knowledge required was narrow and the expertise highly rule-based. The most common example was the medical diagnosis one which asked various questions to ascertain symptoms of ill health, thought a bit and then spat out a diagnosis. Just what we need here.

The Solution

So that was my solution to the problem. Get the user to tell us the basics of the trip, i.e. where they’re going, whether they’re taking a laptop and whether it’s already setup for their use. Then run through some code to tell them what they need to do to make the trip hassle-free, IT-wise. That way, you don’t tell them anything they don’t need to know.

I decided on asking for 6 items of information:

• your network account/logon (of course SSRS already knows this as User!UserID, so it can be filled in by default)
• which office you’re based in and which one you’re going to visit (if you have a list of these somewhere with the associated Windows domains, etc., you’ve got a head start)
• what device you’re going to be logging into once you’re there
• the dates of your visit

The logic of the underlying stored procedure then produces the advice by adding rows to a temporary table according to the parameters given.

Here’s the code of the stored procedure. As you can see, there’s nothing very clever here other than to organise the many IF-ELSE statements into a structure that’s as readable as possible. In particular you want to be able to amend it easily if you find the logic is flawed or a new situation arises which it doesn’t yet cope with.

 alter proc [dbo].[sp_itadvice_travel_ssc] (@userid varchar(100), @baseid int, @destid int, @computer int, @date0 datetime)
 as
 declare @base_domain varchar(20)
 declare @dest_domain varchar(20), @user_domain varchar(20)
 declare @username varchar(50), @profilepath varchar(100)
 declare @assist int
 --table for results
 create table #Temp (hcode varchar(20),message varchar(1000))
 -- assumed that office-related data is in ITDB.dbo.Offices
 select @base_domain = domain from ITDB.dbo.Offices where locid = @baseid
 select @dest_domain = domain from ITDB.dbo.Offices where locid = @destid
 -- @userid in the form: DOMAIN\username
 set @user_domain = left(@userid,charindex('\',@userid)-1)
 set @username = substring(@userid,charindex('\',@userid)+1,99)
 --find  profile path for DOMAIN_MAIN accounts; if exist, they have a roaming profile
 set @profilepath = ''
 select @profilepath = profilepath
 FROM OPENQUERY(ADSI,
      '<LDAP://DC=domain_main,DC=org,DC=com>;(&(objectCategory=Person)(objectClass=User));
      sAMAccountName,   profilepath;subtree')
 WHERE    (sAMAccountName = @username)
 --the main logic...
 -- no travel
 if @baseid = @destid
      insert into #Temp values ('0', 'You are not travelling anywhere!')
 else
 -- no destination domain i.e. outside of WAN
      if @dest_domain ='' or @dest_domain is null
           if @base_domain = 'DOMAIN_MAIN'
                 insert into #Temp values ('0-K','You will need to take a token dongle to access the network. Otherwise you will only be able to use webmail.')
           else 
                 insert into #Temp values ('0-X','You will only be able to access webmail.')
      else
 --MACHINE/PROFILE
           begin
           if @computer=1 --section shared laptop
                 begin
                 insert into #Temp values ('2SA','If you ask IT to setup the laptop for your personal use, specific local applications can be installed so you can continue to use them.')
                 insert into #Temp values ('2SE','For instance, Outlook can be setup to log you in to the standard screen (i.e. not webmail) and keep a local copy of all messages.')
                 if @base_domain = 'DOMAIN_MAIN'
                       if len(@profilepath)> 0                                   
                             insert into #Temp values ('2SPr','You also need to ask IT to amend your profile; otherwise your laptop will take a long time to logon.')
                 else
                       insert into #Temp values ('2SP?','Check with IT - they may need to amend your profile.')
                 end
           if @computer=2 --organisation pool laptop
                 begin
                 if @baseid = 1 -- central office
                       begin
                       insert into #Temp values ('5SB','Submission of this report will prompt IT to find a laptop for you to take.')
                       if datediff(day,getdate(),@date0)< 7
                             insert into #Temp values ('5ST','As you have only given '+ cast(datediff(day,getdate(),@date0)as varchar(3))+ ' days notice. you had better check that one is available now.')
                       else
                             insert into #Temp values ('5ST','As you have given ' + cast(datediff(day,getdate(),@date0)as varchar(3))+ ' days notice, this should not be a problem.')
                       end
                 if @base_domain = 'DOMAIN_MAIN'         
                       if len(@profilepath)> 0 --roaming DOMAIN_MAIN profile
                             insert into #Temp values ('5SPr','You must either login as ''temporary'' or ask IT to setup your usual logon so you can use that instead.')
                       else
                             insert into #Temp values ('5-Px','Once there, you can logon with your usual logon')
                 else
                       insert into #Temp values ('5SP?','Check with IT - they may need to amend your profile.')  
                 end
           -- Applications
           insert into #Temp values ('9-A','Access to applications such as Projects, Sharepoint or Accounts depends entirely on the speed of the local connection.')
           -- search hcodes of advice given for any of the form '_S%'; these mean pre-visit IT Support is required
           select @assist = count(*)from #Temp where hcode like '_S%'
           if @assist >0
                 insert into #Temp values ('9SA','*Remember to arrange a visit to IT before you travel*')
      end
 select * from #Temp

The Process

Now we’re starting to build this in to the established process in our organisation of what you need to do before you travel. When the user runs this report and sees the resulting advice, they are advised to export it to PDF and email the resulting file to IT Support. The people at IT Support then know to expect someone to visit and have their laptop reconfigured or have their roaming profile disabled or whatever else needs to be done. It also embraces the laptop loan process.

Below is the screen as the user sees it with some hypothetical parameters.

A Concession

One concession to actually using data from a database was to find out, from the user’s network account, whether the user had a roaming profile, so that IT Support knew they needed some ‘additional setup’. To do this requires a linked server to be setup which looks at their local Active Directory (AD). Something similar to the code below is all that’s required and an account with view access to AD.

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
 /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN_MAIN\ad_readonly',@rmtpassword='########'

Summary

This is a simple example of how a reporting system can deliver narrative information that is tailored to a user’s specific needs. Though some users delight in knowing the technical detail so they can figure it out for themselves, they’re in the minority and anyway, sometimes they cause more headaches for IT than the rest. Generally users just want to know what they need to know – they’re job isn’t in IT, it’s in the real world.