Data Extraction - Avaya CMS

Extracting from Avaya CMS

In my current role I extract data from CMS using visual basic in Excel. I acquired the code to do this from an online forum but I have modified this slightly to suit my needs. I feel I have to warn you if you use CMS it is usually set up to purge periodic data after a couple of weeks, so if you want to access this data after this time you will need to make your own copy. First thing you need to do is decide what information you may need later and how you are going to save it. I do this by importing the data into excel then exporting this to a text file.

Deciding on Data

First in CMS you will either need to pick one or more of the standard reports or as I have done use the reports wizard to create reports giving me the information I want. Here are some examples of the data I have set up on several reports which help me build my reporting suit.

Periodic Data – for each agent group I extract the following

Periodic Time – preferably have this set to every 15 minutes, you may need to speak to the administrators as this is normally set at 30 minutes
Calls offered
Abandoned calls
Failed Calls – this is the number of calls not answered in your chosen service level agreement time, this is usually set at 20 seconds
Talk Time – These are the totals not the average
Hold Time - These are the totals not the average
Call Work Time - These are the totals not the average
ASA – Average speed of answer
Longest Wait – the longest time a customer has queued for
ACD time – this is the amount of time your advisors accumulated in that 15 minute period.

From these metrics you can build quite a comprehensive periodic calls report.

Advisor Data

Advisors Name
Agent Group – this is the call types taken
Calls handled
Talk time
Call Work
Call Hold
The 10 aux codes
Total sign in time

These are two examples of the kind of data I extract from CMS each day and save as a text file.

Text Files

Now you know what data you have you need to make sure you can find it. My personal preference is to save the data files for each day in a separate folder, and the simplest way I have found for naming these folders is to give them the date as the name, how you format this is up to you but I use yymmdd, as when this is sorted it will always stay in date order. Or another simple solution is to start with a folder named the year eg 2012 then have 12 folders inside of this for each month and finally a folder for each day inside of here.

When making the text file I don’t use “,” as a separator, I used to until I wanted to store free text and found the “,” was being used in the free text and would therefor mess up the order in the file, so I use “^” this character is not used very often so is perfect to separate the columns in the text file.

Export Text File Code

Sub ExportLogFile(FileName, SheetName)

Dim WholeLine, CellValue As String

Dim FNum As Integer
Dim RowValue, ColValue, StartRow, EndRow, StartCol, EndCol as Double

Sheets(SheetName).Activate

sep = "^"

ActiveSheet.Cells(1, 1).Activate

On Error GoTo EndMacro:

FNum = FreeFile

StartRow = 1

StartCol = 1

With ActiveSheet.UsedRange

    EndRow = .Cells(.Cells.Count).Row

    EndCol = .Cells(.Cells.Count).Column

End With

Open fname For Output Access Write As #FNum

For RowValue = StartRow To EndRow

    WholeLine = ""

    For ColValue = StartCol To EndCol

        If Cells(RowValue, ColValue).Value = "" Then

            CellValue = " "

        Else

            CellValue = Cells(RowValue, ColValue).Text

        End If

        WholeLine = WholeLine & CellValue & sep

    Next ColValue

       WholeLine = Left(WholeLine, Len(WholeLine) - Len(sep))

    Print #FNum, WholeLine

Next RowValue

EndMacro:

On Error GoTo 0

Close #FNum

End Sub

Avaya CMS Code

Sub GetCMSReport()

Dim cvsApp As New cvsApplication

Dim cvsConn As New cvsConnection
Dim cvsCatalog As New cvsCatalog
Dim cvsRpt As New cvsReport
Dim DTE As Date

DTE = “Put Date of report here”

If cvsApp.CreateServer("CMS Login", "CMS Password", "", "IP of CMS Server", False, "ENU", cvsSrv, cvsConn) Then

  If cvsConn.Login("CMS Login", "CMS Password", "IP CMS Server", "ENU") Then
  End If
End If

Set cvsCatalog = cvsSrv.Reports

cvsSrv.Reports.ACD = 1

cvsCatalog.CreateReport cvsCatalog.Reports.Item("CMS Report Path and Name"), cvsRpt

If cvsRpt.SetProperty("Date", DTE) And cvsRpt.SetProperty("Splits/Skills", “Skills needed on Report”) Then ‘ set as many properties as the report needs.

End If

cvsRpt.ExportData "", 9, 0, True, True, True

ImportSheet.Activate

ActiveSheet.Range("A1").Select
ActiveSheet.Paste

cvsRpt.Quit

'End CMS

If Not cvsSrv.Interactive Then

    If cvsSrv.ActiveTasks.Count > 0 Then

      cvsSrv.ActiveTasks.Remove cvsRpt.TaskID

    End If

      cvsApp.Servers.Remove cvsSrv.ServerKey

End If

cvsRpt.Quit

cvsSrv.Connected = False
cvsTasksCol = False

Set cvsApp = Nothing

Set cvsConn = Nothing
Set cvsSrv = Nothing
Set cvsCatalog = Nothing
Set cvsRpt = Nothing
Set cvsSrv = Nothing

cvsConn.Logout

cvsConn.Disconnect

cvsConn.Logout

cvsConn.Disconnect

End Sub

So there is the code to export your CMS data, you can modify the code to suit your individual needs. Like I said earlier I did not write the code, either module, but got these from online forums so please help yourself.

If you require any help please drop me a message and I will try to respond as soon as possible. Or if you have any more efficient ways of extracting data from CMS or any ACD please let me know and I will publish this, if you want me too.