Some more SQL script stuff. Both should work for MSSQL and Oracle.
-- 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"
The next one is not from me (found it either somewhere here ;D ) or on Genesys Knowledge Base
-- 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
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):
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'
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!
…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):
Yes! 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;
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’
This is getting really 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 business and operational effectiveness of all of the (voice) routing, end-to-end. And I’ve not seen that done before…
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!
