PowerShell – SQL Inventory – Automatic – Excel File – EMAIL

Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.

Download the file and save as SQLInventoryExcel.PS1.

It has Five mandatory parameters

  1. InputFileName – Text File contains a list of SQL Servers -c:\Server.txt(Example)
  2. DirectoryToSave – Folder where you want to store the file
  3. ToID – to email Address
  4. FromID – From Email Address
  5. SMTP – SMTP Adress

Pre-requisites are –

  1. Windows PowerShell 2.0 must be installed
  2. Permission to access all SQL instances
  3. Permission to create a file in the given directory

Windows PowerShell 2.0 is installed by default on newer versions of the Windows operating systems.You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically. The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features.

Download or Save the file as SQLServerInventory.PS1


PS C:\Blog> .\SQLServerInventory.ps1 -InputFileName C:\server.txt -DirectoryToSaveTo C:\ -To pram@abc.com -From pram@abc.com -SMTP mail.abc.com

You can download :-SQLInventory




# Generated On: 02/04/2014  
# Generated By: Prashanth Jayaram  
# Version     : 1.0  
# Desc        : SQL Inventory Generation 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo C:\  -To pram@app.com -From pram@app.com  
-SMTP mail.app.com 







# before we do anything else, are we likely to be able to save the file? 
# if the directory doesn't exist, then create it 
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing 
  New-Item "$DirectoryToSaveTo" -type directory | out-null 

#Create a new Excel object using COM  
$Excel = New-Object -ComObject Excel.Application 
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add() 
$Sheet = $Excel.Worksheets.Item(1) 

#Counter variable for rows 
$intRow = 1 

#Read thru the contents of the SQL_Servers.txt file 

$Sheet.Cells.Item($intRow,1)  ="ComputerNamePhysicalNetBIOS" 
$Sheet.Cells.Item($intRow,2)  ="NetName" 
$Sheet.Cells.Item($intRow,3)  ="OS" 
$Sheet.Cells.Item($intRow,4)  ="OSVersion" 
$Sheet.Cells.Item($intRow,5)  ="Platform" 
$Sheet.Cells.Item($intRow,6)  ="Product" 
$Sheet.Cells.Item($intRow,7)  ="edition" 
$Sheet.Cells.Item($intRow,8)  ="Version" 
$Sheet.Cells.Item($intRow,9)  ="VersionString" 
$Sheet.Cells.Item($intRow,10) ="ProductLevel" 
$Sheet.Cells.Item($intRow,11) ="DatabaseCount" 
$Sheet.Cells.Item($intRow,12) ="HasNullSaPassword" 
$Sheet.Cells.Item($intRow,13) ="IsCaseSensitive" 
$Sheet.Cells.Item($intRow,14) ="IsFullTextInstalled" 
$Sheet.Cells.Item($intRow,15) ="Language" 
$Sheet.Cells.Item($intRow,16) ="LoginMode" 
$Sheet.Cells.Item($intRow,17) ="Processors" 
$Sheet.Cells.Item($intRow,18) ="PhysicalMemory" 
$Sheet.Cells.Item($intRow,19) ="MaxMemory" 
$Sheet.Cells.Item($intRow,20) ="MinMemory" 
$Sheet.Cells.Item($intRow,21) ="IsSingleUser" 
$Sheet.Cells.Item($intRow,22) ="IsClustered" 
$Sheet.Cells.Item($intRow,23) ="Collation" 
$Sheet.Cells.Item($intRow,24) ="MasterDBLogPath" 
$Sheet.Cells.Item($intRow,25) ="MasterDBPath" 
$Sheet.Cells.Item($intRow,26) ="ErrorLogPath" 
$Sheet.Cells.Item($intRow,27) ="BackupDirectory" 
$Sheet.Cells.Item($intRow,28) ="DefaultLog" 
$Sheet.Cells.Item($intRow,29) ="ResourceLastUpdatetime" 
$Sheet.Cells.Item($intRow,30) ="AuditLevel" 
$Sheet.Cells.Item($intRow,31) ="DefaultFile" 
$Sheet.Cells.Item($intRow,32) ="xp_cmdshell" 
$Sheet.Cells.Item($intRow,33) ="Domain" 
$Sheet.Cells.Item($intRow,34) ="IPAddress" 

  for ($col = 1; $col –le 34; $col++) 
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True 
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 


foreach ($instanceName in Get-Content $InputFileName) 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$s=$server1.Information.Properties |Select Name, Value  
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value 
$edition=$s | where {$_.name -eq "edition"}|select value 
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value 
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value 
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value 
$Platform =$s | where {$_.name -eq "Platform"}|select value 
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value 
$Language =$s | where {$_.name -eq "Language"}|select value 
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value 
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value 
$NetName =$s | where {$_.name -eq "NetName"}|select value 
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value 
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value 
$Processors =$s | where {$_.name -eq "Processors"}|select value 
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value 
$Product =$s | where {$_.name -eq "Product"}|select value 
$VersionString =$s | where {$_.name -eq "VersionString"}|select value 
$Collation =$s | where {$_.name -eq "Collation"}|select value 
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value 
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value 
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value 
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value 
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value 
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value 
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value 
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value 
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value 
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue 
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue 
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 

if ($HasNullSaPassword.value -eq $NULL) 
if($DefaultFile.value -eq '') 
if ($VersionString.value -like '8*') 
    $SQLServer='SQL SERVER 2000' 
elseif ($VersionString.value -like '9*') 
    $SQLServer='SQL SERVER 2005' 
elseif ($VersionString.value -like '10.0*') 
    $SQLServer='SQL SERVER 2008' 
elseif ($VersionString.value -like '10.5*') 
    $SQLServer='SQL SERVER 2008 R2' 
elseif ($VersionString.value -like '11*') 
    $SQLServer='SQL SERVER 2012' 

if ($OSVersion.value -like '5.0*') 
    $OSVer='Windows 2000' 
elseif ($OSVersion.value -like '5.1*') 
    $OSVer='Windows XP' 
elseif ($OSVersion.value -like '5.2*') 
    $OSVer='Windows Server 2003' 
elseif ($OSVersion.value -like '6.0*') 
    $OSVer='Windows Server 2008' 
elseif ($OSVersion.value -like '6.1*') 
    $OSVer='Windows Server 2008 R2' 
elseif ($OSVersion.value -like '6.2*') 
    $OSVer='Windows Server 2012' 

        $Sheet.Cells.Item($intRow,1)  =$ComputerNamePhysicalNetBIOS.value 
        $Sheet.Cells.Item($intRow,2)  =$NetName.value 
        $Sheet.Cells.Item($intRow,3)  =$OSVer 
        $Sheet.Cells.Item($intRow,4)  =$OSVersion.value 
        $Sheet.Cells.Item($intRow,5)  = $Platform.value 
        $Sheet.Cells.Item($intRow,6)  = $Product.value 
        $Sheet.Cells.Item($intRow,7)  = $edition.value 
        $Sheet.Cells.Item($intRow,8)  = $SQLServer 
        $Sheet.Cells.Item($intRow,9)  = $VersionString.value 
        $Sheet.Cells.Item($intRow,10) = $ProductLevel.value 
        $Sheet.Cells.Item($intRow,11) = $Dbs 
        $Sheet.Cells.Item($intRow,12) = $HasNullSaPassword.value 
        $Sheet.Cells.Item($intRow,13) = $IsCaseSensitive.value 
        $Sheet.Cells.Item($intRow,14) = $IsFullTextInstalled.value 
        $Sheet.Cells.Item($intRow,15) = $Language.value 
        $Sheet.Cells.Item($intRow,16) = $LoginMode.value 
        $Sheet.Cells.Item($intRow,17) = $Processors.value 
        $Sheet.Cells.Item($intRow,18) = $PhysicalMemory.value 
        $Sheet.Cells.Item($intRow,19) = $Max.Configvalue 
        $Sheet.Cells.Item($intRow,20) = $Min.Configvalue 
        $Sheet.Cells.Item($intRow,21) = $IsSingleUser.value 
        $Sheet.Cells.Item($intRow,22) = $IsClustered.value 
        $Sheet.Cells.Item($intRow,23) = $Collation.value 
        $Sheet.Cells.Item($intRow,24) = $MasterDBLogPath.value 
        $Sheet.Cells.Item($intRow,25) = $MasterDBPath.value 
        $Sheet.Cells.Item($intRow,26) = $ErrorLogPath.value 
        $Sheet.Cells.Item($intRow,27) = $BackupDirectory.value 
        $Sheet.Cells.Item($intRow,28) = $DefaultLog.value 
        $Sheet.Cells.Item($intRow,29) = $ResourceLastUpdateDateTime.value 
        $Sheet.Cells.Item($intRow,30) = $AuditLevel.value 
        $Sheet.Cells.Item($intRow,31)= $DefaultFile.value 
        $Sheet.Cells.Item($intRow,32)= $xp_cmdshell.Configvalue 
        $Sheet.Cells.Item($intRow,33)= $FQDN 
        $Sheet.Cells.Item($intRow,34)= $IPAddress 

$intRow ++ 


$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$Excel.Saved = $True 

Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath) 
#initate message 
$email = New-Object System.Net.Mail.MailMessage  
$email.From = $emailFrom 
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment  
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
#initiate sending email  
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 

#Call Function  
sendEmail -emailFrom $from -emailTo $to "SQL INVENTORY" "SQL INVENTORY DETAILS - COMPLETE DETAILS" -smtpServer $SMTP -filePath $filename

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , , . Bookmark the permalink.

7 Responses to PowerShell – SQL Inventory – Automatic – Excel File – EMAIL

  1. Aaron Street says:

    Get following error when running your script. Please advise what maybe issue

    The following exception was thrown when trying to enumerate the collection: “Failed to retrieve data
    At C:\temp\SQLServerInventory.ps1:95 char:5
    + $CP= <<<< $server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
    + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

    Property 'value' cannot be found on this object; make sure it exists and is settable.
    At C:\temp\SQLServerInventory.ps1:132 char:21
    + $HasNullSaPassword. <<<< value='No'
    + CategoryInfo : InvalidOperation: (value:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound


    • Hello Aaron,

      I hope you are running from your workstation where you’ve SQL Server SSMS and Excel

      Just download the script and execute it in PowerShell ISE.

      Let me know the steps you are following to execute this code.



  2. Blake says:

    Fantastic information, man. Cannot wait for the next one.


  3. CrazyDBA says:

    Hi There,

    I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports.



  4. Stimulating reading. Fine points, well made. I think your are spot on.


  5. Fantastic reading. Fantastic points, well-made.
    I think you’re spot on there.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s