Blog Post

PowerShell – SQL Inventory – Automatic – Excel File – EMAIL

,

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

  1. InputFileName – Text File contains a list of SQL Servers -c:\Server.txt(Example)
  2. DirectoryToSave – Folder where you want to store the file
  3. ToID – to email Address
  4. FromID – From Email Address
  5. SMTP – SMTP Adress

Pre-requisites are -

  1. Windows PowerShell 2.0 must be installed
  2. Permission to access all SQL instances
  3. 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 pram@abc.com -From pram@abc.com -SMTP mail.abc.com

You can download :-SQLInventory

Output:-

Image

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 pram@app.com -From pram@app.com  
-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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating