Powershell - Get Servers Information

The original script came from MSSQLTIPS.COM

http://www.mssqltips.com/sqlservertip/3045/script-to-get-cpu-and-cores-for-sql-server-2012-licensing/#comments

I enhanced the script from MSSQLTIPS and added additonal information such as SQL server maxmemory, Ip address and etc...There are two script files. SQLServerInfo.ps1 contains a function Get-ServerInfo which retrieves information from all the servers. SQLServerInfo_Call.ps1 is to call SQLServerInfo.ps1 script.
You saved two script files in E:\ or C:\ as powershell files. You just need to change the path of the $errorlog,$exportcvs variables if you are putting your files in different drives.

How to run the script. - Open SQLServerInfo_Call and Click Run.


Enjoy scripting..

SQLServerInfo.ps1

param([string]$SQLServerList=$(Throw
"Paramater missing: -SQLServerList ConfigGroup"))

$errorlog = 'E:\PowerShell\SQLServersInfo\Error.txt'
$RunTime = $(get-date -f MM-dd-yyyy_HH_mm_ss)

Function Get-ServerInfo{
    [CmdletBinding()]
    Param(
    [parameter(Mandatory = $TRUE,ValueFromPipeline = $TRUE)]   [String] $ComputerName
    # I have defined an input parameter - computer name, This can accept input from the pipleline.
    #This means you can call this function in two distinct ways
    #Get-Content names.txt | Get-ServerInfo
    #OR
    #Get-ServerInfo –computername SERVER1,SERVER2
    )

 
 
    Process{
 
                Try{

         
            $sqlconn = new-object System.Data.SqlClient.SqlConnection(`
            "server=$ComputerName;Trusted_Connection=true");
                     
            #SQL server Edition
            $query = "select SERVERPROPERTY('ProductVersion') As ProductVersion,SERVERPROPERTY('Edition') as Edition;"
         
            #SQL server Memory
            $query2 = "select value as MaxMemory from sys.configurations where name like 'max server memory (MB)';"
     
         
            #execute query Get Product Version and Edition
            $sqlconn.Open()
            $sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
            $sqlcmd.CommandTimeout = 0;
            $dr = $sqlcmd.ExecuteReader();        
                   
            while ($dr.Read()) {
             $SQLVersion=$dr.GetValue(0);
             $SQLEdition=$dr.GetValue(1);
            }
         
            $dr.Close()
            $sqlconn.Close()
           
            #execute query Get SQL Server Max Memory
            $sqlconn.Open()
            $sqlcmd2 = new-object System.Data.SqlClient.SqlCommand ($query2, $sqlconn);
            $sqlcmd2.CommandTimeout = 0;
            $dr2 = $sqlcmd2.ExecuteReader();
           
             while ($dr2.Read()) {
             $SQLMaxMemory=$dr2.GetValue(0);
           
            }
                 
            $dr2.Close()
            $sqlconn.Close()
                 
         
         
 
            #Get processors information          
            $CPU=Get-WmiObject -ComputerName $ComputerName -class Win32_Processor
         
            #Get Computer model information
            $OS_Info=Get-WmiObject -ComputerName $ComputerName -class Win32_ComputerSystem
         
            #Get Computer IP Address        
            $OS_IP =Get-WmiObject -ComputerName $ComputerName -class Win32_NetworkAdapterConfiguration -Filter IPEnabled=True                    
         
         
            #Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter IPEnabled=TRUE -ComputerName $ComputerName -Property IPAddress
            #Get OS Version
            $OS_Ver=Get-WmiObject -ComputerName $ComputerName -class Win32_OperatingSystem
   
           #Reset number of cores and use count for the CPUs counting
           $CPUs = 0
           $Cores = 0
         
           foreach($Processor in $CPU){

           $CPUs = $CPUs+1
         
           #count the total number of cores      
           $Cores = $Cores+$Processor.NumberOfCores
     
          }
       
          #Calculate Physical Memory
          $totalMemory = 0
       
           #Get Computer Physical Memory Information
           $OS_Mem=Get-WmiObject -ComputerName $ComputerName -class Win32_PhysicalMemory
       
          foreach($ram in $OS_Mem)
          {
           $totalMemory+= $ram.capacity
          }
       
         
           $InfoRecord = New-Object -TypeName PSObject -Property @{
                    Server = $ComputerName;
                    OS = $OS_Ver.Caption;                      
                    Model = $OS_Info.Model;
                    IpAddress = $OS_IP.IPAddress;
                    Domain = $OS_Info.Domain;
                    SQLEdition = $SQLEdition;
                    SQLVersion = $SQLVersion;
                    PhysicalMemory_MB = $totalMemory/(1024 * 1024);
                    SQLMaxMemory_MB = $SQLMaxMemory;
                    CPUNumber = $CPUs;
                    TotalCores = $Cores;
                    };

                    }

    Catch{

     "Fail to get information $ComputerName +' ' + $RunTime :$_" |Out-File $errorlog -Append
    }
 
    Write-Output $InfoRecord
     }
                         
   }
#loop through the server list and get information about CPUs, Cores and Default instance edition
   $exportcsv ='E:\PowerShell\SQLServersInfo\SQLServerInfo.csv'
   Get-Content $SQLServerList | Foreach-Object {Get-ServerInfo $_ }|Select Server, OS, Model,{$_.IpAddress},Domain,SQLEdition,SQLVersion,PhysicalMemory_MB,SQLMaxMemory_MB,CPUNumber,TotalCores|Export-csv $exportcsv  -noType -Append
   Get-Job | Wait-Job | Out-Null
   Remove-Job -State Completed

-------------End--------------
SQLSeverInfo_Call.ps1

SL "E:\PowerShell\SQLServersInfo"
.\SQLServerInfo.ps1 -SQLServerList "E:\PowerShell\SQLServerList\SQLSrvList.txt"