Audit trail shows blank page and form export fails
Article ID: 6527
Updated On:
Products
Issue/Introduction
Audit trail tab shows blank when accessed the page and exporting form with form name failed to export
Environment
Resolution
This is a know bug and was fixed with some steps updating the DB directly.
Caution: Before running the below queries please ensure that the test fixes are implement and followed with post installation steps. If needed, make a back up of the SQL environment for there no reverting back after implementing the post installation steps.
Note: The patch level for the below sql script to work is to have T6D9170 and T6D9190 implemented as pre-reqs.
Please follow below steps in the order mentioned
1. Open SQL Studio (example referring to SQL DB)
2. Click new query
3. Ensure that you selected MDB as current DB for executing the query
4. Execute the below query as per the mentioned sequence
======================================================
Query 1:
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fnSplitString]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[fnSplitString]
GO
Query 2:
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
GO
===================================================================
5. Execute the second set of SQL queries i.e.
Query 1:
Alter table casm_ldap_config ALTER Column ldap_user nvarchar(100)
Alter table casm_ldap_config ALTER Column ldap_search_base nvarchar(100)
Query 2:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail]
END
GO
Query 3:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail_1')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail_1]
END
GO
Query 4:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail_2')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail_2]
END
GO
Query 5:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail_3')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail_3]
END
GO
Query 6:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail_4')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail_4]
END
GO
Query 7:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_details_multi_param')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_details_multi_param]
END
GO
Query 8:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change]
END
GO
Query 9:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_multi_param')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_multi_param]
END
GO
Query 10:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_multi_param_1')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_multi_param_1]
END
GO
Query 11:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_multi_param_2')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_multi_param_2]
END
GO
Query 12:
IF EXISTS ( SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'usm_sp_system_change_detail_ext')
AND type IN ( N'P', N'PC' ) )
BEGIN
DROP PROCEDURE [dbo].[usm_sp_system_change_detail_ext]
END
GO
Query 13:
SET ANSI_NULLS ON
GO
Query 14:
SET QUOTED_IDENTIFIER ON
GO
Query 15:
CREATE procedure [dbo].[usm_sp_system_change_detail]
@id varchar(128) as
begin
select * from usm_system_change_detail(NOLOCK) where [email protected]
end
GO
Query 16:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail TO usmgroup;
GO
Query 17:
CREATE procedure [dbo].[usm_sp_system_change_detail_1]
@id varchar(128)
as
begin
select name, new_value, old_value from usm_system_change_detail(NOLOCK) where id = @id and ( name = 'item_id' or name='status' or name='login_device') order by name
end
GO
Query 18:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail_1 TO usmgroup;
GO
Query 19:
CREATE procedure [dbo].[usm_sp_system_change_detail_2]
@id varchar(128)
as
begin
select name, new_value, old_value from usm_system_change_detail(NOLOCK) where id = @id and (name='status' or name='name')
end
GO
Query 20:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail_2 TO usmgroup;
GO
Query 21:
CREATE procedure [dbo].[usm_sp_system_change_detail_3]
@id varchar(128)
as
begin
select name, new_value, old_value from usm_system_change_detail(NOLOCK) where id = @id and (name='Status' or name='RequestName' or name='emailFromUserID')
end
GO
Query 22:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail_3 TO usmgroup;
GO
Query 23:
CREATE procedure [dbo].[usm_sp_system_change]
@id varchar(128)
as
begin
select * from usm_system_change(NOLOCK) where [email protected]
end
GO
Query 24:
GRANT EXECUTE ON dbo.usm_sp_system_change TO usmgroup;
GO
Query 25:
CREATE procedure [dbo].[usm_sp_system_change_detail_ext]
@id varchar(128)
as
begin
select * from usm_system_change_detail_ext(NOLOCK) where [email protected]
end
GO
Query 26:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail_ext TO usmgroup;
GO
Query 27:
CREATE procedure [dbo].[usm_sp_system_change_multi_param]
@id varchar(128),
@tst datetime
as
begin
select id,name from usm_system_change(NOLOCK) where name not like 'BILL_SUBSCRIPTION%' and [email protected] and
timestamp < @tst order by timestamp desc
end
GO
Query 28:
GRANT EXECUTE ON dbo.usm_sp_system_change_multi_param TO usmgroup;
GO
Query 29:
CREATE procedure [dbo].[usm_sp_system_change_multi_param_1]
@id varchar(128),
@tst datetime,
@namelist varchar(8000)
as
begin
select id,name from usm_system_change(NOLOCK) where name not like 'BILL_SUBSCRIPTION%' and [email protected] and
timestamp < @tst and name in (SELECT splitdata FROM dbo.fnSplitString(@namelist,'')) order by timestamp desc
end
GO
Query 30:
GRANT EXECUTE ON dbo.usm_sp_system_change_multi_param_1 TO usmgroup;
GO
Query 31:
CREATE procedure [dbo].[usm_sp_system_change_multi_param_2]
@objid varchar(256),
@idlist varchar(8000)
as
begin
select id as sc_id, name as sc_name, type as sc_type, user_id as user_id, timestamp as sc_timestamp,
domain as domain, object_id from usm_system_change where (object_id= @objid and ( name like 'CAT_REQ_%' or
name like 'CAT_REQ_EMAIL')) or object_id in (SELECT splitdata FROM dbo.fnSplitString(@idlist,'')) and ( name like
'BILL_SUBSCRIPTION_%' or name like 'FORM_ITEM_CHANGE') order by timestamp desc
end
GO
Query 32:
GRANT EXECUTE ON dbo.usm_sp_system_change_multi_param_2 TO usmgroup;
GO
Query 33:
CREATE procedure [dbo].[usm_sp_system_change_details_multi_param]
@idlist varchar(8000)
as
begin
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
where id in (SELECT splitdata FROM dbo.fnSplitString(@idlist,''))
end
GO
Query 34:
GRANT EXECUTE ON dbo.usm_sp_system_change_details_multi_param TO usmgroup;
GO
Query 35:
CREATE procedure [dbo].[usm_sp_system_change_detail_4]
@id varchar(128)
as
begin
select id, name, data_type, new_value as old_value, new_value, multi_value from usm_system_change_detail(NOLOCK)
where [email protected]
end
GO
Query 36:
GRANT EXECUTE ON dbo.usm_sp_system_change_detail_4 TO usmgroup;
GO
Query 37:
update usm_rule_event_param
set event_param_data_type = 1
where event_param_name = 'track_as_asset'
Once done, Recycle the services, clear the translets and browser cache (on browsers)
Login to Catalog check the behavior by selecting an open request
Click on Tracking tab and refresh the page to see all the related entries.
Additional Information
Note: This above mentioned queries will be formatted and added accordingly in the up coming CP04 patch. Until then we ned to manually follow the execution sequence as mentioned in this document
Feedback
