Genesys CTI User Forum
Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: Adam G on October 03, 2015, 08:26:17 AM
-
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]
-
...if anyone needs anything similar, put your requests in this thread.
-
Thanks Adam - keep them coming!
-
....List Agents / All CIM Agent Groups...
[color=blue][font=times new roman]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"[/font][/color]
...Inter-Site Switch Connectivity...
[color=blue][font=times new roman]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[/font][/color]
...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!
-
[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.
-
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
-
Ohhh come on dude...do a little work there
-
Lol fair enough... I'll try.
Sent from my iPhone using Tapatalk
-
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.
-
...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:
[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]
-
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...
-
[quote author=adamgill link=topic=9136.msg41958#msg41958 date=1452938578]
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!
[/quote]
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 :(
-
Some more SQL script stuff. Both should work for MSSQL and Oracle.
[code]-- all installed applications (except GUIs) and their parameters and autostart options
SELECT
cfg_host.name AS "Host",
cfg_host.ip_address AS "IP",
cfg_application.name AS "App",
cfg_app_tenant.tenant_dbid AS "Tenant",
cfg_application.version AS "Version",
cfg_server.port AS "Port",
cfg_application.work_directory AS "Path",
cfg_application.command_line AS "Command Line",
cfg_application.cmd_line_args AS "Arguments",
CASE cfg_application.auto_restart
WHEN 1 THEN 'no'
WHEN 2 THEN 'yes'
ELSE 'unknown'
END AS "Auto-Restart",
cfg_application.startup_timeout AS "Startup Timeout",
CASE (
SELECT LOWER (prop_value)
FROM cfg_flex_prop
WHERE prop_name = 'autostart'
and object_dbid = cfg_application.dbid
and object_type = 9) -- CfgObjectType Application
WHEN 'true' THEN 'true'
WHEN 'false' THEN 'false'
ELSE 'null'
END AS "autostart"
FROM cfg_application
INNER JOIN cfg_app_tenant ON cfg_application.dbid = cfg_app_tenant.app_dbid
INNER JOIN cfg_server ON cfg_application.dbid = cfg_server.app_dbid
INNER JOIN cfg_host ON cfg_server.host_dbid = cfg_host.dbid
-- WHERE cfg_application.type != 8 -- Optional to omit DAPs
ORDER BY "Host", "APP"
[/code]
The next one is not from me (found it either somewhere here ;D ) or on Genesys Knowledge Base
[code]-- all applications and their connections with ADDP parameters
SELECT
hst.name AS "Host",
appfrom.name AS "Application",
appto.name AS "Connects To",
con.conn_protocol AS "Protocol",
con.timout_local AS "Local T/O",
con.timout_remote AS "Remote T/O",
lc.lc_value AS "Trace Mode"
FROM cfg_application appfrom
INNER JOIN cfg_app_server con
ON appfrom.dbid = con.app_dbid
INNER JOIN cfg_application appto
ON con.app_server_dbid = appto.dbid
INNER JOIN cfg_server srv
ON srv.app_dbid = appfrom.dbid
INNER JOIN cfg_host hst
ON srv.host_dbid = hst.dbid
INNER JOIN cfg_locale lc
ON con.mode_ = lc.lc_subtype
WHERE
lc.lc_class = 8
AND lc.lc_type = 30
ORDER BY hst.name, appfrom.name, appto.name
[/code]
-
[quote author=adamgill link=topic=9136.msg43187#msg43187 date=1461932576]
...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!
[/quote]
maybe you can start from here, assuming a properly configured Info Mart (works on Oracle, might need some changes for connid and timestamps in sqlserver):
[code]
select
to_char(to_date('19700101', 'yyyymmdd') + msf.end_ts / 86400 + cast(dt.label_tz as number)/24,'yyyy-mm-dd hh24:mi:ss') as route_time,
to_char(to_date('19700101', 'yyyymmdd') + msf.end_ts / 86400,'yyyy-mm-dd hh24:mi:ss') as route_time_gmt,
vq.resource_name as virtual_queue,
lpad(trim(to_char(i.media_server_ixn_id, 'XXXXXXXXXXXXXXXX')),16,'0') as connid,
rt.SKILL_EXPRESSION as skill_expression,
ag.resource_name as agent_username,
ag.employee_id as agent_employee_id
from mediation_segment_fact msf
inner join resource_ vq on vq.resource_key = msf.resource_key
inner join interaction_resource_fact irf on irf.interaction_resource_id = msf.TARGET_IXN_RESOURCE_ID
inner join strategy s on s.strategy_key = irf.strategy_key
inner join date_time dt on dt.date_time_key = msf.start_date_time_key
inner join technical_descriptor td on td.technical_descriptor_key = msf.technical_descriptor_key
inner join ROUTING_TARGET rt on rt.ROUTING_TARGET_KEY = irf.routing_target_key
inner join interaction_fact i on i.interaction_id = msf.interaction_id
inner join resource_ ag on ag.resource_key = irf.resource_key
where dt.label_yyyy_mm_dd = '2016-05-01'
and td.technical_result_code = 'DIVERTED'
and td.result_reason_code = 'ANSWEREDBYAGENT'
[/code]
-
Thank you - I will give this a try.//
To put some context around this;
A lot of complex Routing can get a bit messy and, sometimes, it's a good idea to try and find out exactly which Skill/Level the URS used (in the end) on which to base the route for the call. It's also very useful to troubleshoot your Routing Strategies and Sub-Routines and compare them with actual results.
I knew it would be possible in GI2 but I never got around to finding a definitive answer - I'm hoping this is it!
Thanks again!
-
....I think this also belongs here - it's not 100% accurate, but better than nothing for attempting an "offline" conversion between DB Scripting languages (Oracle<>SQL Server):
[url=http://www.sqlines.com/online]http://www.sqlines.com/online[/url]
-
[b]Yes! [/b] With a little tweaking for SQL Server - and the removal of the provided date constraints, this returns all of the routing/vq/skill/level information, per interaction, for everything on record;
[color=blue][font=times new roman]select
dateadd(s, dt.DATE_TIME_KEY, '19700101') as effective_date_timeslice,
vq.resource_name as virtual_queue_used,
rt.SKILL_EXPRESSION as skill_and_skill_level_used,
ag.resource_name as target_agent_username,
ag.employee_id as target_agent_id
from mediation_segment_fact msf
inner join resource_ vq on vq.resource_key = msf.resource_key
inner join interaction_resource_fact irf on irf.interaction_resource_id = msf.TARGET_IXN_RESOURCE_ID
inner join strategy s on s.strategy_key = irf.strategy_key
inner join date_time dt on dt.date_time_key = msf.start_date_time_key
inner join technical_descriptor td on td.technical_descriptor_key = msf.technical_descriptor_key
inner join ROUTING_TARGET rt on rt.ROUTING_TARGET_KEY = irf.routing_target_key
inner join interaction_fact i on i.interaction_id = msf.interaction_id
inner join resource_ ag on ag.resource_key = irf.resource_key
where td.technical_result_code = 'DIVERTED'
and td.result_reason_code = 'ANSWEREDBYAGENT'[/font][/color]
This is getting [i]really [/i]interesting... (sad? lol) I see this as "Part 2" of 3 Parts;
For each ConnID:
Part 1 = What did the customer ask for? Answer = collect digits/decode from the IVR
Part 2 = What did the routing do? Answer = collect Skill/Level data from URS
Part 3 = What did the agent confirm? Answer = collect data from DispositionCode/ReasonCode/Softphone/ACW
In this way, it would be possible to check the [i]business and operational effectiveness[/i] of all of the (voice) routing, end-to-end. And I've not seen that done before....
Is anyone else keeping up with me...? :)
-
Good :)
The date constraint was intentional, because I am used to work with very large GIM databases, so if you don't filter those, well... that's a looot of wait time and processing on the DBMS.
Note that the requested skills is only possible to extract on GIM when a target to the call is actually selected. This, because URS attach some RTarget* user data just before it issues a RequestRouteCall to T-Server/SIP Server when the option default\report_targets is set to true on URS.
So, to avoid taking wrong data (e.g. calls that Agent did not answer the call, got back to the strategy and then was abandoned), I filtered that query to only get the calls that have been effectively answerer (result_reason_code = 'ANSWEREDBYAGENT').
For Part 1, depending on the details, I would think the best way would be to get some reports from Reporting Server on Genesys Administrator [Extension].
For Part 3, maybe a StatServer view with filters could help.
It is also possible to take both information on GIM, given the proper configuration and some tweaking.
IMPORTANT: don't get too close to GIM. It eats people alive!
-
lol - I have been swimming in CCA and GIM data for quite some time.... I know what you mean.... ;)