Development Lessons of IT Administration: July 2012

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;
}