SQL Server PowerShell: Export Server inventory to CSV
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:
- Mandatory -InputFileName - Text File contains a list of SQL Servers - C:\Server.txt(Example)
- Mandatory -DirectoryToSave - Folder where you want to store the file
- Mandatory -OutputType - CSV or Excel
- Mandatory - Email - Yes or No - If Yes, Requires below three parameter entries
- ToID - To Email Address - One ore more recipients
- FromID - From Email
- SMTP - Mail Server Name
Logical Flow
https://sqlpowershell.files.wordpress.com/2016/07/inventory_2.jpg
Pre-requisites
- Permission to access all SQL instances
- Permission to create the output file
- 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.
- 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
- 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
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