Find Alert Actions E-Mail Addresses

shocko

I'm trying to find all alerts with e-mail actions against then and pull out the individual addresses in the To/CC and BCC configurations. My goal is to parse these and check in Exchange to see if the addresses are still valid As I have noticed many are not. How could i do this with either SQL/SWQL or the API?

KMSigma.SWI

This information is stored in the 'Orion.AlertActions' table in SWQL, but it's munged all together.  It can be parsed out with some (fairly) simple PowerShell.

$SwisConnection = Connect-Swis -Hostname "kmsorion01v.kmsigma.local" -Certificate
$Query = @"
SELECT ActionID, ActionTypeID, Title, Description, Enabled, SortOrder
FROM Orion.Actions
WHERE Enabled='TRUE'
  AND ActionTypeID = 'Email'


$EmailActions = Get-SwisData -SwisConnection $SwisConnection -Query $Query
$EmailActions | Add-Member -MemberType ScriptProperty -Name EmailSettings -Value { [System.Web.HttpUtility]::HtmlDecode( $this.Description ) } -Force
$EmailActions | Add-Member -MemberType ScriptProperty -Name FromAddress -Value { $SearchString = "From:"; ( $this.EmailSettings -split '
' | Where-Object { $_ -like "$SearchString *" } ).Replace("$SearchString ", "") } -Force
$EmailActions | Add-Member -MemberType ScriptProperty -Name ToAddress -Value { $SearchString = "To:"; ( $this.EmailSettings -split '
' | Where-Object { $_ -like "$SearchString *" } ).Replace("$SearchString ", "") } -Force
$EmailActions | Add-Member -MemberType ScriptProperty -Name CCAddress -Value { $SearchString = "CC:"; ( $this.EmailSettings -split '
' | Where-Object { $_ -like "$SearchString *" } ).Replace("$SearchString ", "") } -Force
$EmailActions | Add-Member -MemberType ScriptProperty -Name BCCAddress -Value { $SearchString = "BCC:"; ( $this.EmailSettings -split '
' | Where-Object { $_ -like "$SearchString *" } ).Replace("$SearchString ", "") } -Force
$EmailActions | Add-Member -MemberType ScriptProperty -Name Subject -Value { $SearchString = "Subject:"; ( $this.EmailSettings -split '
' | Where-Object { $_ -like "$SearchString *" } ).Replace("$SearchString ", "") } -Force

$EmailActions | Select-Object -Property ActionID, ActionTypeID, Title, Enabled, SortOrder, Subject, FromAddress, ToAddress, CCAddress, BCCAddress

One example return:

ActionID     : 203
ActionTypeID : Email
Title        : Send an Email (Server configuration item has changed)
Enabled      : True
SortOrder    : 1
Subject      : Server configuration item '${N=SwisEntity;M=Details.DisplayAlias}' changed on node ${N=SwisEntity;M=ServerConfiguration.Node.Caption}.
FromAddress  : ${DefaultEmailFrom}
ToAddress    : ${DefaultEmailTo}
CCAddress    : ${DefaultEmailCC}
BCCAddress   : ${DefaultEmailBCC}