UCCE Script Data SQL
How to find the Queue Priority's set in your scripts
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.
SetQueuePriority
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
| ScriptID | ScriptName | Version | Pos | MyStr | StrLen |
|---|---|---|---|---|---|
| 9050 | MyScript1 | 3 | 3082 | ScriptSetQueuePriority {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.
ScriptQueueTarget
Example Output for ScriptQueueTarget
ScriptID^ScriptName^Version^Pos^MyStr^StrLen^
| 9050 | 3 | 1749 | ScriptQueueTarget {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.
SQL Exporting names of the Scripts ScriptQueueTarget and their Queue Node data for all active scripts
(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
Labels 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 |