Archive | Orchestrator

Orchestrator PowerShell script / SQL query to get last ran jobs

I got asked how to get the last ran jobs in Orchestrator by a SQL query. This is what I came up with.

select * from [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs AS jobs join POLICIES ON jobs.RunbookId = POLICIES.UniqueID where jobs.CreationTime >= dateadd(hh,-2,getdate())


I then decided to add some PowerShell around the SQL query. This because I wanted a fast access to the result when running PowerShell together with Orchestrator. The reason is that I found it faster than accessing the result by using the Orchestrator Webservice. Ether copy the script below or download it at the following link: GetRunningRunbooks

Remember you will need read access to the Orchestrator database.

# Input strings for the script
$DatabaseServerName = "localhost"
$DatabaseName = "Orchestrator"
$h = 2

try
{
    $Connection = new-object system.data.sqlclient.sqlconnection
    $Connection.ConnectionString ="server=$DatabaseServerName;database=$DatabaseName;trusted_connection=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

    $SqlQuery = @"
        select * from [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].Jobs AS jobs join POLICIES ON jobs.RunbookId = POLICIES.UniqueID  where jobs.CreationTime >= dateadd(hh,-$h,getdate())
"@

    $Connection.open()
    $SqlCmd.CommandText = $SqlQuery
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlCmd.Connection = $Connection
    $DataSet = New-Object System.Data.DataSet
    $q = $SqlAdapter.Fill($DataSet)
    [array]$r = $DataSet.Tables[0]
    $DataSet.Tables.Clear()

    ""
    Foreach ($id in $r)
    {
         $id.Name +" - "+ $id.CreationTime
    }
    ""
    $Connection.Close()
}
Catch
{
    Write-host "Connection to database unsuccessful."
    $Connection.Close()
}

Screenshot of the result when running the PowerShell script

Orchestrator PowerShell script SQL query to get last ran jobs1

0

Shrink an Orchestrator database log file

To shrink an Orchestrator database log file (ldf) there is an option in SQL to shrink a database log file.

My experience is that this does not always work so the other solution is to remove the log file. Just remember to back up before doing any of the mention steps below because this solution insolvens deleting information.

Below is a picture of a ldf file from Orchestrator that is about 33Gb.

1

To remove the Orchestrator ldf file start off by stopping the Orchestrator services.

2

After the services are stopped, detach the Orchestrator database.

3

When this window pops up just select drop connections and click ok.

4

(If you are out of disk space there will be an error dialog showing up. Try to free some disk space and then detach again)

When the database is detached, delete the ldf file.

1

The next step is to attach the Orchestrator database again.

5

When this window shows up click Add and select the Orchestrator database file.

6

In the same window as above, remove the Orchestrator database log file by selecting it and click Remove.

7

Click Ok on the Attach Databases window.

When Attaching the Orchestrator database a new log file is created.

9

Start the Orchestrator services.

8

0

Use the integrated MDT 2013 Execute Runbook task with SCCM 2012 together with Orchestrator using SSL

When using the SCCM 2012 integrated MDT 2013 Execute Runbook task together with an Orchestrator web service that are SSL encrypted one need to add this to the input. Below is an example how to add the port and https to get this to work.

https://OrchestratorServerName:<port>

The port at the end is only needed if not using the standard port 443 for SSL.

1

Check if drive is mapped before mapping it with Orchestrator Runbook

If you are a frequent user of the Orchestrator Runbook Activity “Map Network Drive” you maybe sometimes have problems with that the path is already mapped with another Activity. It could be, for example, that another Runbook is mapping it with a different account. The result could be that the Activity fails and if you do not have a link that handles this the Runbook will stop at this Activity.

One way of solving this is to check if the drive is already mapped with the PowerShell command “Test-Path”. By doing so you can decide if you want to skip the “Map Network Drive” or if you want to disconnect the drive before mapping it again with the Runbook.

Below is an example on how to skip mapping it if already mapped.

The example Runbook

1

Activites
Below you can see some of the Activities.

2

3

4

Link
Below you can see the link “Not Mapped”. The link “Mapped” is almost the same but have the Result set to “True”.

5

0

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator

When using Active Directory (AD) application groups for deploying applications in Configuration Manager (SCCM) it might be nice to get the groups removed automatically when removing or putting the applications to the status retire in SCCM. The following example will do exactly that.

There is a few prerequisites for getting this to work with this solution. First the application groups in AD needs to have the PackageID in their names. This to be sure that the application group in AD corresponds to the right application in SCCM.

Other prerequisites is that the Integration Pack “Execute PowerShell Script” is installed on the Runbook server and that WinRM is enabled on the SCCM 2012 server.

To remove the application groups two Runbooks has been created. One that removes the application groups for the expired SCCM applications and one that removes the application groups for deleted SCCM applications.

Below the two Runbooks are presented together with some explanations.

Delete AD Application Group for Expired SCCM Application
This Runbook will delete the AD application groups for the expired applications in SCCM 2012.

 Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator1

Activities information
Below one can see screenshots of the Activities.

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator2
PS Scipt 01
Import-Module “C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1”;
Set-Location P01:
Get-CMApplication | Select-Object -Property LocalizedDisplayName, PackageID, IsExpired | % {if ($_.IsExpired -eq $True) {$_.PackageID +”,”+ $_.LocalizedDisplayName}} 

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator3

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator4

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator5

Delete AD Application Group for Removed SCCM Application
This Runbook will delete the AD Application Groups for the Removed Applications in SCCM 2012.

 Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator6

Activities information
Below one can see screenshots of the Activities.

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator7

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator8

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator9

Delete AD Group when application is removed or retired in Configuration Manager 2012 with Orchestrator10

0