Powershell script to find Windows Server reboot date

• Sreejith!

  SSCrazy

  Points: 2401

  More actions

  October 16, 2015 at 9:23 am

  #399199

  Hello

  I am searching for a Powershell script which picks Windows Server names from SQL server table(eg: Instance.DB.tbServerList) & writes last reboot date to SQL server table(can be same or different table)

  Can someone help me in this?

  Cheers,

  Sree

• SpeedySQL

  Ten Centuries

  Points: 1019

  More actions

  October 23, 2015 at 6:01 am

  #1835133

  --Create test database and table.

  CREATE DATABASE Test

  GO

  USE Test

  GO

  CREATE TABLE dbo.tbServerList (Srv sysname, lastRebootDate datetime)

  GO

  --Replace server names as required.

  INSERT dbo.tbServerList (Srv)

  VALUES ('Srv1'),('Srv2');

  GO

  /** Run PowerShell script **/

  --Check results.

  SELECT

  Srv

  ,lastRebootDate

  FROM dbo.tbServerList;

  GO

  Here is the PowerShell script.

  $inst = 'yourServer'

  $DB = 'Test'

  #Import the sqlps module to allow use of invoke-sqlcmd.

  Import-Module “sqlps” -DisableNameChecking

  $srvList = invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SELECT Srv FROM dbo.tbServerList;" | %{'{0}' -f $_[0]}

  foreach ($srv in $srvList)

  {

  $rebootDate = invoke-sqlcmd –ServerInstance $srv -Query "SELECT sqlserver_start_time FROM sys.dm_os_sys_info;" | %{'{0}' -f $_[0]}

  invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SET dateformat dmy; UPDATE dbo.tbServerList SET lastRebootDate = '$rebootDate' WHERE Srv = '$srv';"

  }

  SpeedySQL

• Sreejith!

  SSCrazy

  Points: 2401

  More actions

  October 29, 2015 at 10:25 am

  #1836447

  Thanks for your reply:-)Very useful script..:-):-):-)

  The code is showing the SQL server service restart date & time. But I am looking for server reboot time?Is it possible to tweak the same code to show server reboot time?

  Kind Regards,

  Sree

• SpeedySQL

  Ten Centuries

  Points: 1019

  More actions

  October 29, 2015 at 11:53 am

  #1836475

  Try this:

  $inst = 'yourserver'

  $DB = 'Test'

  #Import the sqlps module to allow use of invoke-sqlcmd.

  Import-Module “sqlps” -DisableNameChecking

  $srvList = invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SELECT Srv FROM dbo.tbServerList;" | %{'{0}' -f $_[0]}

  foreach ($srv in $srvList)

  {

  $os = Get-WmiObject win32_OperatingSystem

  $rebootDate = $os.ConvertToDateTime($os.lastbootuptime)

  invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SET dateformat mdy; UPDATE dbo.tbServerList SET lastRebootDate = '$rebootDate' WHERE Srv = '$srv';"

  }

  SpeedySQL

• Sreejith!

  SSCrazy

  Points: 2401

  More actions

  October 30, 2015 at 6:35 am

  #1836650

  Thanks for the quick response..:)

  I tried to implement the same. lastRebootDate column for all servers is updated with the reboot time of first server in the table

  Srv lastRebootDate

  ----- ------------------------------

  Srv12015-10-18 04:20:12.000

  Srv22015-10-18 04:20:12.000

  Srv32015-10-18 04:20:12.000

  Could you please help?

• SpeedySQL

  Ten Centuries

  Points: 1019

  More actions

  October 30, 2015 at 11:19 am

  #1836778

  Sorry, I had removed the -ComputerName switch during testing.

  $inst = 'yourServerName'

  $DB = 'Test'

  #Import the sqlps module to allow use of invoke-sqlcmd.

  Import-Module “sqlps” -DisableNameChecking

  $srvList = invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SELECT Srv FROM dbo.tbServerList;" | %{'{0}' -f $_[0]}

  foreach ($srv in $srvList)

  {

  $os = Get-WmiObject win32_OperatingSystem -ComputerName $srv

  $rebootDate = $os.ConvertToDateTime($os.lastbootuptime)

  invoke-sqlcmd –ServerInstance $inst -Database $DB -Query "SET dateformat mdy; UPDATE dbo.tbServerList SET lastRebootDate = '$rebootDate' WHERE Srv = '$srv';"

  }

  SpeedySQL

• johnwalker10

  SSCrazy Eights

  Points: 9124

  More actions

  November 1, 2015 at 10:02 pm

  #1836979

  Run this to see all your history... and no UI necessary 🙂

  get-eventlog System | where-object {$_.EventID -eq "6005"} | sort -desc TimeGenerated

• Sreejith!

  SSCrazy

  Points: 2401

  More actions

  November 9, 2015 at 3:35 pm

  #1838717

  The modified script worked for me....:-):-)Thank you very much 🙂

• Sreejith!

  SSCrazy

  Points: 2401

  More actions

  November 14, 2015 at 5:16 am

  #1839800

  Hey..I have one question..what happens if any of the server mentioned in the server list is down?I cannot test it by bringing one of the server down;-)

• SoHelpMeCodd

  SSCertifiable

  Points: 5871

  More actions

  November 14, 2015 at 10:55 am

  #1839819

  Add a fictitious server name