Exchange 2013 - 사서함 용량 DB에 저장 (쓰레드 사용)

대량의 사서함을 호스팅하는 Exchange 환경에서 메일 사서함의 용량을 전부 찾아 처리하는 방식을 시간이 오래 걸린다.

이를 보완하기위 Powershell에 지원하는 쓰레드를 사용하여 시사험 용량을 DB에 저장하는 코드를 작성해보았다.

 

 
function Invoke-Async{
param(
#The data group to process, such as server names.
[parameter(Mandatory=$true,ValueFromPipeLine=$true)]
[object[]]$Set,
#The parameter name that the set belongs to, such as Computername.
[parameter(Mandatory=$true)]
[string] $SetParam,
#The Cmdlet for Function you'd like to process with.
[parameter(Mandatory=$true, ParameterSetName='cmdlet')]
[string]$Cmdlet,
#The ScriptBlock you'd like to process with
[parameter(Mandatory=$true, ParameterSetName='ScriptBlock')]
[scriptblock]$ScriptBlock,
#any aditional parameters to be forwarded to the cmdlet/function/scriptblock
[hashtable]$Params,
#number of jobs to spin up, default being 10.
[int]$ThreadCount=10,
#return performance data
[switch]$Measure
)
Begin
{

    $Threads = @()
    $Length = $JobsLeft = $Set.Length

    $Count = 0
    if($Length -lt $ThreadCount){$ThreadCount=$Length}
    $timer = @(1..$ThreadCount  | ForEach-Object{$null})
    $Jobs = @(1..$ThreadCount  | ForEach-Object{$null})
    
    If($PSCmdlet.ParameterSetName -eq 'cmdlet')
    {
        $CmdType = (Get-Command $Cmdlet).CommandType
        if($CmdType -eq 'Alias')
        {
            $CmdType = (Get-Command (Get-Command $Cmdlet).ResolvedCommandName).CommandType
        }
        
        If($CmdType -eq 'Function')
        {
            $ScriptBlock = (Get-Item Function:\$Cmdlet).ScriptBlock
            1..$ThreadCount | ForEach-Object{ $Threads += [powershell]::Create().AddScript($ScriptBlock)}
        }
        ElseIf($CmdType -eq "Cmdlet")
        {
            1..$ThreadCount  | ForEach-Object{ $Threads += [powershell]::Create().AddCommand($Cmdlet)}
        }
    }
    Else
    {
        1..$ThreadCount | ForEach-Object{ $Threads += [powershell]::Create().AddScript($ScriptBlock)}
    }

    If($Params){$Threads | ForEach-Object{$_.AddParameters($Params) | Out-Null}}

}
Process
{
    while($JobsLeft)
    {
        for($idx = 0; $idx -lt ($ThreadCount-1) ; $idx++)
        {
            $SetParamObj = $Threads[$idx].Commands.Commands[0].Parameters| Where-Object {$_.Name -eq $SetParam}
             
            If($Jobs[$idx].IsCompleted) #job ran ok, clear it out
            {  
                $result = $null
                if($threads[$idx].InvocationStateInfo.State -eq "Failed")
                {
                    $result  = $Threads[$idx].InvocationStateInfo.Reason
                    Write-Error "Set Item: $($SetParamObj.Value) Exception: $result"
                }
                else
                { 
                    $result = $Threads[$idx].EndInvoke($Jobs[$idx])
                }
                $ts = (New-TimeSpan -Start $timer[$idx] -End (Get-Date))
                if($Measure)
                {
                    new-object psobject -Property @{
                        TimeSpan = $ts
                        Output = $result
                        SetItem = $SetParamObj.Value}
                }
                else
                {
                    $result
                }
                $Jobs[$idx] = $null
                $JobsLeft-- #one less left
                write-verbose "Completed: $($SetParamObj.Value) in $ts"
                write-progress -Activity "Processing Set" -Status "$JobsLeft jobs left" -PercentComplete (($length-$jobsleft)/$length*100)
            }
            If(($Count -lt $Length) -and ($Jobs[$idx] -eq $null)) #add job if there is more to process
            {
                write-verbose "starting: $($Set[$Count])"
                $timer[$idx] = get-date
                $Threads[$idx].Commands.Commands[0].Parameters.Remove($SetParamObj) | Out-Null #check for success?
                $Threads[$idx].AddParameter($SetParam,$Set[$Count]) | Out-Null
                $Jobs[$idx] = $Threads[$idx].BeginInvoke()
                $Count++
            }
        }

    }
}
End
{
    $Threads | ForEach-Object{$_.runspace.close();$_.Dispose()}
}
}


cls

$sb = [scriptblock] {
        Param (
        [string]$object
        )
        
        $limit = 0
        Add-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.SnapIn"
        
        # DB 연결 정보는 환경에 따라 달라짐
        $ConnectionString = "Server=vs-db\instance;Database=MailData;Integrated Security=True;Connect Timeout=30"
        $conn = New-Object System.Data.sqlClient.SQLConnection
        $conn.connectionstring = $ConnectionString
        $conn.Open()
        $sqlCmd = New-Object System.Data.sqlClient.SqlCommand

        $Result = @()
        $StartTime = (get-date -Format "yyyy-MM-dd HH:mm:ss")

        if($limit -eq 0){
            Get-Mailbox -Database $object -ResultSize unlimited | ?{$_.DisplayName -notlike '*퇴직자*'} | %{
                #$DB = New-Object psobject
                $dateTime = (get-date -Format "yyyy-MM-dd HH:mm:ss")
                #$DB | Add-Member Noteproperty -Name "Date" -Value $dateTime
                #$DB | Add-Member Noteproperty -Name "SamAccountName" -Value $_.SamAccountName
                #$DB | Add-Member Noteproperty -Name "Name" -Value $_.Name
                #$DB | Add-Member Noteproperty -Name "PrimarySmtpAddress" -Value $_.PrimarySmtpAddress
                
                if($_.UseDatabaseQuotaDefaults -ne $true){
                    $IssueWarningQuota = ($_.IssueWarningQuota.Value.ToMB())
                }else{
                    $IssueWarningQuota = ((Get-MailboxDatabase -Identity $object).IssueWarningQuota.Value.ToMB())
                }

                #$DB | Add-Member Noteproperty -Name "IssueWarningQuota" -Value $IssueWarningQuota
                
                #$Result += $DB
                Get-MailboxStatistics -Identity $_.Identity | %{
                    $TotalItemSize = ($_.TotalItemSize.Value.ToMB())
                    $ItemCount = $_.ItemCount
                    #$DB | Add-Member Noteproperty -Name "ItemCount" -Value $ItemCount
                    #$DB | Add-Member Noteproperty -Name "TotalItemSize" -Value $TotalItemSize
                }

                $query = "INSERT INTO MailData.dbo.TB_MAILUSERQUOTA (date,SamAccountName,Name,PrimarySmtpAddress,IssueWarningQuota,TotalItemSize,ItemCount,MailDB) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f $dateTime, $_.SamAccountName, $_.Name, $_.PrimarySmtpAddress, $IssueWarningQuota, $TotalItemSize,$ItemCount,$object
                $sqlCmd.CommandText = $query
                $sqlCmd.Connection = $conn
                $sqlCmd.CommandTimeout = 30
                $sqlCmd.ExecuteNonQuery()

            }

        }else{
            Get-Mailbox -Database $object -ResultSize $limit | %{
                #$DB = New-Object psobject
                $dateTime = (get-date -Format "yyyy-MM-dd HH:mm:ss")
                #$DB | Add-Member Noteproperty -Name "Date" -Value $dateTime
                #$DB | Add-Member Noteproperty -Name "SamAccountName" -Value $_.SamAccountName
                #$DB | Add-Member Noteproperty -Name "Name" -Value $_.Name
                #$DB | Add-Member Noteproperty -Name "PrimarySmtpAddress" -Value $_.PrimarySmtpAddress
                
                if($_.UseDatabaseQuotaDefaults -ne $true){
                    $IssueWarningQuota = ($_.IssueWarningQuota.Value.ToMB())
                }else{
                    $IssueWarningQuota = ((Get-MailboxDatabase -Identity $object).IssueWarningQuota.Value.ToMB())
                }

                #$DB | Add-Member Noteproperty -Name "IssueWarningQuota" -Value $IssueWarningQuota
                
                #$Result += $DB
                Get-MailboxStatistics -Identity $_.Identity | %{
                    $TotalItemSize = ($_.TotalItemSize.Value.ToMB())
                    $ItemCount = $_.ItemCount
                    #$DB | Add-Member Noteproperty -Name "ItemCount" -Value $ItemCount
                    #$DB | Add-Member Noteproperty -Name "TotalItemSize" -Value $TotalItemSize
                }

                $query = "INSERT INTO MailData.dbo.TB_MAILUSERQUOTA (date,SamAccountName,Name,PrimarySmtpAddress,IssueWarningQuota,TotalItemSize,ItemCount,MailDB) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f $dateTime, $_.SamAccountName, $_.Name, $_.PrimarySmtpAddress, $IssueWarningQuota, $TotalItemSize,$ItemCount,$object
                $sqlCmd.CommandText = $query
                $sqlCmd.Connection = $conn
                $sqlCmd.CommandTimeout = 30
                $result = $sqlCmd.ExecuteNonQuery()
            }

        }

        $delquery = "delete from [dbo].[TB_MAILUSERQUOTA] where Date < '{0}' and MailDB = '{1}'" -f $StartTime,$object
        $sqlCmd.CommandText = $delquery
        $sqlCmd.Connection = $conn
        $sqlCmd.CommandTimeout = 30
        $result = $sqlCmd.ExecuteNonQuery()
        
        
        $conn.Close()

        return $Result
        
   }


$object = (Get-MailboxDatabaseCopyStatus -Server $env:computername  | ? {$_.Status -eq "Mounted" -and $_.Name -notlike "OABMBX*"}).databasename

if($object.count -ne 0){
    $rtn = Invoke-Async -Set $object -SetParam object -ScriptBlock $sb -ThreadCount $object.count -Verbose 
}

PowerShell Gallery | Invoke-Async.ps1 1.0.3

PowerShell Gallery | Invoke-Async.ps1 1.0.3

PowerShell: Run SQL Update command (ExecuteNonQuery) (mylifeismymessage.net)

PowerShell: Run SQL Update command (ExecuteNonQuery)