Reporting on Kayako eSupport in MSSQL

So here’s the challenge we faced:

Our support system runs on Kayako eSupport using Rackspace Cloud and a mySQL database

Our main system (our application, or admin area – everything!) runs on our own MSSQL server.

As the business is growing, the volume of support tickets is growing too and so is the size of the support team. The Support Manager needs a better overview of what’s going on with support and individual members of the team.

We have no mySQL/PHP skills in-house, so we really need this data in our MSSQL database to be able to write reports against it.

Here’s how we achieved it.

Add a Linked Server

First we installed the MySQL ODBC driver on our MSSQL databse server
Add then added the mySQL database as a Linked Server in MSSQL (we called the linked server ‘kfsupport’)

Create Tables

The data we want is in the mySQL databases ‘swstaaff’, ‘swtickets’ and ‘swticketposts’
So we need to create tables in MSSQL to hold this data

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[st_staff](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[staffid] [int] NULL,
	[fullname] [nvarchar](250) NULL,
	[username] [nvarchar](150) NULL,
	[lastvisit] [bigint] NULL,
	[lastactivity] [bigint] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[st_ticketposts](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ticketpostid] [int] NULL,
	[ticketid] [int] NULL,
	[dateline] [bigint] NULL,
	[fullname] [nvarchar](250) NULL,
	[creator] [int] NULL,
	[staffid] [int] NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[st_tickets](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ticketid] [nchar](10) NULL,
	[ticketmaskid] [nvarchar](50) NULL,
	[ticketstatusid] [int] NULL,
	[ownerstaffid] [int] NULL,
	[assignstatus] [int] NULL,
	[fullname] [nvarchar](250) NULL,
	[lastreplier] [nvarchar](250) NULL,
	[subject] [nvarchar](350) NULL,
	[dateline] [bigint] NULL,
	[lastactivity] [bigint] NULL,
	[laststaffreplytime] [bigint] NULL
) ON [PRIMARY]

GO

Date Formats

As with most LAMP stuff, the date format for the data in mySQL is UNIXTIME (number of seconds since 1/1/1970). Whereas we prefer to work with ISO formats (ie 20110129 or 20110229161700)

We will need a couple of functions to deal with converting from UNIXTIME  to ISO format

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetISODateLong]
(
	@date datetime
)
RETURNS bigint
AS
BEGIN
DECLARE @ISOdate varchar(8)
DECLARE @ISOTime varchar(8)
DECLARE @ISOTimeStamp bigint

SET @ISOdate = convert(varchar(8), @date, 112)
SET @ISOTime = replace(convert(varchar(8), @date, 108),':','')

SET @ISOTimeStamp = @ISOdate + '' + @ISOTime

RETURN @ISOTimeStamp

END

GO

CREATE FUNCTION [dbo].[f_UnixTimeToISO_Long]
(
	@unixtime bigint
)
RETURNS bigint
AS
BEGIN
	RETURN dbo.GetISODateLong(dateadd(ss,@unixtime,'1970-01-01'))

END

GO

Copying the data across

So that’s everything set up – we have a connection to the mySQL database and we have local tables for storing the data. Now a stored procedure to copy the data across

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[p_Support_RefreshData]
	AS
BEGIN
	set nocount on

	--staff
	TRUNCATE table st_staff

	INSERT INTO st_staff
	(staffid, fullname, username, lastvisit, lastactivity)
	SELECT
		staffid, fullname, username,
		dbo.f_UnixTimeToISO_Long(lastvisit),
		dbo.f_UnixTimeToISO_Long(lastactivity)
	FROM
	openquery (kfsupport, 'select staffid, fullname, username,
 lastvisit, lastactivity FROM swstaff')

	--ticket headers
	TRUNCATE table st_tickets

	INSERT INTO st_tickets
	( [ticketid]
      ,[ticketmaskid]
      ,[ticketstatusid]
      ,[ownerstaffid]
      ,[assignstatus]
      ,[fullname]
      ,[lastreplier]
      ,[subject]
      ,[dateline]
      ,[lastactivity]
      ,[laststaffreplytime])
	SELECT [ticketid]
      ,[ticketmaskid]
      ,[ticketstatusid]
      ,[ownerstaffid]
      ,[assignstatus]
      ,[fullname]
      ,[lastreplier]
      ,[subject]
      ,dbo.f_UnixTimeToISO_Long(dateline)
      ,dbo.f_UnixTimeToISO_Long(lastactivity)
      ,dbo.f_UnixTimeToISO_Long(laststaffreplytime) FROM
	openquery (kfsupport, 'select  ticketid,ticketmaskid,ticketstatusid,ownerstaffid,assignstatus,
fullname,lastreplier,subject,dateline,lastactivity,
laststaffreplytime FROM swtickets order by ticketid desc LIMIT 5000')

	TRUNCATE TABLE st_ticketposts

	INSERT INTO st_ticketposts
	(ticketpostid,ticketid,dateline,fullname,creator,staffid)
	SELECT [ticketpostid]
      ,[ticketid]
      ,dbo.f_UnixTimeToISO_Long(dateline)
      ,[fullname]
      ,[creator]
      ,[staffid] FROM
	openquery (kfsupport, 'select ticketpostid,ticketid,dateline,fullname,creator
,staffid FROM swticketposts ORDER BY ticketid DESC limit 25000')

	--update t_sys with current timestamo
	UPDATE t_sys SET supportdata = dbo.GetISOTimeStamp()
END

GO

Right at the end we update t_sys with the current date and time so the Support Manager knows when the data was last imported and can update it if need be.

Also note we’re limiting the amount of data we pull across to the last 5,000 tickets and the last 25,000 posts. There’s no point copying all of the data across as we’re unlikely to want to report on tickets that old.

It takes just under 6 seconds to refresh the data.

So we now have all of the recent support data in MSSQL and can start writing reports on it.

I’ve no idea whether this is going to be of use to anyone else – I hope it is. If you do use it then let me know in the comments below and I may then also add the code we use to generate reports.