SCSM Reports have enum values for lists!
I want to start this post by thanking our customers who were willing to vet this process and Antoni Hanus who helped take my raw process and turn it into a more friendly version. :)
SCSM has an ETL (extract, transform and load) process which synchronizes the data from the operational database (ServiceManager) to the final data resting place (DWDataMart). Along the way, there are several tasks that the jobs take to help ensure they don't fail. One of these tasks is when the ETL moves list items through the DW, specifically within the transform job. List values are made up of their displaystring values and the GUIDs which represent them. What can happen is the GUID (enumeration id) can get to the transform job before the display string value. When this occurs, rather than failing, the transform job adds the enumeration id as both the id and the display string value. This helps, right? Well, not really, because we now have enum.GUID values in our reports. :(
There are a few ways to address this issue. First, you could manually override the 'listname.value' column in the affected outrigger with whatever the value is supposed to be. This method works well if there are only a few affected values. Unfortunately, there are typically many affected tables with many incorrect values. Versus trying to do all of these manually, this process below will resync all of them.
BEFORE YOU START:
This process can irreparably damage your data warehouse so be sure to backup all databases and follow this process exactly. This process is technically not supported unless a Microsoft engineer is on the phone. So, if you have an issue, you will need to restore the DBs and start over with a support case. PLEASE use this process at your own risk. I am publishing things like this because I know many people struggle with these issues. However, anything mentioned in this blog has no warranties, and is subject to the defined terms and conditions for TechNet blog content.
With all of that said, you have been sufficiently warned :) Now, let's get to the good part!
RESET ENUM PROCEDURE
In summary, the high-level process is:
- Backup databases
- Disable the ETL job schedules so that the jobs do not try to run while we are performing this process
- Delete the old extracted data from the inbound tables
- Reset the job module watermarks for the ETL that are related to enumeration values
- Run the ETL process
- Verify all of the data is correct
- Enable all of the schedules so that the jobs can resume
STEP 1 Backup all of the Databases FIRST!
Although changes will be made to only the DWStagingAndConfig database, it is wise to ensure all databases (ServiceManager, DWRepository, DWStagingAndConfig, DWDataMart, CMDWDataMart, OMDWDataMart are backed up to allow for any restore scenario / sync possibility that may need to be performed.
STEP 2 In PowerShell, disable the data warehouse job schedules so that they do not run until you want them to.
On the DW Management Server, import the service manager warehouse cmdlets.
Navigate to Service Manager installation directory e.g.
Cd “C:\Program Files\Microsoft System Center 2012 R2\Service Manager”
Once navigated to the appropriate directory, run the following command:
import-module ".\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1"
Run the following to get the schedules and set them to the $jslist variable:
$jslist = Get-SCDWJobSchedule
Run the following command to disable all job schedules:
foreach($obj in $JSList) {Write-Host "Disabling Schedule for Job: " $obj.Name; Disable-SCDWJobSchedule -JobName $obj.Name;}
STEP 3 Verify all the jobs are in a status of ‘Not Started’
Run the following command and ensure all the job schedules are disabled:
get-scdwjobschedule | format-table
Also, be sure that none of them are running before starting. (This is huge! If you do this process while the jobs are running the DW jobs could get irreparably stuck)
get-scdwjob | format-table
NOTE: 3 or ‘Not Started’ is what you should see for all of the jobs prior to continuing. Once you have disabled the schedules, the jobs will not rerun after they complete their current execution. For documentation's sake, below are all of the job and module statuses that are used in the DW and their numerical value. This is especially beneficial when you are looking in the infra.process, infra.batch, and infra.workitem tables in the DWStagingandConfig DB.
| 1 | Success |
| 2 | Failed |
| 3 | Not Started |
| 4 | Running |
| 5 | Stopped |
| 6 | Completed |
| 7 | Waiting |
STEP 4 Query Number of Display Strings in Service Manager DB
Check and note the number of rows in the display string view in the SCSM DB, by using the following command.
Use ServiceManager
select count(DisplayName) from ServiceManager.dbo.DisplayStringView
This should output the total number of display strings in the Service Manager database (should be around 210k). This will be the number we will check for after each execution of the extract job in Step 7.
STEP 5: In the DWStagingAndConfig DB, Clear the inbound tables for enum values and validate they are empty:
To prepare for the data reset, we need to clear everything related to enumerations that was previously extracted so that we can do a full resync.
Run this against the DWStagingandConfig DB:
Use DWStagingAndConfig
delete from inbound.EnumType
delete from inbound.DisplayString
Verify they are now empty with these commands:
select * from inbound.EnumType
select * from inbound.DisplayString
STEP 6 : In the DWStagingAndConfig DB, Reset the Extract watermarks for the enums and display strings so that all of them can be resynced from SCSM:
The first step in the ETL process is Extract. So, we start by resetting the extract job module watermarks which are related to enumeration values:
Use DWStagingAndConfig
update etl.WarehouseModuleDependency
set WaterMark = '2009-01-31 00:38:11.753'
where WarehouseEntityId IN (select WarehouseEntityId from DWStagingandConfig.etl.WarehouseEntity
where (WarehouseEntityName like '%enumtype%' or WarehouseEntityName like '%displaystring%') and SourceId = 3 and WarehouseEntityTypeId = 6)
IMPORTANT: It is fine to use the date shown above for the query.
STEP 7: Run the Extract Job manually via PowerShell commands
Back in PowerShell on the Data Warehouse Management Server, Run the Main Management Group’s Extract Job (This will be Extract_ManagementGroupName not the Extract_DW_ManagementGroupName)
NOTE: In the command below change Extract_ManagementGroupName to the name of your extract job (run get-scdwjob if needed to determine the job name).
Start-SCDWJOB -JobName Extract_ManagementGroupName
Then run a get-scdwjob to ensure it shows as status = Running (or 4):
get-scdwjob | format-table
Re-run the get-scdwjob every 5 / 10 minutes or so until it changes to ‘Not Started’.
STEP 8: Verify Count of rows in Inbound.displayString table
Run this Query in the DWstagingandConfig database:
Use DwStagingAndConfig
select count(DisplayName) from DWStagingAndConfig.inbound.DisplayString
This typically is around 50,000 / 52.000 after 1 extract job (this is predictable, due to the number of records we process per batch.)
STEP 9: Repeat Steps 7 and 8 until the result in Step 8 matches that which you noted in STEP 4
Repeat Steps 7 and 8 running the extract, verifying the number in SQL until the number in SQL matches the number that was recorded in Step 4.
This will typically take a total of 5 runs of the extract job to get to that point.
STEP 10 Reset the Transform and Load watermarks for all outriggers
Now that the data is in the DWStagingandConfig DB, it is time to transform and load it into the DWRepository and DWDataMart.
Run the following SQL statements 1 by one to reset the Transform and load watermarks for all outriggers:
Use DWStagingAndConfig
update etl.WarehouseModuleDependency
set WMBatchId = 0
where ModuleId IN (select ModuleId from etl.WarehouseEntity Entity
inner join etl.WarehouseModule module on module.TargetWarehouseEntityId= Entity.WarehouseEntityId
where Entity.WarehouseEntityTypeId = 3)
Use DWStagingAndConfig
update etl.WarehouseModuleDependency
set CompletedBatchId = 0
where ModuleId IN (select ModuleId from etl.WarehouseEntity Entity
inner join etl.WarehouseModule module on module.TargetWarehouseEntityId= Entity.WarehouseEntityId
where Entity.WarehouseEntityTypeId = 3)
Use DWStagingAndConfig
update etl.WarehouseModuleDependency
set WaterMark = '2009-01-31 00:38:11.753'
where ModuleId IN (select ModuleId from etl.WarehouseEntity Entity
inner join etl.WarehouseModule module on module.TargetWarehouseEntityId= Entity.WarehouseEntityId
where Entity.WarehouseEntityTypeId = 3)
STEP 11 : Manually Execute Transform.Common Job
Back in PowerShell on the Data Warehouse Management Server, Run the Transform.Common Job:
Start-SCDWJOB -JobName Transform.common
Then run a get-scdwjob to ensure it shows as status = Running (or 4)
get-scdwjob | format-table
Re-run the get-scdwjob command every 5 minutes or so until it changes to ‘Not Started’.
STEP 12: Verify the enum values are no longer in the DWRepository
The schema in the DWRepository is the same as the DWDataMart. So - after the transform job has run, you should be able to check one of the affected outriggers and verify that the enum values are no longer present. I like to do this before running the load job because there isn't a point in doing that if the values aren't fixed in the DWRepository.
STEP 13: Manually Execute Load.Common Job
Back in PowerShell on the Data Warehouse Management Server, Run the Load.Common Job:
Start-SCDWJOB -JobName Load.common
Then run a get-scdwjob to ensure it shows as status = Running (or 4)
get-scdwjob | format-table
Re-run the get-scdwjob every 5 / 10 minutes or so until it changes to ‘Not Started’.
STEP 14: Verify outriggers in the DWDataMart
Check the outriggers in the DWDataMart and ensure their values no longer contain enumeration ids. For any enums that exist in the DWDataMart but no longer exist in the ServiceManager DB, these will remain until manually cleaned out of the DWDataMart Database. If they are still used on workitems, you will need to do a manual update on the value in the outrigger or write them out of your reports using a NOT LIKE or something similar in the WHERE portion of your queries.
STEP 15: Re-enable job schedule for MPSyncJob
It is important to turn the MPSyncJob and DWMaintenance jobs back on by themselves. If they try to run together, the DW jobs can get stuck.
Back on the Data Warehouse Management Server, Enable the MPSyncJob schedule and run it:
Enable-SCDWJobSchedule -JobName MPSyncJob
Start-SCDWJob -JobName MPSyncJob
Then run a get-scdwjob to ensure it shows as status = Running (or 4)
get-scdwjob | format-table
Re-run the get-scdwjob every 5 / 10 minutes or so until it changes to ‘Not Started’.
STEP 16: Re-enable job schedule for DWMaintenance
Back on the Data Warehouse Management Server, Enable the DWMaintenance schedule and run it:
Enable-SCDWJobSchedule -JobName DWMaintenance
Start-SCDWJob -JobName DWMaintenance
Then run a get-scdwjob to ensure it shows as status = Running (or 4)
get-scdwjob | format-table
Re-run the get-scdwjob every 5 / 10 minutes or so until it changes to ‘Not Started’.
STEP 17: Re-enable the remaining Job Schedules
Run the following to get the schedules and set them to the $jslist variable:
$jslist = Get-SCDWJobSchedule
foreach($obj in $JSList) {Write-Host "Enabling Schedule for Job: " $obj.Name; Enable-SCDWJobSchedule -JobName $obj.Name;}
STEP 17 Create Test Incident (or work item) to monitor and ensure syncing is back to normal.
Create a test incident so you can ensure data is syncing across normally as expected.
Data should be synced all the way through from the live ServiceManager DB to DWDataMart in no more than 2 hours. You can also run the ETL manually to push things along.
The following query can be run to easily observe the most recent incidents:
Use DWDataMart
select * from incidentdimvw order by createddate desc
YOU'RE FINISHED! :) I hope this helps some folks that are struggling with this.