Link to home
Start Free TrialLog in
Avatar of Jerry Seinfield
Jerry SeinfieldFlag for United States of America

asked on

Help to build a PowerShell Script

Hello Experts,

I would like to include all power shell cmdlets below into a single master script, and run from any server, therefore, I have to include the power shell AD and Exchange modules plus all the commands below. I would like to save all the results[txt files] to a shared folder, and displayed the information for each test on a HTML page, as well to send an email to a specific email mailbox

1. Get-DatabaseAvailabilityGroup -Status | fl >c:\dag.txt
2. Get-DatabaseAvailabilityGroupNetwork | fl >c:\dagNetwork.txt
3. Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | fl > c:\MdbCopy.txt
4. Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus >c:\mdbcopy2.txt
5. Get-MailboxDatabase -Status | fl >c:\mdb.txt
6. Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | fl >c:\rphealth.txt
7. Get-ExchangeServer -Status | fl >c:\srv.txt
8. Get-MailboxServer -Status | fl >c:\mbsrv.txt
9. Get-DomainController >c:\dc.txt


Any ideas?
Avatar of Dale Harris
Dale Harris
Flag of United States of America image

I think one of the core ingredients you're missing is to store the output into variables first.  Then you can work with that data appropriately to build an HTML output page, do your separate outputs to your log files, and then email the results.

$OverallVariable = @()
$Variable = <command>
$OverallVariable+= $Variable

$HTML = "<HTML>`n `
<HEAD>`n `
<TITLE>Test Title</TITLE>`n `
</HEAD>`n `
<BODY>`n `
This is my first HTML Variable output<br>`n `
$DatabaseAvailabilityGroupOutput `n `
<p>`n `
This is my second variable output<br> `n `
$DatabaseAvailabilityGroupNetworkOutput `n `
</BODY>`n `
</HTML>"
$HTML > "OutputHTMLFile.htm"

Things to note: `n = new line which will make your HTML file easier to read when it's output into the htm file, but as you know, spacing doesn't matter for HTML.  Also ` at the end of each line (backtick is the name of the symbol, next to the 1 key) signifies to Powershell that you're doing a continuation of the line, so essentially it's all one big line.  This is done to allow you to easily edit your powershell script output so you're not having to deal with a huge long line of code.

Hope that gets you started in the right direction, but like I mentioned, if you can get one to do all of those things (googling each separate thing you want to do like send an email is very doable), you can get them all.  It will turn into a pretty lengthy script.  I made a script in the past where I was doing the exact same thing, and it would have 5 or 6 functions, all based on health and availability, and then I would use that to test different left and right limits of acceptable operation and have it tell me when it needed to be looked at.  So I would get an email with "Green" or "Red" status of each service I was testing.  Getting the output into emails is one thing, but testing the output for what signifies work to be done on your part as the administrator is the thing that Powershell could really help with.  For example, if the backup wasn't done daily, our logs would be >24 hours old, I could test for that and it would send me an email saying to backup the mailstore since it wasn't done.  Also, I would go through each server and get the hard drive space percentages.  Based on that data, I wanted an email that rolled up the servers that had less than 30% space remaining, I didn't want to see which servers were above that level.

-Dale Harris
Avatar of Jerry Seinfield

ASKER

Hi Dale,

What if we remove the HTML request from the equation and just build the script with the commands above, and save all results onto a shared folder, and in one line send an email to a mailbox with all results above?

if so, how would the script looks?
Here's an example of your first command, just rinse and repeat for each:

$FilePath = "\\10.10.10.10\sharedfolder"
$FileName = "LogFile.txt"
$Body = @()
$GDAG = Get-DatabaseAvailabilityGroup -Status | fl
$GDAG >> "$FilePath\$FileName"

$Body += $GDAG
send-mailmessage -to "User01 <user01@example.com>" -from "User02 <user02@example.com>" -subject "Test mail" -body $Body -smtpserver "Exchange.contoso.com"
Should I repeat all code above for each PowerShell cmdlets? Basically what I need is

1. Run all powershell cmdlets above, save all results[each txt file] onto a shared folder
2. Send a single email with all the results[files above] to a specific email address
Avatar of Qlemo
That depends on whether you want to have individual emails for each report. If not, I recommend to convert the output of each command to a HTML Table (fragment), concat those together,  put some HTML code around and in between. The result can be used in a HTML page and as email.

Whatever you do, I recommend to write a function doing the work, so you can use something like:
Get-DatabaseAvailabilityGroup -Status | Set-Report '\\server\share\dag.txt'

Open in new window

if sending one mail per result, or
$body = Get-DatabaseAvailabilityGroup -Status | Set-Report '\\server\share\dag.txt'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report '\\server\share\dagNetwork.txt'
# ...
Send-MailMessage <# etc #>

Open in new window

with your self-created function for the latter
function Set-Report (
  [parameter (ValueFormPipeline=$true)} $obj,
  [parameter (Position=0)] $file
)
{
  $obj |  fl * > $file
  $obj | convertToHtml -Fragment
}

Open in new window

The Set-Report can do additional formatting, sending the mail, or whatever you like.
Thanks Qlemo,

Can you please combine all code above, and send me the final script?
Ok. I'll use  the "single HTML mail" approach. You can add more HTML formatting if needed, and change the titles to something more appealing.
function Set-Report (
  [parameter (ValueFormPipeline=$true)} $obj,
  [parameter (Position=0)] $file
  [parameter (Position=1)] $title
)
{
  $obj |  fl * > $file
  "<h2>$title</h2>"
  $obj | convertToHtml -Fragment -Title $title
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

you rock

Thanks

I have one more question, and please let me know if another ticket is requested as the original ticket is resolved.

I am thinking of creating an automatic task on my domain controller or create a runbook on the orchestrator server to call the script above, but if I want to avoid duplication of the files obtained above,


Any chance to get those files with a format yyyy/dd/mm.txt? where yyyy/dd/mm is the actual date when the script is executed

For example

Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag20142301.txt

Please advice
The best, but more complex, approach is to add the date in Set-Report itself.
The simple one is to add it to the call of Set-Report, as in your example above.
I'll show the complex one.
function Set-Report (
  [parameter (ValueFormPipeline=$true)} $obj,
  [parameter (Position=0)] $file
  [parameter (Position=1)] $title
)
{
  $file = (split-path -Paren
  $obj |  fl * > ($file -replace '\.', ('.'+(get-date -format 'yyyyMMdd')+'.'))
  "<h2>$title</h2>"
  $obj | convertToHtml -Fragment -Title $title
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

Note that the files will be   xyz.yyyymmdd.txt  - you should always use a sortable date format, and yyyyddmm isn't, as "20142302" is "earlier" than "20142401", which certainly is not intented.
Thanks QLemo

As this script will run in a member server

Should we import the modules for AD and Exchange?  Can you please add the lines before the function?
This should be all you need. Put it to the very top of the script.
add-pssnapin Microsoft.Exchange.Management.PowerShell.Admin
import-module ActiveDirectory

Open in new window

That script looks great, Qlemo.  I just noticed a slight hiccup on your 2nd line:
[parameter (ValueFormPipeline=$true)} $obj,

I think Value*From*PipeLine will need to be used.
That's absolutely correct. And there was a wrong line 7. So:
add-pssnapin Microsoft.Exchange.Management.PowerShell.Admin
import-module ActiveDirectory

function Set-Report (
  [parameter (ValueFromPipeline=$true)} $obj,
  [parameter (Position=0)] $file
  [parameter (Position=1)] $title
)
{
  $obj |  fl * > ($file -replace '\.', ('.'+(get-date -format 'yyyyMMdd')+'.'))
  "<h2>$title</h2>"
  $obj | convertToHtml -Fragment -Title $title
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

Thanks You Folks for the help
Folks, I know that i just close this ticket, but I was unable to run the powershell script above either by running the script or manually from powershell console and /or Exchange management shell

Please see attached screen shoots

Any ideas?

I tried to run from a domain controller, from an exchange servers, still no luck
ErrorUnabletoSetReport.png
Still some typos - but you should have seen the important error messages at the top.
add-pssnapin Microsoft.Exchange.Management.PowerShell.Admin
import-module ActiveDirectory

function Set-Report (
  [parameter (ValueFromPipeline=$true)] $obj,
  [parameter (Position=0)] $file,
  [parameter (Position=1)] $title
)
{
  $obj |  fl * > ($file -replace '\.', ('.'+(get-date -format 'yyyyMMdd')+'.'))
  "<h2>$title</h2>"
  $obj | convertToHtml -Fragment -Title $title
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

Hi Qlemo,

still no luck, please see errors from screen shoots
ErrorFromMSExchangeServer.jpg
ErrorFromPowershellinDomainContr.jpg
My keyboard seems to suck up keys. convertToHtml is missing a dash, and should sound convertTo-Html. And there should be no space after Parameter ...
I don't know what to do with the second screenshot - it tells the Exchange cmdlets were not found, though they are in the first one.
add-pssnapin Microsoft.Exchange.Management.PowerShell.Admin
import-module ActiveDirectory

function Set-Report (
  [parameter(ValueFromPipeline=$true)] $obj,
  [parameter(Position=0)] $file,
  [parameter(Position=1)] $title
)
{
  $obj |  fl * > ($file -replace '\.', ('.'+(get-date -format 'yyyyMMdd')+'.'))
  "<h2>$title</h2>"
  $obj | convertTo-Html -Fragment -Title $title
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

still no luck, I ran manually the script from windows power shell console on both domain controller and exchange mailbox server with no luck, please see new attached files[error 4 and 5]

Even if I run the script from exchange management shell, got same errors

Just to clarify, our DCs are Wndows 2008 R2, same OS for exchange 2010 SP3 servers
Error4.jpg
Error5.jpg
Firstly, you won't just copy & paste the content in a shell window. It is much better to store the code in a .ps1 file, and in PowerShell just call the script (by using the full path name to the script).

Pasting directly into PS console only makes sense if you do it line after line, and only if there is no error.

It is impossible that the Exchange Management Shell behaves exactly the same. The Exchange snap-in is loaded, the environment prepared for Exchange, including some convinience commands. The only issue which should come up is an error message stating the Exchange snap-in is already loaded.
You can always check for registered snap-ins with Get-PsSnapin -Registered. It should show the Exchange one.
Hi Qlemo

I did save the script and run as ps1, still no luck. same errors

Any ideas?

why most cmdlet are not recognized as cmdlet by the powershell console?
Please type Get-PsSnapin -Registered in a PS console, and see if you can spot Exchange. I don't get what happens here.
Hi Qlemo,

I ran get-psnapin-registered in a ps console from a domain controller and from an exchange servers. please see the attached docs
User generated imageUser generated image
Any ideas?
You have the Quest ActiveRole (AD) Cmdlets on the DC only, and the Exchange ones only on Exchange server. As-is, you need to run the commands on the Exchange server, and replace my first two code lines with
Add-PsSnapin Microsoft.Exchange.Management.Powershell.E2010

Open in new window

That should do.
Here are the results

All the txt files were created now, but the html page was not created. The email was sent fine, but see the format below and notice any of the items contain anyUser generated image values, please see also attached screen shoot with the errors displayed

DAG
DAGNetwork
MdbCopy
MdbCopy2
MDB
Replication Health
MSX Status
MB Server Status
DC
I don't understand what you try to say here:
The email was sent fine, but see the format below and notice any of the items contain any values, please see also attached screen shoot with the errors displayed
The HTML part indeed contains a typo, I forgot to remove the title option, which does not work with -fragment:
Add-PsSnapin Microsoft.Exchange.Management.Powershell.E2010

function Set-Report (
  [parameter(ValueFromPipeline=$true)] $obj,
  [parameter(Position=0)] $file,
  [parameter(Position=1)] $title
)
{
  $obj |  fl * > ($file -replace '\.', ('.'+(get-date -format 'yyyyMMdd')+'.'))
  "<h2>$title</h2>"
  $obj | convertTo-Html -Fragment
}

$body = Get-DatabaseAvailabilityGroup -Status | Set-Report c:\dag.txt 'DAG'
$body+= Get-DatabaseAvailabilityGroupNetwork | Set-Report c:\dagNetwork.txt 'DAGNetwork'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\MdbCopy.txt 'MdbCopy'
$body+= Get-MailboxDatabase| Get-MailboxDatabaseCopyStatus | Set-Report c:\mdbcopy2.txt 'MdbCopy2'
$body+= Get-MailboxDatabase -Status | Set-Report c:\mdb.txt 'MDB'
$body+= Get-Mailboxserver | where {$_.DatabaseAvailabilityGroup -ne $null} | Test-ReplicationHealth | Set-Report c:\rphealth.txt 'Replication Health'
$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'
$body+= Get-MailboxServer -Status | Set-Report c:\mbsrv.txt 'MB Server Status'
$body+= Get-DomainController | Set-Report c:\dc.txt 'DC'

Send-MailMessage -Body $body -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

Thanks qlemo

The issue of cannot convert to html is gone, but a new issue resurfaces

please see attached screen shoot. The email also was not sent this time, and it used to work before i guess is because the new issue

Can you please also clarify what type of file is .txtm?

The script is generating a file name srv.20140131.txtm. see line code below

$body+= Get-ExchangeServer -Status | Set-Report c:\srv.txtm 'MSX Status'

User generated image
Yes, that is a consequence of the convert to HTML working now :/. Stupid me, of course you need to join the multiple strings (one string per line) to a single string before sending as mail, that is a requirement of Send-MailMessage which I tend to forget each time. Last line should be
Send-MailMessage -Body ($body -join "`n") -BodyAsHtml -SmtpServer mx.domain.com -From admin@domain.com -To admin@domain.com -Subject 'Exchange Stats Report'

Open in new window

Hi Qlemo,

The Script worked like a rock, however the format of the html page displayed on the email is wrong. I have attached a MSG file with the information displayed on the email, you can open the file with Microsoft outlook and you will see

Any ideas on why the information is being displayed on that format?
Exchange-Stats-Report.msg
There are several issues. Too many properties to nicely show on a table, properties not expanding (so you don't see the content, but some type info), and such.

Creating a proper HTML report for each object is more complex than you should think. There is no direct way to do a format-list into HTML, sadly.
Thanks Qlemo,

Is there an option to export all the txt objects into a CSV file, and use format-list? I mean, just wonder if we could export all reports [txt files] onto a single master csv report
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Qlemo, the latest code basically is what i was looking for. Many thanks for your patience and help
Excellent help