With IT Analytics it's possible to save the views you create for the cubes. The saved view information is stored inside the Symantec_CMDB database. Along with the information on how the cube was formatted the system also saves the connection information for the Symantec_CMDB SQL server at the time the view was created. When the Symantec_CMDB database is migrated to a different SQL server these saved views become invalid due to their references back to the old SQL Server. All this information is saved in the State field for the cube in an URL encoded format. To recover these views you need to perform the following steps.
1. Inside the SMP Console go to Reports\All Reports\IT Analytics\Cubes and right click on any cube that has saved views and choose properties. 2. Copy the guid of the cube. 3. Open SQL Management Studio & open a new query window against the Symantec_CMDB database. 4. Run the following query replacing the text XXXX with the guid of the cube.
SELECT Name, Guid, Description, CAST(State as XML) AS 'State' FROM Item WHERE Guid = 'XXXX'
5. Now click on the hyperlink displayed for the State field. This will cause a new query window to open displaying the contents of the State field in a readable XML format. When the new query window opens you will see a line called <savedViews> which stretches off the right hand side of the screen. This line contains all the information for the saved views for this cube. It will look similiar to the line below.
7. Using the decoded information do a search for the words 'Data Source='.This entry will be found in the ConnectionString property for each saved view. When you find the entry look after the equal sign to see the SQL Server specified to be used for this saved view.
8. Copy the name / ip address specified after the equal sign. 9. Use the following query to update all saved views for all the cubes in the Symantec_CMDB database to now use the updated SQL server information. Remember to properly replace the oldSQLServer & newSQLServer text with the proper information.
DECLARE @oldServer AS NVARCHAR(50) DECLARE @newServer AS NVARCHAR(50)
SET @newServer = 'newSQLServer'
SET [State] = CAST(REPLACE( CAST([State] AS NVARCHAR(MAX)), @oldServer, @newServer) AS NTEXT)
WHERE Guid IN (
SELECT Guid FROM vItem
WHERE ClassGuid = 'FD283A60-19DD-4775-92E7-A0429D948D10')
Imported Document ID: HOWTO110293
Subscribing will provide email updates when this Article is updated. Login is required.