PowerShell script to get SQL Server information.
##################################################################
#
# Name: ServerInformation.ps1
# Author: Omid Afzalalghom
# Date: 07/10/2015
# Requires: PS Version 2, SQL Tools.
# Revisions:
##################################################################
#Requires --Version 2.0
#Import the sqlps module to allow use of invoke-sqlcmd.
Import-Module "sqlps" -DisableNameChecking -WarningAction SilentlyContinue
#Assign variable values.
$inst = $args[0]
$dir = $args[1]
$reports = "$($args[1])Reports\Temp"
try
{
$ErrorActionPreference = "Stop";
$memOS = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CEILING(physical_memory_kb/1024./1024) FROM sys.dm_os_sys_info;" | %{'{0}' -f $_[0]}
$memSQLMax = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CEILING(CAST(value AS INT)/1024.) FROM sys.configurations WHERE name = 'max server memory (MB)';" | %{'{0}' -f $_[0]}
$memSQLCurr = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CEILING(cntr_value/1048576.) FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)';" | %{'{0}' -f $_[0]}
$memTokenStore = invoke-sqlcmd --ServerInstance $inst -Query "SELECT SUM(pages_kb)/1024 MB FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND pages_kb > 8;" | %{'{0}' -f $_[0]}
$NetBiosName = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('ComputerNamePhysicalNetBIOS'));" | %{'{0}' -f $_[0]}
$MachineName = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('MachineName'));" | %{'{0}' -f $_[0]}
$ServerName = invoke-sqlcmd --ServerInstance $inst -Query "SELECT @@SERVERNAME;" | %{'{0}' -f $_[0]}
$InstanceName = invoke-sqlcmd --ServerInstance $inst -Query "SELECT ISNULL(CONVERT(sysname, SERVERPROPERTY ('InstanceName')), 'Default');" | %{'{0}' -f $_[0]}
$Edition = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('Edition'));" | %{'{0}' -f $_[0]}
$ProductVersionName = invoke-sqlcmd --ServerInstance $inst -Query "SELECT SUBSTRING(@@VERSION, 1, (PATINDEX('%)%', @@VERSION)));" | %{'{0}' -f $_[0]}
$ProductLevel = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('ProductLevel'));" | %{'{0}' -f $_[0]}
$ProductVersion = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('ProductVersion'));" | %{'{0}' -f $_[0]}
$Reboot = invoke-sqlcmd --ServerInstance $inst -Query "SELECT sqlserver_start_time FROM sys.dm_os_sys_info;" | %{'{0}' -f $_[0]}
$Clustered = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('IsClustered'));" | %{'{0}' -f $_[0]}
$FullText = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('IsFullTextInstalled'));" | %{'{0}' -f $_[0]}
$Authentication = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CASE WHEN CONVERT(sysname, SERVERPROPERTY ('IsIntegratedSecurityOnly')) = 1 THEN 'Windows' ELSE 'Mixed' END;" | %{'{0}' -f $_[0]}
$ProcessID = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('ProcessID'));" | %{'{0}' -f $_[0]}
$Collation = invoke-sqlcmd --ServerInstance $inst -Query "SELECT CONVERT(sysname, SERVERPROPERTY ('Collation'));" | %{'{0}' -f $_[0]}
$MaxWorkers = invoke-sqlcmd --ServerInstance $inst -Query "SELECT max_workers_count FROM sys.dm_os_sys_info;" | %{'{0}' -f $_[0]}
$CurrWorkers = invoke-sqlcmd --ServerInstance $inst -Query "SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers;" | %{'{0}' -f $_[0]}
$CPUproperty = "Name","CurrentClockSpeed","MaxClockSpeed","NumberOfCores"
$cpu = Get-WmiObject Win32_Processor -ComputerName $MachineName -Property $CPUproperty | Select-Object -Property $CPUproperty | sort -unique
$comp = Get-WmiObject -class Win32_ComputerSystem -ComputerName $MachineName | Select-Object -Property NumberOfLogicalProcessors, NumberOfProcessors, Manufacturer, Model
$power = Get-WmiObject -Class Win32_PowerPlan -ComputerName $MachineName -Namespace root\cimv2\power -fi "isactive = 'true'"| select-object ElementName
$os = Get-WmiObject Win32_OperatingSystem -ComputerName $MachineName | select Caption, OSArchitecture
$bios = Get-WmiObject Win32_bios -ComputerName $MachineName |select-object Manufacturer, Name, Version
#Is hyperthreading enabled?
if ($comp.NumberOfProcessors * $cpu.NumberOfCores -lt $comp.NumberOfLogicalProcessors)
{
$HT = "Enabled"
}
else
{
$HT = "Disabled"
}
#Write custom table to temporary text file.
@"
"Item", "Value"
"Manufacturer","$($comp.Manufacturer)"
"Model","$($comp.Model)"
"CPU Type","$($cpu.Name)"
"Physical Processors","$($comp.NumberOfProcessors)"
"Cores Per Processor","$($cpu.NumberOfCores)"
"Total Logical Processors","$($comp.NumberOfLogicalProcessors)"
"Current Clock Speed","$($cpu.CurrentClockSpeed)"
"Max Clock Speed","$($cpu.MaxClockSpeed)"
"Power Plan","$($power.ElementName)"
"Hyperthreading","$($HT)"
"OS","$($os.Caption)"
"OS Architecture","$($os.OSArchitecture)"
"BIOS Manufacturer","$($bios.Manufacturer)"
"BIOS Name","$($bios.Name)"
"BIOS Version","$($bios.Version)"
"Physical Memory (GB)","$($memOS)"
"Max SQL Memory (GB)","$($memSQLMax)"
"Current SQL Memory (GB)","$($memSQLCurr)"
"TokenPermStore (MB)","$($memTokenStore)"
"NetBios Name","$($NetBiosName)"
"Machine Name","$($MachineName)"
"Server Name","$($ServerName)"
"Instance Name","$($InstanceName)"
"Version Name","$($ProductVersionName)"
"Edition","$($Edition)"
"Product Level","$($ProductLevel)"
"Product Version","$($ProductVersion)"
"SQL Server Last Rebooted","$($Reboot)"
"Is Clustered","$($Clustered)"
"Is Full-Text Installed","$($FullText)"
"Authentication","$($Authentication)"
"ProcessID","$($ProcessID)"
"Collation","$($Collation)"
"Max Workers","$($MaxWorkers)"
"Current Workers","$($CurrWorkers)"
"@ | Out-File "$reports\temp.txt"
#Export text file to CSV file.
$txt = Import-Csv "$reports\temp.txt"
write-output $txt |Export-Csv -NoTypeInformation -Path "$reports\ServerInformation.csv"
#Remove temporary text file.
get-childitem -path "$reports\temp.txt" | remove-item
}
catch
{
Write-output "$(Get-Date --f G) - Error: ServerInformation.ps1 - $($_.Exception.Message)" | out-file -filepath "$dir\ErrorLog.txt" -append
}