Access Avaya CMS database via ODBC link

As there is a linked server on our SQL server to Avaya Call Management System (CMS), we can make use of the link server to directly get CMS data:


The below samples assumes that you have a correctly setup ODBC connection and linked server at SQL server.  Therefore, we can use SQL query inside the SQL server via the Linked Server.

1. User the link server directly:
select top 1000 * from cmsserver.cms.root.hagent

2. For some tables, you need to use OPENQUERY() to enhance the query speed:


declare @sql  nvarchar(1000)
declare @sql1  nvarchar(1000)
set @sql = N'SELECT * FROM hsplit WHERE row_date = TODAY'
set @sql1 = 'select * from OPENQUERY ( cmsserver  ''' + @sql + ''') order by row_date, starttime'
print @sql1
exec sp_executesql @sql1

3. The time presented at the DB is in UNIX time.  We may use the below SQL statement to convert UNIX time to SQL time

select login_datetime = dateadd(ss,login,'1970-01-01'),  
       -- convert UNIX time to SQL time

       logout_datetime = dateadd(ss,logout,'1970-01-01'),  

       -- convert UNIX time to SQL time


OPENQUERY ( cmsserver, 'SELECT * FROM haglog WHERE row_date = TODAY')
order by logid, seq_num

-- for row_date, you may need to use MM/DD/YY format in OPENQUERY()
-- TODAY is the Informix SQL function to GETDATE()

Table definition:
Avaya™ Call Management System (CMS)
Open Database Connectivity

Other useful SQL for CMS:
Hourly Split information

SELECT [row_date]
      ,case when acdcalls > 0 then acdtime / acdcalls else 0 end as Avg_Talk_Time
      ,case when acdcalls > 0 then acwtime / acdcalls else 0 end as Avg_ACW_Time
      ,(i_stafftime + i_availtime + i_acdtime + i_acwtime + i_acwouttime + i_acwintime + i_auxtime + i_auxouttime + i_auxintime + i_othertime) /60.00/60 as Av_Pos_Stf
  FROM [conso_hsplit]
  WHERE row_date = '2013-02-19'
  and split = 100 
  --and starttime = 1100
  order by starttime


Popular Posts