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
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s