Powershell script help sending the results in email body

• samitha01@gmail.com

  SSC Enthusiast

  Points: 172

  More actions

  July 16, 2021 at 4:25 pm

  #3910721

  Hi,

  i found some script to list out the primary ag groups in servers in powerhsell.

  but the results not coming in attachment.

  i need results through email email body.

  can any one help  please find below script

  ## Setup dataset to hold results

  $dataset = New-Object System.Data.DataSet

  ## populate variable with collection of SQL instances

  $serverlist='server1','','server2','server13','server14','server15'

  ## Setup connection to SQL server inside loop and run T-SQL against instance

  foreach($Server in $serverlist) {

  $connectionString = "Provider=sqloledb;Data Source=$Server;Initial Catalog=Master;Integrated Security=SSPI;"

  ## place the T-SQL in variable to be executed by OLEDB method

  $sqlcommand="

  IF SERVERPROPERTY ('IsHadrEnabled') = 1

  BEGIN

  SELECT

  AGC.name

  , RCS.replica_server_name

  , ARS.role_desc

  , AGL.dns_name

  FROM

  sys.availability_groups_cluster AS AGC

  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS

  ON

  RCS.group_id = AGC.group_id

  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS

  ON

  ARS.replica_id = RCS.replica_id

  INNER JOIN sys.availability_group_listeners AS AGL

  ON

  AGL.group_id = ARS.group_id

  WHERE

  ARS.role_desc = 'PRIMARY'

  END

  "

  ## Connect to the data source and open it

  $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString

  $command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection

  $connection.Open()

  ## Execute T-SQL command in variable, fetch the results, and close the connection

  $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command

  #$dataset = New-Object System.Data.DataSet

  [void] $adapter.Fill($dataSet)

  $connection.Close()

  }

  ## Return all of the rows from dataset object

  $dataSet.Tables | FT -AutoSize

  $from = New-Object System.Net.Mail.MailAddress "ppp@ppp.com"

  $to = New-Object System.Net.Mail.MailAddress "ppp@ppp.com"

  # Create Message

  $message = new-object System.Net.Mail.MailMessage $from, $to

  $message.Subject = "Availability Group Primary List"

  $message.Body = "Availability Group Primary List for our SQL Servers"

  $Attachment = New-Object Net.Mail.Attachment('c:\temp\SQLAvailGroup.txt', 'text/plain')

  $message.Attachments.Add($Attachment)

  # Set SMTP Server and create SMTP Client

  $server = "smtp.mydomain.com"

  $client = new-object system.net.mail.smtpclient $server

  # Send the message

  "Sending an e-mail message to {0} by using SMTP host {1} port {2}." -f $to.ToString(), $client.Host, $client.Port

  try {

  $client.Send($message)

  "Message to: {0}, from: {1} has beens successfully sent" -f $from, $to

  }

  catch {

  "Exception caught in CreateTestMessage: {0}" -f $Error.ToString()

  }

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  July 17, 2021 at 5:10 pm

  #3911243

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.