Topic
Wizard related queries.
Instructions
| Query Description | SQL Query |
| Extract wizards that are created/ imported/ duplicated in the last X days | DECLARE @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 days | DECLARE @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 information | DECLARE @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 Company | SELECT 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 wizards | SELECT [ScriptID] ,[AdvancedCommandName] ,[AdvancedCommandCategory] ,[AdvancedCommandCount] ,[ID] FROM [LeoScriptsAdvancedCommands] |