Inventory is a vital information,whether you’re a consultant or an in-house DBA, you need to have a reliable inventory of the servers you manage.
Download the file and save as SQLInventoryExcel.PS1.
It has Five mandatory parameters
- InputFileName – Text File contains a list of SQL Servers -c:\Server.txt(Example)
- DirectoryToSave – Folder where you want to store the file
- ToID – to email Address
- FromID – From Email Address
- SMTP – SMTP Adress
Pre-requisites are –
- Windows PowerShell 2.0 must be installed
- Permission to access all SQL instances
- Permission to create a file in the given directory
Windows PowerShell 2.0 is installed by default on newer versions of the Windows operating systems.You must install a SQL Server feature that includes the SQL Server PowerShell (SQLPS) module and client libraries. The easiest way to do this is by installing SQL Server Management Studio, which includes the PowerShell feature and client libraries automatically. The SQL Server PowerShell (SQLPS) module contains the PowerShell providers and cmdlets for all SQL Server features.
Download or Save the file as SQLServerInventory.PS1
Call:
PS C:\Blog> .\SQLServerInventory.ps1 -InputFileName C:\server.txt -DirectoryToSaveTo C:\ -To [email protected] -From [email protected] -SMTP mail.abc.com
You can download :-SQLInventory
Output:-
Code:-
<#================================= # Generated On: 02/04/2014 # Generated By: Prashanth Jayaram # Version : 1.0 # Desc : SQL Inventory Generation # EXAMPLE : PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server.txt -DirectoryToSaveTo C:\ -To [email protected] -From [email protected] -SMTP mail.app.com #================================= #> [CmdletBinding()] Param( [Parameter(Mandatory=$True,Position=1)] [string]$InputFileName, [Parameter(Mandatory=$True,Position=2)] [string]$DirectoryToSaveTo, [Parameter(Mandatory=$True,Position=3)] [string]$To, [Parameter(Mandatory=$True,Position=4)] [string]$From, [Parameter(Mandatory=$True,Position=5)] [string]$SMTP ) $Filename='SQLInventory' # before we do anything else, are we likely to be able to save the file? # if the directory doesn't exist, then create it if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing { New-Item "$DirectoryToSaveTo" -type directory | out-null } #Create a new Excel object using COM $Excel = New-Object -ComObject Excel.Application $Excel.visible = $True $Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1) #Counter variable for rows $intRow = 1 $xlOpenXMLWorkbook=[int]51 #Read thru the contents of the SQL_Servers.txt file $Sheet.Cells.Item($intRow,1) ="ComputerNamePhysicalNetBIOS" $Sheet.Cells.Item($intRow,2) ="NetName" $Sheet.Cells.Item($intRow,3) ="OS" $Sheet.Cells.Item($intRow,4) ="OSVersion" $Sheet.Cells.Item($intRow,5) ="Platform" $Sheet.Cells.Item($intRow,6) ="Product" $Sheet.Cells.Item($intRow,7) ="edition" $Sheet.Cells.Item($intRow,8) ="Version" $Sheet.Cells.Item($intRow,9) ="VersionString" $Sheet.Cells.Item($intRow,10) ="ProductLevel" $Sheet.Cells.Item($intRow,11) ="DatabaseCount" $Sheet.Cells.Item($intRow,12) ="HasNullSaPassword" $Sheet.Cells.Item($intRow,13) ="IsCaseSensitive" $Sheet.Cells.Item($intRow,14) ="IsFullTextInstalled" $Sheet.Cells.Item($intRow,15) ="Language" $Sheet.Cells.Item($intRow,16) ="LoginMode" $Sheet.Cells.Item($intRow,17) ="Processors" $Sheet.Cells.Item($intRow,18) ="PhysicalMemory" $Sheet.Cells.Item($intRow,19) ="MaxMemory" $Sheet.Cells.Item($intRow,20) ="MinMemory" $Sheet.Cells.Item($intRow,21) ="IsSingleUser" $Sheet.Cells.Item($intRow,22) ="IsClustered" $Sheet.Cells.Item($intRow,23) ="Collation" $Sheet.Cells.Item($intRow,24) ="MasterDBLogPath" $Sheet.Cells.Item($intRow,25) ="MasterDBPath" $Sheet.Cells.Item($intRow,26) ="ErrorLogPath" $Sheet.Cells.Item($intRow,27) ="BackupDirectory" $Sheet.Cells.Item($intRow,28) ="DefaultLog" $Sheet.Cells.Item($intRow,29) ="ResourceLastUpdatetime" $Sheet.Cells.Item($intRow,30) ="AuditLevel" $Sheet.Cells.Item($intRow,31) ="DefaultFile" $Sheet.Cells.Item($intRow,32) ="xp_cmdshell" $Sheet.Cells.Item($intRow,33) ="Domain" $Sheet.Cells.Item($intRow,34) ="IPAddress" for ($col = 1; $col –le 34; $col++) { $Sheet.Cells.Item($intRow,$col).Font.Bold = $True $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48 $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34 } $intRow++ foreach ($instanceName in Get-Content $InputFileName) { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName $s=$server1.Information.Properties |Select Name, Value $st=$server1.Settings.Properties |Select Name, Value $CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue $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 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) =$ComputerNamePhysicalNetBIOS.value $Sheet.Cells.Item($intRow,2) =$NetName.value $Sheet.Cells.Item($intRow,3) =$OSVer $Sheet.Cells.Item($intRow,4) =$OSVersion.value $Sheet.Cells.Item($intRow,5) = $Platform.value $Sheet.Cells.Item($intRow,6) = $Product.value $Sheet.Cells.Item($intRow,7) = $edition.value $Sheet.Cells.Item($intRow,8) = $SQLServer $Sheet.Cells.Item($intRow,9) = $VersionString.value $Sheet.Cells.Item($intRow,10) = $ProductLevel.value $Sheet.Cells.Item($intRow,11) = $Dbs $Sheet.Cells.Item($intRow,12) = $HasNullSaPassword.value $Sheet.Cells.Item($intRow,13) = $IsCaseSensitive.value $Sheet.Cells.Item($intRow,14) = $IsFullTextInstalled.value $Sheet.Cells.Item($intRow,15) = $Language.value $Sheet.Cells.Item($intRow,16) = $LoginMode.value $Sheet.Cells.Item($intRow,17) = $Processors.value $Sheet.Cells.Item($intRow,18) = $PhysicalMemory.value $Sheet.Cells.Item($intRow,19) = $Max.Configvalue $Sheet.Cells.Item($intRow,20) = $Min.Configvalue $Sheet.Cells.Item($intRow,21) = $IsSingleUser.value $Sheet.Cells.Item($intRow,22) = $IsClustered.value $Sheet.Cells.Item($intRow,23) = $Collation.value $Sheet.Cells.Item($intRow,24) = $MasterDBLogPath.value $Sheet.Cells.Item($intRow,25) = $MasterDBPath.value $Sheet.Cells.Item($intRow,26) = $ErrorLogPath.value $Sheet.Cells.Item($intRow,27) = $BackupDirectory.value $Sheet.Cells.Item($intRow,28) = $DefaultLog.value $Sheet.Cells.Item($intRow,29) = $ResourceLastUpdateDateTime.value $Sheet.Cells.Item($intRow,30) = $AuditLevel.value $Sheet.Cells.Item($intRow,31)= $DefaultFile.value $Sheet.Cells.Item($intRow,32)= $xp_cmdshell.Configvalue $Sheet.Cells.Item($intRow,33)= $FQDN $Sheet.Cells.Item($intRow,34)= $IPAddress $intRow ++ } $filename = "$DirectoryToSaveTo$filename.xlsx" if (test-path $filename ) { rm $filename } #delete the file if it already exists $Sheet.UsedRange.EntireColumn.AutoFit() cls $Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx) $Excel.Saved = $True $Excel.Close() 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) } #Call Function sendEmail -emailFrom $from -emailTo $to "SQL INVENTORY" "SQL INVENTORY DETAILS - COMPLETE DETAILS" -smtpServer $SMTP -filePath $filename
Prashanth Jayaram 
Get following error when running your script. Please advise what maybe issue
The following exception was thrown when trying to enumerate the collection: “Failed to retrieve data
.
At C:\temp\SQLServerInventory.ps1:95 char:5
+ $CP= <<<< $server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
Property 'value' cannot be found on this object; make sure it exists and is settable.
At C:\temp\SQLServerInventory.ps1:132 char:21
+ $HasNullSaPassword. <<<< value='No'
+ CategoryInfo : InvalidOperation: (value:String) [], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFound
LikeLike
Hello Aaron,
I hope you are running from your workstation where you’ve SQL Server SSMS and Excel
Just download the script and execute it in PowerShell ISE.
Let me know the steps you are following to execute this code.
-Prashanth
LikeLike
Fantastic information, man. Cannot wait for the next one.
LikeLike
Thanks for reading….
LikeLike
Hi There,
I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports.
Thnx
LikeLike
Stimulating reading. Fine points, well made. I think your are spot on.
LikeLike
Fantastic reading. Fantastic points, well-made.
I think you’re spot on there.
LikeLike