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.

Now imagine skipping the CSV step and exporting data directly to Excel, complete with visually appealing formatting. Wouldn't that save time and effort and open the door to automation?

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

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


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 an ugly error message!

PS C:\WINDOWS\system32> Install-Module -Name ImportExcel

Install-Module : Administrator rights are required to install modules in 'C:\Program Files\WindowsPowerShell\Modules'. Log on to the computer with an account that has 

Administrator rights, and then try again, or install 'C:\Users\Contaso\Documents\WindowsPowerShell\Modules' by adding "-Scope CurrentUser" to your command. You can also 

try running the Windows PowerShell session with elevated rights (Run as Administrator).

At line:1 char:1

+ Install-Module -Name ImportExcel

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidArgument: (:) [Install-Module], ArgumentException

    + FullyQualifiedErrorId : InstallModuleNeedsCurrentUserScopeParameterForNonAdminUser,Install-Module

Ugh... Local Administrator rights are required to install a module at the computer level.  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 -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.

Gather & Export SQL Server Instance Metadata to CSV with PowerShell and SMO

As a DBA, you are very likely to maintain an up-to-date inventory of all your SQL Servers. It is hard to imagine any environment where this is not the case. However, keeping that inventory current, whether manually or through automation, is another matter. You also need to capture and track detailed metadata about these SQL Servers, such as:

• What edition and version is each SQL Server instance running?
• How are instance‑level settings configured across all servers?
• Which sp_configure options differ from your standard baseline?

You can get these answers from SSMS or by running T‑SQL on each server, but that becomes painful once you manage more than a handful of instances. A simple PowerShell script can collect the same instance‑level metadata into CSV files that you can open in Excel or load into a reporting tool. This though is not meant to replace detailed configuration management or full CMDB tools, but it gives you a solid, scriptable foundation that you can extend over time.

My goal here is to have a reusable script that is easy to understand, easy to run, and easy to adapt as your environment grows.

---

Why use PowerShell and SMO for metadata?

SQL Server exposes a huge amount of metadata through catalog views, DMVs, and system functions. That’s great when you are writing T‑SQL, but it can be less convenient when you want to:

• Pull the same metadata from many instances.
• Store the results as files for later analysis or auditing.
• Automate the process so it runs on a schedule.

PowerShell gives you a scripting environment that is very good at automation, loops, and working with files. SMO SQL Server Management Objects is a .NET library that exposes SQL Server objects (like servers, databases, tables, and so on) as objects you can work with directly in PowerShell.

To keep the script focused and easy to understand, we will export only instance‑level metadata. That means our script will not touch database‑level or object‑level details like tables or indexes; it stays at the server/instance layer.

The script collects three categories of data:

• Information properties: These come from Server.Information in SMO and include things like version, edition, collation, and operating system information. They give you a high‑level description of the instance.

• Settings: These come from Server.Settings and describe how the instance is configured at a higher level (for example, settings that are not necessarily exposed through sp_configure).

• Configuration (sp_configure‑style): These come from Server.Configuration and map closely to what you see when you run sp_configure. They include both the current run values and the config values that take effect after a restart.

The script also adds a few helpful context columns to every CSV:

• Host name.
• Host IP address (IPv4).
• Number of databases on that instance.
• Server instance name (e.g., ServerA\SQL2019).

This extra information makes it easier to filter and group your data later.

---

Prerequisites and permissions

Before you run the script, make sure you have:

• PowerShell 5.1 or later, or PowerShell 7.x on your admin workstation or jump server.
• The SqlServer PowerShell module installed. This module includes SMO and is the recommended way to access SQL Server from PowerShell. You can install it with:

          Install-Module SqlServer -Scope CurrentUser

• Network connectivity to the SQL Server instance(s) you want to query.
• Enough permissions on SQL Server. In most cases, you’ll want to be a member of the sysadmin role or at least have permissions to view server‑level metadata.

Note that modern SQL Server versions use metadata visibility rules, so if your login does not have the required permissions, some properties may be hidden.

---

The PowerShell script: 

As the script is bit lengthy, you can download it from the GitHub:

SQL Server Metadata Export Script

You can save this script as Export-InstanceMetadata.ps1.

This script keeps the logic straightforward:

• It uses parameters so you can pass in the instance name, output folder, and optional SQL credentials.
• It makes sure the output folder exists.
• It loads the SqlServer module (and fails fast with a clear message if it’s missing).
• It builds wide PSCustomObjects where each property corresponds to a metadata field, and each row corresponds to a specific instance.
• It exports three CSV files, one for information, one for settings, and one for configuration values.

---

Running the script: Basic examples

Once you save the script (for example, as Export-InstanceMetadata.ps1), you can run it in several ways.

Single instance with Windows authentication

If your current Windows account has permissions on the SQL instance, you can simply run:

.\Export-InstanceMetadata.ps1 `
    -ServerInstance "SQL01","SQL02\INST1","SQL03" `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

This will: Connect to SQL Server using Windows authentication.

• Write three CSV files into C:\SQL\Metadata:
• SQLInstanceMetadata_Info.csv
• SQLInstanceMetadata_Settings.csv
• SQLInstanceMetadata_Config.csv
• Show progress messages because we used -Verbose.

Using SQL authentication

If you need to use a SQL login instead of Windows auth, you can pass a credential object:

$cred = Get-Credential  # enter SQL login name and password
.\Export-InstanceMetadata.ps1 `
    -ServerInstance "ProdSql01" `
    -OutputFolder "C:\SQL\Metadata" `
    -SqlCredential $cred `
    -Verbose

The script will then connect using that login and password, while the rest of the logic stays the same.

Scaling out: Multiple instances in a loop

The real power of this approach shows up when you have many instances. You can keep a simple text file of instance names and loop through them.

For example, suppose you have a file C:\SQL\instances.txt with one instance per line:

SQLServer1\SQL2019
ProdSql01
ProdSql02
TestSql0

You can run:

$instances = Get-Content "C:\SQL\instances.txt"
.\Export-InstanceMetadata.ps1 `
    -ServerInstance $instances `
    -OutputFolder "C:\SQL\Metadata" `
    -Verbose

---

Working with the CSV output

The script produces three “wide” CSV files per instance. That means:

• Every row represents exactly one instance (or one instance + value type in the config file).
• Every column represents a property (for example, Edition, Version, IsClustered, or max server memory (MB)).
• This layout is especially friendly for tools like:
• Excel or Power BI, where you can quickly filter, sort, and build simple charts.
• A central repository or inventory database, where you might append or merge results from multiple runs to track changes over time.

For example, you can:

• Compare max server memory (MB) across all instances to spot outliers.
• Filter by Version to see which servers are still running out of support version of SQL Server.
• Track VersionString to know which instances need patching.

If you prefer a “long” format (one row per property per instance), you can easily transform the CSV later in PowerShell or another tool, but starting with the wide format makes it easy for junior DBAs to explore the data.

When should you use this approach?

This instance‑metadata export is most useful when you want to:

• Build a quick inventory of instance‑level configuration across many servers.
• Capture a snapshot before and after making changes to compare configuration values.
• Feed metadata into another process or review tool without logging in to SSMS for each server.

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 and view the list databases displayed in the Object Explorer or, query the sys.databases and get the 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"

Note: To run the above Get-WmiObject, you will need to have the Local Administrator access on the target computer. Good thing is that you can run this remotely and don't need to be logged into the remote server.

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 Get-SqlDatabase 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.

The PowerShell 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"

Note: The script assumes that you are using Windows Authentication to connect to SQL Server and have necessary permissions to query the SQL metadata.

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.