Import-Module ActiveDirectory
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Add()
$Sheet1 = $Excel.Worksheets.Item(1)
$Sheet2 = $Excel.WorkSheets.Item(2)
$Sheet1.Name = "Доступные ПК"
$Sheet2.Name = "Нет доступа"

$Sheet1.Cells.Item(3,1) = "Имя ПК"
$Sheet1.Cells.Item(3,1).Interior.ColorIndex = 15
$Sheet1.Cells.Item(3,1).Font.Bold = $true
$Sheet1.Cells.Item(3,1).EntireColumn.AutoFit() | Out-Null 

$Sheet1.Cells.Item(3,2) = "Наличие DeviceLock"
$Sheet1.Cells.Item(3,2).Interior.ColorIndex = 15
$Sheet1.Cells.Item(3,2).Font.Bold = $true 
$Sheet1.Cells.Item(3,2).EntireColumn.AutoFit() | Out-Null

$Sheet1.Cells.Item(3,3) = "Статус службы"
$Sheet1.Cells.Item(3,3).Interior.ColorIndex = 15
$Sheet1.Cells.Item(3,3).Font.Bold = $true 
$Sheet1.Cells.Item(3,3).EntireColumn.AutoFit() | Out-Null

$Sheet2.Cells.Item(1,1) = "Имя ПК" 
$Sheet2.Cells.Item(1,1).Interior.ColorIndex = 15
$Sheet2.Cells.Item(1,1).Font.Bold = $true 
$Sheet1.Cells.Item(1,1).EntireColumn.AutoFit() | Out-Null

$Sheet2.Cells.Item(1,2) = "Наличие DeviceLock"
$Sheet2.Cells.Item(1,2).Interior.ColorIndex = 15
$Sheet2.Cells.Item(1,2).Font.Bold = $true 
$Sheet1.Cells.Item(1,2).EntireColumn.AutoFit() | Out-Null

$Sheet2.Cells.Item(1,3) = "Статус службы"
$Sheet2.Cells.Item(1,3).Interior.ColorIndex = 15
$Sheet2.Cells.Item(1,3).Font.Bold = $true 
$Sheet1.Cells.Item(1,3).EntireColumn.AutoFit() | Out-Null



foreach ($colorItem in $colSheets){
$WorkBook = $colorItem.UsedRange															
$WorkBook.EntireColumn.AutoFit()
clear
}
$intRow = 4
#$intRow1 = 2

$a=0;
$b=0;
$c=0;
$a=read-host "Проверить весь домен? Y/N"


if ($a -eq "Y")
    {
        #Проверяем весь домен zmdb.ua
        $c="OU=GP IVCHENKO-PROGRESS,DC=ZMDB,DC=UA"
        $komp = Get-ADComputer -Filter * -SearchBase $c|select -exp name # Список ПК из OU
        $kol=$komp.Count
        $i=0
        $servers = $komp
        foreach ($Server in $Servers)
        {
            $i++
            write-progress -activity "Search in Progress" -status "% Complete:" -percentcomplete ($i*100/$kol)
            if (test-Connection -ComputerName $Server -Count 2 -Quiet)
            {
                write-Host "$Server; Доступен" -ForegroundColor Green
                
            }
            else
            {
                write-Host "$Server; Нет доступа " -BackgroundColor Red
 
            }

        }       
        
    } 
else
    {
        if ($a -eq "N")
        {
            $b=read-host "Введите имя OU"

            $Row = 1
            $Column = 1
            $Sheet1.Cells.Item($Row, $Column) = 'Отчет о проверке OU '+$b
            
                $Sheet1.Cells.Item($Row, $Column).Font.Size = 18
                $Sheet1.Cells.Item($Row, $Column).Font.Bold = $true
                $Sheet1.Cells.Item($Row, $Column).Font.ThemeFont = 1
                $Sheet1.Cells.Item($Row, $Column).Font.ThemeColor = 4
                $Sheet1.Cells.Item($Row, $Column).Font.ColorIndex = 55
                $Sheet1.Cells.Item($Row, $Column).Font.Color = 8210719

            # Объединяем диапазон ячеек
            $Range = $Sheet1.Range('A1','G2')
            $Range.Merge()

            #Проверка группы
            $c="OU="+$b+",OU=Workstation,OU=GP IVCHENKO-PROGRESS,DC=ZMDB,DC=UA"
            $komp = Get-ADComputer -Filter * -SearchBase $c|select -exp name # Список ПК из OU
            $kol=$komp.Count
            $i=0
            $servers = $komp
            foreach ($Server in $Servers)
            {
                $i++
                write-progress -activity "Search in Progress" -status "% Complete:" -percentcomplete ($i*100/$kol)
                if (test-Connection -ComputerName $Server -Count 2 -Quiet)
                {
                    write-Host "$Server; Доступен" -ForegroundColor Green  
                     $Sheet1.Cells.Item($intRow, 1) = $Server
                     $Sheet1.Cells.Item($intRow,1).EntireColumn.AutoFit() | Out-Null
                  
                            if ($Z = get-service -ComputerName $Server -Name devi*)
                            {
                               $Sheet1.Cells.Item($intRow,2) = $Z.DisplayName
                               $Sheet1.Cells.Item($intRow,2).EntireColumn.AutoFit() | Out-Null
                               $Sheet1.Cells.Item($intRow,3) = "Running" 
                               $Sheet1.Cells.Item($intRow,3).EntireColumn.AutoFit() | Out-Null
                            }

                            else
                               {
                               $Sheet1.Cells.Item($intRow,2) = "Сервис не установлен"
                               $Sheet1.Cells.Item($intRow,2).Interior.ColorIndex = 3
                               $Sheet1.Cells.Item($intRow,2).EntireColumn.AutoFit() | Out-Null
                               $Sheet1.Cells.Item($intRow,3) = "Невозможно отобразить статус"
                               $Sheet1.Cells.Item($intRow,3).Interior.ColorIndex = 3
                               $Sheet1.Cells.Item($intRow,3).EntireColumn.AutoFit() | Out-Null
                               }                     
                     

                     $intRow = $intRow + 1 
                }
                else
                {
                    write-Host "$Server; Нет доступа " -BackgroundColor Red
                    $Sheet2.Cells.Item($intRow, 1) = $Server
                    $Sheet2.Cells.Item($intRow, 1).EntireColumn.AutoFit() | Out-Null
                    $Sheet2.Cells.Item($intRow, 2) = "Не доступно"
                    $Sheet2.Cells.Item($intRow, 2).EntireColumn.AutoFit() | Out-Null
                    $Sheet2.Cells.Item($intRow, 3) = "Не определен"
                    $Sheet2.Cells.Item($intRow, 3).EntireColumn.AutoFit() | Out-Null
                }

               #$intRow = $intRow + 1 
            }       
        }
        else
        {
            #Проверка корректности ввода Да/Нет
            write-host "ОШИБКА! Введите Y или N"
        }
    }