Scripts
- Vishnu Gupthan Nair
- Jan 27, 2022
- 10 min read
$user = [Environment]::username
$a = new-object -comobject wscript.shell
$b = $a.popup("Hi $user . Welcome to Server Status check System",1,'Welcome')
function Invoke-SQLcmd
{
param(
$ServerInstance,
$Database,
$User,
$pw,
$Query
)
$Conn=New-Object System.Data.SQLClient.SQLConnection "Server=$ServerInstance;Database=$Database;Integrated Security=true";
$Conn.Open();
$DataCmd = New-Object System.Data.SqlClient.SqlCommand;
$DataCmd.CommandText = $Query;
$DataCmd.Connection = $Conn;
$DAadapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$DAadapter.SelectCommand = $DataCmd;
$DTable = New-Object System.Data.DataTable;
$DAadapter.Fill($DTable)|Out-Null;
$Conn.Close();
$Conn.Dispose();
$DTable;
}
function sendmail()
{
param($EmailFrom,$EmailTo,$Subject,$SMTPServer,$port,$Username,$pass,$attachments)
$loc=$LocTB.Text
$mailconfigloc=$loc+'\mailconfig.xml'
[xml]$myXML = Get-Content $mailconfigloc
$EmailFrom=$myXML.mailconfig.from
$EmailTo=$myXML.mailconfig.to
$SMTPServer=$myXML.mailconfig.smtp
$port=$myXML.mailconfig.port
$Subject=$myXML.mailconfig.sub
$Username=$myXML.mailconfig.uname
$pass=$myXML.mailconfig.pwd
Write-Host '$pass'$pass
$Password = "$pass" |ConvertTo-SecureString
$Credentials = New-Object System.Management.Automation.PSCredential ($Username,$Password)
Send-MailMessage -From $EmailFrom -to $EmailTo -Body 'Mail from ServerCheckTool' -Subject $Subject -UseSsl -SmtpServer $SMTPServer -Port $port -Credential $Credentials -Attachments $attachments
}
function mailform()
{
param($location)
function sendmail()
{
param($EmailFrom,$EmailTo,$Subject,$SMTPServer,$port,$Username,$pass)
$Password = "$pass" |ConvertTo-SecureString
$Credentials = New-Object System.Management.Automation.PSCredential ($Username,$Password)
Send-MailMessage -From $EmailFrom -to $EmailTo -Body 'Mail from ServerCheckTool' -Subject $Subject -UseSsl -SmtpServer $SMTPServer -Port $port -Credential $Credentials
}
# Create a GUI Form
$Mailform = New-Object System.Windows.Forms.Form
$Mailform.Text = “Mail configuration Page”
$Mailform.Size = New-Object System.Drawing.Size(500,700)
$Mailform.StartPosition = “CenterScreen”
# Add a text box to the Location
$FromTB = New-Object System.Windows.Forms.TextBox
$FromTB.Location = New-Object System.Drawing.Size(150,50)
$FromTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($FromTB)
# Add a text box to the Location
$FromLabel = New-Object System.Windows.Forms.Label
$FromLabel.Location = New-Object System.Drawing.Size(50,50)
$FromLabel.Size = New-Object System.Drawing.Size(200,20)
$FromLabel.Text='From email'
$Mailform.Controls.Add($FromLabel)
# Add a text box to the Location
$ToTB = New-Object System.Windows.Forms.TextBox
$ToTB.Location = New-Object System.Drawing.Size(150,100)
$ToTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($ToTB)
# Add a text box to the Location
$ToLabel = New-Object System.Windows.Forms.Label
$ToLabel.Location = New-Object System.Drawing.Size(50,100)
$ToLabel.Size = New-Object System.Drawing.Size(200,20)
$ToLabel.Text='To email'
$Mailform.Controls.Add($ToLabel)
# Add a text box SMTP the Location
$SMTPTB = New-Object System.Windows.Forms.TextBox
$SMTPTB.Location = New-Object System.Drawing.Size(150,150)
$SMTPTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($SMTPTB)
# Add a text box SMTP the Location
$SMTPLabel = New-Object System.Windows.Forms.Label
$SMTPLabel.Location = New-Object System.Drawing.Size(50,150)
$SMTPLabel.Size = New-Object System.Drawing.Size(200,20)
$SMTPLabel.Text='SMTP '
$Mailform.Controls.Add($SMTPLabel)
# Add a text box Subject the Location
$SubjectTB = New-Object System.Windows.Forms.TextBox
$SubjectTB.Location = New-Object System.Drawing.Size(150,200)
$SubjectTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($SubjectTB)
# Add a text box Subject the Location
$SubjectLabel = New-Object System.Windows.Forms.Label
$SubjectLabel.Location = New-Object System.Drawing.Size(50,200)
$SubjectLabel.Size = New-Object System.Drawing.Size(200,20)
$SubjectLabel.Text='Subject '
$Mailform.Controls.Add($SubjectLabel)
# Add a text box port the Location
$portTB = New-Object System.Windows.Forms.TextBox
$portTB.Location = New-Object System.Drawing.Size(150,250)
$portTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($portTB)
# Add a text box port the Location
$portLabel = New-Object System.Windows.Forms.Label
$portLabel.Location = New-Object System.Drawing.Size(50,250)
$portLabel.Size = New-Object System.Drawing.Size(200,20)
$portLabel.Text='Port No: '
$Mailform.Controls.Add($portLabel)
# Add a text box port the Location
$usernameTB = New-Object System.Windows.Forms.TextBox
$usernameTB.Location = New-Object System.Drawing.Size(150,300)
$usernameTB.Size = New-Object System.Drawing.Size(200,20)
$Mailform.Controls.Add($usernameTB)
# Add a text box port the Location
$usernameLabel = New-Object System.Windows.Forms.Label
$usernameLabel.Location = New-Object System.Drawing.Size(40,300)
$usernameLabel.Size = New-Object System.Drawing.Size(200,20)
$usernameLabel.Text='Email Username '
$Mailform.Controls.Add($usernameLabel)
# Add a text box port the Location
$passwordTB = New-Object System.Windows.Forms.TextBox
$passwordTB.Location = New-Object System.Drawing.Size(150,350)
$passwordTB.Size = New-Object System.Drawing.Size(200,20)
#$Mailform.Controls.Add($passwordTB)
# Add a text box port the Location
$passwordLabel = New-Object System.Windows.Forms.Label
$passwordLabel.Location = New-Object System.Drawing.Size(40,350)
$passwordLabel.Size = New-Object System.Drawing.Size(200,20)
$passwordLabel.Text='Email password '
#$Mailform.Controls.Add($passwordLabel)
# Add a text box port the Location
$Save = New-Object System.Windows.Forms.Button
$Save.Location = New-Object System.Drawing.Size(150,450)
$Save.Size = New-Object System.Drawing.Size(200,30)
$Save.Text='Save Config'
$Mailform.Controls.Add($Save)
# Add a text box port the Location
$testbutton = New-Object System.Windows.Forms.Button
$testbutton.Location = New-Object System.Drawing.Size(150,500)
$testbutton.Size = New-Object System.Drawing.Size(200,30)
$testbutton.Text='Send Test Email'
$Mailform.Controls.Add($testbutton)
# Add a text box port the Location
$SaveLabel = New-Object System.Windows.Forms.Label
$SaveLabel.Location = New-Object System.Drawing.Size(50,350)
$SaveLabel.Size = New-Object System.Drawing.Size(300,20)
$SaveLabel.ForeColor='Green'
$mailconfigloc="$location\mailconfig.xml"
[xml]$myXML = Get-Content $mailconfigloc
$FromTB.Text=$myXML.mailconfig.from
$ToTB.Text=$myXML.mailconfig.to
$SMTPTB.Text=$myXML.mailconfig.smtp
$SubjectTB.Text=$myXML.mailconfig.sub
$portTB.Text=$myXML.mailconfig.port
$usernameTB.Text=$myXML.mailconfig.uname
#$passwordTB.Text=$myXML.mailconfig.pwd
$Mailform.Controls.Add($SaveLabel)
$Save.Add_click({
$from=$FromTB.Text
$to=$ToTB.Text
$smtp=$SMTPTB.Text
$sub=$SubjectTB.Text
$port=$portTB.Text
$uname=$usernameTB.Text
#$pass=$passwordTB.Text
$getcred=Get-Credential -UserName $uname -Message 'Enter your email password'
$encryptedpassword= $getcred.password
$pass = $encryptedpassword | ConvertFrom-SecureString
$mailxml=@"
<mailconfig>
<from>$from</from>
<to>$to</to>
<smtp>$smtp</smtp>
<port>$port</port>
<sub>$sub</sub>
<uname>$uname</uname>
<pwd>$pass</pwd>
</mailconfig>
"@
if(!(Test-Path $mailconfigloc))
{
New-Item $mailconfigloc
$mailxml | Out-File $mailconfigloc
}
else
{
[xml]$myXML = Get-Content $mailconfigloc
$myXML.mailconfig.from=$from
$myXML.mailconfig.to=$to
$myXML.mailconfig.smtp=$smtp
$myXML.mailconfig.port=$port
$myXML.mailconfig.sub=$sub
$myXML.mailconfig.uname=$uname
$myXML.mailconfig.pwd="$pass"
$myXML.Save($mailconfigloc)
}
$SaveLabel.Text='Configurations saved successfully. '
})
$testbutton.Add_Click({
$from=$FromTB.Text
$to=$ToTB.Text
$smtp=$SMTPTB.Text
$sub=$SubjectTB.Text
$port=$portTB.Text
$uname=$usernameTB.Text
[xml]$myXML = Get-Content $mailconfigloc
$pass=$myXML.mailconfig.pwd
$Username = $uname
$Password = $pass |ConvertTo-SecureString
$Credentials = New-Object System.Management.Automation.PSCredential ($Username,$Password)
sendmail -emailfrom $from -emailto $to -subject $sub -Body 'Test mail' -smtpserver $smtp -port $port -Username $uname -pass $pass
$SaveLabel.Refresh()
$SaveLabel.Text='Mail queued. You will shortly receive the email. '
})
$Mailform.ShowDialog()
}
[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Drawing”)
[void] [System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)
$head = @'
<style>
body { background-color:#dddddd;
font-family:Tahoma;
font-size:12pt; }
td, th { border:1px solid black;
border-collapse:collapse; }
th { color:white;
background-color:black; }
table, tr, td, th { padding: 2px; margin: 0px }
table { margin-left:50px; }
</style>
'@
$timenow = Get-date
# Create functions for the functionalities
#=============================================================================
Function SQLInstancerunstatus {
param($loc)
# Defining output format for each column.
$fmtName =@{label="Service Name" ;alignment="left" ;width=30 ;Expression={$_.Name};};
$fmtMode =@{label="Start Mode" ;alignment="left" ;width=10 ;Expression={$_.StartMode};};
$fmtState =@{label="State" ;alignment="left" ;width=10 ;Expression={$_.State};};
$fmtStatus =@{label="Status" ;alignment="left" ;width=10 ;Expression={$_.Status};};
$fmtMsg =@{label="Message" ;alignment="left" ;width=50 ; `
Expression={ if (($_.StartMode -eq "Auto") -and ($_.State -ne "Running") ) {"Alarm: Stopped"} };};
#$colItems = Get-WmiObject Win32_BIOS -Namespace “root\CIMV2" -computername $strComputer
foreach($server in $strComputer)
{
$server+'details' >> $loc\inst.txt
'------------------------------' >> $loc\inst.txt
$srvc = Get-WmiObject `
-query "SELECT *
FROM win32_service
WHERE name LIKE '%SQL%'" `
-computername $server `
| Sort-Object -property name;
if($srvc -ne $null)
{
Write-Output ("Server: {0}" -f $server);
Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg;
$srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg >> $loc\inst.txt
}
else
{
"$server "+'Server does not exist OR not pingable OR network glitch' >> $loc\inst.txt
}
}
}
Function CheckServerPing{
param($loc)
ForEach ($server in $strComputer)
{
"$server"
$status=get-wmiobject win32_pingstatus -Filter "Address='$server'" | Select-Object statuscode
if($status.statuscode -eq 0)
{
"$server"+' is pingable ' >> $loc\check_ping.txt
}
else
{
"$server"+ 'is NOT reachable or not pingable or network issue' >> $loc\check_ping.txt
}
}
}
Function Ddrivespace{
param($loc)
foreach($server in $strComputer)
{
$server+'details' >> $loc\ddrivespace.txt
'-----------------------' >> $loc\ddrivespace.txt
$p = Gwmi Win32_LogicalDisk -ComputerName $server
if($p -ne $null)
{
$count = $p.count
for($i=0;$i -lt $count ;$i++ )
{
if($p[$i].DeviceID -eq "D:" )
{
$totalsize=$p[$i].Size
$D_drivefreespace = $p[$i].Freespace
$sizeinGB = $D_drivefreespace/(1024*1024*1024)
$totspace_GB = $totalsize/(1024*1024*1024)
$roundtotsize = [System.Math]::Round($totspace_GB,2)
$roundsizeGB = [System.Math]::Round($sizeinGB,2)
$percent = ($roundsizeGB/$roundtotsize)*100
$percentround = [System.Math]::Round($percent)
#'Free space in drive D is '+"$sizeinGB" +'GB whick is '+ "$percentround" + '% of the total '+"$totspace_GB"+'GB' >> $loc\ddrivespace.txt
'Free space in drive D is '+"$roundsizeGB" +'GB whick is '+ "$percentround" + '% of the total '+"$roundtotsize"+'GB' >> $loc\ddrivespace.txt
"`n" >> $loc\ddrivespace.txt
}
else
{continue}
}
}
else{
"$server"+'not reachable OR does not exist or network glitch' >> $loc\ddrivespace.txt
}
}
}
function portnumber{
param($loc)
foreach ($server in $strComputer)
{
"`n">> $loc\portnumber.txt
"$server"+' port number details' >> $loc\portnumber.txt
'----------------------------------------' >> $loc\portnumber.txt
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$srvrobject = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server
$instanceprop = $srvrobject.ServerInstances #to get instance properties
"$server"
$instancecount = $instanceprop.count
if($instancecount -ne 0)
{
for($i=0; $i -lt $instancecount ;$i++)
{
$p=$instanceprop[$i].ServerProtocols['tcp']
$ip=$p.IPAddresses['IPAll']
$portnum=$ip.IPAddressProperties['TcpPort'].Value #to get port numbers
$Instance = $instanceprop[$i].Name
'INstance name '+ "$instance"+"`t"+'and'+"`t"+'portnum'+" $portnum" >> $loc\portnumber.txt
}
}
else
{
'The server '+"$server"+' have no instances OR server does not exist or network glitch' >> $loc\portnumber.txt
}
}
}
Function jobstatus {
param($loc)
$jobquery=@'
SELECT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]
'@
add-pssnapin SqlServerCmdletSnapin100
foreach ($server in $strComputer)
{
"$server"+ 'details' >> $loc\jobcheck.html
'----------------------------' >> $loc\jobcheck.html
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$srvrobject = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server
$instanceprop = $srvrobject.ServerInstances #to get instance properties
"$server"
$connstring="$server\$Instance,$portnum"
$instancecount = $instanceprop.count
if($instancecount -ne 0)
{
for($i=0; $i -lt $instancecount ;$i++)
{
$p=$instanceprop[$i].ServerProtocols['tcp']
$ip=$p.IPAddresses['IPAll']
$portnum=$ip.IPAddressProperties['TcpPort'].Value #to get port numbers
$Instance = $instanceprop[$i].Name
'INstance name '+ "$instance"+"`t"+'and'+"`t"+'portnum'+" $portnum" >> $loc\jobcheck.html
if($portnum -eq '')
{
$connstring="$server\$Instance"
}
if($portnum -eq 1433)
{
$connstring="$server"
}
$connstring
$jobdata = Invoke-Sqlcmd -ServerInstance $connstring -Query $jobquery
#$jobdata | fl >> $loc\jobcheck.html
#"`n" >> $loc\jobcheck.html
'Instance : ' + $Instance | Out-File $loc\jobcheck.html -Append
'<html><br>------------------------<br><br></html>' | Out-File $loc\jobcheck.html -Append
$jobdata | Select-Object JobName,JobOwner,IsEnabled,JobCreatedOn,JobLastModifiedOn,OriginatingServerName,JobStartStepName,IsScheduled | ConvertTo-Html -Head $head | Out-File $loc\jobcheck.html -Append
}
}
else
{
'The server '+"$server"+' does not have any instances OR server not pingable OR cannot be accessed from this server'
}
}
}
Function Cdrivespace{
param($loc)
foreach($server in $strComputer)
{
$server+'details' >> $loc\cdrivespace.txt
'--------------------' >> $loc\cdrivespace.txt
$p = Gwmi Win32_LogicalDisk -ComputerName $server
if($p -ne $null)
{
$count = $p.count
for($i=0;$i -lt $count ;$i++ )
{
if($p[$i].DeviceID -eq "C:" )
{
$totalsize=$p[$i].Size
$C_drivefreespace = $p[$i].Freespace
$sizeinGB = $C_drivefreespace/(1024*1024*1024)
$totspace_GB = $totalsize/(1024*1024*1024)
$roundtotsize = [System.Math]::Round($totspace_GB)
$roundsizeGB = [System.Math]::Round($sizeinGB)
$percent = ($roundsizeGB/$roundtotsize)*100
$percentround = [System.Math]::Round($percent)
'Free space in drive C is '+"$roundsizeGB" +'GB whick is '+ "$percentround" + '% of the total '+"$roundtotsize"+'GB' >> $loc\cdrivespace.txt
"`n" >> $loc\cdrivespace.txt
}
else
{continue}
}
}
else
{
"$server "+'not reachable OR does not exist or network glitch' >> $loc\cdrivespace.txt
}
}
}
Function LastReboot{
param($loc)
foreach($server in $strComputer)
{
$p = Gwmi Win32_LogicalDisk -ComputerName $server
if($p -ne $null)
{
$data = Get-WmiObject -class win32_OperatingSystem -computername $server
$uptime = $data.ConvertToDateTime($data.LastBootUpTime)
"The last reboot time is $uptime"
"`n" >> $loc\lastreboot.txt
"$server" >> $loc\lastreboot.txt
"-------------------" >> $loc\lastreboot.txt
$d=Get-WmiObject Win32_LocalTime -computerName $server
$day=$d.day
$month=$d.Month
$year=$d.Year
$hour=$d.Hour
$minute=$d.Minute
$sec = $d.Second
'The last reboot time is '+"$uptime" +' while the server time is '+"$month"+'/'+"$day"+'/'+"$year"+'.......'+"$hour"+':'+"$minute"+':'+"$sec">> $loc\lastreboot.txt
"`n" >> $loc\lastreboot.txt
}
else
{
"$server "+'not reachable OR does not exist or network glitch' >> $loc\lastreboot.txt
"`n" >> $loc\lastreboot.txt
}
}
}
Function LocalAdmins{
param($loc)
foreach($server in $strComputer)
{
Vishnu
$server
$Searcher = New-Object DirectoryServices.DirectorySearcher([ADSI]"")
$Searcher.Filter = "(objectClass=computer)"
$Computers = ($Searcher.Findall())
$Searcher.FindOne()
try{
$members =[ADSI]"WinNT://$server/Administrators"
$members
}
catch
{
"$server "+'not reachable OR does not exist or network glitch' >> $loc\localadmins.txt
"`n" >> $loc\localadmins.txt
}
$members = @($members.psbase.Invoke("Members"))
$mems=$members | foreach {$_.GetType().InvokeMember("AdsPath", 'GetProperty',$null, $_, $null)}
$mems
$server +'Details' >> $loc\localadmins.txt
"-----------------------------------------------" >> $loc\localadmins.txt
"`n" >> $loc\localadmins.txt
$mems>> $loc\localadmins.txt
"`n" >> $loc\localadmins.txt
}
}
Function Sysadmins{
param($loc)
'Getting into Sysadmin'
foreach($server in $strComputer)
{
$server +'Details' >> $loc\sysadmins.csv
"-----------------------------------------------" >> $loc\sysadmins.csv
"`n" >> $loc\sysadmins.csv
$sysadminquery =
@'
SELECT p.name AS [loginname] ,
p.type ,
p.type_desc ,
p.is_disabled,
CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins
AND s.sysadmin = 1
'@
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
try{
$srvrobject = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $server
$instanceprop = $srvrobject.ServerInstances #to get instance properties
$instancecount = $instanceprop.count
}
catch
{
"$server "+'not reachable OR does not exist or network glitch' >> $loc\sysadmins.csv
"`n" >> $loc\sysadmins.csv
}
if($instancecount -ne 0)
{
for($i=0; $i -lt $instancecount ;$i++)
{
$p=$instanceprop[$i].ServerProtocols['tcp']
$ip=$p.IPAddresses['IPAll']
$portnum=$ip.IPAddressProperties['TcpPort'].Value #to get port numbers
$Instance = $instanceprop[$i].Name
$connstring="$server\$Instance,$portnum"
if($portnum -eq '')
{
$connstring="$server\$Instance"
}
if($portnum -eq 1433)
{
$connstring="$server"
}
$connstring
$sysadminoutputs=Invoke-Sqlcmd -ServerInstance $connstring -query $sysadminquery
"$server\$Instance,$portnum" +'Details' >> $loc\sysadmins.csv
"-----------------------------------------------" >> $loc\sysadmins.csv
"`n" >> $loc\sysadmins.csv
'Login Name'+','+'Type'+','+'type_desc'+','+'isdisabled'+','+'created'+','+'Update' >> $loc\sysadmins.csv
foreach($sysadminoutput in $sysadminoutputs)
{
$loginname=$sysadminoutput.loginname
$type=$sysadminoutput.type
$type_desc=$sysadminoutput.type_desc
$isdisabled=$sysadminoutput.is_disabled
$created=$sysadminoutput.created
$update=$sysadminoutput.update
$loginname+','+$type+','+$type_desc+','+$isdisabled+','+$created+','+$update >> $loc\sysadmins.csv
}
}
}
else
{
"$server\$Instance,$portnum" +'Details' >> $loc\sysadmins.csv
"-----------------------------------------------" >> $loc\sysadmins.csv
"`n" >> $loc\sysadmins.csv
"`Cannot connect" >> $loc\sysadmins.csv
}
}
}
# Create a GUI Form
$MainForm = New-Object System.Windows.Forms.Form
$MainForm.Text = “Server Remote Checker Ver 1.0 ”
$MainForm.Size = New-Object System.Drawing.Size(800,800)
$MainForm.StartPosition = “CenterScreen”
$MainForm.KeyPreview = $True
#$MainForm.Add_KeyDown({if ($_.KeyCode -eq “Enter”)
# {$x=$objTextBox.Text;$MainForm.Close()}})
$MainForm.Add_KeyDown({if ($_.KeyCode -eq “Escape”)
{$MainForm.Close()}})
# Add a drop-down Combo box
$FunctionalityComboBox = New-Object System.Windows.Forms.Combobox
$FunctionalityComboBox.Location = New-Object System.Drawing.Size(250,250)
$FunctionalityComboBox.Size = New-Object System.Drawing.Size(280,20)
$FunctionalityComboBox.Text = “Please select”
# Add items to Combo box
$commands = @("SQL server Running Status","D drive Free space","C drive Free space","Portnumber of instances in this server","Check jobs in this server","Check if the server is pingable","Last Reboot Time of the server(s)","Local Admins of server(s)","SysAdmins of SQL server in server(s)")
ForEach ($command in $commands){
[void]$FunctionalityComboBox.items.add($command)
}
# Add combo box to Userform
$MainForm.Controls.Add($FunctionalityComboBox)
# Set to the first item
$FunctionalityComboBox.SelectedIndex = 0;
# Set selection to ComboBox selected text.
$selection = $FunctionalityComboBox.Text;
$selection
# Add a text box to the Userform
$Locdetails = New-Object System.Windows.Forms.Label
$Locdetails.Location = New-Object System.Drawing.Size(100,595)
$Locdetails.Size = New-Object System.Drawing.Size(400,30)
$Locdetails.Text='Create a folder structure here for storing the output files. Edit the text box if you need to specify a new location'
$MainForm.Controls.Add($Locdetails)
# Add a text box to the Location
$LocTB = New-Object System.Windows.Forms.TextBox
$LocTB.Location = New-Object System.Drawing.Size(500,600)
$LocTB.Size = New-Object System.Drawing.Size(200,20)
$LocTB.Text='C:\PS'
$MainForm.Controls.Add($LocTB)
# Add a text box to the Location
$LocSaveButton = New-Object System.Windows.Forms.Button
$LocSaveButton.Location = New-Object System.Drawing.Size(720,600)
$LocSaveButton.Size = New-Object System.Drawing.Size(50,20)
$LocSaveButton.Text='Save'
#$MainForm.Controls.Add($LocSaveButton)
# Add a text box to the Userform
$SMTPdetails = New-Object System.Windows.Forms.Label
$SMTPdetails.Location = New-Object System.Drawing.Size(100,695)
$SMTPdetails.Size = New-Object System.Drawing.Size(400,30)
$SMTPdetails.Text='Specify the smtp server here(in case you need to send mails).'
#$MainForm.Controls.Add($SMTPdetails)
# Add a text box to the Location
$SMTPTB = New-Object System.Windows.Forms.Button
$SMTPTB.Location = New-Object System.Drawing.Size(250,455)
$SMTPTB.Size = New-Object System.Drawing.Size(275,30)
$SMTPTB.Text='Configure'
$MainForm.Controls.Add($SMTPTB)
# Add a text box to the Location
$smtpSaveButton = New-Object System.Windows.Forms.Button
$smtpSaveButton.Location = New-Object System.Drawing.Size(720,700)
$smtpSaveButton.Size = New-Object System.Drawing.Size(50,20)
$smtpSaveButton.Text='Save'
#$MainForm.Controls.Add($smtpSaveButton)
$SMTPTB.Add_Click({
$LocfromTB=$LocTB.Text
if((Test-Path $LocTB.Text))
{
mailform $LocfromTB
}
else
{
$a.popup("Cannot access the location specified : $LocfromTB. Please enter a valid location ",1,'Location Error')
}
})
function createxml()
{
param($smtp,$loc)
@'
'@
}
<#
# Add a text box to the Userform
$LocButton = New-Object System.Windows.Forms.Button
$LocButton.Location = New-Object System.Drawing.Size(250,695)
$LocButton.Size = New-Object System.Drawing.Size(150,30)
$LocButton.Text='Save log location'
$MainForm.Controls.Add($LocButton)
#>
# Add a text box to the Userform
$objTextBox = New-Object System.Windows.Forms.TextBox
$objTextBox.Location = New-Object System.Drawing.Size(250,200)
$objTextBox.Size = New-Object System.Drawing.Size(280,20)
$MainForm.Controls.Add($objTextBox)
<#
# Add a text box to the email
$objTextEmail = New-Object System.Windows.Forms.TextBox
$objTextEmail.Location = New-Object System.Drawing.Size(250,450)
$objTextEmail.Size = New-Object System.Drawing.Size(280,20)
$MainForm.Controls.Add($objTextEmail)
#>
# Add a Browse for servers label
$browse = new-object windows.Forms.OpenFileDialog
#$browse.text = "..."
$browsebutton = New-Object system.Windows.Forms.Button
$browsebutton.Text = "Select Servers from List"
$browsebutton.Size = New-Object System.Drawing.Size(200,23)
$browsebutton.left = 550
$browsebutton.top = 200
$MainForm.controls.add($browsebutton)
$browsebutton.Add_Click({
if ($browse.ShowDialog() -eq "OK")
{
$servers=Get-Content $browse.FileName
$objTextBox.Text= $servers -replace(" ",",")
$objTextBox.Text=$objTextBox.Text -replace(" ",",")
}
})
# Button for Backup Status and name it as “Submit”
$BackupStatusButton = New-Object System.Windows.Forms.Button
$BackupStatusButton.Location = New-Object System.Drawing.Size(600,500)
$BackupStatusButton.Size = New-Object System.Drawing.Size(150,30)
$BackupStatusButton.Text = “Go To Backup Check Tool”
#$MainForm.Controls.Add($BackupStatusButton)
# Add an OK button and name it as “Submit”
$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(250,500)
$OKButton.Size = New-Object System.Drawing.Size(75,25)
$OKButton.Text = “Submit”
$MainForm.Controls.Add($OKButton)
# Add a Cancel button
$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(350,500)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = “Cancel”
$CancelButton.Add_Click({$MainForm.Close()})
$MainForm.Controls.Add($CancelButton)
# Add an Exit button
$ExitButton = New-Object System.Windows.Forms.Button
$ExitButton.Location = New-Object System.Drawing.Size(450,500)
$ExitButton.Size = New-Object System.Drawing.Size(75,23)
$ExitButton.Text = “Exit”
$ExitButton.Add_Click({$MainForm.Close()})
$MainForm.Controls.Add($ExitButton)
# Add a Text label
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(250,160)
$objLabel.Size = New-Object System.Drawing.Size(310,50)
$objLabel.Text = “Please enter Server Names Seperated by commas”
$MainForm.Controls.Add($objLabel)
# Add a Text label
$objLabel = New-Object System.Windows.Forms.Label
$Font = New-Object System.Drawing.Font("Times New Roman",18,[System.Drawing.FontStyle]::Bold)
# Font styles are: Regular, Bold, Italic, Underline, Strikeout
$objLabel.Font = $Font
$objLabel.Location = New-Object System.Drawing.Size(250,100)
$objLabel.Size = New-Object System.Drawing.Size(350,30)
$objLabel.$Font
$objLabel.Text = “Server Status Checker”
$MainForm.Controls.Add($objLabel)
# Add a Text label for email
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(250,400)
$objLabel.Size = New-Object System.Drawing.Size(290,50)
$objLabel.Text = “Configure your email settings below if you want to receive emails with the execution output.”
$MainForm.Controls.Add($objLabel)
# Set form on top
#$MainForm.Topmost = $True
# Show fim
$MainForm.Add_Shown({$MainForm.Activate()})
#[void] $MainForm.ShowDialog()
$OKButton.Add_Click({
if($objTextBox.Text)
{
$loc=$LocTB.Text
if(Test-path $loc)
{
#$smtpserver=$SMTPTB.Text
##CLearing the historic notepad files
Clear-Content $loc\check_ping.txt
Clear-Content $loc\inst.txt
Clear-Content $loc\ddrivespace.txt
Clear-Content $loc\portnumber.txt
Clear-Content $loc\jobcheck.html
Clear-Content $loc\lastreboot.txt
Clear-Content $loc\cdrivespace.txt
Clear-Content $loc\localadmins.txt
Clear-Content $loc\jobcheck.html
Clear-Content $loc\sysadmins.csv
#$emailto = $objTextEmail.Text
$x = $objTextbox.Text
$vals = $x.split(“,”)
foreach ($strComputer in $vals){
switch ($FunctionalityComboBox.SelectedItem)
{
"SQL server Running Status"{SQLInstancerunstatus $loc}
"Check Server connectivity"{CheckServerPing $loc}
"D drive Free space" {Ddrivespace $loc}
"C drive Free space"{Cdrivespace $loc}
"Portnumber of instances in this server" {portnumber $loc}
"Check jobs in this server" {jobstatus $loc}
"Check if the server is pingable" {CheckServerPing $loc}
"Last Reboot Time of the server(s)" {LastReboot $loc}
"Local Admins of server(s)" {LocalAdmins $loc}
"SysAdmins of SQL server in server(s)" {Sysadmins $loc}
}
}
if($FunctionalityComboBox.SelectedItem -eq "SQL server Running Status" )
{
$loc
"`n" >> $loc\inst.txt
'PS : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\inst.txt
"`n" >> $loc\inst.txt
if($email -ne "")
{
#Send-MailMessage -From "INstanceRUNStatus@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "Instance Running Status" -Attachments "$loc\inst.txt" -body "PLease find attahced the SQL Server running status " -Priority "High"
sendmail -Attachments "$loc\inst.txt"
}
else{'No email Id given . So we are not sending the mail to any mailbox' >> $loc\inst.txt }
$instancerun=Get-Content $loc\inst.txt
$instancerun | Out-GridView
}
if($FunctionalityComboBox.SelectedItem -eq "Portnumber of instances in this server")
{
"`n" >> $loc\portnumber.txt
'P S : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\portnumber.txt
"`n" >> $loc\portnumber.txt
# $port=Get-Content $loc\portnumber.txt
# $port | Out-GridView
Invoke-Item $loc\portnumber.txt
#Send-MailMessage -From "CheckPortNum@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "Port Number Checking" -Attachments "$loc\portnumber.txt" -body "Please find attached the port number of instances of the requested servers " -Priority "High"
sendmail -Attachments "$loc\portnumber.txt"
}
if($FunctionalityComboBox.SelectedItem -eq "Check jobs in this server" )
{
"`n" >> $loc\jobcheck.html
'P S : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\jobcheck.html
"`n" >> $loc\jobcheck.html
# $job=Get-Content $loc\jobcheck.html
#$job | Out-GridView
$body= Get-Content $loc\jobcheck.html
Invoke-Item $loc\jobcheck.html
#Send-MailMessage -From "JobStatusCheck@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "Job Satus Statistics" -Attachments "$loc\jobcheck.html" -body "$body" -Priority "High" -BodyAsHtml
sendmail -Attachments "$loc\jobcheck.html"
}
if($FunctionalityComboBox.SelectedItem -eq "D drive Free space")
{
"`n" >> $loc\ddrivespace.txt
'PS : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\ddrivespace.txt
"`n" >> $loc\ddrivespace.txt
# $dspace=Get-Content $loc\ddrivespace.txt
# $dspace | Out-GridView
INvoke-Item $loc\ddrivespace.txt
#Send-MailMessage -From "CheckDdrive@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "D Drive Statistics" -Attachments "$loc\ddrivespace.txt" -body "Please find attached the server D drive status " -Priority "High"
sendmail -Attachments "$loc\ddrivespace.txt"
}
if($FunctionalityComboBox.SelectedItem -eq "C drive Free space")
{
"`n" >> $loc\cdrivespace.txt
'P S : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\cdrivespace.txt
"`n" >> $loc\cdrivespace.txt
# $cspace=Get-Content $loc\cdrivespace.txt
# $cspace | Out-GridView
Invoke-Item $loc\cdrivespace.txt
#Send-MailMessage -From "CheckCdrive@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "C Drive Statistics" -Attachments "$loc\cdrivespace.txt" -body "Please find attached the server C drive status " -Priority "High"
sendmail -Attachments "$loc\cdrivespace.txt"
}
if($FunctionalityComboBox.SelectedItem -eq "Check if the server is pingable")
{
"`n" >> $loc\check_ping.txt
'PS : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\check_ping.txt
"`n" >> $loc\check_ping.txt
$serverping= "$loc\check_ping.txt"
Invoke-Item $serverping
#Send-MailMessage -From "PingStatus@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "Ping status of server(s)" -Attachments "$loc\check_ping.txt" -body "Please find attached the server pingable status " -Priority "High"
sendmail -Attachments "$loc\check_ping.txt"
}
if($FunctionalityComboBox.SelectedItem -eq "Last Reboot Time of the server(s)")
{
"`n" >> $loc\lastreboot.txt
'P S : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\lastreboot.txt
"`n" >> $loc\lastreboot.txt
# $rebootcheck= Get-Content $loc\lastreboot.txt
# invoke-item $rebootcheck
Invoke-Item $loc\lastreboot.txt
#Send-MailMessage -From "CheckPortNum@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "Last Reboot Time Checking" -Attachments "$loc\lastreboot.txt" -body "Please find attached the reboot time of the requested servers " -Priority "High"
sendmail -Attachments "$loc\lastreboot.txt"
}
#$MainForm.close()
#Local Admins
if($FunctionalityComboBox.SelectedItem -eq "Local Admins of server(s)")
{
"`n" >> $loc\localadmins.txt
'PS : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\localadmins.txt
"`n" >> $loc\localadmins.txt
# $dspace=Get-Content $loc\ddrivespace.txt
# $dspace | Out-GridView
INvoke-Item $loc\localadmins.txt
#Send-MailMessage -From "localadmins@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "localadmins" -Attachments "$loc\localadmins.txt" -body "Please find attached the server localadmins status " -Priority "High"
sendmail -Attachments "$loc\localadmins.txt"
}
#Sys admins Admins
if($FunctionalityComboBox.SelectedItem -eq "SysAdmins of SQL server in server(s)")
{
"`n" >> $loc\sysadmins.csv
'PS : The above script ran by '+ " $user " +'at server time'+" $timenow " >> $loc\sysadmins.csv
"`n" >> $loc\sysadmins.csv
# $dspace=Get-Content $loc\ddrivespace.txt
# $dspace | Out-GridView
INvoke-Item $loc\sysadmins.csv
#Send-MailMessage -From "sysadmins@gmail.com" -To $emailto -UseSSL -SmtpServer $smtpserver -Subject "sysadmins to SQL Server" -Attachments "$loc\sysadmins.csv" -body "Please find attached the server sysadmins status " -Priority "High"
sendmail -Attachments "$loc\sysadmins.csv"
}
}
else
{
$a.popup("Location specified not valid : $loc . Please recheck the location.",1,'Error')
}
}
else
{
$a.popup("Please enter some value in the server field.",1,'Error')
}
})
# Add a text box to the Location
$MyLabel = New-Object System.Windows.Forms.LinkLabel
$MyLabel.Location = New-Object System.Drawing.Size(50,700)
$MyLabel.Size = New-Object System.Drawing.Size(200,20)
$MyLabel.Text='@Powershell4u.com'
$MyLabel.add_Click({[system.Diagnostics.Process]::start("www.powershell4u.com")})
$MainForm.Controls.Add($MyLabel)
$MainForm.ShowDialog()
Comments