SQL Server PowerShell: Export Server inventory to CSV

  • Article

   This article won Technet GURU Gold medal. You are welcome to update and give your valuable feedback

Abstract

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.This inventory can take any number of forms but, ideally, will allow some aggregation of information. When server hardware or software changes are taking place, it’s easy to forget that the inventory has to be updated as well. For this reason, we like to have a script that creates the inventory, rather than have to keep a manual log of changes as they occur.

It has seven input parameters:

  1. Mandatory -InputFileName - Text File contains a list of SQL Servers - C:\Server.txt(Example)
  2. Mandatory -DirectoryToSave - Folder where you want to store the file
  3. Mandatory -OutputType - CSV or Excel
  4. Mandatory - Email - Yes or No - If Yes, Requires below three parameter entries
  5. ToID - To Email Address - One ore more recipients
  6. FromID - From Email
  7. SMTP - Mail Server Name

Logical Flow

https://sqlpowershell.files.wordpress.com/2016/07/inventory_2.jpg

Pre-requisites 

  1. Permission to access all SQL instances
  2. Permission to create the output file
  3. 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.
  4. Replication Management Objects

 

Advantages

 The advantage of this implementation

  • Excel or CSV
  • Auto email option for one ore more recipients.
  • Console output

Examples

# EXAMPLE 1 :Output CSV
 
#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  
 
# EXAMPLE 2 :Output Excel 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com  
 
# EXAMPLE 3 : One or More recipients 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com 
-SMTP mail.app.com
 
# EXAMPLE 4 :  Save the Copy under c:\ 
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'csv' -email 'No'

Details

Usually recommend generating CSV file from the server where you have loaded all sqlps modules(SMO and RMO). While generating Excel, make sure excel components are installed on the machine from which the code is being called.

It gathers 41 very useful information such as Server Configuration(SMO),DB Count(SMO), Traceflag(EnumActiveGlobalTraceFlags()), Replication(RMO) and Mirroring, Service Account Details.

Partial display of items in the console is due to the property of FormatEnumerationLimit. The $FormatEnumerationLimit preference variable has a value of 4, and it determines how many items are displayed when a property contains more than a single item. You can set it as per your requirement. If you set it to -1 then it's unlimited, it will display all the items. If you wish to display the result in the console, then select output type csv, refer EXAMPLE 2

The traceflags items are concatenated into a single array variable. To Avoid System.Object[] (or Similar Output) using Export-Csv have used join.

The 41 fields are listed below

  1. Name
  2. ComputerNamePhysicalNetBIOS
  3. NetName
  4. OSVer
  5. OSVersion
  6. Platform
  7. Product
  8. edition
  9. SQLServer
  10. VersionString
  11. ProductLevel
  12. DbCount
  13. HasNullSaPassword
  14. IsCaseSensitive
  15. IsFullTextInstalled
  16. Language
  17. LoginMode
  18. Processors
  19. PhysicalMemory
  20. MaxMem
  21. MinMem
  22. IsSingleUser
  23. IsClustered
  24. Collation
  25. MasterDBLogPath
  26. MasterDBPath
  27. ErrorLogPath
  28. BackupDirectory
  29. DefaultLog
  30. ResourceLastUpdateDateTime
  31. AuditLevel
  32. DefaultFile
  33. xp_cmdshell
  34. FQDN
  35. IPAddress
  36. Traceflag
  37. Replication
  38. Mirroring
  39. ServiceAccount
  40. ServiceStartMode
  41. State

Code:  Get 41 column output - Single instance of SQL Server 

To get 41 desired configuration details of server by replacing the $instancename parameter in the below code.

$instanceName ='<InstanceName>'
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") 1 | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction 'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
$dbs=$server1.Databases.count
$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
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {   
    [string]$b=$_.status
    [string]$c=$_.TraceFlag
    $a+=$c.ToString() +'->'+ $b.ToString()+ '  '
     
      }
       
      
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State
  
  
Foreach($Database in $server1.Databases)
   {
       if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
   }
   
if ($HasNullSaPassword.value -eq $NULL)
{
    $HasNullSaPassword.value='No'
}
if($DefaultFile.value -eq  '')
{
    $DefaultFile.value='NA'
}
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'
}
else
{
    $SQLServer='Invalid'
}
   
   
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'
}
else
{
    $OSVer='NA'
} 
  
  
  $Properties = @{Name=$instanceName
        ComputerNamePhysicalNetBIOS   =$ComputerNamePhysicalNetBIOS.value
        NetName   =$NetName.value
        OSVer   =$OSVer
        OSVersion   =$OSVersion.value
        Platform   = $Platform.value
        Product   = $Product.value
        edition   = $edition.value
        SQLServer   = $SQLServer
        VersionString = $VersionString.value
        ProductLevel  = $ProductLevel.value
        DbCount  = $Dbs
        HasNullSaPassword  = $HasNullSaPassword.value
        IsCaseSensitive  = $IsCaseSensitive.value
       IsFullTextInstalled  = $IsFullTextInstalled.value
        Language  = $Language.value
        LoginMode  = $LoginMode.value
        Processors  = $Processors.value
        PhysicalMemory  = $PhysicalMemory.value
        MaxMem  = $Max.Configvalue
        MinMem  = $Min.Configvalue
        IsSingleUser  = $IsSingleUser.value
        IsClustered  = $IsClustered.value
        Collation  = $Collation.value
        MasterDBLogPath  = $MasterDBLogPath.value
        MasterDBPath  = $MasterDBPath.value
        ErrorLogPath  = $ErrorLogPath.value
        BackupDirectory  = $BackupDirectory.value
        DefaultLog  = $DefaultLog.value
        ResourceLastUpdateDateTime  = $ResourceLastUpdateDateTime.value
        AuditLevel  = $AuditLevel.value
        DefaultFile = $DefaultFile.value
        xp_cmdshell = $xp_cmdshell.Configvalue
        FQDN = $FQDN
        IPAddress = ($IPAddress  -join ',')
        Traceflag = ($a -join ',')
        Replication = $replication
        Mirroring = $isMirror
        ServiceAccount = $SQL.ServiceAccount
        ServiceStartMode = $SQL.ServiceStartMode
        State = $SQL.State
        }
  
$Results += New-Object psobject -Property $properties
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation

https://sqlpowershell.files.wordpress.com/2016/07/inventory_12.jpg?w=680

 Code: Looping through I/P Server Lists

<#================================= 
# Generated On: 07/18/2016 
# Generated By: Prashanth Jayaram 
# Version : 1.1 
# Desc : SQL Inventory Generation 
# EXAMPLE 1 :Output CSV
#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\PowerSQL\ -OutputType 'csv' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 2 :Output Excel
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\ -OutputType 'excel' -email 'Yes' -To pram@app.com -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 3 :Multiple receipients
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo 
C:\Test\ -OutputType 'excel' -email 'Yes' -To "pram@app.com,pjayaram@app.vom" -From pram@app.com -SMTP mail.app.com 
# EXAMPLE 4 :No Email
PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt 
-DirectoryToSaveTo C:\123\ -OutputType 'csv' -Email 'No'
 
 
#================================= 
#> 
[CmdletBinding()] 
Param( 
 [Parameter(Mandatory=$True,Position=1)] 
 [string]$InputFileName, 
  
 [Parameter(Mandatory=$True,Position=2)] 
 [string]$DirectoryToSaveTo, 
  
 [Parameter(Mandatory=$True,Position=3)] 
 [string]$OutputType,
  
 [Parameter(Mandatory=$True,Position=4)] 
 [string]$Email,
  
 [Parameter(Position=5)] 
 [string]$To, 
  
 [Parameter(Position=6)] 
 [string]$From, 
  
 [Parameter(Position=7)] 
 [string]$SMTP 
  
) 
  
$Filename='SQLInventory'  
 
$Results = @()
  
 $a=@()
 
# 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 
 } 
  
 If( $OutputType -eq 'Excel')
 {
  
#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 
$xlOpenXMLWorkbook=[int]51  
  
#Read thru the contents of the SQL_Servers.txt file 
$Sheet.Cells.Item($intRow,1) ="InstanceName" 
$Sheet.Cells.Item($intRow,2) ="ComputerNamePhysicalNetBIOS" 
$Sheet.Cells.Item($intRow,3) ="NetName" 
$Sheet.Cells.Item($intRow,4) ="OS" 
$Sheet.Cells.Item($intRow,5) ="OSVersion" 
$Sheet.Cells.Item($intRow,6) ="Platform" 
$Sheet.Cells.Item($intRow,7) ="Product" 
$Sheet.Cells.Item($intRow,8) ="edition" 
$Sheet.Cells.Item($intRow,9) ="Version" 
$Sheet.Cells.Item($intRow,10) ="VersionString" 
$Sheet.Cells.Item($intRow,11) ="ProductLevel" 
$Sheet.Cells.Item($intRow,12) ="DatabaseCount" 
$Sheet.Cells.Item($intRow,13) ="HasNullSaPassword" 
$Sheet.Cells.Item($intRow,14) ="IsCaseSensitive" 
$Sheet.Cells.Item($intRow,15) ="IsFullTextInstalled" 
$Sheet.Cells.Item($intRow,16) ="Language" 
$Sheet.Cells.Item($intRow,17) ="LoginMode" 
$Sheet.Cells.Item($intRow,18) ="Processors" 
$Sheet.Cells.Item($intRow,19) ="PhysicalMemory" 
$Sheet.Cells.Item($intRow,10) ="MaxMemory" 
$Sheet.Cells.Item($intRow,21) ="MinMemory" 
$Sheet.Cells.Item($intRow,22) ="IsSingleUser" 
$Sheet.Cells.Item($intRow,23) ="IsClustered" 
$Sheet.Cells.Item($intRow,24) ="Collation" 
$Sheet.Cells.Item($intRow,25) ="MasterDBLogPath" 
$Sheet.Cells.Item($intRow,26) ="MasterDBPath" 
$Sheet.Cells.Item($intRow,27) ="ErrorLogPath" 
$Sheet.Cells.Item($intRow,28) ="BackupDirectory" 
$Sheet.Cells.Item($intRow,29) ="DefaultLog" 
$Sheet.Cells.Item($intRow,20) ="ResourceLastUpdatetime" 
$Sheet.Cells.Item($intRow,31) ="AuditLevel" 
$Sheet.Cells.Item($intRow,32) ="DefaultFile" 
$Sheet.Cells.Item($intRow,33) ="xp_cmdshell" 
$Sheet.Cells.Item($intRow,34) ="Domain" 
$Sheet.Cells.Item($intRow,35) ="IPAddress" 
$Sheet.Cells.Item($intRow,36) ="TraceFlag" 
$Sheet.Cells.Item($intRow,37) ="Mirror" 
$Sheet.Cells.Item($intRow,38) ="Replication" 
$Sheet.Cells.Item($intRow,39) ="ServiceAccount" 
$Sheet.Cells.Item($intRow,40) ="ServiceStartMode" 
$Sheet.Cells.Item($intRow,41) ="State" 
 
 
 
  
 for ($col = 1; $col –le 41; $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 
 } 
  
$intRow++ 
  
foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
 
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) 
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count 
$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 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % {
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString() + ' '
  
 }
  
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State
 
 
 
Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
  
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '')  
{ 
 $DefaultFile.value='NA' 
} 
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' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
  
  
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' 
} 
else 
{ 
 $OSVer='NA' 
} 
 $Sheet.Cells.Item($intRow,1) =$instanceName 
 $Sheet.Cells.Item($intRow,2) =$ComputerNamePhysicalNetBIOS.value 
 $Sheet.Cells.Item($intRow,3) =$NetName.value 
 $Sheet.Cells.Item($intRow,4) =$OSVer 
 $Sheet.Cells.Item($intRow,5) =$OSVersion.value 
 $Sheet.Cells.Item($intRow,6) = $Platform.value 
 $Sheet.Cells.Item($intRow,7) = $Product.value 
 $Sheet.Cells.Item($intRow,8) = $edition.value 
 $Sheet.Cells.Item($intRow,9) = $SQLServer 
 $Sheet.Cells.Item($intRow,10) = $VersionString.value 
 $Sheet.Cells.Item($intRow,11) = $ProductLevel.value 
 $Sheet.Cells.Item($intRow,12) = $Dbs 
 $Sheet.Cells.Item($intRow,13) = $HasNullSaPassword.value 
 $Sheet.Cells.Item($intRow,14) = $IsCaseSensitive.value 
 $Sheet.Cells.Item($intRow,15) = $IsFullTextInstalled.value 
 $Sheet.Cells.Item($intRow,16) = $Language.value 
 $Sheet.Cells.Item($intRow,17) = $LoginMode.value 
 $Sheet.Cells.Item($intRow,18) = $Processors.value 
 $Sheet.Cells.Item($intRow,19) = $PhysicalMemory.value 
 $Sheet.Cells.Item($intRow,10) = $Max.Configvalue 
 $Sheet.Cells.Item($intRow,21) = $Min.Configvalue 
 $Sheet.Cells.Item($intRow,22) = $IsSingleUser.value 
 $Sheet.Cells.Item($intRow,23) = $IsClustered.value 
 $Sheet.Cells.Item($intRow,24) = $Collation.value 
 $Sheet.Cells.Item($intRow,25) = $MasterDBLogPath.value 
 $Sheet.Cells.Item($intRow,26) = $MasterDBPath.value 
 $Sheet.Cells.Item($intRow,27) = $ErrorLogPath.value 
 $Sheet.Cells.Item($intRow,28) = $BackupDirectory.value 
 $Sheet.Cells.Item($intRow,29) = $DefaultLog.value 
 $Sheet.Cells.Item($intRow,20) = $ResourceLastUpdateDateTime.value 
 $Sheet.Cells.Item($intRow,31) = $AuditLevel.value 
 $Sheet.Cells.Item($intRow,32) = $DefaultFile.value 
 $Sheet.Cells.Item($intRow,33) = $xp_cmdshell.Configvalue 
 $Sheet.Cells.Item($intRow,34) = $FQDN 
 $Sheet.Cells.Item($intRow,35) = ($IPAddress -join ',') 
 $Sheet.Cells.Item($intRow,36) = ($a -join ',')
 $Sheet.Cells.Item($intRow,37) = $replication 
 $Sheet.Cells.Item($intRow,38) = $isMirror
 $Sheet.Cells.Item($intRow,39) = $SQL.ServiceAccount 
 $Sheet.Cells.Item($intRow,40) = $SQL.ServiceStartMode
 $Sheet.Cells.Item($intRow,41) = $SQL.State
  
$intRow ++ 
  
} 
  
$filename = "$DirectoryToSaveTo$filename.xlsx" 
if (test-path $filename ) { rm $filename } #delete the file if it already exists 
$Sheet.UsedRange.EntireColumn.AutoFit() 
cls 
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) 
$Excel.Saved = $True 
$Excel.Close() 
  
 }
 ElseIf( $OutputType -eq 'csv')
 {
 $filename = "$DirectoryToSaveTo$filename.csv" 
 if (test-path $filename ) { rm $filename } #delete the file if it already exists 
 foreach ($instanceName in Get-Content $InputFileName) 
{ 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName 
$rmo = New-Object Microsoft.SqlServer.Replication.ReplicationServer($instanceName) -erroraction 'silentlycontinue'
$s=$server1.Information.Properties |Select Name, Value 
$st=$server1.Settings.Properties |Select Name, Value 
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue 
$dbs=$server1.Databases.count  
$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 
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name 
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName | ? {$_.IPEnabled}).ipaddress 
$a=''
$server1.EnumActiveGlobalTraceFlags()| % { 
 [string]$b=$_.status
 [string]$c=$_.TraceFlag
 $a+=$c.ToString() +'->'+ $b.ToString()+ ' '
  
 }
  
  
If ($rmo.DistributorInstalled -or $rmo.IsDistributor -or $rmo.IsPublisher -or $smo.RegisteredSubscribers) {
$replication= "1"
}
else
{
$replication= "0"
}
$SQL= $server1 | select ServiceAccount,ServiceStartMode,State
 
 
Foreach($Database in $server1.Databases)
 {
 if($Database.IsMirroringEnabled){$ismirror="1"} else { $ismirror="0"}
 }
  
if ($HasNullSaPassword.value -eq $NULL) 
{ 
 $HasNullSaPassword.value='No' 
} 
if($DefaultFile.value -eq '') 
{ 
 $DefaultFile.value='NA' 
} 
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' 
} 
else 
{ 
 $SQLServer='Invalid' 
} 
  
  
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' 
} 
else 
{ 
 $OSVer='NA' 
} 
 
 
 $Properties = @{Name=$instanceName 
 ComputerNamePhysicalNetBIOS =$ComputerNamePhysicalNetBIOS.value 
 NetName =$NetName.value 
 OSVer =$OSVer 
 OSVersion =$OSVersion.value 
 Platform = $Platform.value 
 Product = $Product.value 
 edition = $edition.value 
 SQLServer = $SQLServer 
 VersionString = $VersionString.value 
 ProductLevel = $ProductLevel.value 
 DbCount = $Dbs 
 HasNullSaPassword = $HasNullSaPassword.value 
 IsCaseSensitive = $IsCaseSensitive.value 
 IsFullTextInstalled = $IsFullTextInstalled.value 
 Language = $Language.value 
 LoginMode = $LoginMode.value 
 Processors = $Processors.value 
 PhysicalMemory = $PhysicalMemory.value 
 MaxMem = $Max.Configvalue 
 MinMem = $Min.Configvalue 
 IsSingleUser = $IsSingleUser.value 
 IsClustered = $IsClustered.value 
 Collation = $Collation.value 
 MasterDBLogPath = $MasterDBLogPath.value 
 MasterDBPath = $MasterDBPath.value 
 ErrorLogPath = $ErrorLogPath.value 
 BackupDirectory = $BackupDirectory.value 
 DefaultLog = $DefaultLog.value 
 ResourceLastUpdateDateTime = $ResourceLastUpdateDateTime.value 
 AuditLevel = $AuditLevel.value 
 DefaultFile = $DefaultFile.value 
 xp_cmdshell = $xp_cmdshell.Configvalue 
 FQDN = $FQDN 
 IPAddress = ($IPAddress -join ',')
 Traceflag = ($a -join ',')
 Replication = $replication 
 Mirroring = $isMirror
 ServiceAccount = $SQL.ServiceAccount 
 ServiceStartMode = $SQL.ServiceStartMode
 State = $SQL.State
 }
 
$Results += New-Object psobject -Property $properties 
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$results | select Name ,ComputerNamePhysicalNetBIOS,NetName ,OSVer,OSVersion,Platform,Product,edition,SQLServer,VersionString,ProductLevel,DbCount,HasNullSaPassword,IsCaseSensitive,IsFullTextInstalled,Language,LoginMode,Processors,PhysicalMemory,MaxMem,MinMem,IsSingleUser,IsClustered,Collation,MasterDBLogPath,MasterDBPath,ErrorLogPath,BackupDirectory,DefaultLog,ResourceLastUpdateDateTime,AuditLevel,DefaultFile,xp_cmdshell,FQDN,IPAddress,Traceflag,Replication,Mirroring,ServiceAccount,ServiceStartMode,State| export-csv -Path $filename -NoTypeInformation
 
 }
  
 }
  
  
  
  
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.To.Add($emailTo)  
$email.Subject = $subject 
$email.Body = $body 
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath 
$email.Attachments.Add($emailAttach) 
#initiate sending email  
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$smtp.Send($email) 
} 
 
 
If( $Email -eq 'Yes')
{
If (!($to) -and !($smtp))
{ 
#Call Function 
sendEmail -emailFrom $from -emailTo $to "SQL INVENTORY" "SQL INVENTORY DETAILS - COMPLETE DETAILS" -smtpServer $SMTP -filePath $filename 
}
else
{
write-host 'To or SMTP Invalid'
}
}

Function Get-SQLand GlobalTraceFlagDetails

Many a times we don't need to generate all 41 columns and need to run it over more than one servers  in that case we can invoke this function. The examples section defines various ways of calling the function.Quick way of getting high level configuration information such as Server Details, Service Info, Start Type, Traceflag of one or more servers is given below

function Get-SQLAndGlobalTraceFlagsDetails {
 
<#
.SYNOPSIS
Returns SQL Server Configuration, Service and global TraceFlag details
   
.DESCRIPTION
Get-SQLAndGlobalTraceFlagsDetails is a function that returns server high level server configuration information for
one or more Microsoft SQL Server
   
.PARAMETER ComputerName
The computer that is running Microsoft SQL Server that you’re targeting
   
.EXAMPLE
Get-SQLAndGlobalTraceFlagsDetails -ComputerName sql01
   
.EXAMPLE
Get-SQLAndGlobalTraceFlagsDetails -ComputerName HQDBSP18
   
.EXAMPLE
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails -ServerName $_ }
   
.EXAMPLE
   
ForEach ($server in Get-ChildItem C:\server.txt)
{
Get-SQLAndGlobalTraceFlagsDetails -ServerName $server
   
}
   
.INPUTS
String
   
.OUTPUTS
PSCustomObject , console or CSV
#>
[CmdletBinding()]
param (
[Parameter(Mandatory=$true,
ValueFromPipelineByPropertyName=$true)]
[Alias('ServerName','PSComputerName','ComputerName')]
[string[]]$Ser
)
   
   
# Used to store the result
   
$Results = @()
   
# The below array used to concatenate the Trace flag details
   
$TraceFlag=@()
   
#create an smo object for the SQL Server
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
   
$SQLServer = new-object ('Microsoft.SQLServer.Management.Smo.Server') $Ser
$SQL= $SQLServer | select Name, Edition, BuildNumber, Product, ProductLevel, Version, Processors, PhysicalMemory, DefaultFile, DefaultLog, MasterDBPath, MasterDBLogPath, BackupDirectory, ServiceAccount,ServiceStartMode,State
   
$SQLServer.EnumActiveGlobalTraceFlags()|%{
[string]$b=$_.status
[string]$c=$_.TraceFlag
$TraceFlag+=$c.ToString() +'->'+ $b.ToString()
   
}
   
$Properties = @{
Name = $SQL.name
Edition = $SQL.Edition
BuildNumber = $SQL.BuildNumber
Product=$SQL.Product
ProductLevel=$SQL.ProductLevel
Version=$SQL.Version
Processors=$SQL.Processors
DefaultLog=$SQL.DefaultLog
MasterDBPath=$SQL.MasterDBPath
MasterDBLogPath=$SQL.MasterDBLogPath
BackupDirectory=$SQL.BackupDirectory
ServiceAccount=$SQL.ServiceAccount
StartupMode=$SQL.ServiceStartMode
State=$SQL.State
TraceAndStatus=($TraceFlag -join ',')} 
   
$Results += New-Object psobject -Property $properties 
$Results | Select-Object Name, Edition, BuildNumber,TraceAndStatus,Product,ProductLevel,Version,Processors,DefaultLog,MasterDBPath,MasterDBLogPath,BackupDirectory,ServiceAccount,StartupMode,State
   
   
}
   
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails1 -ServerName $_ }

 https://sqlpowershell.files.wordpress.com/2016/07/sqltraceflag2.jpg?w=640

Output

CSV:

https://sqlpowershell.files.wordpress.com/2016/07/inventory1.jpg?w=1700

CSV output to saved under f:\PowerSQL folder

https://sqlpowershell.files.wordpress.com/2016/07/inventory_1.jpg?w=1700

Excel

https://sqlpowershell.files.wordpress.com/2016/07/inventory_3.jpg?w=1700

Conclusion

  • Effectively perform administrative tasks
  • Effective Asset Management
  • Inventory is important for DB Migration/Upgradation
  • Maintain Corporate standards 
  • License Validation
  • Easy to provide road-map 

References

SQL Server Management Objects (SMO) Programming Guide

Getting started with SQL Server Management Objects (SMO)

How to Document and Configure SQL Server Instance Settings

PowerShell – Export SQL Data to Excel with Powershell