Blog Post

SQL – MultiServer SQL Inventory – PowerShell

,

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, I 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 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

 

Inventory_2

Pre-requisites are –

  1. Permission to access all SQL instances
  2. Permission to create a 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

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'

I usually recommend to generate 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 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.

  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:  Input 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") | 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

Inventory_1

Code

<#================================= 
# 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'
}
}

 

Output

CSV:

Inventory

 

CSV output to saved under f:\PowerSQL folder

Inventory_1

 

Excel

Inventory_3

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads