Audit trail shows blank page and form export fails

Document ID : KB000006527
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

Audit trail tab shows blank when accessed the page and exporting form with form name failed to export

Environment:
CA Service catalog 14.1Cum 1 + Cum 2 + Incremental 1 + Incremental 2
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 id=@id 

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 id=@id 

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 id=@id 

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 object_id=@id 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 object_id=@id 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 id=@id 

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