Sage CRM 7.2 Client-Side API

Recently I was asked to hide the continue button on the ‘Case’ summary screen for one of our clients. As you can see in the image below, it is located on the top right corner of the screen.

'Continue' button on the top right corner of the screen.
Case summary screen with ‘Continue’ button

Since it was a client side customization, I was aware that it could be achieved by one of the many easy to use APIs released with the launch of Sage CRM 7.2. These new APIs can be accessed from http://help.sagecrm.com/js-api/classes/index.html

Sage CRM 7.2 client-side API
Sage CRM 7.2 client-side API

Coming back to our client’s requirement, I went to the above link, and found the ‘hideButton’ API to achieve the goal.

Having found the right API, following are the steps to plug it in Sage CRM 7.2

1.  Go to Administration -> Customisation -> Cases -> Screens

2. Select the correct screen you want the API to trigger for when it loads. Remember you can enable ‘inline customisation’ to find out the right screen.

3. Once you are sure of the screen name, select it from the list of screens in the Case entity (in my case it was ‘CaseWebPicker’). This will give you an access to the ‘Custom Content’ area where in you can type the following javascript code that uses the above API.

4. Code:

     <script>

                crm.ready(function()

                {

                                crm.hideButton(“Continue.gif”);

                })

   </script>

Click update and save the changes.

And voila! The continue button has disappeared!

Case summary screen without the 'Continue' button.
Case summary screen without the ‘Continue’ button.

Similarly, you can use many of the client side APIs with Sage CRM 7.2 to achieve your customisation requirements. The other benefit of using the new APIs is that they are browser independent, and you can use them across IE, Chrome or Firefox without any issues.

I would like to hear about you’ve leveraged Sage CRM 7.2 client side API. Feel free to drop in a line or two about your experience.

Happy Customising!

How to install Nagios XI ?

It’s time to check your IT infrastructure’s performance again!

Today’s blog is the second blog post in the infrastructure performance monitoring series. To recap, in my first blog we looked in brief about how businesses can harness the capabilities of a cool open-source performance monitoring tool – Nagios XI. For those who haven’t read my first blog post in this series can find it here.

In this blog, I’ll be walking you through the installation of Nagios XI step-by-step.  Nagios XI is available under GNU license. A lot of plugins are available and its sizeable community makes Nagios XI the biggest open source performance monitoring tool.

Before we dive into the installation process, it is mandatory you have the latest version of the Linux distribution, CentOS, running on your machine. For those who don’t, you will have to download it from here, and then set it up on your machine before following the steps to install Nagios XI. Setting up CentOS on your machine is beyond the scope of this blog and demands a little space of its own. You can find a detailed instruction manual on installing CentOS server here.

After you have CentOS up and running on your machine, you can start working your way through the steps below to install Nagios XI. So, let’s get going!

If you follow the instructions correctly, you will end up with the following information.

  • Nagios XI and its plugins will be installed under /usr/local/nagios directory.
  • Nagios XI will be configured to monitor a few services of your local machine (Disk usage, CPU load etc)
  • Nagios web interface will be available at http://localhost/nagios

To start with, we need to install required dependencies like Apache, PHP, and libraries like gcc, glibc, glibc-common, GD and its development libraries before installing Nagios XI 4.0.6. To do so, we can use yum package installer.

# yum install httpd gd gd-devel php gcc glibc glibc-common

Create a directory

# mkdir /root/nagios

Navigate to the ‘nagios’ directory you created above

# cd /root/nagios

Download nagios-core & plugin

# wget http://prdownloads.sourceforge.net/sourceforge/nagios/nagios-4.0.6.tar.gz

# wget http://nagios-plugins.org/download/nagios-plugins-2.0.tar.gz

Untar Nagios core

tar xvf nagios-4.0.6.tar.gz

Go to the nagios-4.0.6 directory

# cd nagios-4.0.6

Check all your Nagios XI core configuration settings

# ./configure

If the above configuration settings are as desired, then compile and install all necessary binary files for Nagios XI

# make all

Install all needed libraries in your machine

# make install

Install init scripts for Nagios XI

# make install-init

Install Nagios XI command line. This will enable you to operate Nagios XI from the command prompt.

# make install-commandmode

Install sample Nagios XI files

# make install-config

Install the cool web interface for Nagios XI. The following command will also create a web admin user- “nagiosadmin”

# make install-webconf

After this, we will create a new password for the newly created web admin user. You need to enter the admin password twice and also store it securely in a place for future reference as you will need it to login to the Nagios XI web interface.

# htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin

Start the service and start it on boot

# chkconfig nagios on

# service nagios start

Now, compile and install Nagios XI plugins. We downloaded Nagios XI plugins in /root/nagios, so we will go to that location and install and configure it as below.

# cd /root/nagios

# cd nagios-plugins-2.0

# ./configure

# make

# make install

Start apache service and enable it on boot

# chkconfig httpd on

# service httpd start

That’s it! Now open your new Nagios XI web interface by typing http://localhost/nagios and provide the username “nagiosadmin” along with the password you created earlier in this process.

In my next blog, I’ll show you how you can use ‘bash-shell’ scripting to automate the rotation of performance logs from the CentOS server to your file server. Until then you can explore Nagios XI further and understand its basic configuration settings for start.

Let me know how you go with the Nagios XI installation. See you in my next blog!

Sage CRM and SSRS

In my last post I spoke about being able to use Crystal Reports through browsers other than IE. That got me thinking about the reporting options that are available in Sage CRM and the reasons that each of the different reporting options would be used. As I see it there are 3 different options:

  1. Standard CRM Reporting
  2. Crystal Reports
  3. Other reporting application

Today I will be discussing point 3 as not everyone has experience or access to develop Crystal Reports. The first reporting application that came to mind is SQL Server Reporting Services, or SSRS. The reason I will demonstrate how to use this one is because it is available for most installations utilising MS-SQL as the database engine. The advantages of using SSRS over other application reporting tools are as follows:

  1. Easy to deploy
  2. Easy export to various formats
  3. Reports can be run using report subscriptions
  4. Interactive sorting
  5. Drill down reporting
  6. Browser Based Interface

A lot of these functions are already available through the standard CRM reports or through Crystal Reports, however the decision to use SSRS over other reporting tools will be based on the end user requirements. To generate the report I will make use of the ability of Sage CRM to run ASP pages when they are stored in the reports directory. Jeff Richards wrote about this back in 2009, see here. This is done by saving the file in {CRM Install Director}/wwwroot/reports/{Report Category Name}.

To generate a report, I only need to create one asp page, which will contain 1 line which simply redirects to the report server. I won’t be going into how to create the SSRS report in the first place as there are plenty of resources on the web on how to do this and I would only be wasting my time trying to replicate what other users have already written. Microsoft has a useful guide on how to create the SSRS reports, so if you are new to SSRS then this may be a good place to start. This blog post also assumes that you have SSRS installed and working correctly. For this example I will simply be using the localhost installation.

To get started with a report you only need to create an asp page. This bit is actually really straight forward and can be done with 1 line of code. In this example I created an asp page called Sample Report.asp. The file contains the following:

<% Response.Redirect("http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fOpportunity+Summary&rs:Command=Render") %>

This simply redirects to the report server making and displays the report. You should see something like this:

Step 1 - Sample Report

This report is based on the same report that I did in Crystal in a previous post, although that one was formatted a little bit better than this one. This sample works fine when there are no parameters, if I were to add parameters to the report the following would be displayed first.

Step 1 - Sample Report With Param

Then you can view the report by clicking on the View Report field. Using this reporting method adds a lot of functionality over the standard reporting in CRM, such as:

  1. Ability to sort the data
  2. Export the data into a variety of formats including
    1. XML
    2. CSV
    3. PDF
    4. MHTML (Web Archive)
    5. Excel
    6. Tiff
    7. Word
  3. Refresh the data
  4. Change the parameters used to run the report

So this bit is straight forward, however this is only the tip of the iceberg on what can be done. The next example will show a method on how to replace the Company Summary report that is generated out of CRM. The steps I went through were as follows:

  1. Create my SSRS report to mimic what is currently generated out of CRM. In this case I have done a very limited version of what can be done in SSRS, however it will illustrate the purpose.
  2. Create an asp page as follows:
<!-- #include file ="../eWare.js" --> 
<% var Id = new String(Request.Querystring("Key1")); 
Response.Redirect("http://localhost/ReportServer?%2fCompany+Summary&rs:Command=Render&
rc:Parameters=Collapsed&rc:Toolbar=true&comp_companyid="+Id) 
%>
  1. I saved the asp page in wwwroot\custompages\reports.
  2. Created a button group for the Company summary screen
  3. Added a link to the custom asp page that I created in step 2

As you can see it is quite a straight forward exercise adding in additional reports into CRM, regardless of the reporting tool that is being used.

Crystal Reports and Sage CRM

With Sage CRM now supporting browsers other than Internet Explorer, then can be issues when trying to run Crystal Reports. The issues stems from the fact that Sage CRM uses an ActiveX component to display the report. This ActiveX component is quite powerful giving you access to export the report to multiple formats, search for text, change the display, etc. Only problem is that it is limited to Internet Explorer only.

I personally have a problem with using IE and use Chrome on a daily basis. My main issue with IE is with the limited support for HTML5, particularly in older version of IE. I will concede that there has been an improvement in the latest version and I probably could use it without any problems, but I’ve now moved to Chrome and haven’t come up with a good reason to not to use it. There is a cool little website at html5readiness.com which outlines which features of HTML5 and CSS3 have been implemented in the main stream browsers. Speed is also another consideration and I think the image below sums it up perfectly.

Browsers

Now that I have had my little rant, I will move on to what can be done with Crystal and Sage CRM. In this blog post I will show how to generate a Crystal Report while using Chrome. Unfortunately we do have to forgo some of the functionality that is available through the ActiveX control, however I believe that being able to generate the report in the first place is a real plus.

When trying to run the report in Chrome you should see something like this appear.

Image 1
This is not really all that useful. As I mentioned before this is a problem with Chrome not being able to use the ActiveX components, or at least cannot use it when using a vanilla installation. There are a couple of options available here to get it working:

  1. Use an add in to allow ActiveX components to run, for example you can use IE Tab or ActiveX for Chrome
  2. You can change the way the report is generated and presented to screen.

Both options are valid solutions to the problem; however point 1 requires that software is installed on the workstations for it to work correctly. This blog post will go through point 2 and the changes that are required for a system wide solution.
To make this work you will need to edit the standard Crystal Report asp files that are provided with an installation of Sage CRM. These files will be found in {installation directory}\crm\wwwroot\reports\CrystalReportViewer. In this directory you will see the following files:

  1. Cleanup.asp
  2. RDCrptserver11.asp
  3. RunReport.asp

The only file that needs to be modified to work is the RunReport.asp. The modifications are reasonably straight forward and are documented below.

  1. Backup the RunReport.asp
  2. Find the linesession("oRpt").EnableParameterPrompting = False

    Add the following code:dim CrystalExportOptions
    set CrystalExportOptions = session("oRpt").ExportOptions
    FileNameExport = year(date())&"-"&month(date()) & "-" & day(Date()) & "-" & hour(Time()) & "-" & minute(Time()) & "-" & second(Time())
    FileName = Server.MapPath("../../") + "\Temp\" + FileNameExport + ".pdf"
    CrystalExportOptions.DiskFileName = Server.MapPath("../../") + "\Temp\" + FileNameExport + ".pdf"
    CrystalExportOptions.FormatType = CInt(31)
    CrystalExportOptions.DestinationType = CInt(1)
    session("oRpt").Export False
  3. Find the linesset session("oPageEngine") = session("oRpt").PageEngine
    End If
  4. Add the following code at the endResponse.Redirect("../../Temp/" + FileNameExport + ".pdf")

    dim fs
    Set fs=Server.CreateObject("Scripting.FileSystemObject")
    if fs.FileExists(FileName) then
    fs.DeleteFile(FileName)
    end if
    set fs=nothing
  5. This step is optional as it will run with the code still there, however it is no longer needed. To clean it up remove the script:<script language="javascript">
    function getQueryParam( name )

    </script>
    Leave the script that contains the function CallDestroy()

This will now produce a report below.

Image 2

Now because coding can be loads of fun, I have provided the modified file RunReport available for download. Feel free to use it at your own site(s), the only thing I ask is that if you come up with any cool features let me know so I can share it around.

This method does make use of the Temp directory that is within the Sage CRM website, however the files are immediately deleted after they are returned to the client. This method means that the reports are never stored in the CRM library, however the method for producing reports that are stored against the company or other entities will be investigated in future blog posts.

Some things that I have noticed when doing this is that you don’t need to have the Crystal Report Server on the CRM server itself, it will run using the Crystal Runtime files. This works really well in the situation where you have Sage CRM integrated with Sage ERP 300 as they are provided as part of the workstation installation. One other benefit that I have noticed is the speed at which the report is generated, it is surprisingly quick. I wish I could say that this was by design, but unfortunately it was just one of those items that was just plain good luck.

Please note that the change to these files won’t be supported by Sage, however we do have this running live at a lot of our sites with no issues. These files have been tested with Sage 7.1 and 7.2.

The next post will concentrate on how to generate a Crystal Report from the various screens within Sage CRM. This will concentrate mainly on using button groups for the standard entities and using the CRM.AddButton function for asp pages.

Accsys Framework Series: Sage CRM a New Look!

Welcome to our new series on the Accsys Framework. This is a new add on product that is currently in development by Accsys Consulting and we are giving you a sneak peak on what is coming up.

Sage CRM is a tool that can enhance an organizations business processes by handling the menial and tedious tasks that is necessary for a business to run smoothly by getting the data in order and organised. Accsys Consulting views Sage CRM as one of the most flexible business applications available in the market. This flexibility allows a business to customize Sage CRM to fit the needs of the organisation.

At its very core Sage CRM is an application with three different layers, the user interface, application logic, and the database engine. Making the application adhere to specific business processes requires customization of the three different layers. This customization is done through a variety of means, but most commonly through:

  • HTML
  • Javascript or JQuery
  • SQL Stored Procedures

One of the key advantages that Sage CRM has is the extensibility of the application, allowing businesses to do what they do best without having to change to fit the capabilities of the application.

Technically – extensibility is a system design principle where the system is made to grow and designed so that other components can added on to increase functionality. But if you’re a business partner like us, then you already know that Sage CRM can do that already and that this is nothing new. You can customize Sage CRM by adding new entities, new .asp pages to suit your needs, you can even have CSS to give it a fresher look, use different plugins and many other customization techniques.

Accsys Consulting has decided to take it a step further.

But before I go on with this ‘next step’ let me say why Accsys Consulting decided that we wanted to go down this path. It started as an idea to improve the UI in general of Sage CRM, to make it more up to date on the programming side of the spectrum we wanted to make use of HTML5, to allow business partners to deploy customisations quicker, easier, with more flexibility. In addition to improving the customization component, we were also aiming to improve the end user experience by speeding up data entry and making it as intuitive as possible.

The Framework isn’t a replacement for Sage CRM, instead we see it as an enhancement that builds on an award winning application by adding additional functionality. Our key goal is to provide a system that provides speed, scalability, and usability while taking advantage of the powerful features provided by Sage CRM.

 

 

Just to get you started, here is a taste of some of the features that we have implemented so far.

Logon Screen

We started with the login screen and made it possible to customize it so that the client can customize it to make it fit their needs. This is possible through the current login page, however we wanted to be able to add new features such as a new s feed for the organization.

 

LogonScreen

Dashboard

We have changed the dashboard so it is more intuitive, in the same fashion as the Dynamic Dashboard in Sage CRM. When a user logs in the Accsys Framework they are presented with the cases that they need to address. To access these cases there are the buttons at the top that indicate how many cases is assigned to the user. By clicking on this button it will point you to an ordered list of your cases

Dashboard

Cases

The first entity we put through the framework was Case Management. We needed this internally to help manage our own clients. We had attempted this a few occasions before, however we did have user adoption issues. When developing the case management screens our approach was to ask the technical consultants what they needed and designed the screens around these requirements. The result was a simple to use interface with as much information as possible on the same screen.

CasesSummary

 

Cases Comparison

In comparison with Sage CRM we have migrated a lot of the standard features to our framework, but added some little touches on the way through. For example, the workflow now shows where you have been and where you can go from the current state.

CaseComparison

Sage CRM with Accsys Framework

 

CaseComparison2

 

Find/Search – we have implemented a global search in the framework that searches through all the records in CRM (item 1)

 

Search

 

Workflow actions – Changed the look and feel of the workflow, this shows visibility with what steps have been taken and that the next steps can be done (item 2)

WF

Case menu Items – changed the menu items to be icons with tool-tips that shows the name of the tab when hovered (item 3)

notes

Notes tab – These tabs were designed in such a way so that the page itself doesn’t refresh every time a user clicks on the other tabs. This is due to the structure of the page altogether as the whole web page and the whole of the Accsys Framework is following the design pattern of MVC or Model-View-Controller. For each page in the framework we have separated parts to follow a certain model. So for instance in the Case Summary screen there are a number of different models with different controllers attached to different views. (item 4)

Default/Accsys Framework – a button to return back to the Default CRM or to go to the Accsys Framework. (item 4)

default

 

This is just a taste of the Accsys Framework and we are currently working towards having a full version released that will be available to other Sage Business Partners. In future articles we will discuss some of the key advantages of using the Accsys Framework and how the Accsys Framework works in conjunction with Sage CRM

Upgrade to Sage CRM 7.2!

Sage CRM 7.2, has been released for almost a year now. If you’re a business owner or an IT manager who rely on the earlier versions of Sage CRM to facilitate your day-to-day operations, then it’s about time you look into what Sage CRM 7.2 has to offer. This latest offering from Sage gives your organisation the edge to be able to communicate with your customers through mobility, social media and business collaboration.

In today’s blog, I’ll be walking you through a detailed procedure on how you can upgrade to the latest version of Sage CRM, version 7.2, and enjoy its new features. So, let’s get going!

Preparing for a Test Upgrade

Sage has released three hot-fixes since the release of Sage CRM 7.2 (b, c, d). Sage CRM 7.2d can be upgraded from 7.1 / SP1 / SP2. For any earlier version, it has to be first upgraded to 7.1. It is recommended to upgrade from 7.1 to SP1, and then to SP2 before upgrading to 7.2.

Before performing the upgrade, it is very essential that you build a test environment to mirror your production environment. This will enable you to test the upgrade in a controlled manner and also inform you about the possible issues that you may encounter during the live upgrade, in advance.

Prior to building the test environment, make sure you’ve got the following items in place:

System Requirements: You can refer to Sage CRM 7.2’s software support matrix to understand the server, client and integration platform which Sage CRM 7.2 will support.

License Key: The license key for version 7.2 can be acquired from Sage.

Hot-Fixes: The hot-fixes (b, c, d) can be downloaded from the Sage portal.

Server: If the existing production server does not match with the server requirements of version 7.2, then it’s recommended to prepare a new server before the upgrade.

Once you have the above things ready, it’s time to build a test server on which you will be performing a test upgrade. So let me brief you about one of the ways you can build one.

What is Hyper-V?

Hyper-V is Windows Server virtualization platform that is included as a role of Windows Server 2008 and 2012. It’s a cool technology, in which you can create multiple snapshots of your software installations (virtual machines), thus enabling you to build your test environment incrementally.

You can begin with installing the server operating system, database, and Sage CRM similar to the versions you have on your production environment. Following this, you can upgrade the Sage CRM to the desired version by running the correct service packs and patches.

You can divide the above installation in the form of ‘snapshots’ that allows you to revert back to the previous installation in case something goes wrong. Following is a screen shot of a Hyper-V interface. Creating virtual machines and snapshots in Hyper-V demands a separate space of its own, and I plan to write a small blog-tutorial on that very soon!

test

With the above things ready, you can take the following steps to perform a test upgrade on the virtual machine you’ve created in Hyper-V:

  • Backup the live database, registry, program files of the existing CRM system, and copy them in an accessible location on your virtual machine.
  • Install the existing version of Sage CRM on the test server.
  • Restore the backup of the live database in Sage CRM’s database created in the database server on your test server. You can run the following script for a hassle free database restore:

Use Master

ALTER DATABASE YOURDB

SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB

FROM DISK =’C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak’

WITH REPLACE

  • Copy the backup program files (custom page folder etc.) to the relevant folder in the test environment.
  • Test the data, functions, reports and customisations in the test CRM system.
  • Change some fields in the Custom_SysParams table of the CRM database as required.
  • Upgrade to 7.1 / SP1 / SP2 if the current version is earlier than 7.1 and test the upgrade.
  • Upgrade to 7.2 from 7.1 SP2 and test the upgrade.
  • Outlook plugin may be applied to the client workstations as required.

Testing the exchange integration

It will be easier to test an exchange integration if the exchange server is on a local domain network. If it’s hosted outside, then you will have to liaise with your system administrator for SMTP access.

Live Upgrade

The procedure for performing a live upgrade is very similar to the test upgrade. Performing a test upgrade helps you to know the possible errors and corresponding solutions you might encounter during the live upgrade.

Hence, I will highly recommend you to document the whole test upgrade process and use it as a guide during your live upgrade.

You must be also very mindful of the CRM down-time during the live upgrade, as it is needed by your staff for daily operations.  The test upgrade should guide you well in this case.

Well, with that, I’ll conclude my blog. As discussed earlier, there are many ways you can create a test environment for performing a test Sage CRM upgrade. How about sharing your thoughts about the same? Let the good ideas flow!

You can also refer to this Sage article on the CRM community for further information:

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2014/04/08/sage-crm-7-2-preparing-for-an-upgrade.aspx

Escalation Rules – The Basics

Escalation Rules

I.T. based solutions and I.T. software in general came about as a need to automate tasks. It allows businesses to focus more on their business objectives and not worry about the system that supports it. Escalation Rules allows the creation of automated tasks from notifications, setting values on screen, sending emails given a particular criteria – tasks that users shouldn’t have to worry about in doing their job.

Services on CRM Server

Services

Escalation Rules is a windows service that gets installed by default with Sage CRM. This service is responsible for running any enabled escalation rules that have been set up in CRM. But before we delve into how to create them we need to look at the logical components of an Escalation Rule to avoid issues later down the track. These components are as follows:

  • Criteria
  • Control
  • Task

Criteria – defines the conditions of the Escalation Rule being triggered.

Control – this component ensures that the escalation rule doesn’t run forever (or if you want you can also make it run till the end of time!). This is especially crucial when setting up Escalation Rules that notify other users via email. Without the control component users can and will continue to receive emails until someone turns off the Escalation Rules Service.

Task – this component does the bulk of the work in an Escalation Rule whether it be sending emails, on-screen notifications or just changing a value on the screen.

Both the Criteria and the Control aspects of an Escalation Rule is used in tandem to create a SQL ‘WHERE’ clause

WhereTrigger

The task aspect is executed through a workflow action but without the use of a workflow. It follows the exact the same steps as to how a workflow action is created.

WFactions

 Setup

Let’s say as part of the business process a manager needs to be notified if a lead has a high chance of turning to an opportunity, better yet a client. In this situation we could automate this task by creating an Escalation Rule

  1. Ensure that the escalation service is running.
  2. Go to Administration > Advanced Customisations > Escalation and click on New on the top left side of the screen.
  3. Enter the table or entity that we want this rule to be applied for – in this case ‘Lead’
  4. Enter in  Rule Name ‘Lead Notification’
  5. Enter in the Trigger SQL Clause Lead_Rating = ‘High’ and Lead_deleted is not null and Lead_notified is null.

 

This will ensure that it will only perform our task (sending an email) when

  • The lead rating is set to High
  • The lead hasn’t been deleted
  • And the lead notified hasn’t been flagged

In this SQL clause the lead_rating and the lead_deleted acts as our criteria and the lead_notified (which is a custom field) acts as our control field.

The control works by ensuring not to send emails once it has been filled in with a value.

When you’re done entering the fields hit save.

Now that we have set up the rule itself we now need to add actions to this rule in our case we want an email and to modify our control field so as to not send infinite emails to the manager, which could have dear consequences.

newRules

Choose the new rule that you have created ‘Lead Notification’

  1. Scroll down at the bottom of the screen and hit ‘New’
  2. You will be presented to a screen with the possible workflow actions that we can do; for now choose ‘Send E-mail’
  3. Fill in the Details of the email by adding in the manager that needs to be notified and adding a message to the email. Make sure to fill in the ‘from’ field otherwise Exchange will not send the email out.
  4. Hit Save when you are finished.
  5. Now we have to create the second part of our control component for this escalation rule; again scroll down at the bottom of the screen and choose ‘New’
  6. Select the set Column Value action and choose the column ‘Lead_notified’
  7. Enter in the value ‘Yes’ in the value field
  8. Choose the Attribute as ‘Read-Only’
  9. Hit Save when you are satisfied.

Now we have our escalation rule! Make sure that the escalation rule is enabled otherwise it won’t run at all. Additionally I would advise to test this out so that your poor manager doesn’t get a billion emails about Leads with a High rating, we wouldn’t want him to give him false hope!

 

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.