Intro
Recently I had a need to re-write one of my old scripts. It was a simple case: once a month send an email with the number of received and sent messages from the previous month for a shared mailbox. The original one used Exchange on-prem. The new one connects to Exchange Online. What seemed to be very simple, occurred a little complicated. Not too much but enough to force to think about it. Because I recently decided to create low level script documentation in Azure DevOps Wiki and keep my markdown files together with code files I started by creating a Mermaid diagram. If you don’t know Mermaid, keep reading, I’ll explain. By doing it I realized that it’s a great possibility to dry test the flow of my script and in the end, increase its process-wise maturity.
The case
When I asked Bing Chat to create the script for me (for simplicity I skipped sending notifications) it produced the following output:
# Define the mailbox and date range
$Mailbox = "user@example.com"
$StartDate = (Get-Date).AddMonths(-1)
$EndDate = Get-Date
# Get the number of sent messages
$SentMessages = Get-MessageTrace -SenderAddress $Mailbox -StartDate $StartDate -EndDate $EndDate -PageSize 5000 | Where-Object { $_.Status -eq "Delivered" } | Measure-Object | Select-Object -ExpandProperty Count
# Get the number of received messages
$ReceivedMessages = Get-MessageTrace -RecipientAddress $Mailbox -StartDate $StartDate -EndDate $EndDate -PageSize 5000 | Where-Object { $_.Status -eq "Delivered" } | Measure-Object | Select-Object -ExpandProperty CountWhat’s wrong with it? The Get-MessageTrace cmdlet allows to check messages only from the past 10 days. See this Microsoft article: link. Their suggestion is to use Start-HistoricalSearch and Get-HistoricalSearch cmdlets instead. It’s fine for attended scripts but not really the best approach for an unattended one. Those two cmdlets are asynchronous. You first need to start the search. Then after 20 – 60 minutes or even more, the report will be available. You can download it as a csv file and then parse in your script. I didn’t like it so I started to think of a different approach. Why not execute the script every few days (less than 10), collect small pieces of information with Get-MessageTrace, and send a summary once a month? That’s where it starts to be a little fragile for mistakes in the process.
Mermaid
Before continuing I will write a few words about Mermaid. You can skip the next chapter if you know it already.
Simply saying Mermaid is a “JavaScript based diagramming and charting tool that renders Markdown-inspired text definitions to create and modify diagrams dynamically” (link). It allows to create diagrams as a code and renders them in an environment supporting JavaScript. This is for example Azure DevOps Wiki or GitHub (link). This is also VisualStudio Code, my IDE of choice for PowerShell. Mermaid support is built into the Markdown rendering engine. To set it up in VS Code just install the “Markdown Preview Mermaid Support” extension:
I’ll not explain here all the details of how to use Mermaid diagrams because this is not the purpose of this article. If you need to learn more about it, check the official site: link.
Create the flow
I knew I needed to run the script once per few days, store the result in a storage place (I chose json file), update the file, and once a month send a notification. I also wanted to make the script robust, so it doesn’t matter if it’s running every day or once per few days. Here the question came: how to define the process to make it efficient and robust? Let’s try to create a general flow. To use a Mermaid diagram in a markdown file it’s enough to put its definition inside :::mermaid & :::. The first line defines the type of diagram (flowchart) and direction of nodes (TD – top down). So, let’s start from:
:::mermaid
flowchart TD
ST(Start)
RE[Read data from Exchange]
GE[Get number of sent and received emalis]
UP[Update and save json file]
IS{Is it a new month?}
SE[Send notification]
EN(End)
ST --> RE --> GE --> UP --> IS --> |yes|SE --> EN
IS --> |no|EN
:::
Looks good for begging. Without writing any line of code, it’s quite easy to spot some issues. First, what will be the start date for the Exchange search? We need to collect it from the json file. Another one, what happens if a month changes? When to clear the json file? And one more, how to ensure that a result from one month is sent if the script is executed for example on 31-sep and then on 5-oct? Just going through the process in a diagram and asking different questions it’s easy to spot those possible issues. And that’s all before writing any line of code. Let’s try to correct it:
:::mermaid
flowchart TD
ST(Start)
RF[Read data from json file]
IS{Is it a new month?}
GE["Get number of sent and received emails
from last date to current date"]
AD["Add received numbers to those stored
in json file"]
UP[Update and save json file]
SE[Send notification]
CL["Clear numbers for json file and
last first date of current month"]
GEM["Get number of sent an received emails
from last date to the end of previous month"]
EN(End)
ST --> RF --> IS --> |no|GE --> AD --> UP --> EN
IS --> |yes|GEM --> SE --> CL --> UP
:::
Advantages
I simplified the creation of the best flow to 2 steps to make it easier. In the real world, I did it in a few.
- I had a mature flow before starting to write the code. It took some time but I would spend more time on rewriting my code in case I would go for designing and writing at the same time.
- I didn’t lose the general perspective while keeping track of all the detailed steps.
- I had a ready diagram in my markdown file used for documentation. If I, or anyone else, need to update the script in a few years, this general level diagram will be really helpful.
To sum up, I achieved few things:
The script
Now it’s time for the script itself. Having the flow diagram it’s only a matter of finding good cmdlets and that’s it. No need to think much in this phase.
$credentialFile = "c:\temp\credentials.txt" # file with encoded password
$emailAddress = "abcd@abc.com" # email address to be trakced
$dataFile = "data.json" # a local storage place for the script
$global:globReceived
$global:globSent
function Get-EmailNumbers {
param (
$startDate,
$endDate
)
$cred = [System.Management.Automation.PSCredential]::New($user, (ConvertTo-SecureString(Get-Content $credentialFile)))
Connect-ExchangeOnline -ShowBanner:$false -Credential $cred
$global:globReceived = 0
$global:globSent = 0
Get-MessageTrace -RecipientAddress $emailAddress -StartDate $startDate.ToShortDateString() -endDate $endDate.ToShortDateString() | ForEach-Object {
If ($_.Status -eq "Delivered") {
$global:globReceived ++
}
}
Get-MessageTrace -SenderAddress $emailAddress -StartDate $startDate.ToShortDateString() -endDate $endDate.ToShortDateString() | ForEach-Object {
If ($_.Status -eq "Delivered") {
$global:globSent ++
}
}
Disconnect-ExchangeOnline -Confirm:$false
}
$data = [PSCustomObject]@{
LastExecutionDate = Get-Date
IncrementNumberSent = 0
IncrementNumberReceived = 0
}
$data = Get-Content $dataFile | ConvertFrom-Json
$today = Get-Date
if ($data.LastExecutionDate.Month -eq $today.Month) {
Get-EmailNumbers -startDate $data.LastExecutionDate -endDate $today
$data.IncrementNumberReceived += $global:globReceived
$data.IncrementNumberSent += $global:globSent
$data.LastExecutionDate = $today
}
else{
$firstDayOfCurrentMonth = ($today.AddDays(-$today.Day + 1))
Get-EmailNumbers -startDate $data.LastExecutionDate -endDate $firstDayOfCurrentMonth
$global:globReceived += $data.IncrementNumberReceived
$global:globSent += $data.IncrementNumberSent
Write-Output "Total in previous month: received: $($global:globReceived) , sent: $($global:globSent)"
# send the email
# this line is skipped because it's a standard action, not relate to this script topic
$data.IncrementNumberReceived = 0
$data.IncrementNumberSent = 0
$data.LastExecutionDate = $today
}
$data | ConvertTo-Json | Out-File $dataFile
Contact Me!
- Use PowerShell to integrate OpenAI GPT with context menu - October 25, 2023
- Let OpenAI improve and correct your PowerShell code - October 11, 2023
- Create Web Link with PowerShell in Intune - September 27, 2023