UCCE Script Data SQL

Many thanks to the Gary Fearn for the SQL and details on how to do this
His website containing this info can be found here:

Reference: https://ciscocontactcentersecrets.wordpress.com/

I copied some of the SQL here - as the format from the above webpage corrupted it when I copied and pasted.

To Search for Priority Node change the search parameter in the below SQL Query to:

SET @SEARCH = 'ScriptSetQueuePriority' -- The string you want to search for.

Example Output for SetQueuePriority

ScriptIDScriptNameVersionPosMyStrStrLen
9050MyScript133082ScriptSetQueuePriority {711 1 117 5 546 453}44

The 4th integer in SetQueuePriority Node string is the Queue Priority Value.
So in the “MyString” field above we have a value of '5' and so this is what the priority node is set to.

Example Output for ScriptQueueTarget
ScriptID^ScriptName^Version^Pos^MyStr^StrLen^

905031749ScriptQueueTarget {533 6 71 711 711 2 -1 5 0 {1 3} {1 5} {1 -1} 2 0 426 {1 “”}}7

The 8th integer in ScriptQueueTarget Nodes is the Priority Value So in the “MyStr” Field above this case '5' and so this is what the priority node is set to in this SG Queue Node.

(change the @Search value to SetQueuePriority when trying to find data for set Priority nodes)

DECLARE @SEARCH AS VARCHAR(32)
SET @SEARCH = 'ScriptQueueTarget' -- The string you want to search for.
DECLARE @ChunkSize INT
SET @ChunkSize = 8000
DECLARE @pos INT
DECLARE @tmpstrlen INT
DECLARE @strlen INT
DECLARE @chunk VARCHAR(MAX)
DECLARE @scriptid INT
DECLARE @scriptname VARCHAR(32)
DECLARE @version INT
DECLARE @teststr VARCHAR(1)
-- Delete tables if in use
IF(OBJECT_ID('tempdb..#Nodes') IS NOT NULL)
BEGIN
DROP TABLE #Nodes
END
 
IF(OBJECT_ID('tempdb..#Scr') IS NOT NULL)
BEGIN
DROP TABLE #Scr
END
 
CREATE TABLE #Scr
(
ScriptID INT,
Flag INT
)
 
CREATE TABLE #Nodes
(
ScriptID INT,
ScriptName VARCHAR(32),
Version INT,
Pos INT,
MyStr VARCHAR(MAX),
StrLen INT
)
INSERT INTO #Scr
 
SELECT s.ScriptID, 0
FROM Script s
JOIN Master_Script ms ON (ms.MasterScriptID = s.MasterScriptID)
WHERE (s.Version = ms.CurrentVersion OR
(ms.CurrentVersion=-1 AND ms.NextAvailableVersion=s.Version+1)) -- restricts to Current version only.
 
SELECT @scriptid = (SELECT top 1 ScriptID FROM #Scr WHERE Flag = 0)
While EXISTS (SELECT top 1 ScriptID FROM #Scr WHERE Flag = 0)
 
BEGIN
SELECT @chunk = CONVERT(VARCHAR(MAX),SUBSTRING(sd.ScriptData,0,@ChunkSize)),
@pos = CHARINDEX(@SEARCH, CONVERT(VARCHAR(MAX),SUBSTRING(sd.ScriptData,0,@ChunkSize))) ,
@strlen = LEN(SUBSTRING(CONVERT(VARCHAR(MAX),SUBSTRING(sd.ScriptData,0,@ChunkSize)), @pos, CHARINDEX('}'+CHAR(10), CONVERT(VARCHAR(MAX),SUBSTRING(sd.ScriptData,0,@ChunkSize)),@pos) - @pos +1)),
@scriptname = ms.EnterpriseName, @scriptid = s.ScriptID,
@version = s.Version
FROM Script_Data sd
JOIN Script s ON (s.ScriptID = sd.ScriptID)
JOIN Master_Script ms ON (ms.MasterScriptID = s.MasterScriptID)
WHERE s.ScriptID = @scriptid
While @pos>0 AND @pos < @ChunkSize
BEGIN
 
INSERT INTO #Nodes
SELECT @scriptid, @scriptname,@version,@pos, Mystr = SUBSTRING(@chunk, @pos, CHARINDEX('}'+CHAR(10), @chunk,@pos) - @pos +1), @strlen
SELECT @teststr = SUBSTRING(@chunk, @pos+@strlen+1,1)
While @teststr = '*'
BEGIN
SELECT @tmpstrlen = @pos+@strlen+1
INSERT INTO #Nodes
SELECT @scriptid, @scriptname,@version, @tmpstrlen,
REPLACE(SUBSTRING(@chunk, @pos+@strlen+1,CHARINDEX('}'+CHAR(10),@chunk, @pos+@strlen+1)-(@pos+@strlen) ),'* {', @SEARCH+' {') AS MyStr,
Len(REPLACE(SUBSTRING(@chunk, @pos+@strlen+1,CHARINDEX('}'+CHAR(10),@chunk, @pos+@strlen+1)-(@pos+@strlen) ),'* {', @SEARCH+' {'))
SET @strlen = Len(SUBSTRING(@chunk, @pos+@strlen+1,CHARINDEX('}'+CHAR(10),@chunk, @pos+@strlen+1)-(@pos+@strlen) ))
SET @pos = @tmpstrlen
SELECT @teststr = SUBSTRING(@chunk, @pos+@strlen+1,1)
END
SELECT @pos = CHARINDEX(@SEARCH, @chunk,@pos+1),
@strlen = LEN(SUBSTRING(@chunk, @pos, CHARINDEX('}'+CHAR(10), @chunk,@pos) - @pos +1))
END
UPDATE #Scr SET Flag = 1 WHERE #Scr.ScriptID = @scriptid
SELECT @scriptid = (SELECT top 1 ScriptID FROM #Scr WHERE Flag = 0)
END
 
SELECT * FROM #Nodes

To get details on all Label and DivertLabels

To Search change the search parameter in the SQL Query to:

SET @SEARCH = 'ScriptDirectLabel'

Example output

ScriptID ScriptName Version Pos MyStr StrLen
9078 MyScriptName1 295 4729 ScriptDivertLabel {719 3 126 {0} 2 “ 1 0 -1 1370 {3 ”#16#“ ”#16#“ ”#24#“}} 75
10756 MyScriptName2 14 518 ScriptDivertLabel {391 2 17 {0} 2 ” 1 0 -1 1381 {2 “\”0015551234\“ “\”0016661234\”}} 87
8254 MyScriptName3 18 460 ScriptDivertLabel {55 1 10 {0} 2 “ 1 0 -1 1437 {1 “concatenate(556,Call.PeripheralVariable1)”}} 96

Node Names

I got bored and didn't document any more….

NodeNames
ScriptCallType
ScriptCancelQueuing
ScriptCollectData
ScriptDayOfWeek
ScriptDirectLabel
ScriptQualifyCallerDigits
ScriptQualifyCallerLineID
ScriptQualifyDialedNumber
ScriptQueueTarget
ScriptReleaseCall
ScriptSendAgent
ScriptSetQueuePriority
ScriptStart
ScriptTarget
  • vendors/cisco/uc/icm/sql/scriptdata.txt
  • Last modified: 2022/11/11 17:22
  • by gerardorourke