Crowd Powershell 이용하여 Excel로 사용자 생성 및 그룹 추가

#Url 및 api 주소
$base_url = "http://10.1.25.100:8095"
$user_api = "/crowd/rest/usermanagement/1/user"

# crowd application 정보
$user ="crowd"
$password = "crowd"

$LogArray = @()
# SSL 인증서 오류 일 경우, SSL 인증서 문제 없을 경우 해당 부분 필요 X
Add-Type @"
    using System.Net;
    using System.Security.Cryptography.X509Certificates;
    public class TrustAllCertsPolicy : ICertificatePolicy {
        public bool CheckValidationResult(
            ServicePoint srvPoint, X509Certificate certificate,
            WebRequest request, int certificateProblem){
                return true;
            }
    }
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls, [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12, [Net.SecurityProtocolType]::Tls13, [Net.SecurityProtocolType]::Ssl3

# 인증 인코딩
function Get-BasicAuthCreds {
    param([string]$Username, [string]$Password)
    $AuthString = "{0}:{1}" -f $Username,$Password
    $AuthBytes  = [System.Text.Encoding]::ASCII.GetBytes($AuthString)
    return [Convert]::ToBase64String($AuthBytes)
}
$BasicCreds = Get-BasicAuthCreds -Username $user -Password $password;

#엑셀 파일 선택 및 열기
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog
$FileBrowser.Title = "선택하세요"
$FileBrowser.Filter = "xlsx (*.xlsx)| *.xlsx"
[void]$FileBrowser.ShowDialog()
$FilePath = $FileBrowser.FileName.ToString()
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$WorkBook = $Excel.Workbooks.add($FilePath)
$sheet = $WorkBook.worksheets.Item(1)

#첫행 무시
$row = 2
$usernumber = 0
#엑셀 라인 읽기, 1열의 값이 없을 시 중지

while($Sheet.Cells.Item($row,1).Text -ne ""){
    # 열 라인, 그룹 시작
    $groupcol = 7
    # 로그
    $Log = ""
    # 각 행 유저 정보 가져오기
    $adduser = $Sheet.Cells.Item($row,1).Text
    $pass = $Sheet.Cells.Item($row,2).Text
    $first_name = $Sheet.Cells.Item($row,3).Text
    $last_name = $Sheet.Cells.Item($row,4).Text
    $display_name = $Sheet.Cells.Item($row,5).Text
    $email = $Sheet.Cells.Item($row,6).Text

    #해당 유저 존재여부 조회
    try{
        $user_check = Invoke-WebRequest -Uri "${base_url}${user_api}?username=${adduser}" -Method Get -ContentType 'application/json' -Headers @{"Authorization"="Basic $BasicCreds"}
    } catch {
        $user_check = $null
    }
    # user 추가하기
    if($user_check -eq $null){
        $Body1 = '{"name":"' + ${adduser} + '", "password":{"value": "' + ${pass} +'"}, "active": true, "first-name": "'+ ${first_name} + '", "last-name": "'+ ${last_name} +'", "display-name": "'+ ${display_name} + '", "email": "' + ${email} + '"}'
        # 한글 처리
        $Body = [System.Text.Encoding]::UTF8.GetBytes($Body1)
        $user_add = Invoke-WebRequest -Uri "${base_url}${user_api}.json" -Method Post -ContentType 'application/json' -Headers @{"Authorization"="Basic $BasicCreds"} -Body $Body
        $result = [System.Text.Encoding]::UTF8.GetString($user_add.RawContentStream.ToArray()) | ConvertFrom-json
        $username = $result.name
        $Log += "Username: ${username} 생성되었습니다. "

        #Group 조회
        while($Sheet.Cells.Item($row,$groupcol).Text -ne ""){
            $group = $Sheet.Cells.Item($row,$groupcol).Text
            $group_list = "/crowd/rest/usermanagement/1/group.json?groupname=${group}"
            $group_check = $null
            try{
                $group_check = Invoke-WebRequest -Uri "${base_url}${group_list}" -Method Get -ContentType 'application/json' -Headers @{"Authorization"="Basic $BasicCreds"}
            } catch{
                $group_check = $null
                $Log += "${group}이 없습니다. "
            }
            $group_api = "/crowd/rest/usermanagement/1/group/user/direct.json?groupname=${group}"
            #Group 추가 
            if($group_check -ne $null){
                try{
                    $form1 = '{"name": "' + ${adduser} + '"}'
                    $form = [System.Text.Encoding]::UTF8.GetBytes($form1)
                    $group_add = Invoke-WebRequest -Uri "${base_url}${group_api}" -Method Post -ContentType 'application/json' -Headers @{"Authorization"="Basic $BasicCreds"} -Body $form
                    $Log += "${group}에 추가했습니다. "
                } catch{
                    $Log += "${group}에 이미 있습니다. "
                }
            }
            $groupcol++
        }
        $LogArray += $Log
        $usernumber++
    } else {
        $LogArray += "${adduser} is already exist"
    }
    $row++    
}
$row -= 2
$LogArray += "시도: ${row} 성공: ${usernumber}"
$date = Get-Date -UFormat %Y%m%d%H%M
$LogArray > .\${date}_CrowdAdd.log