Automate Gathering SQL Server Information and CPU Count

By:   |   Comments (2)   |   Related: More > Database Administration


You're truing-up your SQL Server licenses and need a list containing CPU core information plus version, edition, etc. for each of your SQL Servers. Ideally you would like it ordered by server name in a nice neat .csv file. How can this be accomplished?


The following PowerShell script will create a .csv file with version, edition and CPU information based on a list of SQL Server names in a text file.

It was tested on PowerShell version 5 against SQL Server versions 2008R2, 2012, 2014, 2016, 2017 and 2019 / US English.

script name: GetServerInfoAndCpuCount.ps1
Writes SQL Server name, version, edition, Service Pack level, CU level, version number, number of sockets, number of 
cores per socket and number of cores from a list of SQL Servers specified in a file to a .csv file.
$workdir = 'C:\scripts\GetServerInfoAndCpuCount' # change if you're using a directory other than C:\scripts\GetCpuCount
$outfile = 'ServerInfoAndCpuCount.csv'           # output file name
$servers = '.\servers.txt'                       # list of your SQL Servers
$EmailOn = 'n'                                   # y to email logs (case insensitive)
# $Email variables must be populated if $EmailOn = 'y'  
$PSEmailServer = ''  # mail server 
$EmailFrom = 'mailfrom@' # mail from - must be populated with properly formatted ([email protected]) but doesn't have to be real 
$EmailTo = 'mailfrom@ '  # mail to
$EmailSubject = 'ServerInfoAndCpuCount'   # mail subject
Set-Location $workdir
$sqlservers = Get-Content $servers | Sort-Object # read in and sort to order output 
# sql query
$sql = "
DECLARE @ProductVersion NVARCHAR(30)
SET @ProductVersion = CONVERT(NVARCHAR(20),SERVERPROPERTY('ProductVersion')) 
SELECT @ProductVersion = 
      CASE SUBSTRING(@ProductVersion,1,4)
         WHEN '15.0' THEN 'SQL Server 2019'
         WHEN '14.0' THEN 'SQL Server 2017' 
         WHEN '13.0' THEN 'SQL Server 2016' 
         WHEN '12.0' THEN 'SQL Server 2014' 
         WHEN '11.0' THEN 'SQL Server 2012' 
         WHEN '10.5' THEN 'SQL Server 2008 R2' 
         WHEN '10.0' THEN 'SQL Server 2008'  
       @ProductVersion AS ProductVersion,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
       SERVERPROPERTY('ProductVersion') AS Version,
       cpu_count/hyperthread_ratio AS [Sockets], 
       hyperthread_ratio AS [CoresPerSocket], 
       cpu_count AS [Cores] 
FROM sys.dm_os_sys_info
# delete old output file if it exists
If (Test-Path $outfile){Remove-Item $outfile}
# gather info from each server in file and export to .csv
Foreach ($ss in $sqlservers) 
   Invoke-Sqlcmd -ServerInstance $ss -Query $sql | Export-Csv $outfile -NoTypeInformation -Append
# email $outfile
If ($EmailOn -eq 'y') 
   Send-MailMessage -From "$EmailFrom" -To "$EmailTo" -Subject "$EmailSubject" -Attachments "$outfile"

For our example I've created a directory called GetServerInfoAndCpuCount under C:\scripts. This matches the script variables above.

Create C:\scripts\GetServerInfoAndCpuCount.ps1 from the above PowerShell code.

Next, create a file under C:\scripts\GetServerInfoAndCpuCount\ called servers.txt and enter each SQL Server name on its own line.

Servers for the PowerShell script to run against

That's it. Run the script.

Running the PowerShell script
CSV Output file from the PowerShell script

Open the output file ServerInfoAndCpuCount.csv. It shows us SQL Server names in alphabetical order, SQL Server version, Edition, Service Pack level, CU (if applied), number of CPU sockets, number of CPU Cores per socket, and number of CPU cores.

CSV file with server information

Automate Data Collection

This is fine for an ad-hoc query, but what if we need to need to automatically receive the .csv via email on a regular basis? That's easy. We can take it a step further and run it from a SQL Agent Job that will email the .csv as an attachment.

First, change $EmailOn variable from 'n' to 'y'. This will tell the Send-MailMessage line at the end of the script to run. Then we populate:

  • $PSEmailServer with the name of your SMTP server
  • $EmailFrom with a properly formatted email address in the form [email protected] (doesn't have to be a valid email, just properly formatted or SMTP will reject it)
  • $EmailTo with the email group you want to send the report to
  • $EmailSubject with the subject you want receivers to see - omitting any the variables when $EmailOn = 'y' will cause the Send-MailMessage cmdlet to fail. Send-MailMessage switches do not accept NULLs if they are specified
$EmailOn = 'y'                            # y to email logs (case insensitive)
# $Email variables must be populated if $EmailOn = 'y'  
$PSEmailServer = ''  # mail server 
$EmailFrom = 'mailfrom@' # mail from - must be populated with properly formatted ([email protected]) but doesn't have to be real 
$EmailTo = 'mailfrom@ '  # mail to
$EmailSubject = 'ServerInfoAndCpuCount'   # mail subject

It's a good idea to manually execute the script to validate email settings to be sure we're adding a SQL Server Agent Job that will work as we want it to. Same as before, we'll run powershell.exe -File GetInfoAndCpuCount.ps1 from the from a command prompt in the C:\scripts\GetServerInfoAndCpuCount directory.

Email the CSV file

And here it is. The .csv has been emailed to us as an attachment.

CSV file in email

Now we're ready to automate it in a SQL Server Agent Job.

First, from SQL Server Management Studio, expand SQL Server Agent, right click on Jobs, and select New Job…

Begin to configure a SQL Server Agent Job in Management Studio

Name the Job, change owner to sa, and of course add a description so we'll remember what this job is for.

Click on the Steps page.

New SQL Server Agent Job

Click New...

SQL Server Agent Job Steps

Enter Step_name, choose Operating system (CmdExec) in the Command dropdown and enter powershell.exe -File C:\scripts\GetServerInfoAndCpucount.ps1

Note: The reason we choose Operating system (CmdExec) instead of PowerShell is because we want to control the version of PowerShell we're using and presumably tested our script with. If we chose PowerShell from the dropdown it would use the version of PowerShell tied to the version of the SQL Server we're running the Job on.

SQL Server Agent Job Step

Click OK.

Click on the Schedules page.

SQL Server Agent Schedules

Enter Job Schedule Name, be sure Enabled is checked, choose how often you want Job to run in Frequency dropdown, then choose Time you want Job to run.

New SQL Server Agent Job Schedule

Click OK.

It's time to test it. Right click on the newly created job, choose Start Job at Step… (there is only one step so it will automatically run)

Run the SQL Server Agent Job in Management Studio

If both Actions return Success you should expect the email with the attachment.

SQL Server Agent Job final status
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 27, 2019 - 8:02:49 AM - Joe Gavin Back To Top (81606)

Paul, you would probably need to use a tool like the free Microsoft Assessment and Planning Toolkit that will search your network for SQL Servers. Pretty easy to query AD with PowerShell to get a list of machines but I don't know of any way to determine if they have SQL Servers on them. 

Wednesday, June 26, 2019 - 12:32:33 PM - paul lee Back To Top (81596)

How can I run an powershell script to pull all of SQL server's name and then generate the output for this powershell script to get edition and cpu information?

assuming the remote powershell is turn on domain SQL server.

get free sql tips
agree to terms