A simple powershell script to look up server hardware and OS information

This is a simple (IMHO) PowerShell script to query and display hardware and OS information from a remote computer.

It uses the CIM (Common Information Model) at first, as that is available since Powershell version 3 and is the recommended direction.  Please see the following article on why "we" should use CIM instead of the WMI.

https://devblogs.microsoft.com/scripting/should-i-use-cim-or-wmi-with-windows-powershell/

---

# Specify the server name here

$server         = "server1"


# pull all the information
$hardware         = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $server
$OS               = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $server
$CPU              = Get-CimInstance -ClassName Win32_Processor -ComputerName $server
$PhysicalMemory   = Get-CimInstance -ClassName CIM_PhysicalMemory -ComputerName $server
$Bios             = Get-CimInstance -ClassName Win32_BIOS -ComputerName $server

$total_memory = ($PhysicalMemory | measure-object -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | fl

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

---

Caveat:

That worked on most servers but on some I ran into an error with the CIM.

So I tried the solution suggested by the error message, which is to run the winrm quickconfig on the remote computer.  That threw message that the "WinRM service is already running on this machine", so maybe there is GPO, firewall or something else that is blocking it. 

So I decided to go back to the good old faithful WMI for those servers. The powershell methods are still interchangeable between CIM and WMI so all it took was to do a global search/ replace for  Get-CimInstance / Get-WmiObject.

# Specify the server name here

$server         = "server1"


# pull all the information
$hardware         = Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $server
$OS               = Get-WmiObject -ClassName Win32_OperatingSystem -ComputerName $server
$CPU              = Get-WmiObject -ClassName Win32_Processor -ComputerName $server
$PhysicalMemory   = Get-WmiObject -ClassName CIM_PhysicalMemory -ComputerName $server
$Bios             = Get-WmiObject -ClassName Win32_BIOS -ComputerName $server

$total_memory = ($PhysicalMemory | measure-object -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | fl

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

Simple enough?  Nah... but I am sticking with the title!

Download both versions of this script from GitHub:

PowerShell-WMI-Get-Hardware-OS-Specs

PowerShell-CMI-Get-Hardware-OS-Specs

Get email alert when number of queries waiting for CPU exceeds thresold

You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available.

This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold.  Please update the value for the @operator variable to whatever is the email operator you have setup in the SQL Server Agent.

And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment.

Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs.

USE [msdb]
GO
declare @operator varchar(500)             -- email operator name
declare @threshold int                     -- number of queries waiting for the CPU
declare @delay_between_email_alerts int    -- this value is in seconds 
declare @drop_alert_if_exists bit          -- drops and recreates the alert if already exists

--  Assign default values to variables
set @operator                   = 'DBA'
set @threshold                  = 10
set @delay_between_email_alerts = 900
set @drop_alert_if_exists       = 0


declare @sql_add_alert nvarchar(4000)
declare @sql_add_notification nvarchar(4000)
declare @sql_drop_alert_if_exists nvarchar(4000)

if @drop_alert_if_exists = 1
begin
  if exists (select * from msdb..sysalerts where name = 'Alert: Number of processes waiting for CPU exceeded thresold')
    EXEC msdb.dbo.sp_delete_alert @name=N'Alert: Number of processes waiting for CPU exceeded thresold'
end

set @sql_add_alert =
'EXEC msdb.dbo.sp_add_alert @name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=' + cast(@delay_between_email_alerts as nvarchar(10)) + ', 
  @include_event_description_in=1, 
  @category_name=N''[Uncategorized]'', 
  @performance_condition=N''Wait Statistics|Wait for the worker|Waits in progress|>|' + cast(@threshold as nvarchar(10)) + ''', 
  @job_id=N''00000000-0000-0000-0000-000000000000''
'
print @sql_add_alert 
exec(@sql_add_alert)
set @sql_add_notification = 
'EXEC msdb.dbo.sp_add_notification 
                @alert_name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
                @operator_name=N''' + @operator + ''', 
                @notification_method = 1
'
print @sql_add_notification
exec(@sql_add_notification)
go

Powershell one liner to export data directly from SQL Server to Excel

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually.

But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to?

No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site.

https://www.powershellgallery.com/packages/ImportExcel/5.2.0


You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell

If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x version and used the following command to register it.

Register-PSRepository -Default

If that does not work then try the full command:

Register-PSRepository -Name "myNuGetSource" -SourceLocation "https://www.myget.org/F/powershellgetdemo/api/v2" -PublishLocation "https://www.myget.org/F/powershellgetdemo/api/v2/Packages" -InstallationPolicy Trusted

Next I checked and it was now registered as an Un-Trusted source. Either I would need to add the -Force parameter to the Import-Module command or it will prompt me to confirm if I trust the source.

I decided to update the InstallationPolicy for it by issuing the command Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted to update that setting.

So far so good, great.

I copied the powershell command provided at the Powershell Gallary site  to import the module.

Right off the bat I get the following error message.

Now I could relaunch the powershell as Administrator but since I am only testing/playing around, I decided to take up the second suggestion in the error message and add the -Scope Currentuser to the import-module command.

Install-Module -Name ImportExcel -RequiredVersion 5.2.0 -Scope CurrentUser

Success!

And finally, the one liner to export the query output to Excel.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -Path sc.xlsx

At the minimum, you need to specify the Connection string, connection type (MsSQLServer), SQL query and the Excel file name.

Here is another example of same with some additional parameters.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path sc.xlsx

If you would like to explore all the parameters, options etc. of Send-SQLDataToExcel look up the complete help file in the powershell:

get-help Send-SQLDataToExcel

Partial Screen Shot:

And if you would like to explore all the things you can do with the ImportExcel module:

Get-Command -Module ImportExcel

OR

(Get-Module ImportExcel).ExportedCommands

I must admit that this is a two or three pages blog for something I claim to be a one-liner. But we need Excel module in order for the one liner to work. I did not have it in my PowerShell so I also included the steps I followed to get it.

SQL Server Metadata using Powershell

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible.

Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata.

And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups.

So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis.

If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers.

<#
Export SQL Server properties, settings and configuration values to CSV files
#>

# name of the sql server instance you would like to gather information about
$instanceName = "SQLServer1\SQL2016AG01"

# the folder where the csv files will be stored, by default it will be the current folder
$folder_path = ""

if ($folder_path -eq "" -or $folder_path -eq $null)
{
        $folder_path = (Get-Item -Path ".\").FullName
}

"Folder Path: " + $folder_path

# variables to hold the names of the csv files where information will be stored

$export_file_name_info     = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_info.csv'
$export_file_name_settings = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_settings.csv'
$export_file_name_config   = $folder_path + "\" +  ($instanceName -replace '\\', '_') + '_config.csv'


# Load the sql server assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# connect to the sql instance and gather some basic info
# Notice that I have not specified SQL credentials as I would like to use the current user's windows credentials
$instance_object = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$host_name       = $instance_object.NetName
$host_ip         = ([System.Net.Dns]::GetHostAddresses("$host_name") | ? {$_}).IPAddressToString
$db_count=$instance_object.Databases.count

"Host Name: "  + $host_name
"IP Address: " + $host_ip
"Databases: "  + $db_count

# Load up all the information into arrays
$sql_instance_info     = $instance_object.Information.Properties |Select Name, Value 
$sql_instance_settings = $instance_object.Settings.Properties |Select Name, Value 
$sql_instance_config   = $instance_object.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue

$sql_instance_info
$sql_instance_settings
$sql_instance_config

<#
Here I can simply export these arrays to respective CSV files but then it will be in the tabular format.
But I want the information to be displayed in wide (transposed) format.
That way if I want to gather information on multiple servers and stored in the same files, I can do that.

It takes little bit of additional coding but I think the results are worth it.
#>



# SQL Server properties

$info = New-Object -TypeName PSObject

$info | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$info | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$info | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

foreach ($info_value in $sql_instance_info) 
{


    $Name  = $info_value.Name
    $value = $info_value.Value
    $info  | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server settings

$settings = New-Object -TypeName PSObject

$settings | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$settings | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$settings | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($settings_value in $sql_instance_settings) 
{
   $Name     = $settings_value.Name
   $value    = $settings_value.Value
   $settings | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server configuration (sp_configure)
# we need two custom objects here to store the configured as well as run values from sp_configure
$config_value   = New-Object -TypeName PSObject
$run_value      = New-Object -TypeName PSObject


$config_value | Add-Member -MemberType NoteProperty -Name ValueType -Value "Config Value"
$run_value    | Add-Member -MemberType NoteProperty -Name ValueType -Value "Run value"

$config_value | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$config_value | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$config_value | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

$run_value    | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$run_value    | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$run_value    | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($c in $sql_instance_config) 
{
   $DisplayName  = $c.DisplayName
   $Description  = $c.Description
   $RunValue     = $c.RunValue
   $ConfigValue  = $c.ConfigValue
   $config_value | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $ConfigValue
   $run_value    | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $RunValue
   
   
   
}

# combine the config and run values in a single array
$config = @($config_value,$run_value)


# export the arrays into respective CSV files

$info     | Export-Csv -Path $export_file_name_info -NoTypeInformation
$settings | Export-Csv -Path $export_file_name_settings -NoTypeInformation
$config   | Export-Csv -Path $export_file_name_config -NoTypeInformation

Powershell script to get list of databases on a server

At one of my clients I received an email from one of the IT Project Managers asking a simple question:

"Can you please let us know which databases reside on the server below, Server1?"

First thought in mind, well from what particular sql instance on that server? It was our general practice to install multiple instances on a server. But at that moment I was not even sure if that server has multiple instance, is it a stand alone sever or a node/virtual name of a cluster server, alwayson cluster etc...

But, I kept that thought to myself.

Now, I could launch SSMS, connect to the sql instance, query the sys.databases and get requested information.

But I don't know the instance name top of my head. So I would need to RDP into the server or look up the meta data somewhere else, like maybe a spreadsheet with database inventory, assuming its up to date.

Instead of that, I decide to launch the Powershell and issue this command:

Get-WmiObject -Query "select * from win32_service `

where PathName like '%%sqlservr.exe%%'" `

-ComputerName "Server1"

It has only one sql instance, great.

Then I issued the following command to grab the databases on that list and send him the results.

Get-SqlDatabase -ServerInstance Server1

OR - To search for a specific pattern in the database name:

Get-SqlDatabase -ServerInstance Server1 | 

Where-Object {$_.Name -like '*report*'}

That last command requires the SqlServer module loaded, which I already have in my powershell startup.

Import-Module -Name SqlServer

But to add bit more value to this blog, I decided to turn this into a small PowerShell script.

PS variable $server_name is where you specify the server  name where you would like to look up database names. If that value is not specified, it will use the local computer name.

try
{
    Import-Module -Name SqlServer -ErrorAction Stop

    $server_name   = "Server1" # SERVER/HOST NAME HERE  
    $database_name = "admin"   # NAME OF THE DATABASE YOU WOULD LIKE TO SEARCH OTHERWISE LEAVE THIS BLANK
    $exact_match   = "N"       # WHETHER TO SEARCH FOR AN EXACT DATABASE NAME

    $logfile = "$env:TEMP\logfile_" + (Get-Date).toString("yyyyMMdd_HHmmss") + ".txt"
    
    "Start Time: " + (Get-Date)  | Out-File -Append $logFile
    "Log: $logfile"  | Out-File -Append $logFile
    ""  | Out-File -Append $logFile
    "Server: $server_name" | Out-File -Append $logFile
    "Database: $database_name" | Out-File -Append $logFile
    "Exact Match: $exact_match" | Out-File -Append $logFile
    ""  | Out-File -Append $logFile


    if ($server_name -eq "" -or $server_name -eq $null)
    {
        $server_name = $env:computername
    }

    $sql_services = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "$server_name" -ErrorAction Stop

    foreach ($sql_service in $sql_services) 
    {
        $instance_name = $sql_service.Name -replace "MSSQL\$", ""
        if ($sql_service.State -eq "Running")
        {
            $sql_connection = if ($instance_name -eq "MSSQLSERVER") { $sql_service.PSComputerName } else { $sql_service.PSComputerName + "\" + $instance_name }

            if ($database_name -eq "")
            {
                Get-SqlDatabase -ServerInstance $sql_connection | 
                    FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
            }
            else
            {

                if ($exact_match -eq "Y")
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -eq $database_name} | 
                        FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
                }
                else
                {
                    Get-SqlDatabase -ServerInstance $sql_connection | Where-Object {$_.name -like "*$database_name*"} | 
                        FT Parent, Name, Owner, ReadOnly, RecoveryModel, Size, Status, UserAccess
                }
            }


        }
        else
        {
            "Skipping $sql_connection as it's not running..." | Out-File -Append $logFile
        }
    }

    ""  | Out-File -Append $logFile
    "Completion Time: " + (Get-Date)  | Out-File -Append $logFile
  
}
Catch
{
    $errorMessage = (Get-Date).ToString() + ": Error Occurred - " + $_.Exception.Message
    $errorMessage | Out-File -Append $logFile
    throw
}

"# Launch the notepad.exe to view the log file"
"notepad.exe $logfile"

Download this PowerShell script from GitHub at:

Find SQL Databases

 
I have conducted some initial testing on this script, but it has not undergone extensive or thorough testing. Additionally, I have not tested it on SQL servers hosted on Linux platforms. Therefore, I would greatly appreciate your feedback. If you decide to test this script, please feel free to share your thoughts, experiences, and suggestions for enhancements, or point out any errors or issues you encounter.