Power Shell Script To Read Registry Of Multiple Server

How To Run Single Script on Multiple Servers

Last weekend my production server rebooted automatically which cause many processes killed. This cause unnecessary burden on DBAs to re-run all jobs and monitoring them till they completed successfully.

After investigation we found this is caused due to windows auto update setting. Here after windows update installation server rebooted automatically. This was one of unexpected issue on production so to fix this issue the major task was to check all servers and find those who have auto status update.

There are couples of ways to do that

1. Check server properties one by one on each server
2. Check administrative tools one by one on each server
3. Use Sql query and run on all server one by one  via SSMS from one server
4. Use power shell script and get output with one click.

This Sql script will read registry of machine where sql server is installed

/************ Sql script to read registry of machine ******************/

DECLARE @test int ,

        @key varchar(100),
        @keyupdate varchar (100)

SET @key = 'SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@key,@value_name='NoAutoUpdate',@value=@test OUTPUT

If @test = 0
            set @keyupdate = 'Automatic Updates is enabled'
Else
            set @keyupdate = 'Automatic Updates is disabled'
SELECT @@ServerName,convert(varchar(100),@keyupdate)as [Automatic Updates Status] , @test

/********************************************************************/

Using Power shell

1. Create a file name server_list.txt. This file will keep list of all servers on which query need to be executed.
2. Create a power shell script to execute desired code of reading registry on all servers
3. Create a output file on same location

/**********************Power shell Script: *******************************/
$SearchValue = "0"
$RegPath = "SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU"
$Serverlist = gc "Drive:\Automation\serverlist.txt"
$output = "Drive:\Automation\Autoupdatestatus_Output.txt"

"Computer,Results" | out-file $output
            ForEach ($Computername in $Serverlist)
            {
            write-host $Computername
            $results = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address =        '$Computername'"
            if($results.statuscode -ne 1)
            {
            $BaseKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LOCALMACHINE", $ComputerName)
            $OpenSubKey = $BaseKey.OpenSubKey($RegPath)
            $Values = $OpenSubKey.GetValue('NoAutoUpdate')
            if ($Values -match $SearchValue)
            {
            write-host $Computername "- Automatic Updates is enabled"
            "$Computername,- Automatic Updates is enabled" | out-file -append $output
            }
            else
                        {
            write-host $Computername "- Automatic Updates is disabled"
            "$Computername,- Automatic Updates is disabled" | out-file -append $output
            }
                                }
            Else
                {
                        write-host $Computername "- Server is not pingable"
                        "$Computername,- Server is not pingable" | out-file -append $output
                        }
}

/********************************************************************/

We have to run power shell script from a batch file

/*************************Batch files code: ******************************/

Powershell.exe  Drive:\Automation\Windows_Auto_update_status.ps1  G:\Automation\Output.txt

/********************************************************************/

We have to just run batch file from command prompt and we will get result in output file