Automation: Orchestrator Database Maintenance

Pin it

I have made the Orchestrator database maintenance activity automated. You can get the following Script in the below GitHub repository.

https://github.com/souravmahato7/Codes/blob/SCORCH/DatabaseMaitenanceScript.PS1

##################################################################
#Author: Sourav Mahato
#Created Date:09/25/2019
#Purpose: Script for grooming the data for Orchestrator database

# Please save the Script in the following location: C:\Software

# Also please donwload the module SCOrchestratorManagement.psm1 and kept it in the following location: C:\Software

# https://gallery.technet.microsoft.com/scriptcenter/SCOrchestratorManagementpsm-5c16093f

#How to run: .\Runbooks.PS1 -MS SCORCHRB.Domain.COM -SQLServer SQLServername -SQLDB SQLDAtabaseName
##################################################################
###### Function for SQL Query
#######################################################################

param([String] $MS,$SQLServer, $SQLDB)

Function Get-SQLTable($strSQLServer, $strSQLDatabase, $strSQLCommand, $intSQLTimeout = 3000)
{ 
trap [System.Exception]
{
Write-Host "Exception trapped, $($_.Exception.Message)"
Write-Host "SQL Command Failed. Sql Server [$strSQLServer], Sql Database [$strSQLDatabase], Sql Command [$strSQLCommand]."
continue;
}

#build SQL Server connect string
$strSQLConnect = "Server=$strSQLServer;Database=$strSQLDatabase;Integrated Security=True;Connection Timeout=$intSQLTimeout" 

#connect to server and recieve dataset
$objSQLConnection = New-Object System.Data.SQLClient.SQLConnection
$objSQLConnection.ConnectionString = $strSQLConnect
$objSQLCmd = New-Object System.Data.SQLClient.SQLCommand
$objSQLCmd.CommandTimeout = $intSQLTimeout
$objSQLCmd.CommandText = $strSQLCommand
$objSQLCmd.Connection = $objSQLConnection
$objSQLAdapter = New-Object System.Data.SQLClient.SQLDataAdapter
$objSQLAdapter.SelectCommand = $objSQLCmd
$objDataSet = New-Object System.Data.DataSet
$strRowCount = $objSQLAdapter.Fill($objDataSet)

If ($?)
{
#pull out table
$objTable = $objDataSet.tables[0]
}

#close the SQL connection
$objSQLConnection.Close()

#return array of values to caller 
return $objTable
}


####################################################################################################
#
# Saving the Information about the Runbooks which are in running state in Orchestrator
#
####################################################################################################

Write-Host "Saving the Information about the Runbooks which are in running state in Orchestrator" -ForegroundColor Yellow

$RunbooksInfo = "Select RB.Name, RT.RunbookId, RB.Path, RT.Id, RT.RunbookServerId, A.Computer , RT.Status, RT.Parameters, RT.LastModifiedTime, RT.LastModifiedBy from 
[Microsoft.SystemCenter.Orchestrator.Runtime].[Jobs] as RT
inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] RB on RB.Id= RT.RunbookId 
inner join ACTIONSERVERS A on A.UniqueID = RT.RunbookServerId
where Status = 'Running'
order by RT.CreationTime desc"

$RBResult = Get-SqlTable $SQLServer $SQLDB $RunbooksInfo

$RBResult.RunbookID.guid


####################################################################################################
#
# Get the Information about the RunbookServer and Stop the Runbook service
#
####################################################################################################

Write-Host "We will now stop the Runbook service on Runbook servers" -ForegroundColor Yellow

$RunbookServers = "Select * from ACTIONSERVERS"

$RBSResult = Get-SqlTable $SQLServer $SQLDB $RunbookServers

$RBSResult.Computer

Foreach ($RS in $RBSResult.Computer)

{

$status = "Running"

$ServicetoStart= Get-Wmiobject -Class win32_service -computer $RS -filter "name = 'orunbook'"

if ($ServicetoStart.state -eq $status)

{
Write-Host "Runbook Service is in Running State on Runbook Server $RS, Stopped the Service" -ForegroundColor Yellow

(Get-Wmiobject -Class win32_service -computer $RS -filter "name = 'orunbook'").InvokeMethod("StopService",$null)

Start-Sleep -Seconds 30

Write-Host "Stopped the Runbook service on $RS" -ForegroundColor Green 
}

}


####################################################################################################
#
# Execute below SQL queries to clean tables (Policyinstances, Objectinstances, Objectinstancedata, Events, Policy_publish_queue)
#
####################################################################################################

Write-Host "Now we will clean tables (Policyinstances, Objectinstances, Objectinstancedata, Events, Policy_publish_queue)" -ForegroundColor Yellow

$DeleteEvent = "DELETE FROM POLICY_PUBLISH_QUEUE
TRUNCATE TABLE EVENTS
TRUNCATE TABLE OBJECTINSTANCEDATA
TRUNCATE TABLE OBJECTINSTANCES
DELETE FROM POLICYINSTANCES"

Get-SqlTable $SQLServer $SQLDB $DeleteEvent


####################################################################################################
#
# to take a note of all the policies for which logging is enabled
#
####################################################################################################

Write-Host "Get the Information about the RunbookServer and Stop the Runbook service" -ForegroundColor Yellow

$LogCommonDatas = "Select * from POLICIES where LogCommonData = 1"

$LogCommonData = Get-SqlTable $SQLServer $SQLDB $LogCommonDatas

$LogCommonData.UniqueID

$LogSpecificDatas = "Select * from POLICIES where LogSpecificData = 1"

$LogSpecificData = Get-SqlTable $SQLServer $SQLDB $LogSpecificDatas

$LogSpecificData.UniqueID

####################################################################################################
#
# Query to disable the logging
#
####################################################################################################

Write-Host "Now disabling Logging for all Runbooks" -ForegroundColor Yellow

$cmd1 = "update POLICIES set LogCommonData = 0 where LogCommonData = 1"

Get-SqlTable $SQLServer $SQLDB $cmd1

$cmd2 = "update POLICIES set LogSpecificData = 0 where LogSpecificData = 1"

Get-SqlTable $SQLServer $SQLDB $cmd2


####################################################################################################
#
# Now stopping all running runbooks
#
####################################################################################################

Write-Host "Now stopping all running runbooks" -ForegroundColor Yellow

$cmd3 = "DECLARE @JobId UNIQUEIDENTIFIER
DECLARE job_cursor CURSOR FOR
SELECT Id FROM [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs
WHERE StatusId < 2
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @JobId 
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [Microsoft.SystemCenter.Orchestrator.Runtime].CancelJob @JobId, 'S-1-5-500'
FETCH NEXT FROM job_cursor INTO @JobId
END
CLOSE job_cursor
DEALLOCATE job_cursor"


Get-SqlTable $SQLServer $SQLDB $cmd3


####################################################################################################
#
# Now clearing all the orphaned runbook instances
#
####################################################################################################

Write-Host "Now clearing all the orphaned runbook instances" -ForegroundColor Yellow

Get-SqlTable $SQLServer $SQLDB "exec [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[ClearOrphanedRunbookInstances]"


####################################################################################################
#
# Now we will do log purging
#
####################################################################################################

Write-Host "Now we will do log purging" -ForegroundColor Yellow

$cmd4 = "DECLARE @Completed bit
SET @Completed = 0
WHILE @Completed = 0 EXEC sp_CustomLogCleanup @Completed OUTPUT, @FilterType=1,@XEntries=0"

Get-SqlTable $SQLServer $SQLDB $cmd4


####################################################################################################
#
# Now executing maintenance operations
#
####################################################################################################

Write-Host "Now executing maintenance operations" -ForegroundColor Yellow

Get-SqlTable $SQLServer $SQLDB "ALTER QUEUE [Microsoft.SystemCenter.Orchestrator.Maintenance].MaintenanceServiceQueue WITH STATUS = ON"

Get-SqlTable $SQLServer $SQLDB "TRUNCATE TABLE [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache"

Get-SqlTable $SQLServer $SQLDB "EXEC [Microsoft.SystemCenter.Orchestrator.Maintenance].[EnqueueRecurrentTask] @taskName = 'Statistics'"

Get-SqlTable $SQLServer $SQLDB "EXEC [Microsoft.SystemCenter.Orchestrator.Maintenance].[EnqueueRecurrentTask] @taskName = 'Authorization'"

Get-SqlTable $SQLServer $SQLDB "EXEC [Microsoft.SystemCenter.Orchestrator.Maintenance].[EnqueueRecurrentTask] @taskName = 'ClearAuthorizationCache'"


$cmd5 = "SELECT 
[m].[Name], 
[m].[IsEnabled], 
[m].[IntervalInSeconds], 
[m].[LastExecutionTime] 
FROM [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Maintenance].[MaintenanceTasks] [m]"


$Output = Get-SqlTable $SQLServer $SQLDB $cmd5

$Output.LastExecutionTime

####################################################################################################
#
# Now Enable the logging which were disabled earlier
#
####################################################################################################

Write-Host "Now Enable the logging which were disabled earlier" -ForegroundColor Green

foreach ($LCD in $LogCommonData.UniqueID)

{

Write-Host "Working on $LCD" -ForegroundColor Yellow

Get-SqlTable $SQLServer $SQLDB "update POLICIES set LogCommonData = 1 where UniqueID = '$LCD'"

}


foreach ($LSD in $LogSpecificData.UniqueID)

{

Write-Host "Working on $LSD" -ForegroundColor Yellow

Get-SqlTable $SQLServer $SQLDB "update POLICIES set LogSpecificData = 1 where UniqueID = '$LSD'"

}


####################################################################################################
#
# Now starting the RUnbook service for the Runbook servers
#
####################################################################################################


Write-Host "Now starting the RUnbook service for the Runbook servers" -ForegroundColor Yellow


Foreach ($RS in $RBSResult.Computer)

{
$status = "stopped"

$ServicetoStart= Get-Wmiobject -Class win32_service -computer $RS -filter "name = 'orunbook'"

if ($ServicetoStart.state -eq $status)

{
Write-Host "Runbook Service is in stopped State on Runbook Server $RS, so we will start the service" -ForegroundColor Yellow

(Get-Wmiobject -Class win32_service -computer $RS -filter "name = 'orunbook'").InvokeMethod("StartService",$null)

Start-Sleep -Seconds 30

Write-Host "started the Runbook service on $RS" -ForegroundColor Green 
}

}

####################################################################################################
#
# Now starting all the runbooks which were stopped earlier
#
####################################################################################################


Write-Host "Now starting all the runbooks which were stopped earlier" -ForegroundColor Yellow

Import-Module "C:\Software\SCOrchestratorManagement.psm1"

foreach ($RunbookID in $RBResult.RunbookID.guid)

{

Write-Host "Working on $RunbookID" -ForegroundColor Yellow

$Runbook = Get-scoRunbook -ManagementServer $MS | ? {$_.ID -match "$RunbookID"}

If ($Runbook)

{

Start-scoRunbook -Runbook $Runbook

Write-Host "Started Runbook $RunbookID successfully" -ForegroundColor Green

}

}

The above Script is intend to perform the steps mentioned in the following article. Additionally this Script will start the Runbooks which will be stopped during the whole process.

Delete Orphan entries from SCROCH database

Please check and let me know if you have any feedback.

Cheers!