SQL Server: Discuss Execute(SQL) At LinkedServer

Abstract

This article details the use of “Execute AT LinkedServer” clause. It’s best in some ways when you are trying to run dynamic complex queries  across heterogeneous data source. There are many instances that OpenQuery/OpenRowSet and four-part qualifier calling might not work in the complex SQL design.  The limitation of linked server will explode when are trying to manipulate data and write complex queries with heterogeneous data sources.

Table of Contents

Posted in Uncategorized | Tagged , , , , , | Leave a comment

SQL : How to Find Unused Indexes details

The Dynamic Management View (DMV) named sys.dm_db_index_usage_stats that track  index usage details of the database. This DMV gives an information about an index which is being updated but not used in any seeks, scan or lookup operations.

The below query list

  1. Table name
  2. Index name
  3. No of Rows
  4. Size of Index
  5. Type of Index
  6. Drop SQL statement

Index dropping is done at your risk. Validate.. the data before dropping any information from the database.

select object_name(i.object_idas ObjectNamei.name as [Unused Index],MAX(p.rowsRows 
,8 * SUM(a.used_pagesAS 'Indexsize(KB)'case  
    when i.type = 0 then 'Heap'  
    when i.type1 then 'clustered' 
    when i.type=2 then 'Non-clustered'   
    when i.type=3 then 'XML'   
    when i.type=4 then 'Spatial'  
    when i.type=5 then 'Clustered xVelocity memory optimized columnstore index'   
    when i.type=6 then 'Nonclustered columnstore index'  
end index_type'DROP INDEX ' + i.name + ' ON ' + object_name(i.object_id'Drop Statement' 
from sys.indexes i 
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id 
     and i.index_id = s.index_id 
     and s.database_id = db_id() 
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id 
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id 
where objectproperty(i.object_id'IsIndexable') = 1 
AND objectproperty(i.object_id'IsIndexed') = 1 
and s.index_id is null -- and dm_db_index_usage_stats has no reference to this index 
or (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)-- index is being updated, but not used by seeks/scans/lookups 
GROUP BY object_name(i.object_id) ,i.name,i.type 
order by object_name(i.object_idasc

 

Posted in SQL, Uncategorized | Tagged , , , | Leave a comment

PowerShell : CSV – Disk Space Report – Excel

Abstract

Monitoring disk space utilization of server(s) is a critical and important job for any administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 10 years, I have faced and handled/managed lot of issues with disk space. This article takes us through the in-detail steps to read each drive and report every drive details based on threshold values. The output is integrated with excel charts. The step by step process quickly take us through the disk space utilization details of server(s). You’ll basically feed a list of servers to watch over, and it will report back on these for you, meaning you could also use it as a more general “daily server disk space report”

↑ Return to Top


Introduction

This article talks about the use of credentials. The credentials can be used to query external servers which have a trust relationship between the domains. Also, list various methods to secure the password. The process iterates through a list of servers and drives that you have listed in a csv file. Checking for disk space status of every listed drive and its status may fall under one of the four statuses that is defined as critical, warning, low and good. If the disk in question is below the threshold then increment the corresponding status counter which acts as a data source for depicting excel charts. The nice thing about this script is that it will consolidate health status of each listed disks and gives a summary that needs your attention (you set the threshold as per requirement because the size of the drive may vary from server to server).

↑ Return to Top


Querying WMI objects – Win32_LogicalDisks

  • Using Credentials
  • Without using Credentials

Using Credentials

Get-credential always pop-up dialog box for entering a password, however, you can save your securestring password to a file or directly feed the password. The problem with this is that the password will be exposed to anyone with access to the file.

  • Using Get-Credential cmdlet – Pop up dialog box
  • Directly using password
  • Using secured file

Using Get-Credential cmdlet – Pop dialog box

The Get-Credential displays a window to enter credential details. This will appear every time when you run the script.The $credential variable store the username and password. It’s then fed to the respective queries for further processing.

clear
$credential = Get-Credential 
foreach ( $args in get-Content c:\server.txt ) {
get-WmiObject win32_logicaldisk -Credential $credential -ComputerName $args -Filter "Drivetype=3"  |
ft SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} -autosize
}

Hard code the credentials

The password is hard coded in the script. Of course, the problem with this is that your password will be exposed to anyone with access to the script file.

$User = 'hqnt\abcd'
 $Pass = ConvertTo-SecureString 'abcd@2015' -AsPlainText -Force
 $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
 foreach ( $args in get-Content c:\server.txt ) {
get-WmiObject win32_logicaldisk -ComputerName $args -Credential $Credentials -Filter "Drivetype=3"  |
ft SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} -autosize
}

Using Secured file

First, Password has to be written to a file

ps:\>read-host -AsSecureString |ConvertFrom-SecureString |Out-File C:\SecurePassword.txt

Second, The credentials are read from the file using PSCredential class. You don’t need to re-enter the password over and over again.

clear
$User = 'hqnt\abcdv'
$pass= cat C:\passwordstring.txt |ConvertTo-SecureString
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
foreach ( $args in get-Content c:\server.txt ) {
get-WmiObject win32_logicaldisk -ComputerName $args -Credentials $cred -Filter "Drivetype=3"  |
ft SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} -autosize
}

↑ Return to Top


Without using CredentialsYou don’t need to pass credential parameter in any of the cmdlet execution.

clear
foreach ( $args in get-Content c:\server.txt ) {
get-WmiObject win32_logicaldisk -ComputerName $args -Filter "Drivetype=3"  |
ft SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} -autosize
}

↑ Return to Top


Download – Gallery link

↑ Return to Top


Code in detail

This section describes the coding and other details

Input File

The template of server.csv is given below. Change the content as per your requirement/environment

Server,Drive,LowTh,WarnTh,CritTh
HQDBSP008,E:,8,5,3
HQDBSP008,F:,8,20,3
HQDBSP0018,G:,8,5,3
HQSPDB9901,E:,8,5,3
HQSPDB0901,F:,20,5,3
HQSPDB8001,G:,8,5,3

Output and filename

This below code defines the output file location and filename. The $date variable hold the date formatting part. It’s then appended to the $filename to generate the most meaningful filename. For example, c:\DMZ_Server_Space_Utilization-2016-09-16

$DirectoryToSaveTo = "c:\"
$date=Get-Date -format "yyyy-MM-d"
$Filename="DMZ_Server_Space_Utilization_$($date)"

Password

This portion of code decides whether to pass credentials or not.  The Get-credential always pop-up dialog box for entering a password, however, you can save your securestring password to a file or directly feed the password. The problem with this is that the password will be exposed to anyone with access to the file. If you want to use the default login credentials then you don’t need to mention anything in the code. You can comment the line of code.

$User = 'abcd'
$Pass = ConvertTo-SecureString ''abcd@#2016' -AsPlainText -Force
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass

Invoking Excel components

PowerShell is made available to use Excel component by invoking excel.application COM object that will allow us to work with excel to add data and format that data.  The New-object -ComObject  creates a new excel object using COM components and it made visible for troubleshooting using visible property set to true.  Once everything is defined, we can call the respective methods and properties to create workbook by adding sheet as its item

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
#you can see the Excel sheet and helpful in troubleshooting
$Excel.visible = $True
#After instantiating the excel objects, It's a time use its respective methods and properties
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

Workbook number and details

FileFormat numbers in Mac Excel. These are the main file formats in Windows Excel 2007-2016:

51 = xlOpenXMLWorkbook (without macro’s in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

$xlOpenXMLWorkbook=[int]51

Header and Coloring

Create a header for Disk Space Report, setting each cell to Bold and add a background color. The excel generates Computername, DeviceID, VolumeName,TotalSizeGB,
UsedSpaceGB, FreeSpaceGB, %Free and Status columns. The variable $column is incremented by 1 each time to create a new column.

$Sheet.Cells.Item($row,$column)= 'Computername'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'DeviceID'
#Making the font Bold and adjusting the background color (using the Interior.ColorIndex property of each cell
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'VolumeName'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'TotalSizeGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'UsedSpaceGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'FreeSpaceGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= '%Free'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'Status'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
#Set up a header filter
$headerRange = $Sheet.Range("a3","o3")
$headerRange.AutoFilter() | Out-Null

Import Server and Drive details

This part of the code read values from CSV file. The CSV file has five columns Server, Drive, LowTh, WarnTh, CritTh. The server.csv file shown in the below figure has a specific set of values for each server as well as for each drive. The code reads values and loop through every server.After reading from CSV file, the values are assigned to a local variable and then it’s used to query WMI query by filtering on -computername and -DeviceID which is marked green in color. The credentials parameter would be really helpful when you are querying different domains which have trust relationship  in between. If you are querying the servers that are under the same domain and want to use the default logon credentials then you can ignore the credential parameter.
The next part is to calculate the “free %” and writing data to a sheet.The status column is populated based on input threshold values. The $percentageFree will be used to identify the status of the drive. The four status of each drive is Critical, Warning, Low and Good. The three input parameters $clowth, $cwarnth,$ccritth compared with $percentageFree variable to yield a result for status columns.
Also, the code has used four counters to keep track of status column. The value of the counter is incremented whenever it meets the condition. The final value is used to derive an overall health of the drives of the listed servers.

Import-Csv C:\server.csv|%{
$cserver = $_.Server
$cdrivelt = $_.Drive
$clowth = $_.LowTh
$cwarnth = $_.WarnTh
$ccritth = $_.CritTh
$diskinfo= Get-WmiObject -Class Win32_LogicalDisk -ComputerName $cserver  -Filter "DeviceID='$cdrivelt'" -Credential $Credentials
ForEach ($disk in $diskinfo)
{
If ($diskinfo.Size -gt 0) {$percentFree = [Math]::round((($diskinfo.freespace/$diskinfo.size) * 100))}
Else {$percentFree = 0}
#Process each disk in the collection and write to spreadsheet
    $Sheet.Cells.Item($row,1)= $disk.__Server
    $Sheet.Cells.Item($row,2)= $disk.DeviceID
    $Sheet.Cells.Item($row,3)= $disk.VolumeName
    $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2)
    $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))
    
    #Determine if disk needs to be flagged for warning or critical alert
    If ($percentFree -le  $ccritth) {
        $Sheet.Cells.Item($row,8) = "Critical"
        $critical++
        #Check to see if space is near empty and use appropriate background colors
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null   
        #Critical threshold        
        $range.Interior.ColorIndex = 3
    } ElseIf ($percentFree -gt $ccritth -AND $percentFree -le $cwarnth) {
        $Sheet.Cells.Item($row,8) = "Warning"
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null       
        $warning++
        $range.Interior.ColorIndex = 6
        }
     ElseIf ($percentFree -ge $cwarnth -AND $percentFree -lt $clowth) {
        $Sheet.Cells.Item($row,8) = "Low"
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null       
        $low++
        $range.Interior.ColorIndex = 12
        
    } Else {
        $Sheet.Cells.Item($row,8) = "Good"
        $good++
    }
     $row++
}
}

Charting

The final piece that needs to be added is a chart. The chart shows the number of critical, warning, low  and good states for each drive. Also, the code has used four counters to keep track of status column. The value of the counter is incremented whenever it meets the condition. The final value is used to derive an overall health of the drives of the listed servers.

The first part of the code is preparing an heading and second part assigns the corresponding values to its respective column headers.

$Sheet.Cells.Item($row,$Column) = 'Critical'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Warning'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Low'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Good'
$Column = 1
$row++
#Critical formula
$Sheet.Cells.Item($row,$Column)=$critical
$Column++
#Warning formula
$Sheet.Cells.Item($row,$Column)=$warning
$Column++
#low formula
$Sheet.Cells.Item($row,$Column)=$low
$Column++
#Good formula
$Sheet.Cells.Item($row,$Column)= $good

For example,

Critical Warning Low Good
1 1 1 3

The expanded value of pie char is 70

#Configure the chart
##Use a 3D Pie Chart
$chart.ChartType = 70
$chart.Elevation = 40


The below code places the graph to the top left of the sheet.

#Set the location of the chart
$sheet.Shapes.Item("Chart 1").Placement = 3
$sheet.Shapes.Item("Chart 1").Top = 30
$sheet.Shapes.Item("Chart 1").Left = 600

↑ Return to Top


Code

<#

.SYNOPSIS
Name :  Disk Space Utilizaiton Report (Get-DiskSpaceExcel.ps1)
Description : Get disk space usage informations from remote server(s) with WMI and ouput Excel file

Author : Prashanth Jayaram

* Some ideas extracted Joe Prox Excel Charting. Refer the reference secion for more information
* Select list of servers from a CSV file
* Get remote Servers informations with WMI and Powershell :
* Disk (Disk type, letter, capacity in GB, free space in GB, % free , Status + display a Excel output)

.INPUT
.csv file with servers to activate

.OUTPUTS
Console outputs : You can alter the code to write the data to file or console

.NOTES
Version:        1.0
Author:         Prashanth Jayaram
Creation Date:  2016-26-09
Purpose/Change: Initial script development

.EXAMPLE
.\Get-DiskSpaceExcel.ps1
#>

#########################################################################################
#DirectoryPath - Make sure you have enough rights to write to that path
#########################################################################################
#### Spreadsheet Location - Make sure you change as per your requirement

 $DirectoryToSaveTo = "c:\"
 $date=Get-Date -format "yyyy-MM-d"
 $Filename="DMZ_Server_Space_Utilication_$($date)"
  
 ##Get-credential always pop-up dialog box for entering a password, however you can save your securestring password to a file or directly feed the password.
 ##The problem with this is that the password will be exposed to anyone with access to the file.
  
####User Credentials to access servers
 
 $User = "ccov648"
 $Pass = ConvertTo-SecureString "thanVitha@2015" -AsPlainText -Force
 $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
 
 
# 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
  }
   
 
#PowerShell is made available to use Excel componment by invoking excel.application COM object that will allow us to work with excel to add data and format that data.
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
#you can see the Excel sheet and helpful in troubleshooting
$Excel.visible = $True
#After instantiating the excel objects, It's a time use its respective methods and properties
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
 
#Save the initial row so it can be used later to create a border
#Counter variable for rows
$intRow = $row
 
#FileFormat numbers in Mac Excel
 
#These are the main file formats in Windows Excel 2007-2016:
 
#51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
#52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
#50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
#56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
 
$xlOpenXMLWorkbook=[int]51
 
#define the sheet name
 
$sheet.Name = 'DiskSpace'
 
$Sheet.Activate() | Out-Null
 
#Create a Title for the first worksheet
$row = 1
$Column = 1
$Sheet.Cells.Item($row,$column)= 'Disk Space Information'
 
$range = $Sheet.Range("a1","h2")
$range.Merge() | Out-Null
 
# [Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) | select @{n="Name";e={"$_"}},value__
#To fetch the list of alignment values using the above the enumerator.
 
$range.VerticalAlignment = -4160
 
#Give it a nice Style so it stands out
$range.Style = 'Title'
 
#Increment row for next set of data
$row++;$row++
 
#Save the initial row so it can be used later to create a border
$initalRow = $row
 
#Create a header for Disk Space Report; set each cell to Bold and add a background color
$Sheet.Cells.Item($row,$column)= 'Computername'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'DeviceID'
#Making the font Bold and adjusting the background color (using the Interior.ColorIndex property of each cell
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'VolumeName'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'TotalSizeGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'UsedSpaceGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'FreeSpaceGB'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= '%Free'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$Sheet.Cells.Item($row,$column)= 'Status'
$Sheet.Cells.Item($row,$column).Interior.ColorIndex =48
$Sheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
#Set up a header filter
$headerRange = $Sheet.Range("a3","o3")
$headerRange.AutoFilter() | Out-Null
 
#Increment Row and reset Column back to first column
$row++
$Column = 1
$critical=0
$warning=0
$low=0
$good=0
 
#Get the drives and filter out CD/DVD drives
 
Import-Csv C:\server.csv|%{
$cserver = $_.Server
$cdrivelt = $_.Drive
$clowth = $_.LowTh
$cwarnth = $_.WarnTh
$ccritth = $_.CritTh
 
$diskinfo= Get-WmiObject -Class Win32_LogicalDisk -ComputerName $cserver  -Filter "DeviceID='$cdrivelt'" -Credential $Credentials
ForEach ($disk in $diskinfo)
{
If ($diskinfo.Size -gt 0) {$percentFree = [Math]::round((($diskinfo.freespace/$diskinfo.size) * 100))}
Else {$percentFree = 0}
#Process each disk in the collection and write to spreadsheet
    $Sheet.Cells.Item($row,1)= $disk.__Server
    $Sheet.Cells.Item($row,2)= $disk.DeviceID
    $Sheet.Cells.Item($row,3)= $disk.VolumeName
    $Sheet.Cells.Item($row,4)= [math]::Round(($disk.Size /1GB),2)
    $Sheet.Cells.Item($row,5)= [math]::Round((($disk.Size - $disk.FreeSpace)/1GB),2)
    $Sheet.Cells.Item($row,6)= [math]::Round(($disk.FreeSpace / 1GB),2)
    $Sheet.Cells.Item($row,7)= ("{0:P}" -f ($disk.FreeSpace / $disk.Size))
     
    #Determine if disk needs to be flagged for warning or critical alert
    If ($percentFree -le  $ccritth) {
        $Sheet.Cells.Item($row,8) = "Critical"
        $critical++
        #Check to see if space is near empty and use appropriate background colors
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null  
        #Critical threshold       
        $range.Interior.ColorIndex = 3
    } ElseIf ($percentFree -gt $ccritth -AND $percentFree -le $cwarnth) {
        $Sheet.Cells.Item($row,8) = "Warning"
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null      
        $warning++
        $range.Interior.ColorIndex = 6
        }
     ElseIf ($percentFree -ge $cwarnth -AND $percentFree -lt $clowth) {
        $Sheet.Cells.Item($row,8) = "Low"
        $range = $Sheet.Range(("A{0}"  -f $row),("H{0}"  -f $row))
        $range.Select() | Out-Null      
        $low++
        $range.Interior.ColorIndex = 12
         
    } Else {
        $Sheet.Cells.Item($row,8) = "Good"
        $good++
    }
 
     $row++
}
}
 
 
#Add a border for data cells have used with the VerticalAlignment property.
#[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) | select @{n="Name";e={"$_"}},value__
$row--
$dataRange = $Sheet.Range(("A{0}"  -f $initalRow),("H{0}"  -f $row))
7..12 | ForEach {
    $dataRange.Borders.Item($_).LineStyle = 1
    $dataRange.Borders.Item($_).Weight = 2
}
 
#Auto fit everything so it looks better
 
$usedRange = $Sheet.UsedRange                                                         
$usedRange.EntireColumn.AutoFit() | Out-Null
 
 
$sheet = $excel.Worksheets.Item(1)
  
$row++;$row++
 
$beginChartRow = $Row
 
$Sheet.Cells.Item($row,$Column) = 'Critical'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Warning'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Low'
$Column++
$Sheet.Cells.Item($row,$Column) = 'Good'
$Column = 1
$row++
#Critical formula
$Sheet.Cells.Item($row,$Column)=$critical
$Column++
#Warning formula
$Sheet.Cells.Item($row,$Column)=$warning
$Column++
#low formula
$Sheet.Cells.Item($row,$Column)=$low
$Column++
#Good formula
$Sheet.Cells.Item($row,$Column)= $good
 
$endChartRow = $row
 
$chartRange = $Sheet.Range(("A{0}" -f $beginChartRow),("d{0}" -f $endChartRow))
 
 
$chart = $sheet.Shapes.AddChart().Chart
 
 
#Configure the chart
##Use a 3D Pie Chart
$chart.ChartType = 70
$chart.Elevation = 40
#Give it some color
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.TintAndShade = .34
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5
$sheet.Shapes.Item("Chart 1").Fill.BackColor.TintAndShade = .765
$sheet.Shapes.Item("Chart 1").Fill.ForeColor.ObjectThemeColor = 5
 
$sheet.Shapes.Item("Chart 1").Fill.TwoColorGradient(1,1)
 
#Set the location of the chart
$sheet.Shapes.Item("Chart 1").Placement = 3
$sheet.Shapes.Item("Chart 1").Top = 30
$sheet.Shapes.Item("Chart 1").Left = 600
 
$chart.SetSourceData($chartRange)
$chart.HasTitle = $True
 
$chart.ApplyLayout(6,69)
$chart.ChartTitle.Text = "Disk Space Report"
$chart.ChartStyle = 26
$chart.PlotVisibleOnly = $False
$chart.SeriesCollection(1).DataLabels().ShowValue = $True
$chart.SeriesCollection(1).DataLabels().Separator = ("{0}" -f [char]10)
 
$chart.SeriesCollection(1).DataLabels().Position = 2
#Critical
$chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = 255
#Warning
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 65535
#Low
$chart.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = 265535
#Good
$chart.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = 5287936
 
#Hide the data
#$chartRange.EntireRow.Hidden = $True
 
$sheet.Name = 'DiskInformation'
 
$filename = "$DirectoryToSaveTo$filename.xlsx"
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Sheet.UsedRange.EntireColumn.AutoFit()
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$Excel.Saved = $True
$Excel.Close()

↑ Return to Top


Output

↑ Return to Top


Conclusion

  1. CSV input – Easy to maintain and manage
  2. Customization can be done at the each drive level as the threshold value may vary on every server and most of the drive depending the size of each drive
  3. Graphical representation of Disk Space Usage Utilization report
  4. Simplest way to keep a cap on every drive to set threshold value
  5. Proactive monitoring and alerting respective teams may avoid unforeseen disk space issues

Note:- I would prefer to read the blog references entered under reference section for more in-depth information about charting with PoSH.

↑ Return to Top


References

Technet

Blogs

Posted in PowerShell, Uncategorized | Tagged , , , | 1 Comment

PowerShell: Extending Modules – System Parameter measurement

The Microsoft Technet Guru Award  (August 2016) winning article  – Silver

PowerShell: Extending Modules – System Parameter measurement

PowerShell: Extending Modules – System Parameter measurement

Abstract

The objective of this post to illustrate the various methods of creating PoSH Modules. To explain different methods, have considered PoSH GUI functions which are used measure system metrics such as Disk, Memory, and Top resource consumption Process. It need not be a function; any valid PowerShell script is an eligible object for creating a Module. The three functions are encapsulated in a PowerShell Module.

When Do I Create A PowerShell Module?

You can easily decide whether to create a module by answering the following questions while writing a script

  • Will the code I’m writing need to be used more than once?
  • Does this code essentially manage a single object or entity?
  • As I write this code, do I find that I’m breaking it apart into functions because it’s getting too complex to be in a single script?
  • Do I need to share the code with others?

If you answered yes to at least three of these four questions, it’s a good bet you should be writing a module instead of a PS1 script.

Modules and Snap-Ins

In PowerShell there are 2 main ways to extend the shell, these are:

Modules – A package that contains Windows PowerShell commands in the form of functions, cmdlets, and workflows, in addition, it may contain variables, aliases, and providers. Modules can be written in PowerShell and/or compiled as DLLs.

For example,

Import-Module SQLPS

You don’t need to register the module, need to just import it. on the other hand, the modules can additionally load functions, variables, aliases to your session.

 

 List loaded modules  Get-Module
 List installed modules  Get-Module -ListAvailable
 Show commands in a module  Get-Command -Module “ModuleName”
 Load a specific module  Import-Module -Name “ModuleName”

Snap-Ins – Are compiled cmdlets into a DLL written in a .Net language and need to register before they can be used. Snap-ins can add cmdlets and providers to your session.

For example,

Add-PSSnapin SqlServerCmdletSnapin100
 List Loaded Snap-Ins Get-PSSnapin
 List installed snap-ins  Get-PSSnapin -Registered
 Show commands in a snap-in  Get-Command -Module “SnapinName”
 Load a specific snap-in  Add-PSSnapin “SnapinName”

PoSH Modules

At its simplest, a module is a collection of PowerShell script contained in a file with a .PSM1 extension. A module is some chunk of code that you can import into PowerShell. Once imported, any cmdlets, scripts, or providers can be accessed.

Types of PowerShell modules:

  • Script Modules
  • Binary Modules
  • Manifest Modules
  • Dynamic Modules

Script Modules

PSM1 files that typically contain mostly functions, but can contain any valid PowerShell code. In this case simply take any valid Powershell script *.ps1 and rename it to *.psm1 file and place it in a folder. The paths where you can install your module are located in the $ENV:PSModulePath global variable. You can save the file in a default module path that can be identified using the environmental variable.

PS:\>$ENV:PSModulePath

For example, a common path to save a module on a system would be

  • %SystemRoot%\users\<user>\Documents\WindowsPowerShell\Modules\<modName> 
  • %windir%\System32\WindowsPowerShell\v1.0\Modules
  • %UserProfile%\Documents\WindowsPowerShell\Modules     (preferred).

Be sure to create a folder for your module. If you did not save your module to one of these paths, you would have to pass in the location of your module in the call to Import-Module. Starting with PowerShell 3.0, if you have placed your module on one of the PowerShell module paths, you do not need to explicitly import it: simply having a user call your function will automatically load it.

Pre-requisites

  • Powershell 2.0 and Above
  • Microsoft Chart Controls for Microsoft .NET Framework 3.5 Setup
  • .Net framework 3.0 or 3.5
  • MS charter for GUI modules which displays Graph

Download

Installation

Installation of a module is now very simple. Download and save the content of file in PowerShellGUI.psm1(Append) to any of the above-mentioned path or save it to a specific folderThis PowerShellGUI.psm1 module going to consists of three functions

  • Get-DiskSpaceGUI
  • Get-MemoryGUI
  • Get-ProcessGUI

Import-Module

There are many ways to load a PowerShell Module. The preferred way is to create folder and save the Module in that folderTo import module and loading a module is as follows.

  • Using Relative Path
  • Using Absolute Path

Using Relative Path

Follow the below steps to load the module from a system path

  • Download the module code
  • Create a folder PowerShellGUI (Same name as that of a Module name) file at any of the below location %windir%\System32\WindowsPowerShell\v1.0\Modules OR %UserProfile%\Documents\WindowsPowerShell\Modules (preferred)
  • Save the PowerShellGUI.psm1 %UserProfile%\Documents\WindowsPowerShell\Modules\PowerShellGUI\ (preferred)
  • Once done with step 1, 2 and 3 open a PowerShell window and run below commands.
PS:\>Import-Module -Name PowerShellGUI

This will import the module and functions into PowerShell

PS:\>Get-Module  -ListAvailable

This will list down all available modules in PowerShell

PS:\>Get-Command -Module PowerShellGUI

Using Absolute Path

Save the module to C:\PowerShellGUI.psm1

If we want to add another path for PowerShell to look at we just add that path to the current environment variable:

$env:psmodulepath = $env:psmodulepath + ";c:\PowerShellGUI;"

PS:\> Import-Module -Name C:\PowerShellGUI.psm1
PS:\>Get-Module PowerShellGUI

PS:\>Get-Command -Module PowershellGUI

Output

Binary Modules

Compiled DLL files typically not created by IT pros; these are usually left up to developers.A binary module is a .NET Framework assembly (.dll) that contains compiled code. Cmdlet developers can use this type of module to create modules that contain cmdlets, providers, and more. (Existing snap-ins can also be used as binary modules.)Compiling Binary cmdlets

$code = @'
using System;
using System.Collections.Generic;
using System.Management.Automation;
namespace CustomCmdlet
{
    [Cmdlet("Get", "Magic", SupportsTransactions = false)]
    public class test : PSCmdlet
    {
        private int _Age;
        [Alias(new string[]
        {
            "HowOld", "YourAge"
        }), Parameter(Position = 0,ValueFromPipeline = true)]
        
        public int Age
        {
            get { return _Age; }
            set { _Age = value; }
        }
        private string _Name;
        [Parameter(Position = 1)]
        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        protected override void BeginProcessing()
        {
            this.WriteObject("Good morning...");
            base.BeginProcessing();
        }
        protected override void ProcessRecord()
        {
            this.WriteObject("Your name is " + Name + " and your age is " + Age);
            base.ProcessRecord();
        }
        protected override void EndProcessing()
        {
            this.WriteObject("That's it for now.");
            base.EndProcessing();
        }
    }
}
'@
# compile C# code to DLL
# use a timestamp to create unique file names
# while testing, when a DLL was imported before, it is in use until PowerShell closes
# so to do repeated tests, use different DLL file names
$datetime = Get-Date -Format yyyyMMddHHmmssffff
$DLLPath = "$env:temp\myCmdlet($datetime).dll"
Add-Type -TypeDefinition $code -OutputAssembly $DLLPath
# import a module
Import-Module -Name $DLLPath -Verbose

Manifest Modules

Script modules that contain a manifest.

How to load Multiple Modules

Load Multiple Modules (.psm1) or *.ps1 using a single psd1 file. You can edit the psd1 file and add the modules or files under NestedModule tag. The New-ModuleManifest cmdlet is used to create a module manifest. As shown below, in PowerShell version 3, the only field that you’re required to provide a value for is the path

  • Launch the Powershell ISE
  • Use the New-ModuleManifest command
  • Follow the instructions here – How to Write a Module Manifest. When asked for nested modules, key in the module as Modulepath\Modulename.psm1
  • Finally, once the .psd1 file is created, load / import it using Import-Module <<module-name>>
New-ModuleManifest -Path .\PowerShellGUI\PowershellGUI.psd1 -Author 'Prashanth Jayaram' -CompanyName 'CTS' -Copyright '(c)2016 Prashanth Jayaram' -ModuleVersion 1.0 -PowerShellVersion 2.0 -NestedModules '.\PowerShellGUI\PowerShellGUI.PSM1','\PowerShellGUI\Get-DiskUsage.psm1'

Dynamic Modules

Modules that are never written to disk and are only available in memory.Dynamic modules are basically an extension of script block concept.For Example, Let’s create a script block. It has two functions, one to retrieve disk space and another one to get the Uptime of a given machine

$scriptblock={
Function Get-DiskSpace([string[]]$server)
{
     Get-WmiObject -Class win32_volume -cn $server |Select-Object @{LABEL='Comptuer';EXPRESSION={$server}},driveletter, label,@{LABEL='GBfreespace';EXPRESSION={"{0:N2}" -f ($_.freespace/1GB)}}
} #end function Get-DiskSpace
Function Get-Uptime([string[]]$server) {
   $os = Get-WmiObject win32_operatingsystem -ComputerName $server
   $uptime = (Get-Date) - ($os.ConvertToDateTime($os.lastbootuptime))
   $Display = "Uptime: " + $Uptime.Days + " days, " + $Uptime.Hours + " hours, " + $Uptime.Minutes + " minutes"
   return $Display
}
}

Create a module using New-Module cmdlet

PS P:\> $server=New-Module -ScriptBlock $scriptblock -AsCustomObject

The New-Module cmdlet creates a module in memory using the script block. The -AsCustomObject parameter tells the PowerShell to pass the module as a PowerShell Object.

To retrieve the attributes of the New-Module

$server|Get-Member

Call the module

PS P:\> $SERVER.'Get-DiskSpace'("HQDBSP18")
PS P:\> $SERVER.'Get-DiskSpace'("HQDBSP18")

How to import custom PowerShell module into the remote session?

Enable remoting on the source system by running Enable-PSRemoting on it. create and load the module on Server 1 and call the module Server 2.

PS C:\> $Session = New-PSSession -ComputerName SERVER1
PS C:\> Import-Module -PSSession $foo -Name Get-DiskUsage
PS C:\> Get-DiskUsage

The above opens a PowerShell session on the server SERVER1 and import the Get-DiskUsage module on the remote computer. The command is run on the remote computer while it appears to be executed locally.

Profile

The Scope is limited to a session. If you want to load automatically then you need to create Profile. Rather than typing “Import-Module -Name PowerShellGUI” cmdlet every time you start Windows PowerShell, you can create a Windows PowerShell profile and add this cmdlet to the $profile global variable. After you create the profile, PowerShell Module is automatically added each time when you start Windows PowerShell.

Follow the below steps to create or edit the $profile.

PS:\> test-path $profile
True
PS:\> notepad $profile
PS:\> $profile
C:\Users\ccov648\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1

 

Conclusion

  • Discussed various types of modules which help in understanding of build and package creation in PoSH
  • The seamless approach to navigating remote modules helps in easy management and administration
  • Day to day activities can be customized into modules
  • Automatic loading of modules made easier with the help of profile creation
  • Discussed various method and usage of each module which helps in creating and decision making about the need for module

References

 

 

 

Posted in PowerShell | Tagged | 2 Comments

SQL Server: Monitoring Disk Space with FSUTIL command line utility

Table of Contents

Introduction

This article talks about one of SQL method to fetch the disk usage details and use of T-SQL in conjunction with invoking FSUTIL utility using SQL Windows Shell interface xp_cmdshell. The FSUTIL FSINFO lists all drives, queries the drive type, queries volume information, queries NTFS-specific volume information, or queries file system statistics.  Other SQL Methods to get Disk Space details are  OLE and WMI

Pre-requisites

  •   The FSUTIL utility requires that you have administrative privileges
  •   sysadmin rights on the sql server
  •   Enable xp_cmdshell

Data flow

The below diagram depicts the flow of the code

Enable xp_cmdshell 

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the extended stored procedure can be executed on a system also this procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdShell', 1;
GO
RECONFIGURE;
GO

The below sql lists drives attached to the file system

EXEC [master].[dbo].[xp_cmdshell] N'FSUTIL FSINFO DRIVES'

The FSUTIL volume diskfree command list the drive usage statistics of the filesystem. The below example gathers the statistics of c drive.

declare @driveName varchar(100)
declare @query varchar(1000)
declare @fsinfo varchar(1000)
set @driveName = 'C'
set @query ='FSUTIL VOLUME DISKFREE '+@driveName+':\'
exec xp_cmdshell @query

The below screen shot shows the output of FSUTIL DRIVES and VOLUME.

In the code,  the where clause construct is used to get the non-null value from FSUTIL FSINFO DRIVES output and then manipulated using string function to get only the drive details A\C\D\E\F\G\

  /* Inserting the non-null values to temp table */
   SELECT @workstring = [output]
      FROM @xp_cmdshell_output
      WHERE [output] LIKE 'Drives:%'
   DELETE FROM @xp_cmdshell_output
 /* Prepare string for XML parsing*/
 -----------------------------------------------
 -- Replace string "Drives",":" and " " by ''
 ---------------------------------------------
   SELECT @workstring = REPLACE(REPLACE(REPLACE(@workstring, 'Drives', ''),':',''),' ','')
--PRINT @workstring

Output : A\C\D\E\F\G\

The code uses XML parsing to split the string. The Splitting of delimited strings Using XML is shown below.  The Parsed value is stored in a temporary table for further processing. The parsed output will be processed row by row to get a usage statistics of each drive
SELECT @XML = CAST(
                             ('<X>'
                              + REPLACE(@workstring
                                       ,'\'
                                       ,'</X><X>')
                              + '</X>')
                              AS XML)
INSERT INTO @drives ([Drive])
   SELECT LTRIM(N.value('.', 'VARCHAR(4000)'))
      FROM @XML.nodes('X') AS T(N)
      WHERE ASCII(LTRIM(N.value('.', 'VARCHAR(4000)'))) != 0
 
select * from @drives
 

The next part of the script is to loop through each drive and manipulate the data using string function. The drive detail is fed to FSUTIL VOLUME DISKFREE command and then its output is stored in a temp table variable. Then the output is transformed, manipulated using where clause and string function to fetch numeric value from string output and then updates corresponding Size columns using conversion formula of table variable. The above steps are repeated to updated the Free columns using the similar conversion formula.

SELECT @recid = 1
    WHILE @recid <= (SELECT MAX([RecID]) FROM @drives)
    BEGIN
       SELECT @workstring = ''
             ,@vexec_str = 'EXEC [master].[dbo].[xp_cmdshell] '
                         + QUOTENAME('FSUTIL VOLUME DISKFREE '
                              + [Drive]
                              + ':'
                           ,CHAR(39))
          FROM @drives
          WHERE [RecID] = @recid
       INSERT INTO @xp_cmdshell_output ([output])
          EXEC (@vexec_str)
       SELECT @workstring = [output]
          FROM @xp_cmdshell_output
          WHERE [output] LIKE '%Total # of bytes%'
       IF @workstring IS NOT NULL AND LEN(@workstring) > 0
       BEGIN
          SELECT @workstring = LTRIM(
                                     SUBSTRING(@workstring
                                              ,CHARINDEX(':'
                                                        ,@workstring
                                                        ) + 1
                                              ,LEN(@workstring)
                                              )
                                     )
          SELECT @workstring = LEFT(@workstring, LEN(@workstring))
          /* update the free field and convert its value to GB */
          UPDATE @drives
             SET [Size] = (CONVERT(numeric, @workstring))/1024/1024/1024.00
             WHERE [RecID] = @recid
       END
       ELSE
       DELETE
          FROM @drives
          WHERE [RecID] = @recid

The last part of the code fetches the data from the table variable

SELECT @@ServerName server,Drive,Size,Free, cast(Free/Size * 100.00  as decimal(5,2)) '%Free' FROM@drives

Download

The code is uploaded in the gallery TSQL_DiskSpace_FSUTIL

SQL Code

The complete code is given below. The code has broken into many pieces and explained above with few screen shots.

/* Variable declaration*/
DECLARE @recid         INT
       ,@workstring    VARCHAR(8000)
       ,@XML XML
       ,@vexec_str     VARCHAR(8000)
-- Create table variable to hold drive size info
DECLARE @drives TABLE (
   [RecID]             TINYINT IDENTITY(1,1)             -- Record ID
  ,[Drive]             VARCHAR(10)                       -- Drive letter
  ,[Size]              NUMERIC NULL                       -- Drive size
  ,[Free]              NUMERIC NULL
   )
-- Create table variable for xp_cmdshell output
DECLARE @xp_cmdshell_output TABLE (
   [output]            VARCHAR(8000) NULL                -- Raw text returned from xp_cmdshell execution
   )
INSERT INTO @xp_cmdshell_output ([output])
   EXEC [master].[dbo].[xp_cmdshell] N'FSUTIL FSINFO DRIVES'
   
/* Error handling*/
-----------------------------------------------------------------
--Check for sql server privilge to execute the FSUTIL utility to gather disk status
-----------------------------------------------------------------
IF (SELECT COUNT(1)
       FROM @xp_cmdshell_output
       WHERE [output] = 'The FSUTIL utility requires that you have administrative privileges.') > 0
  RAISERROR ('SQL Server Service account not an admin on this computer.', 11, 1);
ELSE
BEGIN
   /* Inserting the non-null values to temp table */
   SELECT @workstring = [output]
      FROM @xp_cmdshell_output
      WHERE [output] LIKE 'Drives:%'
   DELETE FROM @xp_cmdshell_output
 /* Prepare string for XML parsing*/
 -----------------------------------------------
 -- Replace string "Drives",":" and " " by ''
 ---------------------------------------------
   SELECT @workstring = REPLACE(REPLACE(REPLACE(@workstring, 'Drives', ''),':',''),' ','')
--PRINT @workstring
END
/* XML Parsing - Spilting the delimited string using XML*/
-----------------------------------------------------
-- the string is parsed for the delimiter '\'
-----------------------------------------------------
SELECT @XML = CAST(
                             ('<X>'
                              + REPLACE(@workstring
                                       ,'\'
                                       ,'</X><X>')
                              + '</X>')
                              AS XML)
                              
/* Store the parsed value into table variable */
                             
INSERT INTO @drives ([Drive])
   SELECT LTRIM(N.value('.', 'VARCHAR(4000)'))
      FROM @XML.nodes('X') AS T(N)
      WHERE ASCII(LTRIM(N.value('.', 'VARCHAR(4000)'))) != 0
----
--Display the results
---
select * from @drives
      -- Get size for each drive
      SELECT @recid = 1
      WHILE @recid <= (SELECT MAX([RecID]) FROM @drives)
      BEGIN
         SELECT @workstring = ''
               ,@vexec_str = 'EXEC [master].[dbo].[xp_cmdshell] '
                           + QUOTENAME('FSUTIL VOLUME DISKFREE '
                                + [Drive]
                                + ':'
                             ,CHAR(39))
            FROM @drives
            WHERE [RecID] = @recid
         INSERT INTO @xp_cmdshell_output ([output])
            EXEC (@vexec_str)
         SELECT @workstring = [output]
            FROM @xp_cmdshell_output
            WHERE [output] LIKE '%Total # of bytes%'
         IF @workstring IS NOT NULL AND LEN(@workstring) > 0
         BEGIN
            SELECT @workstring = LTRIM(
                                       SUBSTRING(@workstring
                                                ,CHARINDEX(':'
                                                          ,@workstring
                                                          ) + 1
                                                ,LEN(@workstring)
                                                )
                                       )
            SELECT @workstring = LEFT(@workstring, LEN(@workstring))
            /* update the free field and convert its value to GB */
            UPDATE @drives 
               SET [Size] = (CONVERT(numeric, @workstring))/1024/1024/1024.00
               WHERE [RecID] = @recid
         END
         ELSE
         DELETE
            FROM @drives
            WHERE [RecID] = @recid
             SELECT @workstring = [output]
            FROM @xp_cmdshell_output
            WHERE [output] LIKE '%Total # of free bytes%'
         IF @workstring IS NOT NULL AND LEN(@workstring) > 0
         BEGIN
            SELECT @workstring = LTRIM(
                                       SUBSTRING(@workstring
                                                ,CHARINDEX(':'
                                                          ,@workstring
                                                          ) + 1
                                                ,LEN(@workstring)
                                                )
                                       )
            SELECT @workstring = LEFT(@workstring, LEN(@workstring))
            /* update the free field and convert its value to GB */
            UPDATE @drives
               SET [free] = (convert(numeric, @workstring))/1024/1024/1024.00
               WHERE [RecID] = @recid
         END
         ELSE
         DELETE
            FROM @drives
            WHERE [RecID] = @recid
         DELETE FROM @xp_cmdshell_output
         SELECT @recid = @recid + 1
      END
      
SELECT @@ServerName server,Drive,Size,Free, cast(Free/Size * 100.00  as decimal(5,2)) '%Free' FROM @drives

Output

Conclusion

There are many ways to gather disk space. Its up-to an individual to gather the metrics using available list of tools and utilities. The above steps only briefs about an other way of capturing the details.

References

FSUTIL

Community Question

See Also

The below article gives an idea to execute sql script over multiple servers using sqlcmd

SQL – Disk Space Monitoring using OLE and WMI 

http://social.technet.microsoft.com/wiki/contents/articles/35609.sql-server-operations-monitoring-disk-space-with-wmi-and-ole-automation-objects.aspx

PoSH – DiskSpaceGUITool 

http://social.technet.microsoft.com/wiki/contents/articles/34900.disk-space-gui-tool-multi-server-s-search-grid-and-email-output-powershell.aspx

Posted in SQL, SQLCMD, Uncategorized | Tagged , , | Leave a comment

SQL Server Operations: Monitoring Disk Space with WMI and OLE Automation Objects

Abstract

This article talks about various SQL methods to fetch the disk usage details and use of T-SQL in conjunction with invoking OLE automation procedures and WMI  class libraries using SQL Windows Shell interface.
The step by step  details about “OLE automation and WMI Query” approach to capture disk space and the use of sqlcmd with looping construct, data storage for querying and reporting are discussed below

Method 1 – Using OLE automation and Extended SP

In this method OLE automation and xp_cmdshell configuration components are enabled on the sql server. The sp_OACreate creates an instance of an Scripting.FileSystemObject OLE class. The sp_OAMethod gets a unique id for each volume attached to filesystem object and sp_OAGetProperty retrieves properties of each drive and filesystem.
The reason for enabling xp_cmdshell is to execute xp_fixeddrives an extended stored procedure. The output is stored in a temporary table. The records are traversed with an while loop construct  for %free space calculation. The sp_OAMethod requires a drive letter as its input . The below SQL has hard coded value for F drive. You can change the sql as per your requirement
DECLARE @hr INT
    @fso INT
    @drive CHAR(1)='F',  /* To demonstrate have hard coded the drive letter to F */
    @odrive INT,
    @TotalSize BIGINT,
    @freespace BIGINT,
    @AvailableSpace   BIGINT,
    @DriveCollection INT,
    @DriveCount INT,
    @FileSystemInstance  INT,
    @volumeName varchar(128),
    @IsReady VARCHAR(5)
  EXEC sp_OACreate 'Scripting.FileSystemObject',@fso OUT
  EXEC sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive 
  EXEC sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
  EXEC sp_OAGetProperty @odrive,'freespace', @freespace OUT
  EXEC sp_OAGetProperty @odrive,'AvailableSpace', @AvailableSpace OUT
  EXEC sp_OAGetProperty @fso,'Drives', @DriveCollection OUT
  EXEC sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT
  EXEC sp_OAGetProperty @odrive,'VolumeName', @volumeName OUT
  EXEC sp_OAGetProperty @odrive,'isReady', @IsReady OUT
    
  SELECT
  @DriveCount driveCount
  ,@Drive DriveLetter,
  (@AvailableSpace/1048576)/1024 FreeSpace,
  @volumeName Volumne,
  @IsReady DriveStatus,
  (@TotalSize/1048576)/1024.00 TotalSize,
  (@freespace/1048576)/1024.00 FreeSpace

Data flow diagram

This process requires to enable OLE automation procedures and xp_cmdshell SQL windows shell components on all the servers. The servers are traversed one by one via batch scripting along with sqlcmd and the result is stored in a shared file. This file has a collection of insert SQL statements. later the insert statement are executed on the centralized server. The repository can be queried, used for forecasting and capacity planning and reporting.

OLE – Capturing Disk Space of multiple servers

The section describes the requirement and briefs about every configuration required for successful execution of the code. The major issue is enabling OLE automation procedures and xp_cmdshell on all the servers. There is a risk of exposing SQL server to threat. The workaround is add the little piece of configure and de-configure sql statement in the sql file. Enable the configuration value in the beginning of the code and disable it at the end.

Pre-requisites

We can check the disk space by executing a T-SQL script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable xp_cmdshell . To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission.

  1. Enable xp_cmdshell on Centralized Server
  2. Enable Ole automation on all the listed servers
  3. Shared path for sql file
  4. Share path for output file to prepare insert statement
  5. Requires membership in the sysadmin fixed server role.

Step By Step Details

The below are the steps to store the data into a central repository

  1. Enable xp_cmdshell
  2. List all Servers in c:\Server.txt
  3. Enable ole automation
  4. Table Creation [TLOG_SpaceUsageDetails]
  5. save T-SQL script in SpaceCheck.sql
  6. Execute dynamic sqlcmd from SSMS
  7. select the output by querying TLOG_SpaceUsageDetails

Enable xp_cmdshell

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdShell', 1;
GO
RECONFIGURE;
GO


Enable OLE automation

Use the Ole Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


List servers in text file

Lists all the servers in a text file c:\server.txt and enable the OLE automation across all the servers. The other way to get around this problem is to include the sp_configure commands in the sql file and take it out after its been executed across all the servers

ABC
DEF
EFG


Create SQL Table

Create TLOG_SpaceUsageDetails on the centralized server

CREATE TABLE [dbo].[TLOG_SpaceUsageDetails](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [VARCHAR](100) NULL,
    [LogDate] [VARCHAR](10) DEFAULT (CONVERT([varchar](10),getdate(),(112))),
    [Drive] [CHAR](3) NULL,
    [FreeSpaceGB] [INT] NULL,
    [TotalSizeGB] [INT] NULL,
    [percentageOfFreeSpace] DECIMAL(5,2) NULL
) ON [PRIMARY]
 

Create SQL File

Save the below content to sql file and place it on the shared path so that sqlcmd can read the file while traversing across listed servers. for example, the content is saved under Spacecheck.sql on \\abcd\hq\. The full path of the file is going to be \\abcd\hq\spacecheck.sql

DECLARE @hr INT
    @fso INT
    @drive CHAR(1), 
    @odrive INT
    @TotalSize VARCHAR(20), 
    @MB NUMERIC
    @FreeSpace INT
    @free INT
    @RowId_1 INT
    @LoopStatus_1 SMALLINT
    @TotalSpace VARCHAR(10), 
    @Percentage VARCHAR(3),
    @drive1 varchar(2),
    @TotalSizeMB varchar(10),
    @FreeSpaceMB varchar(10),
    @percentageOfFreeSpace varchar(10),
    @RowId_2 INT
    @LoopStatus_2 SMALLINT,
    @DML nvarchar(4000) 
     
SET NOCOUNT ON
------------------------------------------------------
--Table to Store Drive related information 
------------------------------------------------------
CREATE TABLE #drives 
 
     id INT IDENTITY(1,1) PRIMARY KEY
     drive CHAR(1), 
     FreeSpaceMB INT
     TotalSizeMB INT NULL
     percentageOfFreeSpace INT
 
  
  
------------------------------------------------------
--Inserting the output of xp_fixeddrives to #SpaceSize Table 
------------------------------------------------------
INSERT #drives(drive,FreeSpaceMB) EXEC master.dbo.xp_fixeddrives 
 
------------------------------------------------------
--Using the sp_OACreate, sp_OAMethod and sp_OAGetProperty system stored procedures to create Ole Automation (ActiveX) applications that can do everything an ASP script can do*/ 
--Creates an instance of the OLE object 
------------------------------------------------------
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
  
 SET @MB = 1048576 
 SET @RowId_1 = 1 
 SET @LoopStatus_1 = 1 
  
------------------------------------------------------
--To Get Drive total space 
------------------------------------------------------
  
WHILE (@LoopStatus_1 <> 0) BEGIN
  
SELECT
 @drive=drive, 
 @FreeSpace=FreeSpaceMB 
 FROM
 #drives 
 WHERE
 ( ID = @RowId_1 ) 
  
 IF ( @@ROWCOUNT = 0 ) 
 BEGIN
    SET @LoopStatus_1 = 0 
 END
 ELSE
BEGIN
     EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive 
     EXEC @hr =sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
 UPDATE #drives SET TotalSizeMB=@TotalSize/@MB 
 WHERE
 drive=@drive 
 UPDATE #drives SET percentageOfFreeSpace=(@FreeSpace/(TotalSizeMB*1.0))*100.0 
 WHERE drive=@drive 
END
 SET @RowId_1 = @RowId_1 + 1 
  
END
 
 
SELECT  @RowId_2=1,@LoopStatus_2=1
 
------------------------------------------------------
--To prepare insert statement 
------------------------------------------------------
WHILE (@LoopStatus_2 <> 0) BEGIN
  
SET @DML=''
SELECT
 @drive1=drive, 
 @FreeSpace=FreeSpaceMB,
 @TotalSizeMB=TotalSizeMB,
 @FreeSpaceMB=FreeSpaceMB,
 @percentageOfFreeSpace=percentageOfFreeSpace
 FROM
 #drives 
 WHERE
 ( ID = @RowId_2 ) 
  
 IF ( @@ROWCOUNT = 0 ) 
 BEGIN
    SET @LoopStatus_2 = 0 
 END
 ELSE
BEGIN
     SET @DML=@DML+ 'insert into TLOG_SpaceUsageDetails(servername,drive,TotalSizeMB,FreeSpaceMB,percentageOfFreeSpace)values('+''''+@@servername+''''+','+''''+@drive1+''''+','+''''+@TotalSizeMB+''''+','+''''+@FreeSpaceMB+''''+','+''''+@percentageOfFreeSpace+''''+')'
END
PRINT @DML
 SET @RowId_2 = @RowId_2 + 1 
  
END
 
 
drop table #drives

Execute SQL

Make sure sql file and output path is a shared path. We are going to write an concatenated output to spaceDetails.sql

The below three parameters are important to loop through all the listed servers

  • Input server list
  • Shared path where you can place query file
  • Shared output path, the prepared insert statement are written into the file
MASTER..XP_CMDSHELL 'for /f %j in ( f:\servers.txt) do sqlcmd -S %j -i "\\share\hq\SpaceCheck.sql" -E >> "\\share\hq\SpaceDetails.sql"'
GO
MASTER..XP_CMDSHELL 'sqlcmd -S ABCD -i "\\share\hq\SpaceDetails.sql"'

Output

The output is selected by querying the TLOG table

select * from TLOG_SpaceUsageDetails

Method 2 – Querying WMI object

On Quering WMI Win32_volume class to gather volume names, free space, total size (capacity) into a temporary table using xp_cmdshell windows shell.
Using xp_cmdshell, the PowerShell.exe is being invoked to gather to required details and further stored in temp table for manipulation.
You can pass the server name, By default it will take the current server name.

DECLARE @svrName VARCHAR(255)
DECLARE @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output (line varchar(255))
--inserting disk name, total space and free space value in to temporary table
INSERT #output EXEC xp_cmdshell @sql
--script to retrieve the values in GB from PS Script output
SELECT rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) ))as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) ))as Float) /1024 ,0)as 'freespace(GB)'
      ,cast(round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) /
      round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) asFloat) /1024 ,0) as decimal(5,2)) as '%Free'
FROM #output
WHERE line like '[A-Z][:]%'
ORDER by drivename
--script to drop the temporary table
DROP TABLE #output

The output of the temp table is is then parsed to get the required values using string functions. Using charindex string function the index is find for the characters |,* and %. Based on the index the respective portion of the string fetched from the main string.

The advantage of this method is that xp_cmdshell enabled only the centralized machine. You don’t have to enable it across all the servers also non-sql database servers space metrics can be gathered as we are querying WMI class.

Pre-requisites

We can check the disk space by executing a T-SQL script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable XP_Cmdshell. To enable XP_Cmdshell you must have at least the ALTER SETTINGS server-level permission.

  1. Enable xp_cmdshell Only on the centralized Server.

Data Flow Diagram

This process requires to enable xp_cmdshell windows shell components only on the centralized servers. The servers are traversed one by one via batch scripting along with sqlcmd and the result is stored in a file. This file has a collection of insert SQL statements. later the insert statement are executed on the centralized server. The repository can be queried, used for forecasting and capacity planning and reporting.

WMI – Capturing Disk Space of multiple servers

The section describes the requirements and briefs about every configuration required for successful execution of the code.

Step by Step details:

  1. Enable XP_CMDShell
  2. List all Servers in c:\Server.txt
  3. Table Creation [TLOG_SpaceUsageDetails]
  4. save T-SQL script in SpaceCheck.sql
  5. Execute dynamic sqlcmd from SSMS
  6. select the output by querying TLOG_SpaceUsageDetails

Enable xp_cmdshell

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdShell', 1;
GO
RECONFIGURE;
GO

List servers in text file

Lists all the servers in a text file c:\server.txt and enable the OLE automation across all the servers. The other way to get around this problem is to include the sp_configure commands in the sql file and take it out after its been executed across all the servers

ABC
DEF
EFG

Create SQL Table

Create TLOG_SpaceUsageDetails on the centralized server

CREATE TABLE [dbo].[TLOG_SpaceUsageDetails](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [VARCHAR](100) NULL,
    [LogDate] [VARCHAR](10) DEFAULT (CONVERT([varchar](10),getdate(),(112))),
    [Drive] [CHAR](3) NULL,
    [FreeSpaceGB] [INTNULL,
    [TotalSizeGB] [INTNULL,
    [percentageOfFreeSpace] DECIMAL(5,2) NULL
ON [PRIMARY]
 
GO

Create SQL File

Save the below content to sql file. for example, the content is saved under Spacecheck_v1.sql

The SQL file has two input parameters that are fed through sqlcmd.

  • ServerName – The servername is used to query the respective server using WMI win32_volume computername parameter also the Servername is used to prepare insert statement. The output of the insert statement is written to an sql file.
  • dbname – The dbname used in conjunction with preparing a fully qualified name for the insert statement.
------------------------------------------------------
--variable declaration
------------------------------------------------------
DECLARE           @svrName varchar(255),
        @sql varchar(400),
        @drive varchar(3),
        @TotalSizeGB varchar(10),
        @FreeSpaceGB varchar(10),
        @percentageOfFreeSpace varchar(10),
        @RowId_1 INT
        @LoopStatus_1 SMALLINT,
        @DML nvarchar(4000) 
------------------------------------------------------
--To stop the message that shows the count of number of rows affected.
------------------------------------------------------
SET NOCOUNT ON
------------------------------------------------------
--The servername parameter are fed through sqlcmd
------------------------------------------------------
SET @svrName = '$(servername)'
------------------------------------------------------
–Querying WMI class win32_volume and store the result into temporary table
——————————————————
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
------------------------------------------------------
--creating a temporary table
------------------------------------------------------
CREATE TABLE #output
(line varchar(255))
------------------------------------------------------
--inserting disk name, total space and free space value in to temporary table
------------------------------------------------------
INSERT #output
EXEC xp_cmdshell @sql
------------------------------------------------------
--Table to Store Drive related information 
------------------------------------------------------
CREATE TABLE #drives 
 
     ID INT IDENTITY(1,1), 
     drive CHAR(5), 
     FreeSpaceGB INT
     TotalSizeGB INT NULL
     percentageOfFreeSpace decimal(5,2) 
 
------------------------------------------------------
--To Get Drive capacity and percentage of free space 
------------------------------------------------------
select * from #output
INSERT INTO #drives(drive,TotalSizeGB,FreeSpaceGB,percentageOfFreeSpace)
------------------------------------------------------
--script to retrieve the values in GB from PS Script output
--Find the string for |,% and * using string function - CHARINDEX
------------------------------------------------------
SELECT rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) asFloat)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) asFloat) /1024 ,0)as 'freespace(GB)'
      ,cast(round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)/round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0)*100 as decimal(5,2)) '%Free'
FROM #output
WHERE line like '[A-Z][:]%'
ORDER BY drivename
------------------------------------------------------
---select the output
------------------------------------------------------
--select * from #drives 
------------------------------------------------------
-- Initialize the counters 
------------------------------------------------------
 
SELECT  @RowId_1=1,@LoopStatus_1=1
------------------------------------------------------
--To prepare insert statement, Have used a logic to concatenate the string into a @DML variable
------------------------------------------------------
WHILE (@LoopStatus_1 <> 0) BEGIN
  
SET @DML=''
SELECT
 @drive=drive, 
 @TotalSizeGB=TotalSizeGB,
 @FreeSpaceGB=FreeSpaceGB,
 @percentageOfFreeSpace=percentageOfFreeSpace
 FROM
 #drives 
 WHERE
 ( ID = @RowId_1 ) 
  
 IF ( @@ROWCOUNT = 0 ) 
 BEGIN
    SET @LoopStatus_1 = 0 
 END
 ELSE
BEGIN
     SET @DML=@DML+ 'INSERT INTO$(dbname).dbo.TLOG_SpaceUsageDetails(servername,drive,TotalSizeGB,FreeSpaceGB,percentageOfFreeSpace)values('+''''+@svrName+''''+','+''''+@drive+''''+','+''''+@TotalSizeGB+''''+','+''''+@FreeSpaceGB+''''+','+''''+@percentageOfFreeSpace+''''+')'
END
PRINT @DML
 SET @RowId_1 = @RowId_1 + 1 
  
END
 
 
------------------------------------------------------
--script to drop the temporary table
------------------------------------------------------
DROP TABLE #output
DROP TABLE #drives

The below SQL generates insert statement and written to file. The @DML concatenates the values into string.  The looping construct use pointer to traverse through record one by one.

SET @DML=@DML+ 'INSERT INTO $(dbname).dbo.TLOG_SpaceUsageDetails(servername,drive,TotalSizeGB,FreeSpaceGB,percentageOfFreeSpace)values('+''''+@svrName+''''+','+''''+@drive+''''+','+''''+@TotalSizeGB+''''+','+''''+@FreeSpaceGB+''''+','+''''+@percentageOfFreeSpace+''''+')'

Execute SQL

The below sql requires

  1. Inputfile – it has the list of the servers
  2.  SQL file – contains the sql code to executed across listed servers – need not to be in shared path
  3. Output file – the output of the sql file written into this file
  4. ServerName parameter for sqlcmd which is an input to WMI query
  5. dbname- Have used a static value. This depends on where are intended to create the sql table. This parameter used to prepare SQLInsert statement
MASTER..XP_CMDSHELL 'for /f %j in ( f:\PowerSQL\servers.txt) do sqlcmd -i "\\share\hq\SQL Server\SpaceCheck_v1.sql" -E -v servername=%j dbname="tempdb">> "\\share\hq\SQL Server\SpaceDetails_v1.sql"'
GO
MASTER..XP_CMDSHELL 'sqlcmd -S <CentralServerName>-i "\\share\hq\SQL Server\SpaceDetails_v1.sql"'
GO
SELECT * FROM TLOG_SpaceusedDetails

HTML Reporting

It requires database mail profile configured on the central server.

DECLARE @tableHTML  NVARCHAR(MAX) ;
 
 SET @tableHTML =
  N'<H1>DiskSpace Report</H1>' +
  N'<table border="1">' +
  N'<tr><th>Server Name</th>
  <th>Drive</th>
  <th>TotalSizeGB</th>
  <th>FreeSpaceGB</th>
  <th>[% Free ]</th>
  <th>LogDate</th>
  </tr>' +
  CAST ( (   
  Select
  td=ServerName,' ',
  td=Drive,' ',
  td =TotalSizeGB,' ',
  td=FreeSpaceGB,' ',
  td=percentageOfFreeSpace,' ',
  td=LogDate,' '
FROM  [TLOG_SpaceUsageDetails]
where LogDate=CONVERT(varchar(10),getdate()-2,112)
      FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'</table>' ;
 
 EXEC msdb.dbo.sp_send_dbmail @recipients='pjayaram@appvion.com',
  @subject = 'Database Backup',
  @body = @tableHTML,
  @profile_name= '<ProfileName>',
  @body_format = 'HTML' ;


Highlights

  • The working of sp_OA* OLE automation and xp_* extended stored procedures
  • The use of xp_cmdhell – Windows SQL shell to execute WMI query and invoke Extended stored procedure
  • Invoke PowerShell.exe using windows shell in SQL
  • Batch programming with sqlcmd in SQL – The use of for loop construct to traverse each server
  • Dynamic SQL to prepare the insert sql file
  • Parameter passing for sqlcmd  – Server and central database repository for data storage and manipulation
  • Database mail configuration and HTML Reporting

Conclusion

Method1 v/s Method 2
 1 OLE automation procedure and xp_cmdshell components
should be enabled on all the servers
 Enable xp_cmdshell configuration only on central server
 2 Non-SQL servers can’t be monitored  Non-SQL servers can be monitored
 3 Risk prone  Less risk prone
 4 Common share is required  Share is not mandatory, sql is executed on central server


The OLE automation stored procedures provide access to the Component Object Model (COM), which grants Visual Basic  or ASP scripting functionality to T-SQL scripts. It could be used to manipulate documents, utilize other COM-compatible code, or send e-mails etc:-
Warning: By default access to the OLE Automation stored procedures is disabled.  If enabled it allows any SQL script to invoke any OLE Automation object on the computer (such as the Windows Shell).  It is a significant security risk and should not be done lightly.  For example you need to be extra careful to protect against SQL-injection attacks.   It is basically the same as allowing xp_cmdshell.

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. Nowadays there is not much need of this particular command. I prefer doing this kind of administration tasks using PoSH. The PoSH provides to flexibility in many ways to handle such operations. However, there was a time when PoSH did not exist and we had to do lots of tasks with the help of the command shell.

Note:- The intention behind this article is not to expose the server to security risk. Its up to an individual to know an implication of using and enabling OLE automation

References

OLE Automation Objects

Posted in PowerShell, SQL, T-SQL | Tagged , , , , , | Leave a comment

Understanding the Different States of SQL Database

Overview

There are multiple numbers states of a SQL Database. These states determine the current status or mode of database in SQL server.

At once, the database is only in one state, which may be any one of the following:

  • Online
  • Offline
  • Restoring
  • Recovering
  • Recovery Pending
  • Emergency
  • Suspect

In this blog, we are going to discuss about all these states in detail.

Description Of Several States of SQL Database

Online: This server state indicates that the database is accessible for end users. The primary filegroup is online, even if the recovery process of undo phase has not been finished.

Offline: The state illustrates that the database is unavailable. This state of server is caused by explicit user action and it remains in offline mode until & unless users don’t perform any action on database.

Note: To determine whether the server is in offline state or in an online state, one can run the following command:

SELECT * FROM sys.databases

After running this command, you will find a table with ColumnName and Value attributes. In this table, search for state_desc option in ColumnName and check the Value attribute that whether it is Online or Offline.

State of SQL Database

Restoring: In this state of SQL database one or more data files are being restored from primary filegroup. Apart from this, the state also occurs when one or more secondary files are being restored in offline mode (i.e., database is unavailable). After completion of restoration procedure, the database is modified into an useable state and hence, users can now access the restored database.

Recovering: At the time of restarting the SQL server, all database undergoes “Recovery” process. In this state, the database comes in its consistent state i.e., during the process its state changes from recovery state to online state. Moreover, the recovering procedure is performed in three phases i.e., Discovery, roll forward, and Rollback

  • Discovery Phase: The first phase discovers that where will the server proceed through log file. During its discovery procedure, it built-in the memory structure for determining the memory required for going to next phases.
  • Roll Forward Phase: The second phase determines the transactions that were committed while shutting down the database but not yet written to MDF file through checkpoints.
  • Rollback Phase: Last phase of this recovering process determines whether there are any uncommitted transactions in database or not. If yes, then they first roll back those transactions and than bring database into consistent state.

TIP: With help of following command, one can recover a database, which is in restoring state:

RESTORE DATABASE ADventureWorks WITH RECOVERY
GO

Recovery Pending: While working with the server, users encounter an error that are related to resources during recovery process. In such cases, database is not corrupted or damaged. This state occurs when some data files are missing or there is some problem with system resources. Therefore, some external operations are being required by users to resolve this error state and continue the recovery process until its end.

Suspect: In this state, the recovery gets started but does not reach to its completion. It does not allow users to connect with database and can be considered as an inconsistent state. However, the common reason behind occurrence of such state is that the primary filegroup may be corrupted & hence, in suspect. Therefore, this state also requires some additional actions that repair the corrupted file & recover data from it.

Emergency: This state is enable by users by making some default changes. Such state implementation can only be performed in single-user mode and could be restored or repaired. In such state, database is in READ_ONLY state, logging is disabled, and access is restricted. Suppose a database is find out as a suspect can be set to emergency state, which will allow admin to perform read-only operation within it. Hence, no one can perform any updation in such database that is in emergency mode.

Conclusion

One can conclude with the fact that some of the states of SQL database are caused due to damage in database files therefore, users are recommended to use a recovery utility in order to recover data from SQL server database files and export that recovered data into SQL server database. One such utility is SQL Database Repair Tool, which recovers entire data from corrupted database file by performing scanning operation on source file. After completion of scanning operation, it convert the source file into SQL server database file. Moreover, it supports MDF and NDF files of the server. Therefore, the server users could use this third-party utility to take out the database from an inconsistent state.

Posted in Backup and Restore, databases_files, SQL | Leave a comment

Disk Space GUI Tool – Multi Server(s) – Grid and Email Output

Monitoring server resources is critical and important job of any Administrator. Keeping things organized might improve application availability and server availability. Being a database administrator for 1o years have faced and handled/managed lot of issues with disk space. There are many times we end up with I/O bottleneck and disk ran out space for transaction log files. To get around that have designed a simple GUI tool which has various features and quickly take us through the disk space utilization details of server(s). You’ll basically feed it a list of servers to watch over, and it will report back on these for you, meaning you could also use it as a more general “daily server disk space report”

The Powershell DiskSpace GUI tool has

  • Accept Input file which contains list of server or type the server name(s) separated by comma
  • Manage disk threshold values
  • Provides Grid or Email view or both
  • Search options

This tool helps you get the disk space information on the computers of your choice. Either you can type the path of an input file or hit ‘Browse Button’ to select the file using dialog option or you can enter the server names in the text box.

Put the select input file and hit ‘GetDisk’. It will query the disk space information from the computer and present it to you in tabular formats. The Grid view output can be sorted on any selected columns shows the amount of disk space in use, the name of the server, the volume name, the total size of the disk, the available free space, the partition identifier and the percentage of free space.

You can do a search by placing a right keyword and hit ‘Search’ button.

Also, Email can be sent to an intended users by making an entry in the corresponding fields in a below portion of the tool and hit ‘E-Mail Output’ button.

The different layouts are explained below

Layout 1 : Default the Input file check box is enabled. Hit browse button to select the input file and select the Get-Disk button.

diskSpace_5

Layout 2:  Displaying the threshold entries. If you want to use the default values then select ‘Cancel’ button. In case you want to define new threshold then set it as per your requirement and hit ‘Ok’ button.

diskSpace_1

Layout 3:  Show you the use Input file(C:\Server.txt), default threshold value(25 and 10), Sorting (On Drive field),Search(Apps key word) and Emailing.

diskSpace_3

 

Layout 4:  Show you the use Input server names and change of threshold value(30 and 15), Sorting (On DriveLabel),Search(OS key word) and Emailing.

diskSpace_4

 

Code

##################################
#Disk Space Report Module - Report Module
#######################################
function Get-DiskSpace_Report
{
function OnApplicationLoad {
return $true
}
function OnApplicationExit {
#Note: This function runs after the form is closed
#TODO: Add custom code to clean up and unload snapins when the application exits
}
#endregion Application Functions
#----------------------------------------------
# Generated Form Function
#----------------------------------------------
function Call-SystemInformation_pff {
    #----------------------------------------------
    #region Import the Assemblies
    #----------------------------------------------
    [void][reflection.assembly]::Load("System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
    [void][reflection.assembly]::Load("System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
    [void][reflection.assembly]::Load("mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
     [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")
    #get-pssnapin -Registered -ErrorAction silentlycontinue
    #endregion Import Assemblies
    #----------------------------------------------
    #region Generated Form Objects
    #----------------------------------------------
    [System.Windows.Forms.Application]::EnableVisualStyles()
    $form1 = New-Object System.Windows.Forms.Form
    $btnRefresh = New-Object System.Windows.Forms.Button
   
    $btnsearch = New-Object System.Windows.Forms.Button
    $btngetdata=New-Object System.Windows.Forms.Button
    $rtbPerfData = New-Object System.Windows.Forms.RichTextBox
    #$pictureBox1 = New-Object System.Windows.Forms.PictureBox
    $lblServicePack = New-Object System.Windows.Forms.Label
    $lblDBName= New-Object System.Windows.Forms.Label
    $lblOS = New-Object System.Windows.Forms.Label
    $lblExpire = New-Object System.Windows.Forms.Label
    $statusBar1 = New-Object System.Windows.Forms.StatusBar
    $btnClose = New-Object System.Windows.Forms.Button
    #$comboServers = New-Object System.Windows.Forms.ComboBox
    $InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState
    $txtComputerName1 = New-Object System.Windows.Forms.TextBox
    $txtComputerName2 = New-Object System.Windows.Forms.TextBox
    $datagridviewResults = New-Object System.Windows.Forms.DataGridview
    $Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart
    $labelSubject = New-Object 'System.Windows.Forms.Label'
    $labelFrom = New-Object 'System.Windows.Forms.Label'
    $labelSMTPServer = New-Object 'System.Windows.Forms.Label'
    $labelTo = New-Object 'System.Windows.Forms.Label'
    $txtSubject = New-Object System.Windows.Forms.TextBox
    $txtFrom = New-Object System.Windows.Forms.TextBox
    $txtSMTPServer = New-Object System.Windows.Forms.TextBox
    $txtTo = New-Object System.Windows.Forms.TextBox
    $buttonEMailOutput = New-Object System.Windows.Forms.Button
    $checkbox=New-Object System.Windows.Forms.CheckBox
    $Warningcheckbox=New-Object System.Windows.Forms.CheckBox
    $Criticalcheckbox=New-Object System.Windows.Forms.CheckBox
    $txtWarning = New-Object System.Windows.Forms.TextBox
    $txtCritical = New-Object System.Windows.Forms.TextBox
   #$dataGrid1 = new-object System.windows.forms.DataGridView
    #endregion Generated Form Objects
    #----------------------------------------------
    # User Generated Script
    #----------------------------------------------
function Sort-ListViewColumn
{
    <#
    .SYNOPSIS
        Sort the ListView's item using the specified column.
    .DESCRIPTION
        Sort the ListView's item using the specified column.
        This function uses Add-Type to define a class that sort the items.
        The ListView's Tag property is used to keep track of the sorting.
    .PARAMETER ListView
        The ListView control to sort.
    .PARAMETER ColumnIndex
        The index of the column to use for sorting.
        
    .PARAMETER  SortOrder
        The direction to sort the items. If not specified or set to None, it will toggle.
    
    .EXAMPLE
        Sort-ListViewColumn -ListView $listview1 -ColumnIndex 0
#>
    param( 
            [ValidateNotNull()]
            [Parameter(Mandatory=$true)]
            [System.Windows.Forms.ListView]$ListView,
            [Parameter(Mandatory=$true)]
            [int]$ColumnIndex,
            [System.Windows.Forms.SortOrder]$SortOrder = 'None')
    
    if(($ListView.Items.Count -eq 0) -or ($ColumnIndex -lt 0) -or ($ColumnIndex -ge $ListView.Columns.Count))
    {
        return;
    }
    
    #region Define ListViewItemComparer
        try{
        $local:type = [ListViewItemComparer]
    }
    catch{
    Add-Type -ReferencedAssemblies ('System.Windows.Forms') -TypeDefinition  @"
    using System;
    using System.Windows.Forms;
    using System.Collections;
    public class ListViewItemComparer : IComparer
    {
        public int column;
        public SortOrder sortOrder;
        public ListViewItemComparer()
        {
            column = 0;
            sortOrder = SortOrder.Ascending;
        }
        public ListViewItemComparer(int column, SortOrder sort)
        {
            this.column = column;
            sortOrder = sort;
        }
        public int Compare(object x, object y)
        {
            if(column >= ((ListViewItem)x).SubItems.Count)
                return  sortOrder == SortOrder.Ascending ? -1 : 1;
        
            if(column >= ((ListViewItem)y).SubItems.Count)
                return sortOrder == SortOrder.Ascending ? 1 : -1;
        
            if(sortOrder == SortOrder.Ascending)
                return String.Compare(((ListViewItem)x).SubItems[column].Text, ((ListViewItem)y).SubItems[column].Text);
            else
                return String.Compare(((ListViewItem)y).SubItems[column].Text, ((ListViewItem)x).SubItems[column].Text);
        }
    }
"@  | Out-Null
    }
    #endregion
    
    if($ListView.Tag -is [ListViewItemComparer])
    {
        #Toggle the Sort Order
        if($SortOrder -eq [System.Windows.Forms.SortOrder]::None)
        {
            if($ListView.Tag.column -eq $ColumnIndex -and $ListView.Tag.sortOrder -eq 'Ascending')
            {
                $ListView.Tag.sortOrder = 'Descending'
            }
            else
            {
                $ListView.Tag.sortOrder = 'Ascending'
            }
        }
        else
        {
            $ListView.Tag.sortOrder = $SortOrder
        }
        
        $ListView.Tag.column = $ColumnIndex
        $ListView.Sort()#Sort the items
    }
    else
    {
        if($Sort -eq [System.Windows.Forms.SortOrder]::None)
        {
            $Sort = [System.Windows.Forms.SortOrder]::Ascending
        }
        
        #Set to Tag because for some reason in PowerShell ListViewItemSorter prop returns null
        $ListView.Tag = New-Object ListViewItemComparer ($ColumnIndex, $SortOrder)
        $ListView.ListViewItemSorter = $ListView.Tag #Automatically sorts
    }
}
function Add-ListViewItem
{
<#
    .SYNOPSIS
        Adds the item(s) to the ListView and stores the object in the ListViewItem's Tag property.
    .DESCRIPTION
        Adds the item(s) to the ListView and stores the object in the ListViewItem's Tag property.
    .PARAMETER ListView
        The ListView control to add the items to.
    .PARAMETER Items
        The object or objects you wish to load into the ListView's Items collection.
        
    .PARAMETER  ImageIndex
        The index of a predefined image in the ListView's ImageList.
    
    .PARAMETER  SubItems
        List of strings to add as Subitems.
    
    .PARAMETER Group
        The group to place the item(s) in.
    
    .PARAMETER Clear
        This switch clears the ListView's Items before adding the new item(s).
    
    .EXAMPLE
        Add-ListViewItem -ListView $listview1 -Items "Test" -Group $listview1.Groups[0] -ImageIndex 0 -SubItems "Installed"
#>
    
    Param(
    [ValidateNotNull()]
    [Parameter(Mandatory=$true)]
    [System.Windows.Forms.ListView]$ListView,
    [ValidateNotNull()]
    [Parameter(Mandatory=$true)]
    $Items,
    [int]$ImageIndex = -1,
    [string[]]$SubItems,
    [System.Windows.Forms.ListViewGroup]$Group,
    [switch]$Clear)
    
    if($Clear)
    {
        $ListView.Items.Clear();
    }
    
    if($Items -is [Array])
    {
        $ListView.BeginUpdate()
        foreach ($item in $Items)
        {      
            $listitem  = $ListView.Items.Add($item.ToString(), $ImageIndex)
            #Store the object in the Tag
            $listitem.Tag = $item
            
            if($SubItems -ne $null)
            {
                $listitem.SubItems.AddRange($SubItems)
            }
            
            if($Group -ne $null)
            {
                $listitem.Group = $Group
            }
        }
        $ListView.EndUpdate()
    }
    else
    {
        #Add a new item to the ListView
        $listitem  = $ListView.Items.Add($Items.ToString(), $ImageIndex)
        #Store the object in the Tag
        $listitem.Tag = $Items
        
        if($SubItems -ne $null)
        {
            $listitem.SubItems.AddRange($SubItems)
        }
        
        if($Group -ne $null)
        {
            $listitem.Group = $Group
        }
    }
}
function Load-DataGridView
{
    <#
    .SYNOPSIS
        This functions helps you load items into a DataGridView.
    .DESCRIPTION
        Use this function to dynamically load items into the DataGridView control.
    .PARAMETER  DataGridView
        The ComboBox control you want to add items to.
    .PARAMETER  Item
        The object or objects you wish to load into the ComboBox's items collection.
    
    .PARAMETER  DataMember
        Sets the name of the list or table in the data source for which the DataGridView is displaying data.
    #>
    Param (
        [ValidateNotNull()]
        [Parameter(Mandatory=$true)]
        [System.Windows.Forms.DataGridView]$DataGridView,
        [ValidateNotNull()]
        [Parameter(Mandatory=$true)]
        $Item,
        [Parameter(Mandatory=$false)]
        [string]$DataMember
    )
    $DataGridView.SuspendLayout()
    $DataGridView.DataMember = $DataMember
    
    
    if ($Item -is [System.ComponentModel.IListSource]`
    -or $Item -is [System.ComponentModel.IBindingList] -or $Item -is [System.ComponentModel.IBindingListView] )
    {
        $DataGridView.DataSource = $Item
      
    }
    else
    {
        $array = New-Object System.Collections.ArrayList
        
        if ($Item -is [System.Collections.IList])
        {
            $array.AddRange($Item)
        }
        else
        {  
            $array.Add($Item)  
        }
        $DataGridView.DataSource = $array
        
       
    }
    
    $DataGridView.ResumeLayout()
   
}
function Convert-ToDataTable
{
    <#
        .SYNOPSIS
            Converts objects into a DataTable.
    
        .DESCRIPTION
            Converts objects into a DataTable, which are used for DataBinding.
    
        .PARAMETER  InputObject
            The input to convert into a DataTable.
    
        .PARAMETER  Table
            The DataTable you wish to load the input into.
    
        .PARAMETER RetainColumns
            This switch tells the function to keep the DataTable's existing columns.
        
        .PARAMETER FilterWMIProperties
            This switch removes WMI properties that start with an underline.
    
        .EXAMPLE
            Convert-ToDataTable -InputObject (Get-Process) -Table $DataTable
    #>
    
    param(
    $InputObject,
    [ValidateNotNull()]
    [System.Data.DataTable]$Table,
    [switch]$RetainColumns,
    [switch]$FilterWMIProperties)
    
    if($InputObject -is [System.Data.DataTable])
    {
        $Table = $InputObject
        return
    }
    
    if(-not $RetainColumns -or $Table.Columns.Count -eq 0)
    {
        #Clear out the Table Contents
        $Table.Clear()
        if($InputObject -eq $null){ return } #Empty Data
        
        $object = $null
        #find the first non null value
        foreach($item in $InputObject)
        {
            if($item -ne $null)
            {
                $object = $item
                break  
            }
        }
        if($object -eq $null) { return } #All null then empty
        
        #Get all the properties in order to create the columns
        $properties = Get-Member -MemberType 'Properties' -InputObject $object
        foreach ($prop in $properties)
        {
            if(-not $FilterWMIProperties -or -not $prop.Name.StartsWith('__'))#filter out WMI properties
            {
                #Get the type from the Definition string
                $index = $prop.Definition.IndexOf(' ')
                $type = $null
                if($index -ne -1)
                {
                    $typeName = $prop.Definition.SubString(0, $index)
                    try{ $type = [System.Type]::GetType($typeName) } catch {}
                }
                if($type -ne $null -and [System.Type]::GetTypeCode($type) -ne 'Object')
                {
                    [void]$table.Columns.Add($prop.Name, $type)
                }
                else #Type info not found
                {
                    [void]$table.Columns.Add($prop.Name)   
                }
            }
        }
    }
    else
    {
        $Table.Rows.Clear()
    }
    
    $count = $table.Columns.Count
    foreach($item in $InputObject)
    {
        $row = $table.NewRow()
        
        for ($i = 0; $i -lt $count;$i++)
        {
            $column = $table.Columns[$i]
            $prop = $column.ColumnName 
            
            $value = Invoke-Expression ('$item.{0}' -f $prop)
            if($value -ne $null)
            {
                $row[$i] = $value
            }
        }
        
        [void]$table.Rows.Add($row)
         
    }
 
}
#endregion
#region Search Function
function SearchGrid()
{
    $RowIndex = 0
    $ColumnIndex = 0
    $seachString = $txtComputerName2.Text
    
    if($seachString -eq "")
    {
        return
    }
    
    if($datagridviewResults.SelectedCells.Count -ne 0)
    {
        $startCell = $datagridviewResults.SelectedCells[0];
        $RowIndex = $startCell.RowIndex
        $ColumnIndex = $startCell.ColumnIndex + 1
    }
    
    $columnCount = $datagridviewResults.ColumnCount
    $rowCount = $datagridviewResults.RowCount
    for(;$RowIndex -lt $rowCount; $RowIndex++)
    {
        $Row = $datagridviewResults.Rows[$RowIndex]
        
        for(;$ColumnIndex -lt $columnCount; $ColumnIndex++)
        {
            $cell = $Row.Cells[$ColumnIndex]
            
            if($cell.Value -ne $null -and $cell.Value.ToString().IndexOf($seachString, [StringComparison]::OrdinalIgnoreCase) -ne -1)
            {
                $datagridviewResults.CurrentCell = $cell
                return
            }
        }
        
        $ColumnIndex = 0
    }
    
    $datagridviewResults.CurrentCell = $null
    [void][System.Windows.Forms.MessageBox]::Show("The search has reached the end of the grid.","String not Found")
    
}
#endregion
Function sendEmail 
{
param($from,$to,$subject,$smtphost,$htmlFileName) 
[string]$receipients="$to"
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtphost)
$validfrom= Validate-IsEmail $from
if($validfrom -eq $TRUE)
{
$validTo= Validate-IsEmail $to
if($validTo -eq $TRUE)
{
$smtp.Send($body)
write-output "Email Sent!!"
}
}
else
{
write-output "Invalid entries, Try again!!"
}
}
function Validate-IsEmail ([string]$Email)
{
                
                return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
}
$FormEvent_Load={
    #TODO: Initialize Form Controls here
    
}
$buttonExit_Click={
    #TODO: Place custom script here
    $formMain.Close()
}
$buttonQuery_Click={
    #TODO: Place custom script here
#   ---------------------------------
#   Sample Code to Load Grid
#   ---------------------------------
#   $processes = Get-WmiObject Win32_Process -Namespace "Root\CIMV2"
#   Load-DataGridView -DataGridView $datagridviewResults -Item $processes
#   ---------------------------------
#   Sample Code to Load Sortable Data
#   ---------------------------------
if($checkbox.checked -eq $TRUE)
{
if ($txtComputerName1.text -eq '')
{
$statusBar1.text="Select Input file or Enter Input file path...Try Again!!!"
}
else
{
#>
$script:Object1 = @()
$freeSpaceFileName = "C:\FreeSpace.htm"
Get-threshold
$warning = $script:Warning
$critical = $script:critical
write-host  $warning,$critical
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk  | Where-Object {$_.drivetype -eq 3 -OR $_.drivetype -eq 2 } | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName  "<!--"
add-content $fileName  "td {"
add-content $fileName  "font-family: Tahoma;"
add-content $fileName  "font-size: 11px;"
add-content $fileName  "border-top: 1px solid #999999;"
add-content $fileName  "border-right: 1px solid #999999;"
add-content $fileName  "border-bottom: 1px solid #999999;"
add-content $fileName  "border-left: 1px solid #999999;"
add-content $fileName  "padding-top: 0px;"
add-content $fileName  "padding-right: 0px;"
add-content $fileName  "padding-bottom: 0px;"
add-content $fileName  "padding-left: 0px;"
add-content $fileName  "}"
add-content $fileName  "body {"
add-content $fileName  "margin-left: 5px;"
add-content $fileName  "margin-top: 5px;"
add-content $fileName  "margin-right: 0px;"
add-content $fileName  "margin-bottom: 10px;"
add-content $fileName  ""
add-content $fileName  "table {"
add-content $fileName  "border: thin solid #000000;"
add-content $fileName  "}"
add-content $fileName  "-->"
add-content $fileName  "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName  "<table width='100%'>"
add-content $fileName  "<tr bgcolor='#CCCCCC'>"
add-content $fileName  "<td colspan='7' height='25' align='center'>"
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName  "</td>"
add-content $fileName  "</tr>"
add-content $fileName  "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width= 10%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
#<td bgcolor='#FF0000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FBB917' align=center>$freePercent</td>"
# #FBB917
Add-Content $fileName "</tr>"
}
}
writeHtmlHeader $freeSpaceFileName
# Email our report out
foreach ($computer in (get-content $txtComputerName1.text))
{
write-host $computer
if(Test-Connection -ComputerName $computer -Count 1 -ea 0) {
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $computer </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $computer |  Where-Object {$_.drivetype -eq 3 }
foreach ($item in $dp)
{
Write-Host  $item.DeviceID  $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
  Add-Content $freeSpaceFileName "</table>"
writeHtmlFooter $freeSpaceFileName
 
$statusBar1.text="Getting  $computer Information...Please wait"
$D=Get-WmiObject win32_logicalDisk -ComputerName $computer -ErrorAction silentlycontinue|where {$_.DriveType -eq 3}|
select __SERVER,DeviceID,VolumeName,
@{Expression={$_.Size /1Gb -as [int]};Label=”TotalSize”},
@{Expression={($_.Size /1Gb -as [int]) – ($_.Freespace / 1Gb -as [int])};Label=”InUse”} ,
@{Expression={$_.Freespace / 1Gb -as [int]};Label=”FreeSize”},
@{Expression={(($_.Freespace /1Gb -as [int]) / ($_.Size / 1Gb -as [int]))*100};Label=”PerFreeSpace”}
foreach($disk in $D)
{
$script:Object1 += New-Object PSObject -Property @{
AppSrvName= $Disk.__Server;
Drive= $Disk.DeviceID;
DriveLabel=$Disk.VolumeName;
DriveCapacityGB=$Disk.TotalSize;
DriveInUseGB=$Disk.InUse;
DriveFreeGB=$Disk.FreeSize;
DrivePercentFree=$Disk.PerFreeSpace;
}
}
}
 
 else
    {
    $statusBar1.text="Could not connect to $computer ...Try Again!!!"
    }
    
$statusBar1.text="Ready"    
}
    $table = New-Object System.Data.DataTable
    Convert-ToDataTable -InputObject ($object1) -Table $table
    Load-DataGridView -DataGridView $datagridviewResults -Item $table
   
}
}
}
$buttonQuery_NoCheckBoxClick={
$freeSpaceFileName = "C:\FreeSpace.htm"
Get-threshold
$warning = $script:Warning
$critical = $script:critical
write-host  $warning,$critical
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk  | Where-Object {$_.drivetype -eq 3 -OR $_.drivetype -eq 2 } | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName  "<!--"
add-content $fileName  "td {"
add-content $fileName  "font-family: Tahoma;"
add-content $fileName  "font-size: 11px;"
add-content $fileName  "border-top: 1px solid #999999;"
add-content $fileName  "border-right: 1px solid #999999;"
add-content $fileName  "border-bottom: 1px solid #999999;"
add-content $fileName  "border-left: 1px solid #999999;"
add-content $fileName  "padding-top: 0px;"
add-content $fileName  "padding-right: 0px;"
add-content $fileName  "padding-bottom: 0px;"
add-content $fileName  "padding-left: 0px;"
add-content $fileName  "}"
add-content $fileName  "body {"
add-content $fileName  "margin-left: 5px;"
add-content $fileName  "margin-top: 5px;"
add-content $fileName  "margin-right: 0px;"
add-content $fileName  "margin-bottom: 10px;"
add-content $fileName  ""
add-content $fileName  "table {"
add-content $fileName  "border: thin solid #000000;"
add-content $fileName  "}"
add-content $fileName  "-->"
add-content $fileName  "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName  "<table width='100%'>"
add-content $fileName  "<tr bgcolor='#CCCCCC'>"
add-content $fileName  "<td colspan='7' height='25' align='center'>"
add-content $fileName  "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName  "</td>"
add-content $fileName  "</tr>"
add-content $fileName  "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width= 10%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=center>$freePercent</td>"
#<td bgcolor='#FF0000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#FBB917' align=center>$freePercent</td>"
# #FBB917
Add-Content $fileName "</tr>"
}
}
writeHtmlHeader $freeSpaceFileName
    #TODO: Place custom script here
#   ---------------------------------
#   Sample Code to Load Grid
#   ---------------------------------
#   $processes = Get-WmiObject Win32_Process -Namespace "Root\CIMV2"
#   Load-DataGridView -DataGridView $datagridviewResults -Item $processes
#   ---------------------------------
#   Sample Code to Load Sortable Data
#   ---------------------------------
if($checkbox.checked -eq $FALSE)
{
if ($txtComputerName1.text -eq '')
{
$statusBar1.text="Enter the Server...Try Again!!!"
}
else
{
$l=$txtComputerName1.text
$split1 = $l.split(",")
#>
$script:Object2 = @()
For($i=0; $i -le $split1.length -1 ;$i++)
{
write-host $split1[$i]
$computer=$split1[$i]
if(Test-Connection -ComputerName $computer -Count 1 -ea 0) {
 
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $computer </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $computer |  Where-Object {$_.drivetype -eq 3 }
foreach ($item in $dp)
{
Write-Host  $item.DeviceID  $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
  Add-Content $freeSpaceFileName "</table>"
writeHtmlFooter $freeSpaceFileName
$statusBar1.text="Getting  $computer Information...Please wait"
$D=Get-WmiObject win32_logicalDisk -ComputerName $computer -ErrorAction silentlycontinue|where {$_.DriveType -eq 3}|
select __SERVER,DeviceID,VolumeName,
@{Expression={$_.Size /1Gb -as [int]};Label=”TotalSize”},
@{Expression={($_.Size /1Gb -as [int]) – ($_.Freespace / 1Gb -as [int])};Label=”InUse”} ,
@{Expression={$_.Freespace / 1Gb -as [int]};Label=”FreeSize”},
@{Expression={(($_.Freespace /1Gb -as [int]) / ($_.Size / 1Gb -as [int]))*100};Label=”PerFreeSpace”}
foreach($disk in $D)
{
$script:Object2 += New-Object PSObject -Property @{
AppSrvName= $Disk.__Server;
Drive= $Disk.DeviceID;
DriveLabel=$Disk.VolumeName;
DriveCapacityGB=$Disk.TotalSize;
DriveInUseGB=$Disk.InUse;
DriveFreeGB=$Disk.FreeSize;
DrivePercentFree=$Disk.PerFreeSpace
}
}
}
 else
    {
    $statusBar1.text="Could not connect to $computer ...Try Again!!!"
    }
    
$statusBar1.text="Ready"    
}
    $table = New-Object System.Data.DataTable
    Convert-ToDataTable -InputObject ($object2) -Table $table
    Load-DataGridView -DataGridView $datagridviewResults -Item $table
   
}
}
}
$buttonSearch_Click={
    #TODO: Place custom script here
    SearchGrid
}
$datagridviewResults_ColumnHeaderMouseClick=[System.Windows.Forms.DataGridViewCellMouseEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.DataGridViewCellMouseEventArgs]
    if($datagridviewResults.DataSource -is [System.Data.DataTable])
    {
        $column = $datagridviewResults.Columns[$_.ColumnIndex]
        $direction = [System.ComponentModel.ListSortDirection]::Ascending
        
        if($column.HeaderCell.SortGlyphDirection -eq 'Descending')
        {
            $direction = [System.ComponentModel.ListSortDirection]::Descending
        }
        $datagridviewResults.Sort($datagridviewResults.Columns[$_.ColumnIndex], $direction)
    }
}
$listviewSort_ColumnClick=[System.Windows.Forms.ColumnClickEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.ColumnClickEventArgs]
    Sort-ListViewColumn -ListView $this -ColumnIndex $_.Column
}
$listviewSort_ColumnClick2=[System.Windows.Forms.ColumnClickEventHandler]{
#Event Argument: $_ = [System.Windows.Forms.ColumnClickEventArgs]
    Sort-ListViewColumn -ListView $this -ColumnIndex $_.Column
}
function Get-threshold{
        [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "Threshold Data Entry Form"
$objForm.Size = New-Object System.Drawing.Size(300,200)
$objForm.StartPosition = "CenterScreen"
$objForm.KeyPreview = $True
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter")
    {$x=$objTextBox1.Text;$y=$objTextBox2.Text;$objForm.Close()}})
$objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape")
    {$objForm.Close()}})
$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.Add_Click({$x=$objTextBox.Text;$objForm.Close()})
$objForm.Controls.Add($OKButton)
$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,120)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Cancel"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)
$objLabel3 = New-Object System.Windows.Forms.Label
$objLabel3.Location = New-Object System.Drawing.Size(10,6)
$objLabel3.Size = New-Object System.Drawing.Size(280,20)
$objLabel3.Text = "Default - Warning:25 Critical:10 ,Click OK"
$objForm.Controls.Add($objLabel3)
$objLabel1 = New-Object System.Windows.Forms.Label
$objLabel1.Location = New-Object System.Drawing.Size(10,35)
$objLabel1.Size = New-Object System.Drawing.Size(150,20)
$objLabel1.Text = "Warning Threshold Value"
$objForm.Controls.Add($objLabel1)
$objTextBox1 = New-Object System.Windows.Forms.TextBox
$objTextBox1.Location = New-Object System.Drawing.Size(160,35)
$objTextBox1.Size = New-Object System.Drawing.Size(100,20)
$objForm.Controls.Add($objTextBox1)
$objLabel2 = New-Object System.Windows.Forms.Label
$objLabel2.Location = New-Object System.Drawing.Size(10,65)
$objLabel2.Size = New-Object System.Drawing.Size(150,20)
$objLabel2.Text = "Critical Threshold Value:"
$objForm.Controls.Add($objLabel2)
$objTextBox2 = New-Object System.Windows.Forms.TextBox
$objTextBox2.Location = New-Object System.Drawing.Size(160,65)
$objTextBox2.Size = New-Object System.Drawing.Size(100,20)
$objForm.Controls.Add($objTextBox2)
$x=$objTextBox1.Text;
$y=$objTextBox2.Text;
$objForm.Topmost = $True
$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()
if ($objTextBox1.Text -eq '')
{
$Script:warning=25
}
else
{
$Script:warning=$objTextBox1.Text;
}
if ($objTextBox2.Text -eq '')
{
$Script:critical=10
}
else
{
$Script:critical=$objTextBox2.Text;
}
<#
$x1= $objTextBox1.Text;
$y1= $objTextBox2.Text;
write-host "the value is $x1"
write-host "the vakue is $y1"
$Script:warning
$Script:critical
#>
        Start-Sleep -Milliseconds 200
    }
    
    $Close={
        $form1.close()
    
    }
    
 $GetData={
        $statusBar1.text=" Checking Email Entries ...Please wait"
            if ($txtfrom.Text -ne '' -and $txtto.Text -ne '' -and $txtSMTPServer.Text -ne '')
           {
           $status1=Validate-IsEmail $txtfrom.Text
           #write-host $status1
           $status2=Validate-IsEmail $txtto.Text
           #write-host $status1
           if($status1 -eq $FALSE)
           {
           [void][System.Windows.Forms.MessageBox]::Show("Check From Email Entries")
           if($status2 -eq $FALSE)
           {
           [void][System.Windows.Forms.MessageBox]::Show(" Check To Email Entries")
           }
           }
           ELSE
           {
           $statusBar1.Text="Sending Email...Please wait"
           #write-host "executing this batch"
           #write-host $txtComputerName1.text
           #$data=Get-DiskSpaceReport -list $txtComputerName1.text -From $txtfrom.text -To $txtTo.text -SMTPMail $txtSMTPServer.text
           $date = ( get-date ).ToString('yyyy/MM/dd')
           sendEmail -from $txtfrom.Text -to $txtto.Text -subject "Disk Space Report - $Date" -smtphost $txtSMTPServer.Text -htmlfilename "C:\FreeSpace.htm"
           $statusBar1.Text="email sent"
           }
           }
       
      $statusBar1.Text="Ready"
     }
 
    #>
    
    
    # --End User Generated Script--
    #----------------------------------------------
    # Generated Events
    #----------------------------------------------
    
    $Form_StateCorrection_Load=
    {
        #Correct the initial state of the form to prevent the .Net maximized form issue
        $form1.WindowState = $InitialFormWindowState
        
      
        
      
    }
    #----------------------------------------------
    #region Generated Form Code
    #----------------------------------------------
    #
    # form1
    #
    $saveFileDialog1 = New-Object System.Windows.Forms.SaveFileDialog
    $form1.Controls.Add($btnRefresh)
    $form1.Controls.Add($btnsearch)
    #$form1.Controls.Add($rtbPerfData)
    #$form1.Controls.Add($pictureBox1)
    $form1.Controls.Add($lblServicePack)
    $form1.Controls.Add($lblOS)
    $form1.Controls.Add($lblDBName)
    $form1.Controls.Add($statusBar1)
    $form1.Controls.Add($btnClose)
    $form1.Controls.Add($txtComputerName1)
    $Form1.controls.add($Chart)
    $Form1.controls.add($txtComputerName2)
    $Form1.controls.add($labelSubject)
    $Form1.controls.add($labelFrom)
    $Form1.controls.add($labelSMTPServer)
    $Form1.controls.add($labelTo)
    $Form1.controls.add($lblExpire)
    $Form1.controls.add($txtFrom)
    $Form1.controls.add($txtTo)
    $Form1.controls.add($txtSubject)
    $Form1.controls.add($txtSMTPServer)
    $Form1.controls.add($buttonEMailOutput)
    $Form1.controls.add($checkBox)
    #$Form1.controls.add($Warningcheckbox)
    #$Form1.controls.add($Criticalcheckbox)
    #$Form1.controls.add($txtWarning)
    #$Form1.controls.add($txtCritical)
    $form1.ClientSize = New-Object System.Drawing.Size(900,650)
    $form1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    #$form1.FormBorderStyle = [System.Windows.Forms.FormBorderStyle]::SizableToolWindow
    $form1.Name = "form1"
    $form1.Text = "Drive Information Tool "
    $form1.add_Load($PopulateList)
    
   
    
# create chart object
    
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 850
$System_Drawing_Size.Height = 450
$datagridviewResults.Size = $System_Drawing_Size
$datagridviewResults.DataBindings.DefaultDataSourceUpdateMode = 0
#$datagridviewResults.HeaderForeColor = [System.Drawing.Color]::FromArgb(255,0,0,0)
$datagridviewResults.Name = "dataGrid1"
$datagridviewResults.DataMember = ""
$datagridviewResults.TabIndex = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X =13
$System_Drawing_Point.Y = 72
$datagridviewResults.Location = $System_Drawing_Point
$Chart.visible=$FALSE
$form1.Controls.Add($datagridviewResults)
#$datagridviewResults.CaptionText='Service Comparison'
    #$CheckBox
    $CheckBox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $CheckBox.Enabled = $TRUE
    $CheckBox.CHECKED = $TRUE
    $CheckBox.Location = New-Object System.Drawing.Point(440,10)
    $CheckBox.Name = "CheckBox"
    $CheckBox.Size = New-Object System.Drawing.Size(72,20)
    $CheckBox.TabIndex = 3
    $CheckBox.Text = "Input File"
    $CheckBox.UseVisualStyleBackColor = $True
    $btnRefresh.add_Click($buttonQuery_Click)
    #$BrowseButton.Enabled = $TRUE;
    
  #  $checkbox.Add_Click({$BrowseButton.Enabled = $TRUE; $btnRefresh.add_Click($buttonQuery_Click)})
    $checkbox.Add_CheckStateChanged({
    If ($Checkbox.Checked -eq $TRUE  ) {
        $BrowseButton.Enabled = $TRUE;
        $txtComputerName1.text=''
        $btnRefresh.add_Click($buttonQuery_Click)
    }
    If ($Checkbox.Checked -eq $FALSE )
    {
        $txtComputerName1.text=''
        $BrowseButton.Enabled = $FALSE;
        $btnRefresh.add_Click($buttonQuery_NoCheckBoxClick)
        
    }
})
    
    
    
    
    
    #$CheckBox.add_Click($buttonQuery_Click)
    
    # btnRefresh
    #
    $btnRefresh.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btnRefresh.Enabled = $TRUE
    $btnRefresh.Location = New-Object System.Drawing.Point(600,10)
    $btnRefresh.Name = "btnRefresh"
    $btnRefresh.Size = New-Object System.Drawing.Size(72,20)
    $btnRefresh.TabIndex = 3
    $btnRefresh.Text = "GetDisk"
    $btnRefresh.UseVisualStyleBackColor = $True
    #$btnRefresh.add_Click($buttonQuery_Click)
    #
    #
    #
    # btnsearch
    #
    #$btnsearch.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btnsearch.Enabled = $TRUE
    $btnsearch.Location = New-Object System.Drawing.Point(570,35)
    $btnsearch.Name = "btnsearch"
    $btnsearch.Size = New-Object System.Drawing.Size(72,20)
    $btnsearch.TabIndex = 6
    $btnsearch.Text = "Search"
    $btnsearch.UseVisualStyleBackColor = $True
    $btnsearch.add_Click($buttonSearch_Click)
    #
  
    # btnClose
    #
    
    $btnClose.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $btngetdata.Enabled = $TRUE
    $btnClose.Location = New-Object System.Drawing.Point(673,10)
    $btnClose.Name = "btnClose"
    $btnClose.Size = New-Object System.Drawing.Size(72,20)
    $btnClose.TabIndex = 4
    $btnClose.Text = "Close"
    $btnClose.UseVisualStyleBackColor = $True
    $btnClose.add_Click($Close)
    #
    
    # lblDBName
    #
    $lblDBName.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblDBName.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblDBName.Location = New-Object System.Drawing.Point(13,10)
    $lblDBName.Name = "lblDBName"
    $lblDBName.Size = New-Object System.Drawing.Size(178,23)
    $lblDBName.TabIndex = 0
    $lblDBName.Text = "Select Input file  "
    $lblDBName.Visible = $TRUE
    #
    
    #$txtComputerName1.text
    #txtComputerName1
    $txtComputerName1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtComputerName1.Location = New-Object System.Drawing.Point(200, 10)
    $txtComputerName1.Name = "txtComputerName1"
    $txtComputerName1.TabIndex = 1
    $txtComputerName1.Size = New-Object System.Drawing.Size(200,70)
    $txtComputerName1.visible=$TRUE
    #
    
    #
    # lblExpire
    #
    $lblExpire.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblExpire.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblExpire.Location = New-Object System.Drawing.Point(13,35)
    $lblExpire.Name = "lblExpire"
    $lblExpire.Size = New-Object System.Drawing.Size(178,23)
    $lblExpire.TabIndex = 0
    $lblExpire.Text = "Enter the search String"
    $lblExpire.Visible = $TRUE
    #
    #$txtComputerName2.text
    #txtComputerName2
    $txtComputerName2.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtComputerName2.Location = New-Object System.Drawing.Point(200,35)
    $txtComputerName2.Name = "txtComputerName2"
    $txtComputerName2.TabIndex = 5
    $txtComputerName2.Size = New-Object System.Drawing.Size(400,70)
    $txtComputerName2.visible=$TRUE
    #
    # lblServicePack
    #
    $lblServicePack.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblServicePack.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblServicePack.Location = New-Object System.Drawing.Point(13,100)
    $lblServicePack.Name = "lblServicePack"
    $lblServicePack.Size = New-Object System.Drawing.Size(278,23)
    $lblServicePack.TabIndex = 0
    $lblServicePack.Text = "ServicePack"
    $lblServicePack.Visible = $False
    #
    # lblOS
    #
    $lblOS.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $lblOS.Font = New-Object System.Drawing.Font("Lucida Console",8.25,1,3,1)
    $lblOS.Location = New-Object System.Drawing.Point(12,77)
    $lblOS.Name = "lblOS"
    $lblOS.Size = New-Object System.Drawing.Size(278,23)
    $lblOS.TabIndex = 2
    $lblOS.Text = "User Information"
    $lblOS.Visible = $False
    #
    # statusBar1
    #
    $statusBar1.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $statusBar1.Location = New-Object System.Drawing.Point(0,365)
    $statusBar1.Name = "statusBar1"
    $statusBar1.Size = New-Object System.Drawing.Size(390,22)
    $statusBar1.TabIndex = 5
    $statusBar1.Text = "statusBar1"
    #
    #>
    # labelSubject
    #
    $labelSubject.Location = '350, 578'
    $labelSubject.Name = "labelSubject"
    $labelSubject.Size = '50, 19'
    #$labelSubject.TabIndex = 18
    $labelSubject.Text = "Subject"
    #
    # labelFrom
    #
    $labelFrom.Location = '17, 538'
    $labelFrom.Name = "labelFrom"
    $labelFrom.Size = '50, 19'
    $labelFrom.Text = "From"
    #
    # label1
    #
    $labelTo.Location = '17, 578'
    $labelTo.Name = "labelTo"
    $labelTo.Size = '50, 19'
    #$labelTo.TabIndex = 16
    $labelTo.Text = "To"
    #
    # labelSMTPServer
    #
    $labelSMTPServer.Location = '350, 538'
    $labelSMTPServer.Name = "labelSMTPServer"
    $labelSMTPServer.Size = '50, 19'
    #$labelSMTPServer.TabIndex = 15
    $labelSMTPServer.Text = "SMTP"
    #
    #txtSubject
    #
    $txtSubject.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtSubject.Location = New-Object System.Drawing.Point(420,578)
    $txtSubject.Name = "txtSubject"
    $txtSubject.Size = New-Object System.Drawing.Size(200,70)
    $txtSubject.visible=$TRUE
    #
    #txtFrom
    #
    $txtFrom.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtFrom.Location = New-Object System.Drawing.Point(67,538)
    $txtFrom.Name = "txtFrom"
    #$txtFrom.TabIndex = 1
    $txtFrom.Size = New-Object System.Drawing.Size(200,70)
    $txtFrom.visible=$TRUE
    
     #
    #txtSMTPServer
    #
    $txtSMTPServer.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtSMTPServer.Location = New-Object System.Drawing.Point(420,538)
    $txtSMTPServer.Name = "txtSMTPServer"
    $txtSMTPServer.Size = New-Object System.Drawing.Size(200,70)
    $txtSMTPServer.visible=$TRUE
    
        #
    #txtTo
    #
    $txtTo.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtTo.Location = New-Object System.Drawing.Point(67,578)
    $txtTo.Name = "txtTo"
    $txtTo.Size = New-Object System.Drawing.Size(200,70)
    $txtTo.visible=$TRUE
    <#
    
    $Warningcheckbox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $Warningcheckbox.Location = New-Object System.Drawing.Point(630,538)
    $Warningcheckbox.Name = "CheckBox1"
    $Warningcheckbox.Size = New-Object System.Drawing.Size(65,20)
    $Warningcheckbox.TabIndex = 3
    $Warningcheckbox.Text = "Warning"
    $Warningcheckbox.UseVisualStyleBackColor = $True
    #
    $txtWarning.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtWarning.Location = New-Object System.Drawing.Point(700,538)
    $txtWarning.Name = "txtWarning"
    $txtWarning.Size = New-Object System.Drawing.Size(50,25)
    $txtWarning.visible=$FALSE
    #
    $Criticalcheckbox.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $Criticalcheckbox.Location = New-Object System.Drawing.Point(630,578)
    $Criticalcheckbox.Name = "CheckBox1"
    $Criticalcheckbox.Size = New-Object System.Drawing.Size(65,20)
    $Criticalcheckbox.TabIndex = 3
    $Criticalcheckbox.Text = "Critical"
    $Criticalcheckbox.UseVisualStyleBackColor = $True
    #
    
     $Criticalcheckbox.Add_CheckStateChanged({
    If ($Criticalcheckbox.Checked -eq $TRUE  ) {
        $txtcritical.visible=$TRUE;
        $script:critical=$txtcritical.text;
          write-host $script:critical;
        }
        else
        {
        $script:critical = 20;
        $txtcritical.visible=$FALSE
        }})
        
    $warningcheckbox.Add_CheckStateChanged({
    If ($warningcheckbox.Checked -eq $TRUE  ) {
        $txtwarning.visible=$TRUE;
        $script:Warning=$txtwarning.text;
         write-host $script:Warning;
        }
        else
        {
        $script:Warning = 50;
        $txtwarning.visible=$FALSE;
        }})
    
  
     
    
    $txtcritical.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
    $txtcritical.Location = New-Object System.Drawing.Point(700,578)
    $txtcritical.Name = "txtcritical"
    $txtcritical.Size = New-Object System.Drawing.Size(50,25)
    $txtcritical.visible=$FALSE
    
   #>
    
    # buttonEMailOutput
    #
    $buttonEMailOutput.Location = '660, 538'
    $buttonEMailOutput.Name = "buttonEMailOutput"
    $buttonEMailOutput.Size = '60, 60'
    #$buttonEMailOutput.TabIndex = 10
    $buttonEMailOutput.Text = "E-Mail Output"
    $buttonEMailOutput.UseVisualStyleBackColor = $True
    $buttonEMailOutput.add_Click($GetData)
        
    
$rtbPerfData.BackColor = [System.Drawing.Color]::FromArgb(255,255,255,255)
$rtbPerfData.BorderStyle = [System.Windows.Forms.BorderStyle]::None
$rtbPerfData.DataBindings.DefaultDataSourceUpdateMode = [System.Windows.Forms.DataSourceUpdateMode]::OnValidation
$rtbPerfData.Font = New-Object System.Drawing.Font("Lucida Console",8.25,0,3,1)
$rtbPerfData.Location = New-Object System.Drawing.Point(13,120)
$rtbPerfData.Name = "rtbPerfData"
$rtbPerfData.Size = New-Object System.Drawing.Size(450,200)
$rtbPerfData.TabIndex = 6
$rtbPerfData.Text = ""
$BrowseButton = New-Object System.Windows.Forms.Button
$BrowseButton.Location = New-Object System.Drawing.Size(520,10)
$BrowseButton.Size = New-Object System.Drawing.Size(75,20)
$BrowseButton.TabIndex = 2
$BrowseButton.enabled = $TRUE
$BrowseButton.Text = "Browse"
$BrowseButton.Add_Click({
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$dialog = New-Object System.Windows.Forms.OpenFileDialog
$dialog.DefaultExt = '.txt'
$dialog.Filter = 'All Files|*.*'
$dialog.FilterIndex = 0
$dialog.InitialDirectory = $home
$dialog.Multiselect = $false
$dialog.RestoreDirectory = $true
$dialog.Title = "Select a Input file"
$dialog.ValidateNames = $true
$dialog.ShowDialog()
$txtComputerName1.Text = $dialog.FileName;})
$form1.Controls.Add($BrowseButton)
    #Save the initial state of the form
    $InitialFormWindowState = $form1.WindowState
   
    #Init the OnLoad event to correct the initial state of the form
    $form1.add_Load($Form_StateCorrection_Load)
    #Show the Form
    return $form1.ShowDialog()
} #End Function
#Call OnApplicationLoad to initialize
if(OnApplicationLoad -eq $true)
{
    #Create the form
    Call-SystemInformation_pff | Out-Null
    #Perform cleanup
    OnApplicationExit
}
}
Get-DiskSpace_Report
Posted in PowerShell, Uncategorized | Tagged , , , | Leave a comment

Minimizing SQL Server DBCC CHECKDB Performance Impacts

Overview

SQL Server is the most popular relational database management system, which is used by many organizations to maintain their data. Every user expects that data should be stored in a consistent and integrated form. SQL Server is equipped with an in-built DBCC command, which allow users to check the integrity of the database. One of the command, DBCC CHECKDB helps to check integrity and allocation of specific objects in a database. This command should be executed on a regular basis to check if there is any corruption issue in the database files. It will analyze the entire database and provides the report regarding consistency of files, integrity of indexes, catalog consistency. So, it can be considered as an alert to detect the corruption in the database. However, the performance related issues associated with this command are a major area of concern for most of the SQL users. The following section aims to discuss on how to Minimize Performance Impact of SQL Server DBCC CHECKDB.

Impact of DBCC CHECKDB on SQL Server Performance

Although DBCC CHECKDB command is quite useful command to check the consistency but a number of issues are associated with this command, which hampers the performance of SQL Server. The top three performance related issues faced due to DBCC CHECKDB include:

1. System Process Delay:

When DBCC CHECKDB command is issued, it performs a thorough page-by-page analysis of the database files. Therefore, it acquires many resources at a time that slow down other system processes.

2. Slow Backup Process:

Since backup is a bit-to-bit copy of the existing database, it consumes much of the allocated resources as well as I/O devices. The backup file has to maintain every header, so it consumes lots of time.

3. Slow Recovery Process:

During the database recovery process, a hidden screen shot is created by DBCC. However, the consistency check of the screen shot is much time taking and slows down the recovery process.

Ways to Minimize Performance Impact of SQL Server DBCC CHECKDB

DBCC CHECKDB command plays a crucial role in the corruption issues, so it cannot be avoided due to its impact on performance. However, users can opt for measures to reduce the impact on performance of SQL Server. Some of the practices that can be followed while using DBCC CHECKDB are:

1. TempDb Maintenance:

DBCC makes a continuous use of TempDB database. Hence, it is advisable to store the TempDB on its own spindles so that any of the I/O spooling process does not get disturbed due to database access.

2. Database Mirroring:

Users can create a mirror image of the database using Database Mirroring setup. This will allow users to run DBCC CHECKDB on that version of database. It will keep the performance of primary database intact.

3. Avoid Information Messages:

Make sure that you use use DBCC CHECKDB with WITH NO_INFOMSGS to suppress all the irrelevant outputs generated during DBCC processing. It will somehow improve the performance rate of command processing.

4. Use Trace Flags:

In order to improve the performance, DBCC CHECKDB can be used with the following trace flags:

  1. Trace Flag 2549: This trace flag optimizes the performance of SQL Server by treating each database file as it resides on a different disk. Thus, it makes a faster scan of the database files.
  2. Trace Flag 2562: This flag treats the entire database as a batch and makes the use of better page reading algorithms. It will lead to a faster database processing.

5. Reduce CPU Impact:

DBCC CHECKDB command supports multithreading by default. In order to limit its CPU usage, users can reduce the parallelism in following ways:

  • Trace Flag 2528 can be used to turn off parallelism during processing of DBCC CHECKDB.
  • User can use SQL Server in-built feature “Maximum Degree of Parallelism” to specify the limit of CPU allocation.

6. Use Optimized Options

On a regular basis, DBCC CHECKDB can be used with other attributes, which can optimize the DBCC processing. Various options such as PHYSICAL_ONLY combined with CHECKSUM allows user to evaluate only physical record, thus helps in saving time and resource allocation.

Conclusion

Database corruption is a major hack, which damages user’s relevant data. It is always advised to perform a regular check to detect any corruption related issue. DBCC CHECKDB command is a significant utility to acknowledge the corruption in database. Once a corruption issue is detected in the files, users can take immediate steps to perform database recovery. However, it is always advisable to maintain an updated backup, which can be used in case of database failure or database corruption.

Posted in Uncategorized | Tagged | 1 Comment

PowerSQL – Get SQL TraceFlag SQL Service and Config Information

Get SQL Server configuration, service info, start type along with enabled Trace Flags of local or remote servers.

This script outputs sql server configuration, service, start type, Service Account along with startup trace flags. The script will work on SQL Server 2005 and up.

function Get-SQLAndGlobalTraceFlagsDetails { 
<# 
.SYNOPSIS 
Returns SQL Server Configuration, Service and global TraceFlag details 
 
.DESCRIPTION 
Get-SQLAndGlobalTraceFlagsDetails is a function that returns server high level server configuration information for 
one or more Microsoft SQL Server 
 
.PARAMETER ComputerName 
The computer that is running Microsoft SQL Server that you’re targeting 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName sql01 
 
.EXAMPLE 
Get-SQLAndGlobalTraceFlagsDetails -ComputerName HQDBSP18 
 
.EXAMPLE 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails -ServerName $_ } 
 
.EXAMPLE 
 
ForEach ($server in Get-ChildItem C:\server.txt) 
{ 
Get-SQLAndGlobalTraceFlagsDetails -ServerName $server 
 
} 
 
.INPUTS 
String 
 
.OUTPUTS 
PSCustomObject , console or CSV 
#> 
[CmdletBinding()] 
param ( 
[Parameter(Mandatory=$true, 
ValueFromPipelineByPropertyName=$true)] 
[Alias('ServerName','PSComputerName','ComputerName')] 
[string[]]$Ser 
) 
 
 
# Used to store the result 
 
$Results = @() 
 
# The below array used to concatenate the Trace flag details 
 
$TraceFlag=@() 
 
#create an smo object for the SQL Server 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null 
 
$SQLServer = new-object ('Microsoft.SQLServer.Management.Smo.Server') $Ser 
$SQL= $SQLServer | select Name, Edition, BuildNumber, Product, ProductLevel, Version, Processors, PhysicalMemory, DefaultFile, DefaultLog, MasterDBPath, MasterDBLogPath, BackupDirectory, ServiceAccount,ServiceStartMode,State 
 
$SQLServer.EnumActiveGlobalTraceFlags()|%{ 
[string]$b=$_.status 
[string]$c=$_.TraceFlag 
$TraceFlag+=$c.ToString() +'->'+ $b.ToString() 
 
} 
 
$Properties = @{ 
Name = $SQL.name 
Edition = $SQL.Edition 
BuildNumber = $SQL.BuildNumber 
Product=$SQL.Product 
ProductLevel=$SQL.ProductLevel 
Version=$SQL.Version 
Processors=$SQL.Processors 
DefaultLog=$SQL.DefaultLog 
MasterDBPath=$SQL.MasterDBPath 
MasterDBLogPath=$SQL.MasterDBLogPath 
BackupDirectory=$SQL.BackupDirectory 
ServiceAccount=$SQL.ServiceAccount 
StartupMode=$SQL.ServiceStartMode 
State=$SQL.State 
TraceAndStatus=($TraceFlag -join ',')} 
 
 
 
$Results += New-Object psobject -Property $properties  
$Results | Select-Object Name, Edition, BuildNumber,TraceAndStatus,Product,ProductLevel,Version,Processors,DefaultLog,MasterDBPath,MasterDBLogPath,BackupDirectory,ServiceAccount,StartupMode,State 
 
 
} 
 
"hqdbst03","hqdbsp16" | % { Get-SQLAndGlobalTraceFlagsDetails1 -ServerName $_ }
 SQLTraceFlag
Posted in PowerShell, SQL | Tagged , , | Leave a comment
%d bloggers like this: