Skip to main content
Nintex Community Menu Bar
Knowledge Base

Database Queries for Wizards


Forum|alt.badge.img+3

Topic

Wizard related queries.
 


Instructions 

Query DescriptionSQL Query
Extract wizards that are created/ imported/ duplicated in the last X daysDECLARE @num_Duration bigint;
SET @num_Duration = 30(Change the duration (days) here as necessary)

SELECT A.ChangeID, A.ScriptID, B.Title AS WizardName, A.ChangeDate, A.UserName,
A.Title, A.Description
FROM LeoScriptChangesHistory A,
LeoScripts B
WHERE A.ScriptID = B.ScriptID
AND A.Title IN ('Wizard created','Wizard insert (during import)','Wizard duplicated')
AND ChangeDate >= DATEADD(DD,-@num_Duration,GETDATE());
Extract wizard changes in the last X daysDECLARE @num_Duration bigint;
SET @num_Duration = 30(Change the duration (days) here as necessary)

SELECT A.ScriptID, A.Title AS WizardName, C.Name AS CatalogName, D.Name AS LibraryName, B.ChangeDate, B.UserName, B.StudioVersion, B.Title, B.Description
FROM LeoScripts A,
LeoScriptChangesHistory B,
LeoLibrariesCategoriesTree C,
LeoScriptsLibraries D
WHERE A.ScriptID = B.ScriptID
AND A.CategoryID = C.CategoryID
AND C.LibraryID = D.LibraryID
AND B.Title LIKE 'Wizard properties changed%'
AND B.ChangeDate >= DATEADD(DD,-@num_Duration,GETDATE());
Extract wizard status changes ONLY in the last X days, including the old and new status informationDECLARE @num_Duration bigint;
SET @num_Duration = 30(Change the duration (days) here as necessary)

SELECT A.ScriptID, A.Title AS WizardName, C.Name AS CatalogName, D.Name AS LibraryName, B.ChangeDate, B.UserName, B.StudioVersion,
RTRIM(SUBSTRING(B.Description, CHARINDEX('Old value:', B.Description) + 11, CHARINDEX('New value:', B.Description) - (CHARINDEX('Old value:', B.Description) + 11)))
AS OldStatus,
CASE
WHEN CHARINDEX('Property: Faulty description', B.Description) = 0 THEN RTRIM(SUBSTRING(B.Description, CHARINDEX('New value:', B.Description) + 11, LEN(B.Description) - (CHARINDEX('New value:', B.Description) + 10)))
ELSE RTRIM(SUBSTRING(B.Description, CHARINDEX('New value:', B.Description) + 11, CHARINDEX('Property: Faulty description', B.Description) - (CHARINDEX('New value:', B.Description) + 11)))
END AS NewStatus
FROM LeoScripts A,
LeoScriptChangesHistory B,
LeoLibrariesCategoriesTree C,
LeoScriptsLibraries D
WHERE A.ScriptID = B.ScriptID
AND A.CategoryID = C.CategoryID
AND C.LibraryID = D.LibraryID
AND B.Title LIKE '%(Include status change)%'
AND B.Description LIKE 'Property: Status%'
AND B.ChangeDate >= DATEADD(DD,-@num_Duration,GETDATE());
Verify wizards with global functions that do not belong to the same library
Note that this query should return blank. If results are returned, these wizards will cause problems after import into another environment (i.e. global function(s) from different library will not be imported)
(Applicable only from 20.9)
SELECT *
FROM
(
SELECT A.CalledBy AS ScriptID, B.Title AS WizardName, C.LibraryID AS WizardLibrary,
F.Name AS LibraryName1, C.FullPathNames AS ScriptPath, A.GlobalFunctionId, D.Title AS GlobalFunctionName, E.LibraryID AS GlobalFunctionLibrary, G.Name AS LibraryName2, E.FullPathNames AS FunctionPath
FROM LeoGlobalFunctionLookup A,
LeoScripts B,
LeoLibrariesCategoriesTree C,
LeoScriptsLibraries F,
LeoScripts D,
LeoLibrariesCategoriesTree E,
LeoScriptsLibraries G
WHERE A.CalledBy = B.ScriptID
AND B.CategoryID = C.CategoryID
AND C.LibraryID = F.LibraryID
AND A.GlobalFunctionId = D.ScriptID
AND D.CategoryID = E.CategoryID
AND E.LibraryID = G.LibraryID
)A
WHERE WizardLibrary <> GlobalFunctionLibrary
Verify wizards with corrupted full path names
Note that this query should return blank. If results are returned, it signifies that the FullPathIDs and FullPathNames are not in sync and may cause problems loading the wizard data in Studio and/or Console
SELECT * FROM
(SELECT
CategoryID,
FullPathIDs,
FullPathNames,
(SELECT COUNT(value) FROM string_split(FullPathIDs, '')) - 1 AS IDs,
(SELECT COUNT(value) FROM string_split(FullPathNames, '')) AS Names
FROM LeoLibrariesCategoriesTree) tree
WHERE tree.IDs <> tree.Names
Identify the number of advanced commands used by CompanySELECT CompanyName, AdvancedCommand, SUM(AdvancedCommandCount) AS
AdvancedCommandCount
FROM
(SELECT E.CompanyName, B.Title AS WizardName, SUBSTRING(A.AdvancedCommandName,PATINDEX('%Programming%',A.AdvancedCom
mandName)+12,PATINDEX('%Expression',A.AdvancedCommandName)-(PATINDEX('%Programming%',A.AdvancedCommandName)+12)) AS
AdvancedCommand,
A.AdvancedCommandCount
FROM LeoScriptsAdvancedCommands A,
LeoScripts B,
LeoLibrariesCategoriesTree C,
LeoConnCompaniesLibraries D,
LeoCompanies E
WHERE A.ScriptID = B.ScriptID
AND B.CategoryID = C.CategoryID
AND C.LibraryID = D.LibraryID
AND D.CompanyID = E.CompanyID
)A
GROUP BY CompanyName, AdvancedCommand
ORDER BY CompanyName, AdvancedCommandCount DESC
To know which all wizards we are using particular AC e.g. “Addtask”SELECT distinct
B.[ScriptID]
,A.[Title]
,B.[AdvancedCommandName]
,B.[AdvancedCommandCategory]
,B.[AdvancedCommandCount]
,B.[ID]
FROM LeoScripts As A left join [LeoScriptsAdvancedCommands] As B
On A.ScriptID = B.ScriptID
Where B.AdvancedCommandName like '%AddTask%'
To get the count of all AC used from all wizardsSELECT [ScriptID]
,[AdvancedCommandName]
,[AdvancedCommandCategory]
,[AdvancedCommandCount]
,[ID]
FROM [LeoScriptsAdvancedCommands]

 


Translate
Did this topic help you find an answer to your question?

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings