Le blog technique

Param(
#[parameter(Mandatory=$true)]
[string]$Runbook
)

$Global:Runbook = $args[0]

$Scriptname = "getRunbookJobPid"
$SQLServer = ""
$SQLDBName = "Orchestrator"
$SQLTempDB= "Temp_$Runbook"+"_$Scriptname"

#FUNCTIONS
function GetProcessInfoById
{
    param
    ([int]$processId)

    Get-WmiObject -class Win32_PerfFormattedData_PerfProc_Process | where-object {$_.idprocess -eq $processId} | select `
    @{Name="Process Id"; Expression = {$_.idprocess}},`
    @{Name="Private Working Set (Ko)"; Expression = {$_.workingSetPrivate / 1kb}}        
}

#END_FUNCTIONS


$SqlQuery = "
SELECT MAX(DATEADD(HOUR,2,TimeStarted)) as TimeStart
      ,ProcessID as ProcessId
      ,Name as Name
      ,j.Status as Jstatus
      ,RI.status as RIStatus
      
      INTO $SQLTempDB
      
  FROM [Orchestrator].[dbo].[POLICYINSTANCES] as p WITH (NOLOCK)
  inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].[Jobs] as j on j.id=p.JobId
  inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator].[Runbooks] as R on R.Id=j.RunbookId
  inner join [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime].RunbookInstances as RI on RI.RunbookId=R.Id
  
  WHERE RI.status = 'InProgress'
  AND j.Status = 'Running'
  AND R.Name = '$Runbook'
  
  
  GROUP BY TimeStarted
      ,ProcessID
      ,Name
      ,j.Status
      ,RI.status
      
        

DECLARE @MaxDate DateTime
SET @MaxDate = (SELECT MAX(TimeStart) FROM $SQLTempDB)

SELECT ProcessId from $SQLTempDB
WHERE TimeStart = @MaxDate

DROP TABLE $SQLTempDB"


if (-not($Runbook))
{
throw "Nom du runbook requis"
}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close() 
#clear 
$ProcessId= $DataSet.Tables[0] | Select-Object -Property ProcessId -ExpandProperty ProcessId

write-host "RUNBOOK: $Runbook"
GetProcessInfoById $ProcessId | fl