# ======================================================================================================================
# 
# NAME: dstcheck_global.ps1
# 
# AUTHOR: Yan Pan
# DATE  : 10/27/2007
# 
# COMMENT: This script checks time on SQL Server instances globally. It takes into consideration all time zones 
#       in the US and EU. It compares the local time on each SQL Server instance with current
#       Eastern Standard Time (EST). If the inaccuracy of the local time of a SQL Server instance
#       is not less than 30 minutes, then the SQL Server instance would be reported as an exception.
# =======================================================================================================================


##############################################################################
# Initialize parameters 
##############################################################################
param ( 
    [switch]$help,
    [string]$baseEST = {''}
    )


##############################################################################
# GetTime returns a new time that is $numOfHours hours ahead of $basetime 
##############################################################################
function GetTime([string] $basetime, [int] $numOfHours)
{       
        $newtime=(Get-Date $basetime).AddHours($numOfHours)
        return $newtime.ToString('yyyy-MM-dd hh:mm')
}


##############################################################################
#               Main body of this script
##############################################################################


$starttime=get-date     # We save the time this script starts execution in $starttime, 
                        # so we can include the script execution duration in the calculation of the inaccuracy.


#########################################################################################################################
# NOTE : In this script, we are taking the year 2007 as an example. 
#       Therefore, all the times in the five variables 
#       - $ESTDSTStart, $ESTDSTEnd, $ESTDSTEndChange, $GMTDSTStart, and $GMTDSTEnd,
#       are for the year 2007.
#
#       For another year, we can simply change the values of these five variables
#       to reflect the DST changing dates of that year, and this script will work for that year.
#
#       In the US, clocks change at 1:59am local time. It means the EST time zone changes first, then CST, MST, PST.
#       In the EST time zone, at 1:59am on 3/11/07, clocks sprang forward to 3am. At 1:59am on 11/4/07, clocks fell back to 1am.
#       Therefore, if the given EST time was after 3am on 3/11/07 and before 1am on 11/4/07, then we assumed the EST time zone observed DST.
#       However, if the given EST time was after 1am but before 2am on 11/4/07, then we needed to ask the question 
#       "Does DST end in EST?". Depending on the user's response, we could decide if the EST observed DST or not.
#
#       In the EU, clocks change at 1am Universal Time (UT). All time zones in EU change together.
#       In spring, clocks spring forward from 12:59am to 2:00am; in fall, clocks fall back from 1:59am to 1:00am
#       
###########################################################################################################################
$ESTDSTStart='2007-03-11 03:00'
$ESTDSTEnd='2007-11-04 01:00'
$ESTDSTEndChange='2007-11-04 02:00'



$GMTDSTStart='2007-03-24 21:00'  # EU started DST at 1am GMT on 3/25/07, which was 21:00 EST on 3/24/07.
$GMTDSTEnd='2007-10-27 21:00'   # EU ended DST at 1am GMT on 10/28/07, which was 21:00 EST on 10/27/07.


if ( $help ) {
        "Usage: dstcheck_global -baseEST <EST time>"
        exit 0
}
elseif ( $baseEST -notmatch '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$' ) {
        "You must enter a time in this format '2007-03-11 03:15'"
        exit 1
}


if (( $baseEST -gt $ESTDSTStart ) -and ( $baseEST -lt $ESTDSTEndChange )) {
        $ESToffset=240 # EST observes DST, so EST is 240 minutes (5 hours) behind UT


        if (( $baseEST -gt $ESTDSTEnd ) -and ( $baseEST -lt $ESTDSTEndChange )) {
                $flagdstend = read-host "Does DST end in EST: [Y/N]"



                if ( $flagdstend -ieq 'Y' )  {
                        $flagdstend='Y'
                        $ESToffset=300 # EST doesn't observe DST, so EST is 300 minutes (6 hours) behind UT
                }
                else {
                        $flagdstend='N' # EST observes DST, so EST is 240 minutes (5 hours) behind UT
                        $ESToffset=240
                }
        }
}       
else {
        $ESToffset=300 # EST doesn't observe DST, so EST is 300 minutes (6 hours) behind UT
}



Write-Debug "EST offset: $ESToffset"



$RPTError=".\dstcheck.exception.$pid"   # This is the exception file that contains the SQL Server instances that have incorrect time.
New-Item $RPTError -type file -force


##############################################################################
# now get the list of servers from the host file
##############################################################################


$serverfile=".\serverfile.txt"  # This file contains the list of SQL Server instances to check.


get-content $serverfile | foreach {


        Write-Debug $_


        # Check if we can connect with an instance
        osql -Q "quit" -S $_ -E | out-null


        if ( $LASTEXITCODE -ne 0 ) {
                "Cannot connect with $_" | Out-File -filePath $RPTError -append  # Reports the instances that we cannot connect with
        }
        else {
                ##########################################################################################################
                # Gets the bias from each SQL Server host. Bias is the normal time difference from UT, 
                # excluding daylight saving in minutes 
                ##########################################################################################################
                [String] $tempstr=( osql -Q "exec master..xp_cmdshell 'reg query HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation /v Bias | findstr -i Bias'" -S $_ -E | findstr -i 'Bias')    
                $temparr=[regex]::Split($tempstr, "[`t| ]+")
                
                $count = 0 
                foreach ($str in $temparr) { 
                        $count ++
                        if ($str -eq "Bias" ) { 
                                break
                        } 
                } 
        
                if ( $temparr[$count] -eq "REG_DWORD" ) {
                        $count ++
                }
                [Int] $bias = $temparr[$count]


                # If the bias starts with 0xF, for example, 0xFFFFFEB6, then it is actually negative. 
                # Time zones ahead of UT have negative bias.
                if ( $bias -ilike  "0xF*" )
                {
                        $bias = $bias - 4294967296
                }


                [int] $actualbias = $bias # $actualbias is the actual time difference between a time zone and UT
                                          # It is the same as $bias when the time zone doesn't observe DST, 
                                          # or 60 minutes less than $bias when the time zone does (see "$actualbias = $bias - 60" below)


                Write-Debug "bias: $bias"


                ##########################################################################################################
                # Gets the time zone from each SQL Server host.         
                ##########################################################################################################
                $tempstr=( osql -Q "exec master..xp_cmdshell 'reg query HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation /v StandardName | findstr -i StandardName'" -S $_ -E | findstr -i 'StandardName' )


                $standardName=( $tempstr.Replace("REG_SZ", "").Replace("StandardName", "").Trim() )
                
                Write-Debug "TimeZone: $standardName"


                ##############################################################################
                # Go through all the time zones in the US that observe DST
                ##############################################################################
        
                if (( $standardName -eq 'Eastern Standard Time' ) -and ( $baseEST -gt $ESTDSTStart ) -and ( $baseEST -lt $ESTDSTEndChange ))
                {
                        if ( "$flagdstend" -ne "Y" )
                        {
                                # When EST observes DST, the actual time difference between EST and UT should be 300-60=240 minutes
                                $actualbias = $bias - 60 
                        }
                }


                # CST springs forward at 3:59am EST, and falls back at 1:59am EST
                if ( $standardName -eq 'Central Standard Time' ) 
                {
                        if (( $baseEST -gt (GetTime $ESTDSTStart 1) ) -and ( $baseEST -lt (GetTime $ESTDSTEnd 1) ))
                        {
                                # When CST observes DST, the actual time difference between CST and UT should be 360-60=300 minutes
                                $actualbias = $bias - 60 
                        }
                }


                # MST springs forward at 4:59am EST, and falls back at 2:59am EST
                if ( $standardName -eq 'Mountain Standard Time' ) 
                {
                        if (( $baseEST -gt (GetTime $ESTDSTStart 2) ) -and ( $baseEST -lt (GetTime $ESTDSTEnd 2) ))
                        {
                                #  When MST observes DST, the actual time difference between MST and UT should be 420-60=360 minutes
                                $actualbias = $bias - 60
                        }
                }
                
                # PST springs forward at 5:59am EST, and falls back at 3:59am EST
                if ( $standardName -eq 'Pacific Standard Time' ) 
                {
                        if (( $baseEST -gt (GetTime $ESTDSTStart 3) ) -and ( $baseEST -lt (GetTime $ESTDSTEnd 3) ))
                        {
                                # When PST observes DST, the actual time difference between PST and UT should be 480-60=420 minutes
                                $actualbias = $bias - 60
                        }
                }


                ##############################################################################
                # Go through all the time zones in the EU that observe DST
                ##############################################################################


                if (( $standardName -eq 'GMT Standard Time' ) -or ( $standardName -eq 'W. Europe Standard Time' ) -or ( $standardName -eq 'Romance Standard Time' ) -or ( $standardName -eq 'GTB Standard Time' ))  
                {
                        if (( $baseEST -gt $GMTDSTStart ) -and ( $baseEST -lt $GMTDSTEnd ))
                        {
                                # When EU observes DST, the actual time difference between each time zone and 
                                # UT should be the normal time difference (bias) minus 60 minutes (1 hour).
                                $actualbias = $bias - 60
                        }
                }


                ##########################################################################################################
                # Calculate the time difference.
                # If the time on the SQL Server is correct, then baseEST + ESToffset + elasped time = UT = local time + bias
                # Therefore, the time difference between the given EST time and the local time of the SQL Server
                #               = baseEST + ESToffset + elasped time - local time - bias
                ##########################################################################################################
                $temparr=( osql -Q "select datediff(mi, getdate(), '$baseEST') + $ESToffset - ($actualbias) " -S $_  -E)
                [Double] $diffminutes=$temparr[2]


                $currenttime = get-date


                [Int] $elaspedminutes=(new-timespan -start $starttime -end $currenttime).totalminutes
        
                $diffminutes = $diffminutes + $elaspedminutes


                Write-Debug "elapsedminutes: $elaspedminutes; diffminutes: $diffminutes"


                if (( $diffminutes -gt 30 ) -or ( $diffminutes -lt -30 ))
                {
                        "$_ is $diffminutes minute(s) behind" | Out-File -filePath $RPTError -append
                }
        }
    }


exit 0