Azure Pack Automation (SMA) Get-SMAJobOutput fails

I recently ran into an issue where regardless of the method used, I was unable to get any job output from Service Management Automation.

Get-SMAJobOutput -JobID '5c773933-5a9b-4021-a793-768b2efd0165' -WebServiceEndPoint $WebServiceEndpoint -Stream Output

simply returned:

Get-SmaJobOutput : The job 5c773933-5a9b-4021-a793-768b2efd0165 cannot be found: System.Data.Services.Client.DataServiceClientException: <?xml version="1.0" 
encoding="utf-8" standalone="yes"?><error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><code></code><message xml:lang="en-US">An error 
occurred while executing the command definition. See the inner exception for details.</message></error>
   at System.Data.Services.Client.BaseAsyncResult.EndExecute[T](Object source, String method, IAsyncResult asyncResult)
   at System.Data.Services.Client.QueryResult.EndExecuteQuery[TElement](Object source, String method, IAsyncResult asyncResult)
At line:1 char:1
+ Get-SmaJobOutput -Id $id -WebServiceEndpoint $webserviceendpoint
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-SmaJobOutput], InvalidOperationException
    + FullyQualifiedErrorId : System.InvalidOperationException,Microsoft.SystemCenter.ServiceManagementAutomation.GetSmaJobOutput

Note that this is not the issue where the output fails with the same error if using the wrong -Stream case.

Additionally, getting the job output from the Azure Pack admin site, simply returned a red exclamation mark.

Next, I adapted a REST based function to get the output from the REST api (originally posted here: http://www.laurierhodes.info/?q=node/105)

# Original script from http://www.laurierhodes.info/?q=node/105
# Modified by Christopher Keyaert (christopher.keyaert@inovativ.be)
 

 Function Get-SMARESTJobOutput{
    Param(
        [Parameter(Mandatory=$true)]
        [string]$ID,
        [Parameter(Mandatory=$true)]
        [validateset('Any','Progress','Output','Warning','Error','Debug','Verbose')]
        [String]$stream,
        [string]$webServiceEndPoint,
        [System.Management.Automation.PSCredential]$Credential
    )
# Ignore SSL Self-Signed certificate  
Try{
    [System.Net.ServicePointManager]::CertificatePolicy = new-object IgnoreSelfSignedCertificate
}Catch{
add-type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
 
        public class IgnoreSelfSignedCertificate : ICertificatePolicy {
        public IgnoreSelfSignedCertificate() {}
        public bool CheckValidationResult(
            ServicePoint sPoint, X509Certificate cert,
            WebRequest wRequest, int certProb) {
            return true;
        }
    }
"@ -ErrorAction SilentlyContinue
    [System.Net.ServicePointManager]::CertificatePolicy = new-object IgnoreSelfSignedCertificate
} 
    $SMAServer = "${WebServiceEndpoint}:9090"
    $VerbosePreference = "Continue"
    #$VerbosePreference = "SilentlyContinue"
 
    Write-Verbose ""

    $URI =  "$SMAServer/00000000-0000-0000-0000-000000000000/Jobs(guid'" + $ID  + "')"
    $Response = if($Credentials){Invoke-RestMethod -Uri $URI  -Method Get -Credential $credentials}else{Invoke-RestMethod -Uri $URI  -Method Get} 
    $JobStatus = $Response.entry.properties.JobStatus 
    
    Write-Verbose "Job Status = $JobStatus"
   
    $URI =   "$SMAServer/00000000-0000-0000-0000-000000000000/JobStreams/GetStreamItems?jobId='" + $ID +"'&streamType='$Stream' "
    Try{
        $Result = if($Credentials){Invoke-RestMethod -Uri $URI  -Method Get -Credential $credentials}else{Invoke-RestMethod -Uri $URI  -Method Get}
    }Catch [exception]{
        $Exception = $_
    }
  
    Write-verbose "StreamText = $($Result.content.properties.StreamText.InnerText)"
 
    $outputname = "Stream${Stream}"
    New-object -TypeName PSCustomObject -ArgumentList @{'ID'=$ID;'Status'=$JobStatus;$outputname=$Result.content.properties.StreamText.InnerText}
}

This returned the same error as Get-SMAJobOutput (An error
occurred while executing the command definition. See the inner exception for details)

The more I researched this, the more it smelled like a performance issue querying the database.
After doing a bit of digging, I found that my [SMA].[Stream].JobStreams table was ~17,000,000 rows. Using the following command, I dropped the job history down to 30 days (we had previously set it to 90):

Set-SmaAdminConfiguration -PurgeJobsOlderThanCountDays 30 -WebServiceEndpoint $webserviceendpoint

This took 8 hours to run and dropped the table down to 9,000,000 rows. The issue still remained, so I dropped it further to 25 days and also disabled debugging and process logging on all runbooks. This dropped the table down to 7,500,000 rows. Now the issue occurred ~75% of the time….Progress!

Since I had now verified that it was an SQL performance issue, I turned to the Activity Monitor of SQL Studio.
Sure enough, the JobStreams table did not have an index usable to query the data required by the Get-SMAJobOutput powershell command. Adding the following index dropped the query time to instantaneous:

USE [SMA]
GO
CREATE NONCLUSTERED INDEX [NC_JobIDTenantID]
ON [Stream].[JobStreams] ([TenantId],[JobId])
INCLUDE([StreamTime],[StreamTypeName],[Stream],[StreamText],[RunbookVersionId])
GO

The issue was now resolved! Please note that this fix may not be supported by Microsoft. Please use at your own discretion.

Advertisements

Starting and waiting for an SMA runbook from remote computers

If you are going to manually trigger a SMA runbook from a single computer you’ll want to install the Powershell module from the Orchestrator setup splash screen by clicking on PowerShell administration under Service Management.

However in order to deploy this to a team of administrators we’ll want to automate this. According to the technet library we should be able to use the command:

msiexec.exe /i PowershellModuleInstaller.msi /qn

We can get this PowershellModuleInstaller.msi file from the System Center 2012 R2 Orchestrator 2012 R2 iso in the SMA subfolder.

Continue reading

SMA – Time to Execution Performance

The following TechNet blog post from the Orchestrator team can be used for measuring stress and load performance of your SMA environment: Configure SMA for Optimum Performance

This got me wondering what other ways I could improve performance. I noticed one key area in my scripts was the time while the script was in the “starting” phase. We know that SMA compiles the workflows at run time…so that was my target area.
Continue reading

Passing Hashtables to Start-SMARunbook as a Parameter

In this post, we are going to explore using hashtables as parameters in an SMA workflow. I’ll let the code do most of the talking this time.

Consider the following SMA runbook:

workflow DEV_MDTestArea
{   
    Param(
        [hashtable]$hashTest
    )
    $hashtest.keys
}

Continue reading

Catching Password Complexity Errors from Set-ADAccountPassword within SMA

Most of the time, I’d like to figure out complete solution before publishing a blog post. There are times however where an error doesn’t make sense to me…..this is one of those times.

$strClientUser="samaccountname"
Try{
    $inSecNewPassword = "Password"
    $secNewPassword = ConvertTo-SecureString -String $inSecNewPassword -AsPlainText -Force 
    #Try set password
    Set-ADAccountPassword -Identity $strClientUser -NewPassword $secNewPassword -Confirm:$false -reset -ErrorAction Stop
}Catch [Exception]{
        "Password doesn't meet complexity requirements"
}

This code works as expected in native powershell….
This code also works as expected in a PS Workflow….
This code does NOT work in a Service Management Automation PS Workflow.
Continue reading