Development Lessons of IT Administration

Friday, July 20, 2012

PowerShell: Server Drive Space Report

Below is the PowerShell script code that generates an email report of drive space sizes on servers listed in an array.

#Error Handling
$erroractionpreference = "SilentlyContinue";

#Function for Email Notices
function uEmailNotice([string]$msgBody,[string]$msgSubject)
{
    #Variable for Email FROM Address
    $mFrom = "fromAddress@mycollege.edu";
    #Variable for EMail TO Address
    $mTo = "toAddress@mycollege.edu";
    #Variable for SMTP Server
    $smtp = "smtpServer.mycollege.edu";

    #Settings for Email Message
    $messageParameters = @{                        
                            Subject = $msgSubject
                            Body = $msgBody                       
                            From = $mFrom                        
                            To = $mTo                        
                            SmtpServer = $smtp                       
                           };                        
    #Send Report Email Message 
    Send-MailMessage @messageParameters –BodyAsHtml;
}

#Var for Disk Percentage to Check
$percentCheck = 12;

#Array for Server Names
$Servers = @(
              "server1.mycollege.edu",
              "server2.mycollege.edu",
              "server3.mycollege.edu",
              "server4.mycollege.edu"
             );



#Array for Systems with Low Free Disk Percentages
$arLFDP = @();

#Var for HTML Message Body
$msgBody = "<html>
            <body>
            <h3>Servers Disk Space Report</h3>";
            
#Var for HTML All Server Table Info
$sTableInfo    = "<table border=""0"" cellpadding=""5"" cellspacing=""2"" style=""font-size:8pt;font-family:Arial,sans-serif"">
               <tr bgcolor=""#000099"">
                <td><strong><font color=""#ffffff"">Server</font></strong></td>
                <td><strong><font color=""#ffffff"">Drive</font></strong></td>
                <td><strong><font color=""#ffffff"">Size (GBs)</font></strong></td>
                <td><strong><font color=""#ffffff"">Free Space (GBs)</font></strong></td>
                <td><strong><font color=""#ffffff"">% Free</font></strong></td>
               </tr>";
               
#Loop Through All Servers
foreach($server in $Servers)
{
    #Pull Server Name from FQDN
    $sName = ($server.ToString().Split("."))[0].ToString().ToUpper();
    
    #Compose Table Row for Server Name
    $sTableInfo += "<tr bgcolor=""#dddddd"" cellspacing=""0"">
                    <td>$sName</td>
                     <td colspan=""4""></td> 
                     </tr>";
    
    #Ping Computer Before Attempting Remote WMI 
      if(test-connection -computername $server -quiet) 
      {
        #Make WMI Call to Remote Server
        $sysDrives = Get-WmiObject –Query "Select * FROM Win32_LogicalDisk WHERE DriveType=3" -ComputerName $server;
        
        #Null Check on $sysDrives  
        if($sysDrives)
        {
            #Loop Through Each Logical Disk on Server
            foreach($drive in $sysDrives)
            {
                #Var for Percentage Free Space
                $dPF = "{0:N2}" -f (($drive.FreeSpace / $drive.Size) * 100);
                #Var for Free Space
                $dFS = "{0:N2}" -f ($drive.FreeSpace / 1GB);
                #Var for Disk Size
                $dSize = "{0:N2}" -f ($drive.Size / 1GB);
                #Var for Drive Letter
                $dLetter = $drive.DeviceID.ToString();
                #Double for Percentage Free Comparison
                $freePercent = [double]$dPF.ToString();
                
                #Check to See If Drive Percentage Free Is Greater Than or Equal to Set Alert Amount
                if($freePercent -ge $percentCheck)
                {
                    #Add Disk Info 
                    $sTableInfo += "<tr>
                                    <td></td>
                                    <td>$dLetter</td>
                                     <td>$dSize</td>
                                     <td>$dFS</td>
                                     <td>$dPF</td>
                                     </tr>";
                    
                }
                else
                {    
                    #Add Disk Info with Alert Formatting
                    $sTableInfo += "<tr>
                                    <td></td>
                                    <td><font color=""#ff0000"">$dLetter</font></td>
                                     <td><font color=""#ff0000"">$dSize</font></td>
                                     <td><font color=""#ff0000"">$dFS</font></td>
                                     <td><font color=""#ff0000"">$dPF</font></td>
                                     </tr>";
                    
                    #Create PS Object for Low Disk Space Alert
                       $uEntry = new-Object PSObject;
                       $uEntry | add-Member -memberType noteProperty -name "Server" -Value $sName.ToString().ToUpper();
                       $uEntry | add-Member -memberType noteProperty -name "Drive" -Value $dLetter.ToString();
                       $uEntry | add-Member -memberType noteProperty -name "Percentage" -Value $dPF.ToString();
                       #Add Entry to Summary Array
                       $arLFDP += $uEntry;
                    
                }#End of Percentage Free Check
                                          
            }#End of Foreach Drive
            
        }
        else
        {
            #RPC Not Avaialable
            $sTableInfo += "<tr>
                            <td></td>
                             <td colspan=""4""><font color=""#ff0000"">RPC Not Available</font></td> 
                           </tr>";
            
        }#End of $sysDrives Null Check
        
    }
    else
    {
        #Server Not Pingable
        $sTableInfo += "<tr>
                        <td></td>
                         <td colspan=""4""><font color=""#ff0000"">Ping Failed</font></td> 
                        </tr>";
        
    }#End of Ping Test
    
    #Add Blank Line After Server Info Placed (Readability)
    $sTableInfo += "<tr>
                    <td colspan=""5""></td> 
                    </tr>";
}

#Write Alerts to HTML Message Body If Any
if($arLFDP.Count -gt 0)
{
    $msgBody += "<strong>Servers with Drives Less than $percentCheck% Free</strong><br />
                <table border=""0"" cellpadding=""5"" cellspacing=""2"" style=""font-size:8pt;font-family:Arial,sans-serif"">
                   <tr bgcolor=""#ff0000"">
                    <td><strong><font color=""#ffffff"">Server</font></strong></td>
                    <td><strong><font color=""#ffffff"">Drive</font></strong></td>
                    <td><strong><font color=""#ffffff"">% Free</font></strong></td>
                 </tr>";

    foreach($alert in $arLFDP)
    {
        $msgBody += "<tr><td>" + $alert.Server.ToString() + "</td><td>" + $alert.Drive.ToString() + "</td><td>" + $alert.Percentage.ToString() + "</td></tr>";
    }

    $msgBody += "</table>
                 <br />";
}

#Title All Servers Table
$msgBody +=  "<strong>All Servers</strong><br />";

#Add Servers Table Info to Message Body
$msgBody += $sTableInfo;

#Close HTML Table and Message
$msgBody += "</table>
            </body>
            </html>";
            
#Get Current Short Date
$rptDate = Get-Date -Format d;

#Format Message Subject
$msgSubject = "Servers Disk Space Report for " + $rptDate;

#Email Report
uEmailNotice $msgBody $msgSubject;

PowerShell: Useful Commands for Beginners

During lunch today I presented the first installment of a "Beginning PowerShell" series for colleagues at my work. We covered setting up the console and command syntax. Below are some of the commands we used during the session.

#Get Version of PowerShell Running on System
$PSVersionTable

#Start a Transcript File
Start-Transcript C:\Users\userID\desktop\MyTranscript.txt
#Or for the File to be Placed in the Current Directory
Start-Transcript "MyTranscript.txt"
#Or for the Default Location (..\Do C:\Users\userID\Documents\PowerShell_transcript.NNNNN.txt
Start-Transcript

#To Stop the Transcript from Recording Commands and Output
Stop-Transcript

#Set the Script Execution Policy for Current User 
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

#Get All Currently Loaded PowerShell Snapins
Get-PSSnapin

#Get All Commands in a Specific PowerShell Snapin
Get-Command -pssnapin NameOfPSSnapin #(e.g. Microsoft.PowerShell.Security)

#Get All PowerShell Modules Available on System
Get-Module -ListAvailable

#Import Module in Current PowerShell Session
Import-Module NameOfModule #(e.g. ActiveDirectory)

#Get All Commands in a Module (Should Only Be Used After Importing)
Get-Command -Module NameOfModule

#Get All Currently Loaded Cmdlets
Get-Command -CommandType Cmdlet

#Online Help for a Cmdlet
Get-Help NameOfCmdlet -Online

#Find .NET Object Used in Cmdlet
NameOfCmdlet | Get-Member

Saturday, July 7, 2012

PowerShell: SQL Stored Procedure that Requires a Parameter

Below is a PowerShell code example of how to use a stored procedure (that requires a parameter) on a remote SQL Server. Additionally, I'm using Yes\No prompt (found the code example here)

#Var for SQL Server FQDN 
[string]$SQLServerFQDN = "MySQLServer.mycollege.edu";

#Var for SQL Instance Name
[string]$SQLInstance = "MyInstanceName";

#Var for SQL Database
[string]$SQLDatabase = "MyDatabaseName";

#Var for Insert Stored Procedure Name
[string]$spInsertServer = "Insert_New_Test_Server";

#Connection String Settings (Using Integrated Security So No UserID and Password Needed)
[string]$sqlConString = "Server=$SQLServerFQDN\$SQLInstance;Database=$SQLDatabase;Integrated Security=SSPI;";

#Load .NET System.Data DLL
[Void][system.reflection.assembly]::LoadWithPartialName("System.Data");

#Read In the New Server Name
[string]$newServer = Read-Host "Enter New Server's FQDN";

#Check to See If the Name is Null or Empty
if(![string]::IsNullOrEmpty($newServer))
{
    #Compose Message for Choice Prompt
    [string]$message = "Is " + $newServer + " the Correct FQDN?";

    #Prompt Choices and Options
    $yes = New-Object System.Management.Automation.Host.ChoiceDescription("&Yes");
    $no = New-Object System.Management.Automation.Host.ChoiceDescription("&No");
    $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no);
    #Create Choice Prompt and Assign Reponse Value (Default Set for No)
    $choice = $host.ui.PromptForChoice("New Server", $message, $options, 1);

    #Check to See If User Chose Yes (0=Yes,1=No)
    if($choice -eq 0)
    {
        #Create a SQL Connection Object 
        $sqlCon = New-Object System.Data.SqlClient.SqlConnection($sqlConString);

        #Create and Configure a SQL Command
        $sqlCommSI = New-Object System.Data.SqlClient.SqlCommand($spInsertServer,$sqlCon);

        #Set the Command Type as a Stored Procedure
        $sqlCommSI.CommandType = [System.Data.CommandType]::StoredProcedure;

        #Add Required Parameters (In This Case Just the Server Name)
        [Void]$sqlCommSI.Parameters.Add("@serverName", [System.Data.SqlDbType]::NVarChar);
        $sqlCommSI.Parameters["@serverName"].Value = $newServer.ToUpper();

        #Open the SQL Connection
        $sqlCon.Open();

        #Execute Insert Stored Procedure
        $cmdStatus = $sqlCommSI.ExecuteNonQuery();

        #Close the SQL Connection
        $sqlCon.Close();

        #Check to See If Command Successfully Completed
        if($cmdStatus -eq 1)
        {
            Write-Output "Command Completed Successfully";
        }
        else
        {
            Write-Output "No Go At This Station";
        }
        
    }
    else
    {
        Write-Output "Please Start Script Over";
    }#End of Choice Check
}
else
{
    Write-Output "Nothing Entered. Please Start Script Over";
}#End of New Server Name Null or Empty Check

PowerShell: Query Remote SQL Server Data Using Stored Procedure

Found a while back a cool blog entry on "Database Access within PowerShell". Using that as a reference, I started configuring a few scripts to utilize stored procedures when accessing SQL Server data. Below is a quick example. Enjoy.

#Var for SQL Server FQDN 
$SQLServerFQDN = "MySQLServer.mycollege.edu";

#Var for SQL Instance Name
$SQLInstance = "MyInstance";

#Var for SQL Database
$SQLDatabase = "MyDatabase";

#Create Empty Array for Storing Server Names
$servers = @();

#Load .NET System.Data DLL
[Void][system.reflection.assembly]::LoadWithPartialName("System.Data");

#Connection String Settings (Using Integrated Security So No UserID and Password Needed)
$sqlConString = "Server=$SQLServerFQDN\$SQLInstance;Database=$SQLDatabase;Integrated Security=SSPI;";

#Stored Procedure Name for Servers Select Statement
$spSelectServers = "Get_All_Servers";

#Create a SQL Connection Object 
$sqlCon = New-Object System.Data.SqlClient.SqlConnection($sqlConString);

#Create and Configure a SQL Command
$sqlCommSR = New-Object System.Data.SqlClient.SqlCommand($spSelectServers,$sqlCon);

#Set the Command Type as a Stored Procedure
$sqlCommSR.CommandType = [System.Data.CommandType]::StoredProcedure;

#Open the SQL Connection
$sqlCon.Open();

#Execute the Command
$sqlRdrSR = $sqlCommSR.ExecuteReader();

#Read Through the Returned Data
#This Specific Stored Procedure Returns a "Server_Name" Column
while ($sqlRdrSR.Read()) 
{
    $servers += $sqlRdrSR["Server_Name"].ToString().Trim();
}

##Close the SQL Reader and Connection
$sqlRdrSR.close();
$sqlCon.close();

#Loop Through Array and Output Server Name
foreach($server in $servers)
{
    Write-Output $server;
}

Wednesday, April 25, 2012

C# and PowerShell EHLO Response

Earlier today, one of my colleagues put down a challenge to create in .NET and PowerShell a way detect if a remote email server was running Exchange via a EHLO response. Below are the code examples that completed the task.

C# (Console Application)
//Var for TCP Port
Int32 port = 25;
//Byte Array for Response Data
Byte[] rData = new Byte[256];
//Var for Converted Response Data
string responseData = string.Empty;
//Create a TcpClient
TcpClient client = new TcpClient("smtp.dept.mycollege.edu", port);
//Byte Array for Message to Be Sent
Byte[] data = System.Text.Encoding.ASCII.GetBytes("EHLO");
//Initate a Stream
NetworkStream stream = client.GetStream();
//Send Message to Server
stream.Write(data, 0, data.Length);
//Initiate Int for Return Bytes and Read Response of Stream
Int32 bytes = stream.Read(rData, 0, rData.Length);
//Convert Response
responseData = System.Text.Encoding.ASCII.GetString(rData, 0, bytes);
//Write Out Response
Console.WriteLine(responseData);
//Close Stream and Client
stream.Close();
client.Close();

PowerShell
#Var for TCP Port
[int]$port = 25;
#Byte Array for Response Data
$rData = New-Object Byte[](256);
#Var for Converted Response Data
$responseData = "";
#Create a TcpClient
$tClient = New-Object System.Net.Sockets.TcpClient("smtp.dept.mycollege.edu",$port);
#ASCII Encoder
$encode = [System.Text.Encoding]::ASCII
#Byte Array for Message to Be Sent
[Byte[]]$sData = $encode.GetBytes("EHLO");
#Initiate a Stream
$stream = $tClient.GetStream();
#Send Message to Server
$stream.Write($sData,0,$sData.Length);
#Initiate Int for Return Bytes and Read Response of Stream
[int]$bytes = $stream.Read($rData,0,$rData.Length);
#Convert Response
$responseData = $encode.GetString($rData,0,$bytes);
#Notify User
Write-Host $responseData;
#Close Stream and Client
$stream.Close();
$tClient.Close();

Sunday, March 11, 2012

PowerShell: AD Create OU and Copy Group Policies

A few weeks ago, a need to came up for a script that would create a new dept OU, and standard sub OUs inside it, and then assign the same 60+ group policies from an another dept OU (including the standard sub OUs). Below the script that took care of this request.

########################################################

# Script Name: AD_Create_OU_and_Link_GPOs.ps1

# Author: Dean Bunn

# Description: Creates Dept OU and Assigns GPOs

########################################################

#Load Group Policy Module

Import-Module grouppolicy

#Var for New Department Name

$deptName = "DeptA"

#Var for Distinguished Path of Parent OU

$parentD = "OU=DEPARTMENTS,DC=ChildDomain,DC=MyCollege,DC=EDU"

#Var for GPO Source OU

$gpoSrcOU = "OU=DeptB,OU=DEPARTMENTS,DC=ChildDomain,DC=MyCollege,DC=EDU"

#LDAP String for Parent Path

$ldapParent = "LDAP://" + $parentD

#Var for OU Class

$class = "organizationalUnit"

#Array for Storing OU Info

$arrOUs = @()

#Array of OU Path with ? for Dept Name Place Holder

$deptOUs = @("OU=?",

"OU=?-OU-Computers,OU=?",

"OU=?-OU-Groups,OU=?",

"OU=?-OU-LocalUsers,OU=?",

"OU=?-OU-Servers,OU=?",

"OU=?-OU-Test Servers,OU=?",

"OU=Faculty-Staff-Grad,OU=?-OU-Computers,OU=?",

"OU=IT,OU=?-OU-Computers,OU=?",

"OU=LAB,OU=?-OU-Computers,OU=?",

"OU=STAFF,OU=?-OU-Computers,OU=?"

)

#Retrieve Parent OU using ADSI

$parentOU = [ADSI]$ldapParent

#Split GPO Source OU String to Get OU First Name

$gOFN = ($gpoSrcOU.ToString().Split(","))[0].ToString().ToLower().Replace("ou=","")

#Loop Through OU Paths

foreach($dOU in $deptOUs)

{

#Replace ? Character with Name of Dept

$ouDN = $dOU.ToString().Replace("?",$deptName)

#Create OU Object and Save It

$oOU = $parentOU.create($class,$ouDN)

$oOU.setInfo()

#Replace ? Character with Name of GPO Source OU and Extra Comma (Will Be Used Later to Compare)

$gpoOUStart = $dOU.ToString().Replace("?",$gOFN) + ","

#Var for the Full Path of the New OU (Used Later When Assigning Linked GPOs)

$newOUFullPath = $ouDN + "," + $parentD

#Create PS Object and Assign OU Data

$uEntry = new-Object PSObject

$uEntry | add-Member -memberType noteProperty -name "gpoOUStart" -Value $gpoOUStart.ToString().ToLower()

$uEntry | add-Member -memberType noteProperty -name "newOUFullPath" -Value $newOUFullPath.ToString()

#Add PS Object to OU Array

$arrOUs += $uEntry

}

#Pause the Script for One Minute to Give AD Time to Acknowledge OU Creation

Start-Sleep -Seconds 60

#Var for LDAP String Path

$gSOLP = "LDAP://" + $gpoSrcOU

#Query AD for All OUs in the GPO Source OU Path

$ADsPath = [ADSI]$gSOLP

$Search = New-Object DirectoryServices.DirectorySearcher($ADsPath)

$Search.filter = "(objectClass=organizationalunit)"

$Search.PageSize = 900

$Search.SearchScope = "SubTree"

$Results = $Search.FindAll()

#Loop Through Each Result

foreach($result in $Results)

{

#Retrieve OU Directory Entry

$objOU = $result.GetDirectoryEntry()

#Loop Throuh Each Individual OU PS Object in the OU Array

foreach($iOU in $arrOUs)

{

#See If Source GPO OU Path Starts with the Same Structure as One We Created Earlier

if($objOU.DistinguishedName.ToString().ToLower().StartsWith($iOU.gpoOUStart))

{

#Retrieve the GPOs Linked on the GPO Source OU

$srcGPOS = Get-GPInheritance -target $objOU.DistinguishedName.ToString()

#Loop Through All Linked GPOs

foreach($gp in $srcGPOS.GpoLinks)

{

#Convert the GpoId to a GUID

$guidGPO = [Guid]$gp.GpoId

#Link the GPO to the New OU

New-GPLink -guid $guidGPO -target $iOU.newOUFullPath -LinkEnabled Yes -domain "childdomain.mycollege.edu"

}#End Foreach GpoLinks

}#End OU DN StartsWith Check

}#End Foreach on PS Object Array

}#End Foreach OU in GPO Source OU

PowerShell: Profile Settings

Last week, I found a cool link (listed in references) that showed me how to configure a PowerShell profile. Basically, it’s a PowerShell script that runs every time you start an instance of PowerShell. I like the idea of not having to type in the same forest command each time we started up the EMS (Exchange Management Shell); however, when I tried configure the forest command in profile script and clicked the EMS link it would error out since it ran the profile script first then the RemoteExchange.ps1 script used in the EMS shortcut. I got around this by configuring my PS profile to check for the existence of Exchange script and then running it like the EMS shortcut would. Then I added the forest command and whammo I have a PS console ready to go to work.

Here is the code from my Microsoft.PowerShell_profile.ps1 file.

#Dean's PowerShell Console Settings

#Place Code in C:\Users\UserID\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

#Create Required File Using: New-item -path $profile -type file -force

#Change Console Colors to Black and Green

$host.UI.RawUI.BackgroundColor = "Black";

$host.UI.RawUI.ForegroundColor = "Green";

#Other Available Color Choices

#Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White

#Change Error Text Color to White (If You Were Using Red for Normal Text)

((Get-Host).PrivateData).ErrorForegroundColor = "White";

#Change Warning and Verbose Text Color to Magenta (If You Were Using Yellow for Normal Text)

#((Get-Host).PrivateData).WarningForegroundColor = "Magenta";

#((Get-Host).PrivateData).VerboseForegroundColor = "Magenta";

#Change Error and Warning Background Color to Gray (If You Were Using Black for Normal Text)

#((Get-Host).PrivateData).ErrorBackgroundColor = "Gray";

#((Get-Host).PrivateData).WarningBackgroundColor = "Gray";

#Clear the Console to Load New Color Settings

Clear-Host;

#Load Exchange If On System

if(Test-Path $env:ExchangeInstallPath\bin\RemoteExchange.ps1)

{

.$env:ExchangeInstallPath\bin\RemoteExchange.ps1;

Connect-ExchangeServer -auto;

Set-ADServerSettings -ViewEntireForest $true;

}

#Change the Prompt Configuration

function prompt

{

#Get the Current Directory

$path = Get-Location;

#Set Prompt for the Computer Name then Directory Path

"PS [$env:computername] $path>";

}

#Change the Window Title

$host.UI.RawUI.WindowTitle = "Dean's PS Goodness";

#Change Location to the Desktop

$dsktop = [Environment]::GetFolderPath("Desktop").ToString();

cd $dsktop;

References:

How to use a PowerShell Profile to simplify tasks

http://www.techrepublic.com/blog/networking/how-to-use-a-powershell-profile-to-simplify-tasks/5393

Managing Exchange 2010 with Remote PowerShell

http://www.mikepfeiffer.net/2010/02/managing-exchange-2010-with-remote-powershell/