Author Topic: Some Useful Genesys Configuration Database PL/SQL Scripts...  (Read 13977 times)

Adam G

  • Guest
Some Useful Genesys Configuration Database PL/SQL Scripts...
« on: October 03, 2015, 05:26:17 PM »
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'


« Last Edit: October 06, 2015, 12:52:34 AM by adamgill »

Adam G

  • Guest
Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
« Reply #1 on: October 08, 2015, 10:51:44 PM »
  • Best Answer
  • ...if anyone needs anything similar, put your requests in this thread.

    Offline genesysguru

    • Sr. Member
    • ****
    • Posts: 282
    • Karma: 11
      • Genesys Guru Blog
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #2 on: October 08, 2015, 11:03:15 PM »
  • Best Answer
  • Thanks Adam - keep them coming!

    Adam G

    • Guest
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #3 on: October 09, 2015, 01:54:26 AM »
  • Best Answer
  • ....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...

    Offline zimmerru

    • Newbie
    • *
    • Posts: 16
    • Karma: 0
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #4 on: October 20, 2015, 03:49:37 AM »
  • Best Answer
  • 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!

    Adam G

    • Guest
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #5 on: October 20, 2015, 05:14:53 AM »
  • Best Answer
  • 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!

    Offline Kubig

    • Hero Member
    • *****
    • Posts: 2686
    • Karma: 42
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #6 on: October 20, 2015, 05:34:25 PM »
  • Best Answer
  • 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.
    « Last Edit: October 20, 2015, 09:12:58 PM by Kubig »
    Genesys certified professional consultant (GVP, SIP, GIR and Troubleshooting)

    Offline zimmerru

    • Newbie
    • *
    • Posts: 16
    • Karma: 0
    Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #7 on: October 20, 2015, 09:15:22 PM »
  • Best Answer
  • 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?


    Sent from my iPhone using Tapatalk
    « Last Edit: October 20, 2015, 09:16:58 PM by zimmerru »

    Offline cavagnaro

    • Administrator
    • Hero Member
    • *****
    • Posts: 7430
    • Karma: 56330
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #8 on: October 20, 2015, 09:22:39 PM »
  • Best Answer
  • Ohhh come on dude...do a little work there

    Offline zimmerru

    • Newbie
    • *
    • Posts: 16
    • Karma: 0
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #9 on: October 20, 2015, 09:23:26 PM »
  • Best Answer
  • Lol fair enough... I'll try.


    Sent from my iPhone using Tapatalk

    Offline Dionysis

    • Sr. Member
    • ****
    • Posts: 408
    • Karma: 8
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #10 on: October 20, 2015, 11:13:17 PM »
  • Best Answer
  • 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]
    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.
    « Last Edit: October 26, 2016, 10:41:28 PM by Dionysis »
    if len(description) < enough:
        from troubleshooting.skills import psychic

    Adam G

    • Guest
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #11 on: October 21, 2015, 01:14:12 AM »
  • Best Answer
  • ...excellent - thanks for keeping up this useful thread!

    Offline zimmerru

    • Newbie
    • *
    • Posts: 16
    • Karma: 0
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #12 on: October 28, 2015, 10:52:36 PM »
  • Best Answer
  • For those who want the objects and their connections when using an MSSQL database instead of Oracle here is the modified query:

    Code: [Select]
    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

    Adam G

    • Guest
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #13 on: January 16, 2016, 07:02:58 PM »
  • Best Answer
  • 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!

    Offline Kingo001

    • Newbie
    • *
    • Posts: 1
    • Karma: 0
    Re: Some Useful Genesys Configuration Database PL/SQL Scripts...
    « Reply #14 on: April 29, 2016, 12:04:01 PM »
  • Best Answer
  • Looking for a script on transfered calls