Databases Reference
In-Depth Information
Microsoft releases patches on a monthly basis and many require reboots. Therefore, it is always useful to
know when a SQL Server host was last rebooted — that is, its uptime. Uptime helps us decide when to
schedule a reboot.
As you learned in Chapter 15, the
Upsert-Host.ps1
script queries the
Win32_OperatingSystem
WMI class on each host, and updates the OS information in the
Hosts
table. One of the columns,
lastBootUpTime
, contains the last time each host was rebooted.
Let's create a script to call
Upsert-Host.ps1
on all the SQL Server hosts in our inventory. The
Update-
SQLServerHosts.ps1
script is shown here:
####################################################
# Source in our library file
####################################################
. C:\DBAScripts\dbaLib.ps1
[String] $strQuerySql
=
""
[String] $strCommand
=
""
# Construct Upsert-Host.ps1 commands for every host in the inventory
$strQuerySql
=
"SELECT 'C:\DBAScripts\Upsert-Host.ps1 -hostName '
+
hostName
+
' -region '
+
region
+
' -location '
+
location
+
' -primaryBU '"
+
primaryBU
+
'" -description '"
+
description
+
''" as Command
FROM dbo.Hosts"
$commands
=
Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $inventoryServer -
Database $inventoryDatabase
# Execute the Upsert-Host.ps1 commands
Foreach ($command in $commands) {
$strCommand
=
$command.Command
Invoke-Expression $strCommand
}
Execute the
Update-SQLServerHosts.ps1
script to update host information (see Figure 18-7):
C:\DBAScripts\Update-SQLServerHosts.ps1
As long as the host information is up-to-date, simply run the following query against the
Hosts
table and
you will get how many days, hours, and minutes the hosts have been up:
USE SQL_Inventory
SELECT Convert(char(20), GETDATE(), 20) AS [Now]
SELECT hostName, lastBootUpTime,
DATEDIFF(MINUTE, lastBootUpTime, GETDATE())/24/60 as 'Days',
DATEDIFF(MINUTE, lastBootUpTime, GETDATE())/60
- (DATEDIFF(MINUTE, lastBootUpTime, GETDATE())/24/60)*24 as 'Hours',
DATEDIFF(MINUTE, lastBootUpTime, GETDATE())
Search WWH ::
Custom Search