rem create startup file first so we can log results of script
%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe d:\mumar\monitor5.ps1
echo ran at this time %DATE% %TIME% > monitor_log.txt
Call the above drive_start.cmd or something tlike that… ensure the path points to your executable of powershell.
This calls the Powershell noted below, but first:
For the below to work create Files of your servers, and name the file name the support DL to get notified when thresholds are breached.
So for example:
CSPA.txt
PFOPS.txt
PHOPS.TXT
Lenel.txt
Within each file have a list of the servers you want monitored.
Next, create a file called: thefilenames, and place just the DL names in there so for above it would be
Cspa
Pfops
Phops
Lenel
Then within SQL server, create an exec sp_email, that will accept the parameters I send below, in the end, I pass the filename which is also the DL which the email will go to.
A Sample SP and the one I use is as follows:
-- =============================================
CREATE PROCEDURE [dbo].[sp_email]
-- Add the parameters for the stored procedure here
@p1 varchar(800) = 'Body not supplied to sp sp_email on bakerx',
@p2 varchar(40),
@p3 varchar(40)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @thebody varchar(800)
declare @recips varchar(40)
declare @servername varchar(200)
-- Insert statements for procedure here
set @thebody = @p1 + ' This alert came from bakerx. sp_email from within MSDB '
set @recips = @p2 +'@microsoft.com'
--set @recips = 'niglen@microsoft.com'
set @servername = @p3+' <
set @thebody = @thebody + '
'
exec sp_send_dbmail @profile_name = 'svcoad',
@recipients = @recips,
@subject = @servername,
@body = @thebody,
@copy_recipients = 'niglen@microsoft.com',
@body_format = 'HTML'
--;xxxxxx@microsoft.com;xxxxxxnbu@microsoft.com;thxxxxxdie@microsoft.com;xxxeets@microsoft.com;shxxxxpavi@microsoft.com;xxxxxnw@microsoft.com',
END
The main powershell is below, the comments should explain pretty good how it works.
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
cls
$mydate = Get-Date
Write-Host "The Date is $mydate"
sleep 2
Write-Host "#####################################" -foregroundcolor red -backgroundcolor yellow
Write-Host "# #"-foregroundcolor red -backgroundcolor yellow
Write-Host "# Real-Time Disk Analyzer #"-foregroundcolor red -backgroundcolor yellow
Write-Host "# By: Nigel Glenwood #"-foregroundcolor red -backgroundcolor yellow
Write-Host "#####################################"-foregroundcolor red -backgroundcolor yellow
# Write-Host "Name, ID, FS, FreeSpace, Size, Usedspace"-foregroundcolor white -backgroundcolor blue
#
# first get-content grabs the filenames that contains the servers for each space
# Then for each space, run through that file name and grab all the servers and send alerts as needed.
# So basically, these are kind of loops inside of loops.
# -Nigel
# beginning of outmost loop
get-content d:\mumar\thefilenames.txt | Foreach-Object {
write-host $_ is the value I just read let me throw that into a variable for me
$filetoprocess=$_
write-host $filetoprocess is the file I am going to process now
sleep 5
# beginner of inner loop
get-wmiobject win32_logicaldisk -filter "drivetype=3" -computer (get-content $filetoprocess'.txt') | Select SystemName,DeviceID,FileSystem,@{Name="Freespace";Expression={([Math]::round([long]$_.FreeSpace/1gb,0))}},@{Name="FreespaceMegs";Expression={([Math]::round([long]$_.FreeSpace/1mb,0))}},@{Name="Size";Expression={([Math]::round([long]$_.Size/1gb,0))}},@{Name="Usedspace";Expression={([Math]::round([long]$_.Size/1gb-[long]$_.FreeSpace/1gb,0))}} | ForEach-Object {
# calculcate percent Free
$percentage=([Math]::round([long]$_.FreeSpace/[long]$_.size,2))
# remove decimals, make it look pretty
$percentage_free=$percentage*100
$the_system_name=$_.SystemName
$free_space=$_.FreeSpace
$free_space_megs=$_.FreeSpaceMegs
$drive_letter=$_.deviceid
write-host "driver letter is: "
write-host $drive_letter
# find files written to within 1 day
#if ($drive_letter -eq 'E:')
#{
# write-host "drive letter does = E running analysis"
# $DateToCompare = (Get-date).AddDays(-1)
# $Dir = get-childitem \\$the_system_name\E$ -recurse | where {$_.lastwritetime -gt $datetocompare}
# write-host "Directory is" $Dir
# $List = | format-table
# $list | format-table name
#, lastwritetime, length
#write-host $list
#write-host $Dir
#write-host "above is the list"
#sleep 5
#}
#write-host "did it write data above of the files?"
#sleep 2
# Nigel Notes, have to put the variables into different variables otherwise for some reason
# they will print all data just for one of the fields, so trying to just display percent free space results
# in all the fields showing
Write-Host $_.SystemName $_.DeviceID $_.FileSystem "FreeSpace:" $_.FreeSpace ", Actual Size:" $_.Size, "Percentage Free" $percentage_free -foreground white -backgroundcolor magenta
if ($percentage_free -lt 3)
{ sqlcmd /S bakerx /E -d msdb /Q "exec sp_email 'Server: $the_system_name, Percentage of Free Disk Space Left is: $percentage_free PERCENT, Free Unused Space is: $free_space GIGS, Free Unused Space: $free_space_megs MEGS DETAILS:$_.SystemName Time of Discovery: $mydate ... Less than 3 percent Free ... ','$filetoprocess', '$the_system_name'"
}
elseif ($percentage_free -lt 7)
{ sqlcmd /S bakerx /E -d msdb /Q "exec sp_email 'Server: $the_system_name Percentage of Free Disk Space Left is: $percentage_free PERCENT Free Unused Space is: $free_space Gigs , Free Unused Space: $free_space_megs MEGS DETAILS: $_.SystemName Time of Discovery: $mydate ... less than 5 Percent Free ... ','$filetoprocess','$the_system_name'"
Write-Host "Sending Email.... less than 5 percent free"
}
}
# end of inner loop
}
# end of outter loop
Write-Host "COMPLETE......................"
# send an email to Nigel to let me know that it at least ran, even if no thresholds were breached
sqlcmd /S bakerx /E -d msdb /Q "exec sp_email5 'Completed AT : %DATE% %TIME%"
Nigel
No comments:
Post a Comment