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:

Prerequisite:

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.

Examples:
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:

e.g.

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

       *


from
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
http://support.avaya.com/css/P8/documents/100016299


Other useful SQL for CMS:
 
Hourly Split information


SELECT [row_date]
      ,[starttime]
      --,[intrvl]
     --,[acd]
      ,[split]
      ,[callsoffered]
      ,[acdcalls]
      ,[busycalls]
      ,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
      ,[maxstaffed]
      ,[busytime]
      ,[i_stafftime]
      ,[i_availtime]
      ,[i_acdtime]
      ,[i_acwtime]
      ,[i_acwouttime]
      ,[i_acwintime]
      ,[i_auxtime]
      ,[i_auxouttime]
      ,[i_auxintime]
      ,[i_othertime]
      ,[acwincalls]
      ,[acwintime]
      ,[auxincalls]
      ,[auxintime]
      ,[acwoutcalls]
      ,[acwouttime]
      ,[acwoutoffcalls]
      ,[acwoutofftime]
      ,[acwoutadjcalls]
      ,[auxoutcalls]
      ,[auxouttime]
      ,[auxoutoffcalls]
      ,[auxoutofftime]
      ,[auxoutadjcalls]
      /*,[event1]
      ,[event2]
      ,[event3]
      ,[event4]
      ,[event5]
      ,[event6]
      ,[event7]
      ,[event8]
      ,[event9]*/
      ,[assists]
      ,[inflowcalls]
      ,[anstime]
      ,[acdtime]
      ,[acwtime]
      ,[o_acdcalls]
      ,[o_acdtime]
      ,[o_acwtime]
      ,[acdcalls1]
      ,[acdcalls2]
      ,[acdcalls3]
      ,[acdcalls4]
      ,[acdcalls5]
      ,[acdcalls6]
      ,[acdcalls7]
      ,[acdcalls8]
      ,[acdcalls9]
      ,[acdcalls10]
      ,[backupcalls]
      ,[holdcalls]
      ,[holdtime]
      ,[holdabncalls]
      ,[transferred]
      ,[conference]
      ,[abncalls]
      ,[abntime]
      ,[abncalls1]
      ,[abncalls2]
      ,[abncalls3]
      ,[abncalls4]
      ,[abncalls5]
      ,[abncalls6]
      ,[abncalls7]
      ,[abncalls8]
      ,[abncalls9]
      ,[abncalls10]
      ,[dequecalls]
      ,[dequetime]
      ,[disccalls]
      ,[disctime]
      ,[outflowcalls]
      ,[outflowtime]
      ,[interflowcalls]
      ,[lowcalls]
      ,[medcalls]
      ,[highcalls]
      ,[topcalls]
      ,[acceptable]
      ,[servicelevel]
      ,[period1]
      ,[period2]
      ,[period3]
      ,[period4]
      ,[period5]
      ,[period6]
      ,[period7]
      ,[period8]
      ,[period9]
      ,[maxinqueue]
      ,[maxocwtime]
      ,[periodchg]
      ,[svclevelchg]
      ,[i_ringtime]
      ,[ringtime]
      ,[ringcalls]
      ,[abnringcalls]
      ,[o_abncalls]
      ,[o_othercalls]
      ,[da_acwincalls]
      ,[da_acwintime]
      ,[da_acwocalls]
      ,[da_acwotime]
      ,[noansredir]
      ,[incomplete]
      ,[acdauxoutcalls]
      ,[i_acdaux_outtime]
      ,[i_acdauxintime]
      ,[i_acdothertime]
      ,[phantomabns]
      ,[othercalls]
      ,[othertime]
      ,[slvlabns]
      ,[slvloutflows]
      ,[i_arrived]
      ,[i_auxtime0]
      ,[i_auxtime1]
      ,[i_auxtime2]
      ,[i_auxtime3]
      ,[i_auxtime4]
      ,[i_auxtime5]
      ,[i_auxtime6]
      ,[i_auxtime7]
      ,[i_auxtime8]
      ,[i_auxtime9]
      ,[i_da_acdtime]
      ,[i_da_acwtime]
      ,[i_tavailtime]
      ,[i_tauxtime]
      ,[maxtop]
      ,[i_normtime]
      ,[i_ol1time]
      ,[i_ol2time]
      ,[i_tothertime]
      ,[max_tot_percents]
      ,[acdcalls_r1]
      ,[acdcalls_r2]
      ,[i_acdtime_r1]
      ,[i_acdtime_r2]
      ,[i_acwtime_r1]
      ,[i_acwtime_r2]
      ,[i_ringtime_r1]
      ,[i_ringtime_r2]
      ,[i_othertime_r1]
      ,[i_othertime_r2]
      ,[i_auxtime_r1]
      ,[i_auxtime_r2]
      ,[i_otherstbytime_r1]
      ,[i_otherstbytime_r2]
      ,[i_auxstbytime_r1]
      ,[i_auxstbytime_r2]
      ,[i_behindtime]
      ,[i_autoreservetime]
      ,[targetpercent]
      ,[targetpctchg]
      ,[targetseconds]
      ,[targetsecchg]
      ,[targetacdcalls]
      ,[targetabns]
      ,[targetoutflows]
  FROM [conso_hsplit]
  WHERE row_date = '2013-02-19'
  and split = 100 
  --and starttime = 1100
  order by starttime


Comments

Popular Posts