Function Invoke-SQL { Param( [string]$svr, [string]$db, [string]$query ) $connString = "Data Source=$svr; " + "Integrated Security=SSPI; " + "Initial Catalog=$db;" $conn = New-Object System.Data.SqlClient.SQLConnection($connString) $cmd = New-Object System.Data.SqlClient.SQLCommand($query,$conn) $conn.Open() $adapter = New-Object System.Data.SQLClient.SQLDataAdapter $cmd $data = New-Object System.Data.DataSet $adapter.Fill($data) | Out-Null $conn.Close() $data.Tables } function IsNull($objectToCheck) { if ($objectToCheck -eq $null) { return $true } if ($objectToCheck -is [String] -and $objectToCheck -eq [String]::Empty) { return $true } if ($objectToCheck -is [DBNull] -or $objectToCheck -is [System.Management.Automation.Language.NullString]) { return $true } if ([string]::IsNullOrWhiteSpace($objectToCheck)) { return $true } if ([string]::IsNullOrEmpty($objectToCheck)) { return $true } return $false } $aiQuery = @" SELECT i.Config_Name, i.Date_Installed, i.Installed_By, i.Date_Expiration, i.Company_RecID, i.Notes, i.Config_Status_RecID, i.Custom_Field FROM [cwwebapp_msinetworks].[dbo].[Config] i WHERE i.Config_Status_RecID = 7 AND ( i.CS_Survey_RecID = 24 OR i.CS_Survey_RecID = 25 ) "@ $aQuery = @" SELECT a.Config_RecID, a.Config_Name, a.Date_Installed, a.Installed_By, a.Date_Expiration, a.Company_RecID, c.Company_Name, a.Notes, a.Config_Status_RecID, a.Custom_Field FROM [cwwebapp_msinetworks].[dbo].[Config] a LEFT JOIN [cwwebapp_msinetworks].[dbo].[Company] c ON a.Company_RecID = c.Company_RecID WHERE a.Config_Status_RecID = 2 AND ( a.CS_Survey_RecID = 24 OR a.CS_Survey_RecID = 25 ) "@ $iQuery = @" SELECT i.Config_Name, i.Date_Installed, i.Installed_By, i.Date_Expiration, i.Company_RecID, i.Notes, i.Config_Status_RecID, i.Custom_Field FROM [cwwebapp_msinetworks].[dbo].[Config] i WHERE i.Config_Status_RecID = 3 AND ( i.CS_Survey_RecID = 24 OR i.CS_Survey_RecID = 25 ) "@ $inactive = Invoke-SQLCmd -ServerInstance "MSICW02" -Query $iQuery $active = Invoke-SQLCmd -ServerInstance "MSICW02" -Query $aQuery $autoInactive = Invoke-SQLCmd -ServerInstance "MSICW02" -Query $aiQuery $all = @() ForEach ($a in $active) { $name = "$($a.Config_Name)" $id = "$($a.Config_RecID)" $i = $inactive | Where {($_.Config_Name -eq "$($a.Config_Name)") -and ($_.Company_RecID -eq "$($a.Company_RecID)")} $ai = $autoInactive | Where {$_.Config_Name -eq "$($a.Config_Name)" -and ($_.Company_RecID -eq "$($a.Company_RecID)")} If ($i -and $ai) { $all += $i $all += $ai $all += $a } ElseIf ($i) { $all += $i $all += $a } ElseIf ($ai) { $all += $ai $all += $a } $name = "$($a.Config_Name)" $company = "$($a.Company_Name)" $id = "$($a.Config_RecID)" $updateStart = "UPDATE [cwwebapp_msinetworks].[dbo].[Config] SET " $updateMiddle = "" $updateEnd = " WHERE Config_RecID = '$id';" $updates = @() If ($ai -or $i) { # INST. DATE SECTION ----------------------------------- If (!(IsNull($ai.Date_Installed))) { If ((get-Date $a.Date_Installed) -gt (get-date $ai.Date_Installed)) { $instDate = "$($ai.Date_Installed)" } Else { $instDate = "$($a.Date_Installed)" } } Else { $instDate = "$($a.Date_Installed)" } If (!(IsNull($i.Date_Installed))) { If ((get-Date $instDate) -gt (get-Date $i.Date_Installed)) { $instDate = "$($i.Date_Installed)" } } If (!(IsNull($instDate))) { $updateMiddle += "Date_Installed = '$instDate'" } # EXP. DATE SECTION ------------------------------------ $expDate = "" If (!(IsNull($a.Date_Expiration))) { If (!(IsNull($ai.Date_Expiration))) { If ((get-Date $a.Date_Expiration) -lt (get-date $ai.Date_Expiration)) { $expDate = "$($ai.Date_Expiration)" } Else { $expDate = "$($a.Date_Expiration)" } } Else { $expDate = "$($a.Date_Expiration)" } If ((!(IsNull($i.Date_Installed))) -And (!(IsNull($expDate)))) { If ((get-Date $expDate) -lt (get-Date $i.Date_Expiration)) { $expDate = "$($i.Date_Expiration)" } } } Else { If (!(IsNull($ai.Date_Expiration))) { If (!(IsNull($i.Date_Expiration))) { If ((get-Date $ai.Date_Expiration) -lt (get-Date $i.Date_Expiration)) { $expDate = "$($i.Date_Expiration)" } Else { $expDate = "$($ai.Date_Expiration)" } } Else { $expDate = "$($ai.Date_Expiration)" } } ElseIf (!(IsNull($i.Date_Expiration))) { $expDate = "$($i.Date_Expiration)" } } If (!(IsNull($expDate))) { $updateMiddle += ", Date_Expiration = '$expDate'" } # INSTALLED BY SECTION --------------------------------- If (IsNull($a.Installed_By)) { If (IsNull($ai.Installed_By)) { If (IsNull($i.Installed_By)) { } Else { $instBy = "$($i.Installed_By)" $updateMiddle += ", Installed_By = '$instBy'" } } Else { $instBy = "$($ai.Installed_By)" $updateMiddle += ", Installed_By = '$instBy'" } } Else { $instBy = "$($a.Installed_By)" $updateMiddle += ", Installed_By = '$instBy'" } # NOTES SECTION ---------------------------------------- If (IsNull($a.Notes)) { If (!(IsNull($ai.Notes))) { $notes = "$($ai.Notes)" $notes = $notes.Replace("`n`n","' + CHAR(10) + CHAR(10) + '") $notes = $notes.Replace("`n","' + CHAR(10) + '") $updateMiddle += ", Notes = '$notes'" } ElseIf (!(IsNull($i.Notes))) { $notes = "$($i.Notes)" $notes = $notes.Replace("`n`n","' + CHAR(10) + CHAR(10) + '") $notes = $notes.Replace("`n","' + CHAR(10) + '") $updateMiddle += ", Notes = '$notes'" } } # VENDOR NOTES SECTION --------------------------------- If (IsNull($a.Custom_Field)) { If (!(IsNull($ai.Custom_Field))) { $vendor = "$($ai.Custom_Field)" $vendor = $vendor.Replace("`n`n","' + CHAR(10) + CHAR(10) + '") $vendor = $vendor.Replace("`n","' + CHAR(10) + '") $updateMiddle += ", Custom_Field = '$vendor'" } ElseIf (!(IsNull($i.Custom_Field))) { $vendor = "$($i.Custom_Field)" $vendor = $vendor.Replace("`n`n","' + CHAR(10) + CHAR(10) + '") $vendor = $vendor.Replace("`n","' + CHAR(10) + '") $updateMiddle += ", Custom_Field = '$vendor'" } If ((!(isNull($i.Date_Installed))) -or (!(isNull($ai.Date_Installed))) -or (!(IsNull($instBy))) -or (!(IsNull($i.Date_Expiration))) -or (!(IsNull($ai.Date_Expiration))) -or (!(IsNull($notes))) -or (!(IsNull($vendor)))) { $update = "$($updateStart)" + "$($updateMiddle)" + "$($updateEnd)" $out = $update | Where {$_ -notlike "*Set Where*"} $out | Out-File C:\Users\Public\CWUpdates.txt -Append $out2 = "$($name)`t`t$($company)" $out2 | Out-File C:\Users\Public\CWUpdates_Summary.txt -Append } } } }