Customized scheduled SharePoint Search Alerts in HTML
Occasionally I get requests for customized notification about documents within a SharePoint farm. Regular Alerts and SharePoint Search Alerts work great out of the box, but sometimes users want something more such as:
– Complex criteria
– Custom sort sequence
– Custom metadata
– Customized notification frequency
– Custom message text, or subject line
– Refined layout
The solution I’ve used is to script a search query in powerShell, and load it into a formatted HTML table, and schedule it to the desired frequency. I’ll outline the framework below that is easily adapted and extended. Note I wrote this for FAST, but using SharePoint Search Query classes you can achieve similar results in regular SharePoint search.
First, let’s establish some basics about search and notification. For this solution, I only want to return documents that are up to two or three days old, so I grab the date, take two off it, and put it into a format we can use later for the query:
$Mydate=get-date $MyDate = $Mydate.AddDays(-2) $MyDateStr = $Mydate.Year.ToString("0000") + "-" + $Mydate.Month.ToString("00") + "-" + $Mydate.day.ToString("00") #formatted YYYY-MM-DD |
Let’s now set up for the search querty. I chose to use FQL (FAST Query Language), but you can use Keyword Search. Note SQL Search is deprecated. I chose 50 results, but you can choose whatever amount you prefer:
$site = New-Object Microsoft.SharePoint.SPSite $webappurl $vc =New-Object Microsoft.Office.Server.Search.Query.KeywordQuery $site $vc.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch $vc.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults #In my case I enabled the FQL syntax and set some other parameters: $vc.EnableFQL = $true # enable FQL $vc.RowLimit = 50 # sets the limit of results $vc.StartRow = 0 # 0 is the default |
Now let’s make sure the query returns the fields you want. These must be Managed Properties configured followed by a Full Crawl.
$vc.SelectProperties.Add("Company Name") $vc.SelectProperties.Add("URL") $vc.SelectProperties.Add("Title") $vc.SelectProperties.Add("Filename") $vc.SelectProperties.Add("Company ClaimNumber") $vc.SelectProperties.Add("Company PolicyNumber") $vc.SelectProperties.Add("Company Modified") $vc.SelectProperties.Add("Company EffectiveYear") |
Now let’s piece together the XML of the FQL. Note two strings ($q1 and $q2) are used to construct the query and put into $BigQ, with the date we formatted earlier. We’re looking for documents newer than two days ago, where a particular field CompanyClaimDocumentType equals a specific value (“Specific Value”). Then we execute the FQL:
$q1='and(filter(Company modified:range(datetime("' $q2='"), max, from="GT")), filter(CompanyClaimDocumentType:equals("Specific Value")))' $BigQ=$q1+$MyDateStr+$q2 $vc.QueryText = $BigQ $results = $vc.Execute() |
Now let’s convert the search results into a DataTable to make it easy to shape into an HTML table for the outbound email alert notification, we’ll define the columns, and load the values. One nice thing is to shape the link column to allow a hyperlink embedded in the table for easy user access to the documents. I also structure a special link using the DMF:// protocol supported by MacroView DMF:
$resultsTable = $results.Item([Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults) $resultsDataTable = $resultsTable.Table $rows = $resultsDataTable.Rows $table = New-Object system.Data.DataTable “SearchReport” $col1 = New-Object system.Data.DataColumn Title,([string]) $col2 = New-Object system.Data.DataColumn CompanyName,([string]) $col3 = New-Object system.Data.DataColumn ClaimNumber,([string]) $col4 = New-Object system.Data.DataColumn Link,([string]) $col5 = New-Object system.Data.DataColumn PolicyNumber,([string]) $col6 = New-Object system.Data.DataColumn Modified,([string]) $col7 = New-Object system.Data.DataColumn EffectiveYear,([string]) $col8 = New-Object system.Data.DataColumn FileName,([string]) $col9 = New-Object system.Data.DataColumn DMF,([string]) $table.columns.add($col1) $table.columns.add($col2) $table.columns.add($col3) $table.columns.add($col4) $table.columns.add($col5) $table.columns.add($col6) $table.columns.add($col7) $table.columns.add($col8) $table.columns.add($col9) if ($rows.count -gt 0) { for ($i=0; $i -lt $rows.Count; $i++) { $row = $table.NewRow() $row.Link = "<a>'+ " Link " + "</a>"; #$row.DMF = ($row.Link.Replace("http://","DMF://")).replace(" File Link", "SP Explorer") Took out, doesn't appear to work quite right $row.Title = $rows[$i].Title; $row.InsuredName= $rows[$i].CompanyName; $row.ClaimNumber= $rows[$i].CompanyClaimNumber; $row.PolicyNumber= $rows[$i].CompanyPolicyNumber; $row.EffectiveYear= $rows[$i].CompanyEffectiveYear; $row.FileName= $rows[$i].FileName; $row.Modified= $rows[$i].Company Modified.substring(0,10); $table.Rows.Add($row) #("<a>" + $_.Portname.SubString(3) + "</a>"}} } |
Now, we want to shape this into an outbound table in the email. To do that we’ll use the handy CovertTo-HTML CmdLet. Just pass in the column names you want to appear. Above I map more than we use below. I shape the borders and colors for a really professional look. However we have a sticky problem where we don’t want it to mess with the embedded HTML such as brackets around the a href for links. To solve that, I pipe the stream into a routine called Convert-HTMLEscape that I will outline shortly. Note the nice H1 header, and we’ll nicely handle single/plural number of rows in the subject line and top of the email:
$a = "" $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color:black;}" $a = $a + "Table{background-color:#EFFFFF;border-collapse: collapse;}" $a = $a + "TH{border-width:1px;padding:5px;border-style:solid;border-color:black;background-color:#DDDDDD}" $a = $a + "TD{border-width:1px;padding-left:5px;padding-right:3px;border-style:solid;border-color:black;}" $a = $a + "" #$MyOutput = $rows | ConvertTo-Html Title, Author, URL, Link -body "<H1>Recent Custom Reports</H1>" -PostContent "Goodbye and thanks for all the fish" if ($rows.count -eq 1) { $Plural=$null; } else { $Plural="s"; } $MyOutput = $table| ConvertTo-Html Title, Link, InsuredName, ClaimNumber, PolicyNumber, EffectiveYear, Modified -head $a -body "<H1>$($rows.count) Recent Property Adjuster Report$($Plural)</H1>" | Convert-HTMLEscape |
Here’s the Convert-HTMLEscape function. It’s a little dense, but works within the pipeline and does what we need, by converting the XML equivalent of the two common XML characters right back to the correct characters, basically undoing the bit of a mess made by ConvertTo-HTML:
Function Convert-HTMLEscape { # convert &lt; and &gt; to <> It is assumed that these will be in pairs [cmdletbinding()] Param ( [Parameter(Position=0,ValueFromPipeline=$True)] [string[]]$Text ) Process { foreach ($item in $text) { if ($item -match "&lt;") { <# replace codes with actual symbols. This line is a shortcut to do two replacements with one line of code. The code in the first set of parentheses revised text with &quot;. #> (($item.Replace("&lt;","")).Replace("&quot;",'"') } else { #otherwise just write the line to the pipeline $item } } } #close process } #close function |
Now the easy part, let’s generate the email. Note the format sets isHTML to $true, and uses SMTP:
#email setup, can move to top #param( [string] $From = "SharePoint Support@MyDomain.com" [string] $To = $null; #$Recipients #"joelplaut@MyDomain.com" [string] $Title = "Daily Report of updated custom Reports" #[string] $Body = "body" #) $Body = $rows | ConvertTo-Html $SmtpClient = New-Object System.Net.Mail.SmtpClient $SmtpServer = "mail.Company limited.com" $SmtpClient.host = $SmtpServer # $SmtpClient.Send($From,$To,$Title,$Body) $MailMessage = New-Object system.net.mail.mailmessage $mailmessage.from = $From; foreach ($recip in $ToRecipientsArray) { $mailmessage.To.add($recip) } foreach ($recip in $CCRecipientsArray) { $mailmessage.CC.add($recip) } $mailmessage.Subject = $Title $mailmessage.Body = $myoutput #"Body" $MailMessage.set_IsBodyHtml($true) $smtpclient.Send($mailmessage) |
Now let’s put it all together. Note I always set an $env for the environment, to make it easy to test in Dev, before deploying in Production.
# SharePoint Search Alerts Clear-Host Write-Host "Start LC Alerts" -ForegroundColor darkblue Add-PsSnapin Microsoft.SharePoint.PowerShell -erroraction silentlycontinue $ToRecipients = "MyADGroup@MyDomain.com" $CCRecipients = "joelplaut@MyDomain.com,Bozo@MyDomain.com" $ToRecipientsArray = $ToRecipients.Split(","); $CCRecipientsArray = $CCRecipients.Split(","); [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration") $env="Prod" if ($env -eq "Dev") { $webappurl = "http ://devFarm/" # Replace with URL of web application that you wish to warm up $filterpath = "http ://devFarm/insureds" } else { $webappurl = "http ://SharePoint/" # Replace with URL of web application that you wish to warm up $filterpath = "http ://SharePoint/insureds" #path for exercising previews } Function Convert-HTMLEscape { &lt;# convert &lt; and &gt; to It is assumed that these will be in pairs #&gt; [cmdletbinding()] Param ( [Parameter(Position=0,ValueFromPipeline=$True)] [string[]]$Text ) Process { foreach ($item in $text) { if ($item -match "&lt;") { &lt;# replace codes with actual symbols This line is a shortcut to do two replacements with one line of code. The code in the first set of parentheses revised text with &quot;. #&gt; (($item.Replace("&lt;","")).Replace("&quot;",'"') } else { #otherwise just write the line to the pipeline $item } } } #close process } #close function $Mydate=get-date $MyDate = $Mydate.AddDays(-2) $MyDateStr = $Mydate.Year.ToString("0000") + "-" + $Mydate.Month.ToString("00") + "-" + $Mydate.day.ToString("00") #formatted YYYY-MM-DD $site = New-Object Microsoft.SharePoint.SPSite $webappurl $vc =New-Object Microsoft.Office.Server.Search.Query.KeywordQuery $site $vc.ResultsProvider = [Microsoft.Office.Server.Search.Query.SearchProvider]::FASTSearch $vc.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults #In my case I enabled the FQL syntax and set some other parameters: $vc.EnableFQL = $true # enable FQL $vc.RowLimit = 50 # sets the limit of results $vc.StartRow = 0 # 0 is the default $vc.SelectProperties.Add("Company Name") $vc.SelectProperties.Add("URL") $vc.SelectProperties.Add("Title") $vc.SelectProperties.Add("Filename") $vc.SelectProperties.Add("Company ClaimNumber") $vc.SelectProperties.Add("Company PolicyNumber") $vc.SelectProperties.Add("Company Modified") $vc.SelectProperties.Add("Company EffectiveYear") #Query / Result $q1='and(filter(Company modified:range(datetime("' $q2='"), max, from="GT")), filter(Company claimdocumenttype:equals("Property Adjuster Reports")))' $BigQ=$q1+$MyDateStr+$q2 $vc.QueryText = $BigQ $results = $vc.Execute() #$results $resultsTable = $results.Item([Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults) $resultsDataTable = $resultsTable.Table $rows = $resultsDataTable.Rows $table = New-Object system.Data.DataTable “SearchReport” $col1 = New-Object system.Data.DataColumn Title,([string]) $col2 = New-Object system.Data.DataColumn InsuredName,([string]) $col3 = New-Object system.Data.DataColumn ClaimNumber,([string]) $col4 = New-Object system.Data.DataColumn Link,([string]) $col5 = New-Object system.Data.DataColumn PolicyNumber,([string]) $col6 = New-Object system.Data.DataColumn Modified,([string]) $col7 = New-Object system.Data.DataColumn EffectiveYear,([string]) $col8 = New-Object system.Data.DataColumn FileName,([string]) $col9 = New-Object system.Data.DataColumn DMF,([string]) $table.columns.add($col1) $table.columns.add($col2) $table.columns.add($col3) $table.columns.add($col4) $table.columns.add($col5) $table.columns.add($col6) $table.columns.add($col7) $table.columns.add($col8) $table.columns.add($col9) if ($rows.count -gt 0) { for ($i=0; $i -lt $rows.Count; $i++) { $row = $table.NewRow() $row.Link = "<a>'+ " Link " + "</a>"; #$row.DMF = ($row.Link.Replace("http://","DMF://")).replace(" File Link", "SP Explorer") Took out, doesn't appear to work quite right $row.Title = $rows[$i].Title; $row.InsuredName= $rows[$i].CompanyName; $row.ClaimNumber= $rows[$i].CompanyClaimNumber; $row.PolicyNumber= $rows[$i].CompanyPolicyNumber; $row.EffectiveYear= $rows[$i].CompanyEffectiveYear; $row.FileName= $rows[$i].FileName; $row.Modified= $rows[$i].Company Modified.substring(0,10); $table.Rows.Add($row) #("<a>" + $_.Portname.SubString(3) + "</a>"}} } $a = "" $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color:black;}" $a = $a + "Table{background-color:#EFFFFF;border-collapse: collapse;}" $a = $a + "TH{border-width:1px;padding:5px;border-style:solid;border-color:black;background-color:#DDDDDD}" $a = $a + "TD{border-width:1px;padding-left:5px;padding-right:3px;border-style:solid;border-color:black;}" $a = $a + "" #Filename removed at My's suggestion #$MyOutput = $rows | ConvertTo-Html Title, Author, URL, Link -body "<H1>Recent Custom Reports</H1>" -PostContent "Goodbye and thanks for all the fish" if ($rows.count -eq 1) { $Plural=$null; } else { $Plural="s"; } $MyOutput = $table| ConvertTo-Html Title, Link, InsuredName, ClaimNumber, PolicyNumber, EffectiveYear, Modified -head $a -body "<H1>$($rows.count) Recent Custom Report$($Plural)</H1>" | Convert-HTMLEscape #$MyOutput &gt; C:A.html #debug technique #email setup, can move to top #param( [string] $From = "SharePoint Support@Company limited.com" [string] $To = $null; #$Recipients #"joelplaut@Company limited.com" [string] $Title = "Daily Report of updated Property Adjuster Reports" #[string] $Body = "body" #) $Body = $rows | ConvertTo-Html $SmtpClient = New-Object System.Net.Mail.SmtpClient $SmtpServer = "mail.Company limited.com" $SmtpClient.host = $SmtpServer # $SmtpClient.Send($From,$To,$Title,$Body) $MailMessage = New-Object system.net.mail.mailmessage $mailmessage.from = $From; foreach ($recip in $ToRecipientsArray) { $mailmessage.To.add($recip) } foreach ($recip in $CCRecipientsArray) { $mailmessage.CC.add($recip) } $mailmessage.Subject = $Title $mailmessage.Body = $myoutput #"Body" $MailMessage.set_IsBodyHtml($true) $smtpclient.Send($mailmessage) } #don't bother, no new hits |
Want to talk?
Drop us a line. We are here to answer your questions 24*7.