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.
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
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