SCSM Datawarehouse Jobs Failing “Automated”


We are going to go back and revisit the datawarehouse article one more time.  You might be saying to yourself, man that was very helpful but there are so many steps, and I have do this once a week, at least, and I have to blah blah blah…that’s why you get paid. Stop complaining!  I’m kidding, I said all that as well.  So I automated the process, and I’m going to share it with whoever stumbles across this page, ’cause that’s just the type of guy I am ;).  

I designed this process to publish on the portal.   This would allow any user, in my case the change team, to kick off the ETL jobs any time they noticed an issue or they just needed fresh data.  There are 2 options for them to choose, quick or full.  Quick will run all of the jobs except the cube processing jobs.  In our environment that should take roughly 25 minutes to complete.  Full runs everything, cubes and all.  This can take almost an hour to complete, again I’m basing this off our environment.  If you work for some fortune 500 company it could take substantially longer YMMV.  So lets get this thing started.

Here is the folder structure that I’m using

structure.jpg

In the DW Mainenance folder we have the main control runbook.  If you follow the orginal article the structure and what is going on should be pretty self explanatory.

main RB.jpg

You might be noticing the Variables IP.  This was a trick I learned from a coworker.  Instead of adding global variables to all of the IPs we add all of the variables to this IP.  When we carry this over to production we only need to change the varialbe in this one IP and not every IP in the runbook.  For example, you see I have 4 email IPs in this runbook.  Normally we would use several global variables in each one, like SMTP, user name, password and so on.  Thats a lot of changes we would need to make when moved to production from a different environment.  Now we only need to change it it one place.  Its a simple PowerShell script and makes your life much easier in the long run.  Here is what it looks like.Variable IP.jpgV Properties.jpg

To shorten the length of this article and since I will be uploading this to technet I wont go into detail on each IP but feel free to ask any questions in the comment section.  This runbook just duplicates each manual step from the orginal article, checks for locks, clears them if there are any, resets all jobs and either runs the full ETL or the basic ETL without cube processing.

Check for locks.  This will run the select statement to determine results.  if nothing is found it moves to the next step.  If there is a lock then it splits the fileds needed for the stored procedure to remove the locks.

locks.jpgselect.jpg

If locks are found it sends the 2 fields to the Clear Locks runbook.

clear locks.jpg

Once this is complete it runs the SQL script from the orginal article to reset the jobs status.  Ill post the SQL script one more time

DECLARE @Batches TABLE (
Id INT IDENTITY(1, 1),
BatchId BIGINT,
ProcessName NVARCHAR(MAX)
)

INSERT INTO @Batches (
BatchId,
ProcessName
) (
SELECT B.BatchId,
P.ProcessName FROM infra.Batch B JOIN infra.Process P ON P.ProcessId = B.ProcessId WHERE B.StatusId NOT IN (3,6)
AND P.ProcessCategoryId IN (1,4,6,7,8,9)
)

DECLARE @RowCount INT

SET @RowCount = (
SELECT COUNT(Id)
FROM @Batches
)

DECLARE @Counter INT

SET @Counter = 1

WHILE (@Counter <= @RowCount)
BEGIN
DECLARE @BatchId BIGINT
DECLARE @ProcessName NVARCHAR(MAX)

SET @BatchId = (
SELECT BatchId
FROM @Batches
WHERE Id = @Counter
)
SET @ProcessName = (
SELECT ProcessName
FROM @Batches
WHERE Id = @Counter
)

UPDATE infra.WorkItem
SET StatusId = 6
WHERE BatchId = @BatchId
UPDATE infra.Batch
SET StatusId = 5
WHERE BatchId = @BatchId

EXEC infra.CreateBatch @ProcessName
SET @Counter = @Counter + 1
END

Ok, all jobs are back to normal now.  It returns to the primary runbook and determines whether the user chose Full or Quick and branches based on that selection.

I will go into a little more detail on this part.  I re-utilized the Powershell script template from here.  This is a great tool because it allows you to run powershell in the latest version installed on the runbook server, run it in x64 and return any errors for proper handling of issues.  I encourage you to read up on how that script template works.

Below is the runbook for the full ETL load.  You will notice I split the jobs and the cubes into 2 different scripts.  For some reason they would not run properly in the same script.  I believe this was due to the script making 2 different remote calls to 2 different servers.  The DW management server and the DW database server.  Oh well, this works as well.

full etl.jpg

Here is the script for the ETL job

# Set script parameters from runbook data bus and Orchestrator global variables
# Define any inputs here and then add to the $argsArray and script block parameters below

$DWServer = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{616F0C68-F00A-4A06-8A0D-31FB43C6840D}\`d.T.~Ed/”
$ASServer = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{715B2EEA-E2D4-4AC0-A8FF-9F991EE7E281}\`d.T.~Ed/”
$ASDBName = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{9289C310-C863-4015-88C5-9D36F3950985}\`d.T.~Ed/”
$wait = \`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{6D5BF2ED-9A1A-4CDD-B88C-E8CDE6847E17}\`d.T.~Ed/
#———————————————————————–

## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = “”
$ErrorMessage = “”
$Trace = (Get-Date).ToString() + “`t” + “Runbook activity script started” + ” `r`n”

# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $DWServer
$argsArray += $ASServer
$argsArray += $ASDBName
$argsArray += $wait

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName \`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{616F0C68-F00A-4A06-8A0D-31FB43C6840D}\`d.T.~Ed/

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$DWServer,

[ValidateNotNullOrEmpty()]
[string]$ASServer,

[ValidateNotNullOrEmpty()]
[string]$ASDBName,

[ValidateNotNullOrEmpty()]
[int]$wait
)

# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + “`t” + $Message + ” `r`n”)
}

# Set external session trace and status variables to defaults
$ResultStatus = “”
$ErrorMessage = “”
$script:CurrentAction = “”
$script:TraceLog = “”

try
{
# Add startup details to trace log
AppendLog “Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process”
AppendLog “Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]”
AppendLog “Parameter values received: DWServer=[$DWServer]; ASServer=[$ASServer]; ASDBName=[$ASDBName]”

# The actual work the script does goes here
AppendLog “Starting quick ETL job”
# Do-Stuff -Value $DWServer
###############Run in PowerShell with elevated Permissions from your DW Management Server###############
###############Also make sure to specific the proper values to the Parameters highlighted###################

$props = Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup”
$instdir = $props.InstallDirectory
$dwpsd = $instdir + “Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1”
Import-Module -Name $dwpsd
$JSList = Get-SCDWJobSchedule -ComputerName $DWServer;
$JList = Get-SCDWJob -ComputerName $DWServer;
function Run-DWJob([String]$DWServer, [String]$JobName) {
AppendLog “Enabling and running Job”;
Enable-SCDWJob -ComputerName $DWServer -JobName $JobName;
Start-SCDWJob -ComputerName $DWServer -JobName $JobName;
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
while($currentJobStatus.Status -eq “Running”) {
Start-Sleep -s $Wait
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
$moduleList = Get-SCDWJobModule -JobName $JobName -ComputerName $DWServer
foreach($obj in $moduleList) {
if([String]::IsNullOrEmpty($obj.ModuleErrorSummary) -ne $true) {
AppendLog “There is no need to wait anymore because there is an error”;
exit;
}
}
}
if($currentJobStatus.Status -ne “Not Started”) {
AppendLog “There is an error and we will exit this – please inspect the status”;
exit;
}
}
foreach($obj in $JSList) {
AppendLog “Disabling Schedule for Job”;
Disable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}
foreach($obj in $JList) {
AppendLog “Stoping and disabling Job”;
Stop-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
Start-Sleep -s $Wait
Disable-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
}
$maintenanceList = New-Object System.Collections.ArrayList;
$MPSyncList = New-Object System.Collections.ArrayList;
$extractList = New-Object System.Collections.ArrayList;
$transformList = New-Object System.Collections.ArrayList;
$loadList = New-Object System.Collections.ArrayList;
$cubeList = New-Object System.Collections.ArrayList;
foreach($obj in $JList) {
if($obj.Name -match “Extract”) {
$extractList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Transform”) {
$transformList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Load”) {
$loadList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Maintenance”) {
$maintenanceList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “MPSync”) {
$MPSyncList.Add($obj.Name) | Out-Null;
}# else {
# $cubeList.Add($obj.Name) | Out-Null;
# }
}
foreach($obj in $maintenanceList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $MPSyncList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $extractList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $transformList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $loadList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $cubeList) {
Run-DWJob $DWServer $obj;
}
# Validate results and set return status
AppendLog “Finished work, determining result”
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = “Success”
}
else
{
$ResultStatus = “Failed”
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = “Failed”
$ErrorMessage = $error[0].Exception.Message
AppendLog “Exception caught during action [$script:CurrentAction]: $ErrorMessage”
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog “Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]”
}
else
{
AppendLog “Exiting external session with result [$ResultStatus]”
}

}

# Return an array of the results. Additional variables like “myCustomVariable” can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog

return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
# Record end of activity script process
$Trace += (Get-Date).ToString() + “`t” + “Script finished” + ” `r`n”

# Close the external session
Remove-PSSession $Session

and here is the script to run the cube jobs as long as there wasnt a failure in the first script, but if there was you will be emailed what that error was due to this handy PS template.

# Set script parameters from runbook data bus and Orchestrator global variables
# Define any inputs here and then add to the $argsArray and script block parameters below

$DWServer = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{616F0C68-F00A-4A06-8A0D-31FB43C6840D}\`d.T.~Ed/”
$ASServer = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{715B2EEA-E2D4-4AC0-A8FF-9F991EE7E281}\`d.T.~Ed/”
$ASDBName = “\`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{9289C310-C863-4015-88C5-9D36F3950985}\`d.T.~Ed/”
$wait = \`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{6D5BF2ED-9A1A-4CDD-B88C-E8CDE6847E17}\`d.T.~Ed/

#———————————————————————–

## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = “”
$ErrorMessage = “”
$Trace = (Get-Date).ToString() + “`t” + “Runbook activity script started” + ” `r`n”

# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $DWServer
$argsArray += $ASServer
$argsArray += $ASDBName
$argsArray += $wait

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName \`d.T.~Ed/{20BA95E5-7846-4148-9988-5409330307BE}.{715B2EEA-E2D4-4AC0-A8FF-9F991EE7E281}\`d.T.~Ed/

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$DWServer,

[ValidateNotNullOrEmpty()]
[string]$ASServer,

[ValidateNotNullOrEmpty()]
[string]$ASDBName,

[ValidateNotNullOrEmpty()]
[int]$wait
)

# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + “`t” + $Message + ” `r`n”)
}

# Set external session trace and status variables to defaults
$ResultStatus = “”
$ErrorMessage = “”
$script:CurrentAction = “”
$script:TraceLog = “”

try
{
# Add startup details to trace log
AppendLog “Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process”
AppendLog “Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]”
AppendLog “Parameter values received: DWServer=[$DWServer]; ASServer=[$ASServer]; ASDBName=[$ASDBName]; Wait=[$wait]”

# The actual work the script does goes here
AppendLog “Starting quick ETL job”
# Do-Stuff -Value $DWServer
###############Run in PowerShell with elevated Permissions from your DW Management Server###############
###############Also make sure to specific the proper values to the Parameters highlighted###################
AppendLog “Doing second action”

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null;
$Server = New-Object Microsoft.AnalysisServices.Server;
$Server.Connect($ASServer);
$Databases = $Server.Databases;
$DWASDB = $Databases[$ASDBName];
$Dimensions = New-Object Microsoft.AnalysisServices.Dimension;
$Dimensions = $DWASDB.Dimensions;
foreach($dim in $Dimensions) {
appendlog “Processing Cube Job” $Dim.Name;
$dim.Process(“ProcessFull”);
Start-Sleep -s $Wait;
}
foreach($obj in $JSList) {
appendlog “Enabling Schedule for Job: ” $obj.Name;
Enable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}

appendlog “FINISHED!”;
#####################################################################################
# Validate results and set return status
AppendLog “Finished work, determining result”
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = “Success”
}
else
{
$ResultStatus = “Failed”
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = “Failed”
$ErrorMessage = $error[0].Exception.Message
AppendLog “Exception caught during action [$script:CurrentAction]: $ErrorMessage”
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog “Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]”
}
else
{
AppendLog “Exiting external session with result [$ResultStatus]”
}

}

# Return an array of the results. Additional variables like “myCustomVariable” can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog

return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
# Record end of activity script process
$Trace += (Get-Date).ToString() + “`t” + “Script finished” + ” `r`n”

# Close the external session
Remove-PSSession $Session

If the user decided to run the quick ETL the runbook looks identical except for some minor changes.

quick.jpg

The Run Quick ETL looks as so

# Set script parameters from runbook data bus and Orchestrator global variables
# Define any inputs here and then add to the $argsArray and script block parameters below

$DWServer = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{0A31F6DA-0679-4F98-B369-AB78259F520E}\`d.T.~Ed/”
$ASServer = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{05491AAA-7F90-4497-810A-FD779500F58F}\`d.T.~Ed/”
$ASDBName = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{CEBBE29E-63B6-400C-8F79-F54D7306F31B}\`d.T.~Ed/”
$wait = \`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{23883E7F-948D-45D8-9F1C-01054F37C205}\`d.T.~Ed/

#———————————————————————–

## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = “”
$ErrorMessage = “”
$Trace = (Get-Date).ToString() + “`t” + “Runbook activity script started” + ” `r`n”

# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $DWServer
$argsArray += $ASServer
$argsArray += $ASDBName
$argsArray += $wait

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName \`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{0A31F6DA-0679-4F98-B369-AB78259F520E}\`d.T.~Ed/

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$DWServer,

[ValidateNotNullOrEmpty()]
[string]$ASServer,

[ValidateNotNullOrEmpty()]
[string]$ASDBName,

[ValidateNotNullOrEmpty()]
[int]$wait
)

# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + “`t” + $Message + ” `r`n”)
}

# Set external session trace and status variables to defaults
$ResultStatus = “”
$ErrorMessage = “”
$script:CurrentAction = “”
$script:TraceLog = “”

try
{
# Add startup details to trace log
AppendLog “Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process”
AppendLog “Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]”
AppendLog “Parameter values received: DWServer=[$DWServer]; ASServer=[$ASServer]; ASDBName=[$ASDBName]”

# The actual work the script does goes here
AppendLog “Starting quick ETL job”
# Do-Stuff -Value $DWServer
###############Run in PowerShell with elevated Permissions from your DW Management Server###############
###############Also make sure to specific the proper values to the Parameters highlighted###################

$props = Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup”
$instdir = $props.InstallDirectory
$dwpsd = $instdir + “Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1”
Import-Module -Name $dwpsd
$JSList = Get-SCDWJobSchedule -ComputerName $DWServer;
$JList = Get-SCDWJob -ComputerName $DWServer;
function Run-DWJob([String]$DWServer, [String]$JobName) {
AppendLog “Enabling and running Job”;
Enable-SCDWJob -ComputerName $DWServer -JobName $JobName;
Start-SCDWJob -ComputerName $DWServer -JobName $JobName;
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
while($currentJobStatus.Status -eq “Running”) {
Start-Sleep -s $Wait
$currentJobStatus = Get-SCDWJob -JobName $JobName -ComputerName $DWServer | Select Status
$moduleList = Get-SCDWJobModule -JobName $JobName -ComputerName $DWServer
foreach($obj in $moduleList) {
if([String]::IsNullOrEmpty($obj.ModuleErrorSummary) -ne $true) {
AppendLog “There is no need to wait anymore because there is an error”;
exit;
}
}
}
if($currentJobStatus.Status -ne “Not Started”) {
AppendLog “There is an error and we will exit this – please inspect the status”;
exit;
}
}
foreach($obj in $JSList) {
AppendLog “Disabling Schedule for Job”;
Disable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}
foreach($obj in $JList) {
AppendLog “Stoping and disabling Job”;
Stop-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
Start-Sleep -s $Wait
Disable-SCDWJob -ComputerName $DWServer -JobName $obj.Name;
}
$maintenanceList = New-Object System.Collections.ArrayList;
$MPSyncList = New-Object System.Collections.ArrayList;
$extractList = New-Object System.Collections.ArrayList;
$transformList = New-Object System.Collections.ArrayList;
$loadList = New-Object System.Collections.ArrayList;
$cubeList = New-Object System.Collections.ArrayList;
foreach($obj in $JList) {
if($obj.Name -match “Extract”) {
$extractList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Transform”) {
$transformList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Load”) {
$loadList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “Maintenance”) {
$maintenanceList.Add($obj.Name) | Out-Null;
} elseif($obj.Name -match “MPSync”) {
$MPSyncList.Add($obj.Name) | Out-Null;
}# else {
# $cubeList.Add($obj.Name) | Out-Null;
# }
}
foreach($obj in $maintenanceList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $MPSyncList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $extractList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $transformList) {
Run-DWJob $DWServer $obj;
}
foreach($obj in $loadList) {
Run-DWJob $DWServer $obj;
}
#foreach($obj in $cubeList) {
# Run-DWJob $DWServer $obj;
#}
#[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null;
#$Server = New-Object Microsoft.AnalysisServices.Server;
#$Server.Connect($ASServer);
#$Databases = $Server.Databases;
#$DWASDB = $Databases[$ASDBName];
#$Dimensions = New-Object Microsoft.AnalysisServices.Dimension;
#$Dimensions = $DWASDB.Dimensions;
#foreach($dim in $Dimensions) {
# AppendLog “Processing Cube Job” $Dim.Name;
# $dim.Process(“ProcessFull”);
# Start-Sleep -s $Wait;
#}
foreach($obj in $JSList) {
AppendLog “Enabling Schedule for Job”;
Enable-SCDWJobSchedule -ComputerName $DWServer -JobName $obj.Name;
}

AppendLog “FINISHED!”;
#####################################################################################

# Validate results and set return status
AppendLog “Finished work, determining result”
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = “Success”
}
else
{
$ResultStatus = “Failed”
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = “Failed”
$ErrorMessage = $error[0].Exception.Message
AppendLog “Exception caught during action [$script:CurrentAction]: $ErrorMessage”
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog “Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]”
}
else
{
AppendLog “Exiting external session with result [$ResultStatus]”
}

}

# Return an array of the results. Additional variables like “myCustomVariable” can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog

return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
# Record end of activity script process
$Trace += (Get-Date).ToString() + “`t” + “Script finished” + ” `r`n”

# Close the external session
Remove-PSSession $Session

You will notice that I commented out the sections that pertains to processing the cubes.  It wont run those jobs but it will still disable them and we dont want to leave those in a disabled state.  That wouldn’t be a good idea, so we move to the next script to re-enable those jobs to run at their normal schedules.

# Set script parameters from runbook data bus and Orchestrator global variables
# Define any inputs here and then add to the $argsArray and script block parameters below

$DWServer = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{0A31F6DA-0679-4F98-B369-AB78259F520E}\`d.T.~Ed/”
$ASServer = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{05491AAA-7F90-4497-810A-FD779500F58F}\`d.T.~Ed/”
$ASDBName = “\`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{CEBBE29E-63B6-400C-8F79-F54D7306F31B}\`d.T.~Ed/”
$wait = \`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{23883E7F-948D-45D8-9F1C-01054F37C205}\`d.T.~Ed/

#———————————————————————–

## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = “”
$ErrorMessage = “”
$Trace = (Get-Date).ToString() + “`t” + “Runbook activity script started” + ” `r`n”

# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $DWServer
$argsArray += $ASServer
$argsArray += $ASDBName
$argsArray += $wait

# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName \`d.T.~Ed/{4BA8FA5C-F0B3-4185-9585-A814CF748774}.{0A31F6DA-0679-4F98-B369-AB78259F520E}\`d.T.~Ed/

# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$DWServer,

[ValidateNotNullOrEmpty()]
[string]$ASServer,

[ValidateNotNullOrEmpty()]
[string]$ASDBName,

[ValidateNotNullOrEmpty()]
[int]$wait
)

# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + “`t” + $Message + ” `r`n”)
}

# Set external session trace and status variables to defaults
$ResultStatus = “”
$ErrorMessage = “”
$script:CurrentAction = “”
$script:TraceLog = “”

try
{
# Add startup details to trace log
AppendLog “Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process”
AppendLog “Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]”
AppendLog “Parameter values received: DWServer=[$DWServer]; ASServer=[$ASServer]; ASDBName=[$ASDBName]”

# The actual work the script does goes here
AppendLog “Starting quick ETL job”
# Do-Stuff -Value $DWServer

$props = Get-ItemProperty “HKLM:\SOFTWARE\Microsoft\System Center\2010\Common\Setup”
$instdir = $props.InstallDirectory
$dwpsd = $instdir + “Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1”
Import-Module -Name $dwpsd

Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterChangeAndActivityManagementCube”
Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterConfigItemCube”
Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterPowerManagementCube”
Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterServiceCatalogCube”
Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterSoftwareUpdateCube”
Enable-SCDWJob -ComputerName $DWServer -JobName “Process.SystemCenterWorkItemsCube”

# Validate results and set return status
AppendLog “Finished work, determining result”
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = “Success”
}
else
{
$ResultStatus = “Failed”
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = “Failed”
$ErrorMessage = $error[0].Exception.Message
AppendLog “Exception caught during action [$script:CurrentAction]: $ErrorMessage”
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog “Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]”
}
else
{
AppendLog “Exiting external session with result [$ResultStatus]”
}

}

# Return an array of the results. Additional variables like “myCustomVariable” can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog

return $resultArray

}#End Invoke-Command

# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
# Record end of activity script process
$Trace += (Get-Date).ToString() + “`t” + “Script finished” + ” `r`n”

# Close the external session
Remove-PSSession $Session

As you can see, this is a simple Enable-SCDWJob command.

These will carry over any success or failure message back to the primary runbook and send out the results in an email.  You could also have this added to the Service Request if you are adding this functionality to the portal.

And there you go, wrap this up in a service offering, hand that over to someone else to deal with, and carry on with your development.  Go ahead and grab the export from technet and customize to your environment and as always ask questions or throw a thanks my direction.

DW_Maintenance_Automated.ois_export

One caveat that I have noticed, if the quick ETL job takes longer then 20 minutes then just stop the runbooks, restart the DWMS and the DWDB and rerun.  This fixes 99% of my issues.  You can add an event notification in the run behaviour tab of the run .net script and set it to send an event after 20 minutes.  Most likey it is hung up on the Maintenance job.  Set SCOM up to monitor for this event notification and you are all good.

Advertisements

3 thoughts on “SCSM Datawarehouse Jobs Failing “Automated”

  1. Eric Krasner

    The “Split” Activity is not available in 2012 R2 (the IP cannot be imported.) Do you have the details on the split so I can just create a PowerShell Activity to do the work? Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s