Genesys CTI User Forum

I thought some of these might come in handy, based on recent requests...  Use them at your own risk - always apply as READ ONLY - and [u]never[/u] use PL/SQL Scripts to UPDATE the cfg database:

List Agent Login ID's:

[color=blue]SELECT CFG_PERSON.FIRST_NAME,
  CFG_PERSON.LAST_NAME,
  CFG_PERSON.USER_NAME,
  CFG_PERSON.EMPLOYEE_ID,
  CFG_AGENT_LOGIN.LOGIN_CODE
FROM CFG_PERSON
INNER JOIN CFG_LOGIN_INFO
ON CFG_PERSON.DBID = CFG_LOGIN_INFO.PERSON_DBID
INNER JOIN CFG_AGENT_LOGIN
ON CFG_LOGIN_INFO.AGENT_LOGIN_DBID = CFG_AGENT_LOGIN.DBID
ORDER BY CFG_PERSON.USER_NAME[/color]

List Agent Skills and Skill Levels:

[color=blue]SELECT CFG_PERSON.FIRST_NAME,
  CFG_PERSON.LAST_NAME,
  CFG_PERSON.USER_NAME,
  CFG_PERSON.EMPLOYEE_ID,
  CFG_SKILL.NAME,
  CFG_SKILL_LEVEL.LEVEL_
FROM CFG_PERSON
INNER JOIN CFG_SKILL_LEVEL
ON CFG_PERSON.DBID = CFG_SKILL_LEVEL.PERSON_DBID
INNER JOIN CFG_SKILL
ON CFG_SKILL_LEVEL.SKILL_DBID = CFG_SKILL.DBID
ORDER BY CFG_PERSON.USER_NAME[/color]

List Agent / Switch:

[color=blue]SELECT CFG_AGENT_LOGIN.LOGIN_CODE,
  CFG_PERSON.FIRST_NAME,
  CFG_PERSON.LAST_NAME,
  CFG_PERSON.EMPLOYEE_ID,
  CFG_PERSON.USER_NAME,
  CFG_SWITCH.NAME AS "SWITCH NAME"
FROM CFG_AGENT_LOGIN
INNER JOIN CFG_LOGIN_INFO
ON CFG_LOGIN_INFO.AGENT_LOGIN_DBID = CFG_AGENT_LOGIN.DBID
INNER JOIN CFG_PERSON
ON CFG_LOGIN_INFO.PERSON_DBID = CFG_PERSON.DBID
INNER JOIN CFG_SWITCH
ON CFG_AGENT_LOGIN.SWITCH_DBID  = CFG_SWITCH.DBID
ORDER BY "SWITCH NAME",
  CFG_PERSON.USER_NAME[/color]

List Applications, Ports, Hosts and IP Address:

[color=blue]SELECT DISTINCT CFG_APPLICATION.NAME AS "APPLICATION NAME",
  CFG_HOST.NAME                      AS "HOST NAME",
  CFG_SERVER.PORT,
  CFG_HOST.IP_ADDRESS
FROM CFG_APPLICATION
INNER JOIN CFG_SERVER
ON CFG_APPLICATION.DBID = CFG_SERVER.APP_DBID
INNER JOIN CFG_HOST
ON CFG_SERVER.HOST_DBID = CFG_HOST.DBID[/color]

List Applications, Port, Host, IP with all of the available Options that may be configured:

[color=blue]SELECT DISTINCT CFG_APPLICATION.NAME AS "APPLICATION NAME",
  CFG_HOST.NAME            AS "HOST NAME",
  CFG_SERVER.PORT,
  CFG_HOST.IP_ADDRESS,
  CFG_APP_OPTION.OPT AS OPTIONS
FROM CFG_APPLICATION
INNER JOIN CFG_SERVER
ON CFG_APPLICATION.DBID = CFG_SERVER.APP_DBID
INNER JOIN CFG_HOST
ON CFG_SERVER.HOST_DBID = CFG_HOST.DBID
INNER JOIN CFG_APP_OPTION
ON CFG_APP_OPTION.OBJECT_DBID = CFG_APPLICATION.DBID[/color]

List the (SCI) Alarm Conditions:

[color=blue]SELECT DISTINCT CFG_ALARM_CONDTN.NAME AS "ALARM CONDITION NAME",
  CFG_ALARM_CONDTN.DESCRIPTION,
  CFG_ALARM_CONDTN.CATEGORY,
  CFG_ALARM_CONDTN.CLEARANCE_TIMEOUT,
  CFG_ALARM_CONDTN.STATE
FROM CFG_ALARM_CONDTN[/color]

Query the (Outbound) Calling Lists and associated Filters, Formats and Table Access:

[color=blue]SELECT DISTINCT *
FROM CFG_CALLING_LIST
INNER JOIN CFG_FILTER
ON CFG_CALLING_LIST.FILTER_DBID = CFG_FILTER.DBID
INNER JOIN CFG_FORMAT
ON CFG_FILTER.FORMAT_DBID = CFG_FORMAT.DBID
INNER JOIN CFG_TABLE_ACCESS
ON CFG_TABLE_ACCESS.FORMAT_DBID = CFG_FORMAT.DBID
WHERE 'xxxxxxxxxxxx' = 'XXXXXXXXXXX'[/color]

Thanks Adam - keep them coming!

How about one for applications and their "connections", aka the applications each is connected to.  I can see it being nice to have a way to specify the application object types to select but I can probably add that...

Thanks for these btw, very helpful to have!

[i]for Oracle[/i]
select app.name as application,listagg(app2.name,', ') WITHIN GROUP (ORDER BY app.name) as connections
from cfg_app_server cfg, cfg_application app, cfg_application app2
where cfg.app_dbid=app.dbid
and cfg.app_server_dbid=app2.dbid
group by app.name
order by app.name;

I am not so familiar with MSSQL, so have not tried find out the alternative for a listagg function.

Ohhh come on dude...do a little work there

Here's one I use quite a bit.  I use Oracle so have no idea if this will work with other DB's, but it's not that complicated so I'd be surprised if it didn't.

It returns all transaction list objects with the name, section, option and value within a specific tenant, if you want all tenants just comment out the tenant id line.

[code]
select
  tr.name transactionname,
  fpparent.prop_name section,
  fp.prop_name "OPTION",
  fp.prop_value "VALUE"
from cfg_transaction tr
join cfg_flex_prop fp
on tr.dbid=fp.object_dbid
  join cfg_flex_prop fpparent
  on fp.parent_dbid = fpparent.dbid
where fp.object_type = 16
and tr.tenant_dbid = 101 -- CHANGE THIS WHATEVER TENANT ID YOU LIKE
and tr.type = 21
order by tr.name, fpparent.prop_name, fp.prop_name;
[/code]

** Update
I came across an issue using this query recently.  If you have long values in some transaction objects (eg. longer than 255 char) you will find that this query returns 2 lines for those specific list values.  To work around this in Oracle I used the LISTAGG function to combine the 2 rows based on the cfg_flex_prop.PART field, not sure what the equivalent method in MSSQL would be.

For those who want the objects and their connections when using an MSSQL database instead of Oracle here is the modified query:

[code]select app.name as application, 
  STUFF((SELECT distinct '' + app2.name + ', ' 
         from cfg_application app2, cfg_app_server cfg 
         where cfg.app_server_dbid = app2.dbid 
                 and cfg.app_dbid=app.dbid 
            FOR XML PATH(''), TYPE 
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') connections 
from cfg_application app 
group by app.name 
        , app.dbid 
order by app.name[/code]

Looking for a script on transfered calls