top of page
Search

Scripts


$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()



4 views0 comments

Kommentare


bottom of page