Some Useful Genesys Configuration Database PL/SQL Scripts...

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 never use PL/SQL Scripts to UPDATE the cfg database:

List Agent Login ID’s:

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

List Agent Skills and Skill Levels:

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

List Agent / Switch:

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

List Applications, Ports, Hosts and IP Address:

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

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

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

List the (SCI) Alarm Conditions:

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

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

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’

…if anyone needs anything similar, put your requests in this thread.

Thanks Adam - keep them coming!

…List Agents / All CIM Agent Groups…

SELECT DISTINCT CFG_PERSON.FIRST_NAME,
CFG_PERSON.LAST_NAME,
CFG_PERSON.USER_NAME,
CFG_PERSON.EMPLOYEE_ID,
CFG_GROUP.NAME AS “AGENT GROUP NAME”
FROM CFG_AGENT_GROUP
INNER JOIN CFG_PERSON
ON CFG_AGENT_GROUP.AGENT_DBID = CFG_PERSON.DBID
INNER JOIN CFG_GROUP
ON CFG_AGENT_GROUP.GROUP_DBID = CFG_GROUP.DBID
ORDER BY “AGENT GROUP NAME”

…Inter-Site Switch Connectivity…

SELECT DISTINCT CFG_SWITCH_ACCESS.FROM_SWITCH_DBID AS “FROM SWITCH”,
CFG_SWITCH_ACCESS.TO_SWITCH_DBID AS “TO SWITCH”,
CFG_SWITCH_ACCESS.ACCESS_CODE,
CFG_SWITCH1.NAME AS “FROM SWITCH NAME”,
CFG_SWITCH.NAME AS “TO SWITCH NAME”,
CFG_PHYS_SWITCH1.NAME AS “TO PHYSICAL SWITCH NAME”,
CFG_PHYS_SWITCH.NAME AS “FROM PHYSICAL SWITCH NAME”
FROM CFG_SWITCH_ACCESS
INNER JOIN CFG_SWITCH
ON CFG_SWITCH_ACCESS.FROM_SWITCH_DBID = CFG_SWITCH.DBID
INNER JOIN CFG_PHYS_SWITCH
ON CFG_SWITCH.PHYS_SWITCH_DBID = CFG_PHYS_SWITCH.DBID
INNER JOIN CFG_SWITCH CFG_SWITCH1
ON CFG_SWITCH_ACCESS.TO_SWITCH_DBID = CFG_SWITCH1.DBID
INNER JOIN CFG_PHYS_SWITCH CFG_PHYS_SWITCH1
ON CFG_SWITCH1.PHYS_SWITCH_DBID = CFG_PHYS_SWITCH1.DBID

…probably a bit random…

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 can’t see any application>application connection details within the data schema… but, then again, mine is an extract, so perhaps it is listed within a View or maybe a Procedure, called at run-time?

Unless someone has already found it - in which case it would be useful to have in this thread!

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

What sql engine is that for? I get an error that listagg isn’t a recognized function on mssql 2008 r2.

Edit: doh I see the oracle note at the top… Can anyone help translate this for mssql?

Ohhh come on dude…do a little work there

Lol fair enough… I’ll try.

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.

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;

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

…excellent - thanks for keeping up this useful thread!

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

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

Does anyone have a cfg plsql script that might extract the UDATA/SDATA KVP’s, as used in Routing Strategies? Or, as I suspect, do they only exist between the Strategy/Sub-Routing and the URS?

Thanks!

Looking for a script on transfered calls

…not really what we are trying to achieve, here. These are scripts which extracts different levels of detail from the configuration layer - not routing… I think you might need to look at the reporting tools more closely…

Udata is not stored on DB Adam. What do exactly wanna achieve?

Enviado de meu E6633 usando Tapatalk

…a very old reporting problem, cav;

Which Skill and Skill Level was actually used in the Routing, to deliver the Call to the Agent.

I know it should be indicated by the Routing/(V)Q which is in use - but when you start using Groups things get a little… “hazy”. Was wondering if anyone had found a definitive way to determine something like;

Timestamp | ConnID | Target (Original/Required) Skill/Level | Skill/Level actually used to Route Caller | Target Agent

The idea is to be able to report on the effectiveness of the Routing to use Skill and Skill Levels more effectively. Not sure if it already exists, though - or whether it is achievable?

Thanks!

Possibly reporting on VAGs in GIM then filter by VQ?

Sent from my SM-N9005 using Tapatalk

Yeah you can get this in GIM if report_targets is set to true on URS.

On cfg db, only the compiled strategy is saved on cfg_flex_prop as binary, so you cannot get from there