IT Performance Monitoring

In the current competitive environment organisations rely heavily on information technology to automate business processes, streamline operations, manage resources, and even store yottabytes of data. If you’re an IT or an infrastructure manager or a small or medium size business owner looking to grow then researching about keeping your systems healthy in the long run can be a good starting point and something worthwhile consideration.

Well with performance comes cost, and if you’re concerned about burning a hole in your client’s or your own pocket, then let me break the good news to you. Performance monitoring of your infrastructure doesn’t have to break bank account, and it can be achieved by some really cool tools out there available for free.

In today’s blog, I will give you a brief outline on a similar tool that can mind your servers while you’re peacefully sleeping (isn’t that so relieving?) along with some tidbits on what will be covered in future blog posts to keep you tuned. So, let’s get going!

What is Nagios XI?

Nagios XI is a comprehensive IT infrastructure monitoring tool that constantly checks on all mission-critical infrastructure components – including applications, services, operating systems, network protocols, system metrics and network infrastructure; thus providing you with a complete view of your entire IT operation network and business processes.

And guess what?! Nagios XI isn’t rocket science. Its integrated web-based configuration interface allows users to easily manage monitoring configurations through configuration wizards.

The other cool thing I like about Nagios XI is its notification feature, in which a user can set up alerts in form of emails being sent out to IT staff providing them with outage details so that they can start resolving issues immediately. Moreover, it also provides performance graphs which can be referred to in case of advance infrastructure planning.

What next?

In this series of blog posts, I’ll be taking you through a small step-by-step tutorial on how you can set up a Nagios XI for your business, followed by some advance configuration settings. In the end, we will sum up with an automation task that uses ‘Bash-Shell’ scripting to rotate server logs, download them and format them into a performance report.

Well the fun has just began! I would like to hear from you about how you are planning to monitor your IT infrastructure performance? Drop in a line or two. Meanwhile, I’ll get back to writing the second blog.

SQL Server: Copy database from recent to older versions

Recently, I needed to copy a database from SQL Server 2012 to SQL Server 2008 R2.  Unfortunately, database backups from one version of SQL Server cannot be read by a lower version.  To work around this, I had to use the “Generate Scripts” function to first create the structure and then populate the database.

Whilst none of this is particularly complex, I thought it might be useful to collate the steps in one document…

Step 1: Generate structure scripts

  1. In the SQL Server instance that contains the database to copy, right-click on the Database in the Object Explorer, select Tasks, then select Generate Scripts.
  2. In the Wizard, progress to Choose Objects and make sure that “Script entire databases and all database objects” is selected.

    Screenshot: Select "entire database" option
    Select the “entire database” option.
  3. Progress to the “Set Scripting Options” stage.  Click on “Advanced” and make sure that the “Type of data to script” option is set to “Schema only”.  Also set the “Script for Server Version” option to the other SQL Server’s version (I still ran into incompatibility problems with the generated script, however).

    Screenshot: Advanced options in Generate Scripts
    Advanced options in Generate Scripts
  4. Finish off the Wizard, copy the generated scripts to a location that the other SQL Server instance can access and run them in that Management Studio.  Here are the main problems and fixes I ran into during this part of the process:
    1. Error message that “CONTAINMENT is marked as incorrect syntax”:  Just comment out the “CONTAINMENT = NONE” line (source: http://stackoverflow.com/a/15410854)
    2. Error message that CREATE Database commands can’t be run in a multi-transaction statement:  Just run the create statement separately.
    3. Error when trying to alter user to add role:  This command is not supported in older versions; change those commands to use “sp_addrolemember” (e.g. EXEC sp_addrolemember ‘db_datareader’, ‘Administrator’) (source: http://technet.microsoft.com/en-us/library/ms187750.aspx).
  5. If that all goes well, you should now have the database skeleton ready.  The next step is to import the data.
  6. Back on the originating SQL Server, go to Generate Scripts again, but this time in the Advanced options, set “Type of data to script” to “Data only”.  Because this will probably require a lot of space, in the “Set Scripting Options” screen, set “Files to generate” to “Single file per object”, to split up all of the scripts by table.  Finish the Wizard.
  7. You should now have a bunch of script files, ready to be run.  Copy the containing directory to a location the older SQL Server can access.  Because I had 219 generated files, to save on running them manually, I used the following batch script (create and run it from the containing folder):
    for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"
    pause

    (source: http://stackoverflow.com/a/6504317)

  8. The above script may take a while to run, but after that, you should be done!

 

Detailed Change Tracking in Sage CRM

A requirement that we’ve come across a few times now is to track when a particular field value changed and what the previous value was (e.g. when did the Assigned User change for Company record X?).  A co-worker of mine came up with a method of achieving this, that involved adding only two custom tables and triggers on triggers; I still remember my feeling of admiration at such an elegant solution!

Detailed setup steps are included below, but the gist of it is:
  1. Create a configuration table
    This defines what fields should be tracked.
    Image
  2. Create a tracking table
    This will contain each recorded change.  In the screenshot below, you can see that on 30/08/2013 the Admin user (trac_CreatedBy = 1) changed the Salutation on Person 1791, from “Mr.” to “Dr.”.
    Image2
  3. Create a master trigger on the configuration table
    This trigger runs whenever the configuration table is updated, has an insert or a delete action performed on it.  This master trigger will loop through the configuration rows and create update/insert triggers on the required entities (e.g. the Company table), that will add insert/update changes to the tracking table.

Detailed Setup

I’ve bundled all the SQL commands to set this up, in this script (it’s a 7z file, so you can use something like 7-Zip to extract it):  crm-detailed-tracking-creation-sql
To start tracking fields, just edit the configuration table in SQL Server and specify:
  • Tbl_Name:  The entity table name to track
  • Action: If insert, update or both (insert,update) actions should be tracked
  • Updated_Collist:  The comma-separated list of fields that should be tracked
  • Is_Enable: Set to ‘1’ to turn it on
Once you commit your changes to the table, the master trigger will pick them up and create or re-create the defined triggers.

Next Steps

  • The configuration table could be exposed in CRM via a regular custom page, allowing CRM admins to maintain the tracking definitions
  • Create CRM reports allowing managers to see what changes have been made, to records managed by their team

 

Update

02/01/2014: This setup assumes that your CRM database has defined primary key constraints on the table that you want to track.  This assumption is correct for new installs of Sage CRM 7.2, but if you have upgraded from an older instance, the database will not have those keys defined, and you will need to set them up manually.

 

 

7 Tips for Running a Requirements Gathering Workshop

Gathering user requirements is generally acknowledged to be one of the most important stages of a CRM project.  However, unearthing what users need from such a system can be difficult, if there is little guidance in the process (either from experienced, internal stakeholders or external consultants).

The following tips come from my personal experiences running requirements gathering workshops:

1.  People will not contribute right away

Attendees may be happy to be present, uninterested or even hostile to the project itself.  Regardless of the mood in the room, people will be reluctant to participate and you will need to invest some time in outlining the situation, what the project is looking to achieve and how they can contribute.

2.  Surveys can be a useful starting point

Distributing a survey a couple of weeks out from the workshop can often provide examples and use cases that will help you personalize your discussion points.  The survey should be brief (<5 questions) and provide an anonymous opportunity for users to highlight the issues important to them.

3.  Demoing functionality is useful

Having a working prototype can be of great assistance in helping people think  about the processes required.  You shouldn’t “build” the solution before you’ve talked to users, but if you can customise the demo with one or two use cases that are relevant to them, it will provide a gentle jumping off point for everyone to think about what you missed, what could be improved and what worked well.

4.  Don’t stress about preparing slides

Whilst a working demo is useful, slides generally are not.  I always make sure I have some, but without fail, the discussion quickly moves to the whiteboard and stays there.

5.  Keep a parking lot for issues

Once you’ve moved onto whiteboarding discussions, immediately cordon off an area as a “parking lot”.  Ideas and feedback will start flowing quickly and important points will need to be put aside frequently, although hopefully not forgotten.

6.  Try to get senior management in the room

On the one hand, attendees who are sceptical of the project will often provide important insights into potential pitfalls for the system (they may have good reason for their scepticism).  On the other, having senior management represented will reinforce their commitment to the success of the project and prevent a negative mood from taking hold.

7.  Ask what reports are needed

Finally, a very useful question during the workshop and follow-up meetings, is to ask what reports each user needs.  Without a framework to build on, users will find it difficult to express what their common tasks are, what hampers those tasks, what information they need and so on.  Placing it in the context of reports allows people to clearly define discrete pieces of information (that can be translated into fields) and to articulate the data sources.

How do I output a link to a CRM record, in an email?

Jeff Richards describes how to do it in this post:
community.sagecrm.com/…/embedded-links-in-workflow-emails.aspx

Basically, your Email template needs to have the following text:

#recordanchor# <Anchor text goes here> #recordanchorend#

If you’re manually constructing the link (e.g. via a script), you can use the same “SessionFind” URL structure e.g.:

/CRM/eware.dll/SessionFind?Act=200&CLk=T&Key0=1&Key1=300123

The above URL will redirect the user to the Company Summary page (Act=200) for the company (key0=1) with a comp_companyid of ‘300123’ (key1=300123).

NOTE: The user will always have to login when clicking these links (even if they already have an open session).

How do I set my account to login to a custom screen?

Sage CRM allows users to customise which screen should be displayed by default, when they login.

image2013-8-7 16-35-27

But what if you want to login to a custom screen (e.g. an ASP file)?  In that case, you need to:

  1. Open the following file for editing
    \CRM\WWWRoot\InteractiveDashboard\InteractiveDashboard.asp
  2. Put in a Javascript redirect for the page you would like to redirect to e.g.:
    <script type="text/javascript">
        var strURL = window.location.href;
        strURL = strURL.replace(/\/InteractiveDashboard\/InteractiveDashboard\.asp/, '/CustomPages/CA/Dashboard/index.asp');
        strURL = strURL.replace(/\?.*(SID=\d+).*/, '?' + "$1");
        window.location = strURL;
    </script>
  3. Because the Dashboard is hardcoded to display a set of tabs at the top of the iframe, you will also need to remove those – you could do this by hiding the tabs. For example, in ‘Dashboard/index.asp’, include the following:
    <script>
    // Hide the InteractiveDashboards tabs, that are included in the parent frameset.
    $("#landingpage_top_frame", parent.document).hide();
    </script>
  4. Now, any user with “Dashboard” set as their “Log Me In To” option should be redirected to your custom page.

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!

My CRM buttons are no longer appearing in Outlook?

If the Sage CRM plugin for Outlook is working correctly, you should see  a “File e-mail” button in your Outlook toolbar.

Sage CRM plugin buttons in Outlook
Sage CRM plugin buttons in Outlook

If not, try the following steps to get it working:

Requirements

Check that your PC has the required libraries installed.  In the Add/Remove Programs window, you should see:

  • Visual Studio Tools for Office 2010 (VSTO)
  • Microsoft Office 2007 Primary Interop Assemblies
  • Microsoft .Net Framework 4 Client Profile

We’ve also encountered issues if the user account is setup as a roaming profile.  If so, try changing the account to a local profile.

Check Trust Centre

In the Outlook client, open the Trust Centre and check if the Sage CRM plugin is listed in the Add-ins section.  If it is, make sure that it is enabled and trusted by Outlook.

Re-install the plugin

You can find the installer for the plugin in the <Sage CRM Install Directory>\CRM\WWWRoot\Plugin directory (e.g. C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\Plugin).  Run the plugin appropriate for your setup (e.g. for Exchange integration, use CRMClientExSetup.exe; for just Outlook-client integration, use CRMClientSetup.exe).

Keep in mind that the Outlook user must have local administrator permissions on their PC, the first time that they use the plugin (e.g. in Windows 7, go to the Control Panel, select Users, click on Give other users access to this computer and then add the Outlook user account as an Administrator).

If this still does not work, try logging into the PC as Administrator and re-installing the plugin.

Cleaning up the registry

If the plugin is still not working, you might need to search the Windows registry to see if there are any old or corrupt entries that are confusing the plugin.  As always, make sure to backup your registry before making any changes.

Ideally, the plugin’s registry entries should be under HKEY_CURRENT_USER\SOFTWARE\Sage\Exchange Outlook Plugin.

Outlook itself will also keep registry entries for its plugins (e.g. HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Outlook\Addins\OutlookAddin2003).

Delete any CRM Outlook entries you find in these folders.

After that, just try searching the registry for any matches on ‘CRM’ and manually checking each.

Remove downloaded files

Check C:\Windows\Downloaded Program Files for any CRM Outlook related files (e.g. crmoutlookplugin1.dll).  Delete all of these files.

Restart the CRM server

Sometimes the plugin will be unable to connect to the CRM server (either during the install or after), due to corrupted metadata on the CRM server.  In these cases, refreshing the metadata on the CRM server (or even restarting the CRM server) will fix the issue.

Sage Australia Business Index

Sage Business Solutions Australia has recently released the Sage Australia Business Index 2013 which highlights many valuable insights gleaned from their annual survey of Australian Businesses.

Below is an excerpt from the study:

This Sage investigation into Australia’s business confidence in 2013 confirms that business conditions have deteriorated within the past year and the outlook for the remainder of 2013 remains bleak. Last year’s Sage Business Index highlighted a concerned, cautious business sector reluctant to invest, but prepared to do so once they believed economic conditions were improving. Unfortunately the attitudes and expectations expressed in the Sage Australia Business Index 2013 suggest that leaner times are afoot and the right conditions for investment have not eventuated.

Within this challenging economic environment the Australian business landscape in 2013 is also being substantially affected by the use of mobile and handheld devices. Across both internal and external business functions, organisations are challenged daily by technological change, much of which is being driven by employees and the consumer. At this critical point in the mobile revolution, how businesses respond to and cope with this change will shape not only businesses themselves, but also significantly influence their likely future success.

This report takes a detailed look at current business attitudes towards a range of subjects including:

  • Current and predicted business confidence levels
  • Concerns around economic issues.

The report also investigates what is happening in Australian businesses in relation to three key technology areas: staff mobility; enterprise mobility; and business use of and demand for mobile applications and mobile optimised websites.

Based on a wide-ranging market research program involving interviews with more than 400 business owners and decision makers across all business sizes, this report breaks new ground in providing a validated understanding of issues including:

  • Business policies, uptake and the perceived value and benefits of mobile enabling staff
  • Relevance, uptake and the perceived value and benefits of mobile enabling the workplace
  • Current uptake and future plans for businesses regarding mobile applications and mobile optimised websites.

This study into the relationship between Australian business and mobile technology, and its examination of broader prevailing attitudes, provides clear insight into how Australian companies are operating today.

Click here to download the document.

Dixon Advisory chooses Sage

Dixon Advisory is one of Australia’s leading independently owned financial advisory firms. Established in 1986, the organisation spent its first 15 years developing a small but loyal client base and a solid business foundation. In early 2000 management change saw Dixon Advisory embark upon a dramatic expansion strategy built around the delivery of family and self-managed superannuation services, financial planning, property and mortgage advice, estate planning and other related services. Within 12 years the company grew from three people to more than 350 employees.

Today, Dixon Advisory boasts a client base of more than 15,000 families with a combined asset base in excess of $4 billion. The firm has offices in Canberra, North Sydney, Melbourne and on Queensland’s Fraser Coast. Following the establishment a few years ago of a strategic property investment listed Australian company, Dixon Advisory also maintains an international office in New Jersey, USA. All offices are connected via a private network.

Adapting to growth

Dixon Advisory is a company of two distinct phases. There’s the pre-2000 organisation with three employees and there’s the turn-of-the century firm that has powered ahead. The earlier version required cost-effective yet professional support tools and systems suitable for a small business. Key among these were multiple Sage ACT! contact management databases and a small business finance package.

As Dixon Advisory grew, the directors were focused on ensuring that the personalised service levels provided to clients were not diminished by a larger firm – a client today receives the exact service model they did prior to 2000 (in fact now that Dixon has established economies of scale, clients receive even more personalised attention than in the past).

As client numbers and business complexity grew however, the small business solutions were no longer capable of meeting all of Dixon Advisory’s needs. Client relationships were the backbone of the firm’s business, yet with numerous databases, there was no easy way to achieve a single view of the customer or to quickly understand all recent interactions.

Andrew McPhillips, Director, Chief Technology Officer at Dixon Advisory explains, “As we started to get to 100 employees we found we had multiple people dealing with a client at any point in time across different services. To avoid duplication or inconsistencies with client communication, we needed an enterprise-grade CRM [customer relationship management] solution, one that would provide a central place for all client contact information, correspondence and documentation.”

A Sage decision

McPhillips began researching alternative CRM solutions. Out of all the options, one solution stood out: Sage CRM. Available through the cloud or for deployment as a traditional in-house solution, Sage CRM had the capacity, document management and workflow capabilities that Dixon Advisory needed.

“It was a good fit,” McPhillips admits. “Cloud was very new and scary at the time so we adopted the on-premise model. The ability to build in workflows was a big attraction. Plus we were at a stage where we needed to upgrade our document management system. When we looked at the Sage CRM document management functionality, we realised it could manage 95 per cent of what we needed without the need for us to invest in a separate piece of software.”

Other draw cards included a foundation of SQL Server, a technology that was already familiar for McPhillips’ team, and a web front-end which ensured the application would be easily accessible from all offices.

To download the success story please click here.