This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
windows:powershell:updatehost [2013/11/04 21:14] haidong Change SysMetrics to JiMetrics |
windows:powershell:updatehost [2018/05/14 22:00] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | Here is a PowerShell script to gather Windows host info such as OS version, hardware model, memory size, CPU type, core count. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get. | + | Here is a PowerShell script to gather Windows host info such as OS version, hardware model, memory size, CPU type, core count, BIOS version, BIOS release date, Host hardware serial number. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to in the local administrator's group on the host whose metrics it is trying to get. |
Server host information normally don't change that often, so it doesn't need to be run daily. Depending on your environment, schedule this to run monthly or even quarterly. You can always manually kick off this job if needed. | Server host information normally don't change that often, so it doesn't need to be run daily. Depending on your environment, schedule this to run monthly or even quarterly. You can always manually kick off this job if needed. | ||
Line 6: | Line 6: | ||
<code powershell updateHost.ps1> | <code powershell updateHost.ps1> | ||
- | $HostList = Invoke-Sqlcmd -Query "exec Windows.Host_Select_HostID_HostName" -ServerInstance "sql1" -Database "JiMetrics" | + | function updateHostSQL($h) { |
- | $HostList | ForEach-Object { | + | $HostName = $h.HostName |
- | $HostName = $_.HostName | + | $HostID = $h.HostID |
- | $HostID = $_.HostID | + | |
try { | try { | ||
$WmiResults = get-wmiobject -computername $HostName -Class Win32_ComputerSystem | $WmiResults = get-wmiobject -computername $HostName -Class Win32_ComputerSystem | ||
+ | $BiosResults = get-wmiobject -computername $HostName -Class Win32_BIOS | ||
} | } | ||
catch [Exception] { | catch [Exception] { | ||
Line 22: | Line 22: | ||
$HardwareModel = $WmiResults.Model | $HardwareModel = $WmiResults.Model | ||
$MemorySizeGB = ($WmiResults.TotalPhysicalMemory / 1gb) + 1 | $MemorySizeGB = ($WmiResults.TotalPhysicalMemory / 1gb) + 1 | ||
+ | | ||
+ | $SMBiosVersion = $BiosResults.SMBIOSBIOSVersion | ||
+ | $BiosReleaseDate = $BiosResults.ReleaseDate.substring(0, 8) | ||
+ | $SerialNumber = $BiosResults.SerialNumber | ||
try { | try { | ||
Line 45: | Line 49: | ||
$OSVersionNumber = $WmiResults.Version | $OSVersionNumber = $WmiResults.Version | ||
- | $sql = "EXEC Windows.Host_Update $HostID, '$Domain', '$OS', '$OSArchitecture', '$OSServicePack', '$OSVersionNumber', '$HardwareModel', '$HardwareVendor', $MemorySizeGB, '$CPUType', $CoreCount" | + | $sql = "EXEC Windows.Host_Update $HostID, '$Domain', '$OS', '$OSArchitecture', '$OSServicePack', '$OSVersionNumber', '$HardwareModel', '$HardwareVendor', $MemorySizeGB, '$CPUType', $CoreCount, '$SMBiosVersion', '$BiosReleaseDate', '$SerialNumber'" |
+ | return $sql | ||
+ | } | ||
+ | |||
+ | $HostList = Invoke-Sqlcmd -Query "exec Windows.Host_Select_HostID_HostName" -ServerInstance "sql1" -Database "JiMetrics" | ||
+ | $HostList | ForEach-Object { | ||
+ | $sql = updateHostSQL($_) | ||
Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics" | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics" | ||
} | } | ||
</code> | </code> |