Updating the CHG_STATUS_HISTORY table

If you are using BMC Analytics for Business Service Management to report on BMC Change Management, you must use the Universe Design Tool to update the CHG_STATUS_HISTORY derived table with the correct schema ID.

To update the CHG_STATUS_HISTORY table

1. Run the following query on the BMC Remedy AR System database:

   select schemaID from arschema
   where name='CHG:Infrastructure Change'

   The query returns the schema ID for the CHG:Infrastructure Change form (for example, 1111).

2. Using the BusinessObjects Universe Design Tool, import the ITSM universe.
3. Select Tools > List of Derived Tables.
4. In the List of Derived Tables dialog box, select the CHG_STATUS_HISTORY table from the list of tables and click Edit.

5. In the Derived Tables window, modify the SQL expression by replacing all 13 instances of originalschemaID with the schema ID returned in step 1 .
   Use the following example, where the original schema ID is 1199 and the schema ID from step 1 is 1111.

   SELECT entryid,'Draft' AS Status_Value,T0 AS UpdatedTime,U0 AS
   UpdatedBy,DATEDIFF(ss,dbo.fn_adjusted_date(T0),dbo.fn_adjusted_date(T1))AS
   TimeSpend_In_Seconds
   FROM    h1199 where T0 is not null

   The SQL expression is now as follows:

   SELECT entryid,'Draft' AS Status_Value,T0 AS UpdatedTime,U0 AS
   UpdatedBy,DATEDIFF(ss,dbo.fn_adjusted_date(T0),dbo.fn_adjusted_date(T1))AS
   TimeSpend_In_Seconds
   FROM    h1111 where T0 is not null
6. Click Check Syntax.
7. In the Derived Tables window, click OK.
8. In the List of Derived Tables dialog box, click OK.
9. Click Save.
10. Select File > Export.
11. In the Export Universe window, select the ITSM universe, and click OK.
12. Close the Universe Design Tool.