Feeds:
Posts
Comments

Archive for the ‘PowerShell’ Category

Disk space is one of those things that frequently runs out of space no matter how much you bump it up irrespective of the service you are running in the server. I know storage is cheap but who wouldn’t want to keep an eye on what’s cooking especially when it has the potential to bring things to halt?

I believe that if you run a Windows Service, or if anything Windows is your job, then implementing PowerShell will make your job a lot easier and lot more fun. This PowerShell script calculates free disk spaces in multiple servers (from a text file) and emails the report in a HTML format. The script can be scheduled using Windows Scheduler or SQL Agent Job to run at a certain time or interval. This is designed to report only servers with less than 20% free space but you can customize for your needs

Prerequisite:

If you have never used PowerShell on your system before, chances are that your PowerShell “Execution Policy” is set to restrict execution of scripts on your machine, and you’ll have trouble running this script. To allow your scripts to execute, you need to set your Execution Policy to RemoteSigned. Here is the procedure to, first of all, check what yours is set to, and then, if necessary, set it to RemoteSigned.

  • Run PowerShell as Administrator on your PC/Server
  • Enter in and run the Get-ExecutionPolicy cmdlet – this will output the current setting. If it is not alreadyRemoteSigned, or Unrestricted, then use the following cmdlet to set it to allow your scripts to run:Set-ExecutionPolicy RemoteSigned
  • You should now be asked to confirm whether you are sure. Cick Yes to confirm as shown below

1

Now that your environment is ready to run the cmdlets and scripts, lets take a look at the basic rundown of the script’s processes:

  1. Iterate through a list of servers you specify in a text file, checking disk space.
  2. Check each free disk space percentage figure against a pre-defined percent threshold figure.
  3. If the disk in question is below this threshold, then add the details to the report, if not, skip past it.
  4. Assemble an e-mail and send it off to the specified recipient(s) if any of the drives were below the free disk space threshold.

The Script


#########################################################
#
# SQLSERVERZEST: Server Disk Space monitoring Report
#
#########################################################
 
#### Provide Below email and SMTP details ####

$fromemail ="abc@email.com" 
$users="recipients@email.com"
$Server= "smptserver.DomainName.Com"

$computers = get-content -Path "//ServerName/../Servers.txt"  # Specify servers' list path


# Set free disk space threshold below in percent (default at 20%)
[decimal]$thresholdspace = 20
 

 #### Main Sctipt Block ####

$tableFragment= Get-CimInstance -ComputerName $computers cim_LogicalDisk -erroraction 'silentlycontinue' `
| select SystemName, DriveType, VolumeName, Name, @{n='Size (Gb)' ;e={"{0:n2}" -f ($_.size/1gb)}},@{n='FreeSpace (Gb)';e={"{0:n2}" -f ($_.freespace/1gb)}}, @{n='PercentFree';e={"{0:n2}" -f ($_.freespace/$_.size*100)}} `
| Where-Object {$_.DriveType -eq 3 -and [decimal]$_.PercentFree -lt [decimal]$thresholdspace} `
| ConvertTo-HTML -fragment 


#### HTML for our body of the email report ####

$HTMLmessage = @"
<font color=""Red"" face=""Segoe UI Light, Segoe UI Light"" size=""8"">
<u><b>Disk Space Storage Report</b></u>
<br>This report was generated because the drive(s) listed below have less than $thresholdspace% free space. Drives above this threshold will not be listed.
<br>
<style type=""text/css"">body{font: .8em ""Segoe UI Light"", Segoe UI Light, Segoe UI Light, Segoe UI Light, Segoe UI Light;}
ol{margin:0;padding: 0 1.5em;}
table{color:#FFF;background:#C00;border-collapse:collapse;width:647px;border:5px solid #900;}
thead{}
thead th{padding:1em 1em .5em;border-bottom:1px dotted #FFF;font-size:120%;text-align:left;}
thead tr{}
td{padding:.5em 1em;}
tfoot{}
tfoot td{padding-bottom:1.5em;}
tfoot tr{}
#middle{background-color:#900;}
</style>
<body BGCOLOR=""white"">
$tableFragment
</body>
"@
 
# Set up a regex search and match to look for any <td> tags in our body. These would only be present if the script above found disks below the threshold of free space.
# We use this regex matching method to determine whether or not we should send the email and report.
$regexsubject = $HTMLmessage
$regex = [regex] '(?im)<td>'
 
# if there was any row at all, send the email
if ($regex.IsMatch($regexsubject)) {
 send-mailmessage -from $fromemail -to $users -subject "Disk Space Monitoring Report" -BodyAsHTML -body $HTMLmessage -priority High -smtpServer $server
}
 
# End of Script

Here is the sample email report

2

 

This is just a quick report that I developed but as with any scripting language, PowerShell will give you plenty of customization to modify the look and feel of your report as desired.

Advertisements

Read Full Post »

Often times it is required to find out the SQL name from the host names in a clustered environment so you can use it to connect through SSMS. There are many ways you can achieve this, however I am showing 2 methods here. Method 1 is the most common way using Failover Cluster Manager (ideal when there is one or just a few servers) and second method focuses on getting the same information using PowerShell (Ideal when you are dealing with a large number of servers)

Method 1:

The easiest way (if not the fastest) is to load up the Failover Cluster Manager and go to each Failover Clustered instance to look for the virtual SQL names. You can also easily see which FCIs are running on a particular node by selecting a node and viewing what resources it currently owns as shown in the below snippets.

Poweshell_SQLVirtualName

This is the same for Windows 2003/ 2008+ and SQL 2005/ 2008.

Method 2:

While the above method is pretty easy, its not practical to log into each server to load up FCM when you have tens of hundreds of servers. For this you can use this simple PowerShell script that can be run remotely and can be fed with as many servers as you want to. This will output the SQL virtual names including those with named instances. Please note that the PowerShell cmdlets used here require admin rights on all of the nodes in the cluster or a security exception will be thrown.

Steps:

1.  Create a notepad file “TestServers” on your desktop that contains the list of servers (Host names) that you want to run the code against

2

2. Open Powershell with Admin Privileges

3. Use the below script to load the servers list  into a Variable (Copy & paste the below code in PowerShell and click Enter – Make sure to change the location of your text file)

$machines = get-content C:\Users\Desktop\testservers.txt 

4. Use the below script to call the servers from the above variable to get the output

import-module failoverclusters
foreach ($machine in $machines) {get-clusterresource -Cluster 
$machine -ErrorAction SilentlyContinue| where-object 
{$_.ResourceType -like ?SQL Server?}| 
get-clusterparameter VirtualServerName,InstanceName | 
group-object ClusterObject | select-object @{Name = ?SQLInstance?;
Expression = {[string]::join(?\?,($_.Group | 
select-object -expandproperty Value))}}} 

Read Full Post »