Powershellによる設定漏れ確認ツール

Add-Type -AssemblyName System.Windows.Forms
$wsobj = new-object -comobject wscript.shell

# ユーザー入力
$Group = Read-Host "資料① F列に指定するグループ名を入力してください（例：E）"

# 固定文字列
$SSO_ALL   = "SSO_ALL"
$OLD_GROUP = "OLD_SrvID_$Group"
$NEW_GROUP = "NEW_SrvID_$Group"
$NOT_SET   = "[※未設定]"

$file1 = "C:\Users\sasio-tech.SASIO.JP\Documents\作業申請ツール\sample_001.xlsx"
$file2 = "C:\Users\sasio-tech.SASIO.JP\Documents\作業申請ツール\sample_003.xlsx"

# ノードID正規化関数
function Normalize-NodeId($str) {
    if (-not $str) { return "" }
    return ($str.Trim() -replace '[\s　]', '' -replace '[`r`n]', '' -replace '[^\u0020-\u007E]', '').ToLower()
}

# Excel起動
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

$wb1 = $excel.Workbooks.Open($file1)
$ws1 = $wb1.Worksheets(1)

$wb2 = $excel.Workbooks.Open($file2)
$ws2 = $wb2.Worksheets(1)

try {
	# 資料②読み込み
	$nodeList = @()
	$lastRow2 = $ws2.Cells($ws2.Rows.Count,1).End(-4162).Row
	for ($r = 2; $r -le $lastRow2; $r++) {
	    $nodeList += [PSCustomObject]@{
	        NodeId = $ws2.Cells($r,1).Text.Trim()
	        NodeName = $ws2.Cells($r,2).Text.Trim()
	        GX = $ws2.Cells($r,3).Text.Trim()
	        Group = $ws2.Cells($r,4).Text.Trim()
	    }
	}

	# 資料① フィルタ確認
	if (-not $ws1.AutoFilterMode) {
	    $ws1.Rows(3).AutoFilter() | Out-Null
	}
	$ws1.Range("A3").AutoFilter(6,$Group) | Out-Null

	try { $visibleCells = $ws1.UsedRange.SpecialCells(12) } catch { $visibleCells = @() }
	if ($visibleCells.Count -le 1) {
	    [System.Windows.Forms.MessageBox]::Show("フィルタ対象がありません。処理を終了します。","警告","OK","Warning")
	    exit
	}

	# ヘッダ出力
	Write-Output "作業日`t`tホスト名`tSSO全体`tSSO_OLD_NODE_ID`tSSO_NEW_NODE_ID`tOLDグループ`tOLDノードID`tNEWグループ`tNEWノードID"

	$lastRow1 = $ws1.Cells($ws1.Rows.Count,2).End(-4162).Row
	$normalizedSSO = Normalize-NodeId $SSO_ALL

	# 変数初期化
	$ssoOldOut = $NOT_SET
	$ssoNewOut = $NOT_SET
	$oldNodeOut = $NOT_SET
	$newNodeOut = $NOT_SET

	# ============================
	# 資料①を1行ずつ処理
	# ============================
	for ($r = 4; $r -le $lastRow1; $r++) {
	    if ($ws1.Rows($r).Hidden) { continue }

	    # 資料①の情報を取得
	    $hostName    = $ws1.Cells($r,2).Text.Trim()
	    $oldNode     = $ws1.Cells($r,3).Text.Trim()
	    $newNode     = $ws1.Cells($r,4).Text.Trim()
	    $group       = $ws1.Cells($r,6).Text.Trim()  # F列
	    $workDateRaw = $ws1.Cells($r,7).Text.Trim()
	    $workDate    = if ($workDateRaw -match '(\d{4}/\d{1,2}/\d{1,2})') { $matches[1] } else { $workDateRaw }

	    # 出力初期化
	    $ssoOldOut  = $NOT_SET
	    $ssoNewOut  = $NOT_SET
	    $oldNodeOut = $NOT_SET
	    $newNodeOut = $NOT_SET

		foreach ($row2 in $nodeList) {
			# nullチェックしてTrim()、空文字に置き換え
			$nodeId2   = if ($row2.NodeId) { $row2.NodeId.Trim() } else { "" }
			$nodeName2 = if ($row2.Name)   { $row2.Name.Trim() } else { "" }
			$gx2       = if ($row2.GX)     { $row2.GX.Trim() } else { "" }
			$group2    = if ($row2.Group)  { $row2.Group.Trim() } else { "" }

			# ノードIDだけ小文字化（比較用）
			$nodeId2Clean = $nodeId2.Trim().ToLower()
			$oldNodeClean = $oldNode.Trim().ToLower()
			$newNodeClean = $newNode.Trim().ToLower()

			# OLD/NEW 用のグループTrim（大文字小文字はそのまま）
			$groupOldTrim = $group2.Trim()
			$groupNewTrim = $group2.Trim()



			# 1. SSO_OLD_NODE_ID
			if ($nodeId2Clean -eq $oldNodeClean -and $gx2 -ne "" -and $nodeId2Clean -notlike "*a") {
			    $ssoOldOut = $nodeId2
			}

			# 2. SSO_NEW_NODE_ID
			if ($nodeId2Clean -eq $newNodeClean -and $gx2 -ne "" -and $nodeId2Clean -like "*a") {
			    $ssoNewOut = $nodeId2
			}

			# ループ内
			$groupOldTrim = $group2.Trim()
			$groupNewTrim = $group2.Trim()

			# OLDノードID
			if ($nodeId2Clean -eq $oldNodeClean -and $groupOldTrim -ne "" -and $groupOldTrim -like "OLD_SrvID*") {
			    if ($oldNodeOut -eq $NOT_SET) {  # 上書き防止
			        $oldNodeOut = $nodeId2
			    }
			}

			# NEWノードID
			if ($nodeId2Clean -eq $newNodeClean -and $groupNewTrim -ne "" -and $groupNewTrim -like "NEW_SrvID*") {
			    if ($newNodeOut -eq $NOT_SET) {  # 上書き防止
			        $newNodeOut = $nodeId2
			    }
			}
		}

	    	# --- 確認出力 ---
        	Write-Output "$workDate`t$hostName`t$SSO_ALL`t$ssoOldOut`t`t$ssoNewOut`t`t$OLD_GROUP`t$oldNodeOut`t`t$NEW_GROUP`t$newNodeOut"
	}






} finally {
    $wb1.Close($false)
    $wb2.Close($false)
    $excel.Quit()
}