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

#Url 및 api 주소
$base_url = "http://10.1.26.30:8090"
$jsonrpc = "/rpc/json-rpc/confluenceservice-v2?os_authType=basic"
 
# admin 유저 정보
$user =""
$password = ""


if(${base_url}.ToString()[-1] -eq '/'){
    $base_url = $base_url.ToString().Substring(0, $base_url.Length - 1)
}

$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 = 5
    # 로그
    # 각 행 유저 정보 가져오기
    $adduser = $Sheet.Cells.Item($row,1).Text
    $pass = $Sheet.Cells.Item($row,2).Text
    $fullName = $Sheet.Cells.Item($row,3).Text
    $email = $Sheet.Cells.Item($row,4).Text
 
    #해당 유저 존재여부 조회
    try{
        $get_user_api = "/rest/api/user"
        $user_check = Invoke-WebRequest -Uri "${base_url}${get_user_api}?username=${adduser}" -Method Get -ContentType 'application/json' -Headers @{"Authorization"="Basic $BasicCreds"}
    } catch {
        $user_check = $null
    }
    # user 추가하기
    if($user_check -eq $null){
        $Body1 = '{"jsonrpc" : "2.0", "method" : "addUser", "params" : [{"email":"' + $email + '","fullname":"' + $fullName + '","name":"' + $adduser + '"}, "' + $pass + '"], "id" : null}'
        # 한글 처리
        $Body = [System.Text.Encoding]::UTF8.GetBytes($Body1)
        $user_add = Invoke-WebRequest -Uri "${base_url}${jsonrpc}" -Method Post -ContentType 'application/json;charset=UTF-8' -Headers @{"Authorization"="Basic $BasicCreds"} -Body $Body
        $result = [System.Text.Encoding]::UTF8.GetString($user_add.RawContentStream.ToArray()) | ConvertFrom-json
        Write-Host $result
        if($result.error -eq $null){
            $LogArray += "Username: ${adduser} 생성되었습니다. "
            $usernumber++
        }
    } else {
        $LogArray += "Username: ${adduser} 이미 있습니다."
    }
    #Group 조회
    while($Sheet.Cells.Item($row,$groupcol).Text -ne ""){
        $group = $Sheet.Cells.Item($row,$groupcol).Text
        $group_list = "/rest/api/group/${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
            $LogArray += "Group: ${group} 이 없습니다. "
        }
        #Group 추가
        if($group_check -ne $null){
            $form1 = '{"jsonrpc" : "2.0", "method" : "addUserToGroup", "params" : ["' + $adduser + '", "' + $group + '"], "id" : null}'
            $form = [System.Text.Encoding]::UTF8.GetBytes($form1)
            $group_add = Invoke-WebRequest -Uri "${base_url}${jsonrpc}" -Method Post -ContentType 'application/json;charset=UTF-8' -Headers @{"Authorization"="Basic $BasicCreds"} -Body $form
            $groupresult = [System.Text.Encoding]::UTF8.GetString($group_add.RawContentStream.ToArray()) | ConvertFrom-json
            Write-Host $groupresult
            if($groupresult.error -eq $null){
                $LogArray += "Username: ${adduser} Group: ${group} 에 추가했습니다."
            }
        }
        $groupcol++
    }
    $row++   
}
$row -= 2
$LogArray += "시도: ${row} 성공: ${usernumber}"
$date = Get-Date -UFormat %Y%m%d%H%M
$LogArray > .\${date}_ConfluenceUser.log