Brianary

A pedant that hangs out in the dark corner-cases of the web.

Wednesday, December 31, 2014

Using SQL Type Providers and FSharp.Charting to generate an email report with inline charts in F#


open System
// Needed to send an email with the charts.
open System.Net.Mail
open System.Net.Mime
// Needed for FSharp.Charting's ShowChart() method.
#r "System.Windows.Forms.DataVisualization"

// Tip: Create a paket.dependencies file to install FSharp.Data.SqlClient and FSharp.Charting.
//   see http://fsprojects.github.io/Paket/
#I @".\packages\FSharp.Data.SqlClient\lib\net40"
#r "FSharp.Data.SqlClient"
open FSharp.Data
#I @".\packages\FSharp.Charting\lib\net40"
#r "FSharp.Charting"
open FSharp.Charting

// Install the AdventureWorks2014 database from 
// https://msftdbprodsamples.codeplex.com/releases/view/125550
// With VS installed, you should be able to unzip 
// Adventure Works 2014 OLTP Script.zip into
// C:\Program Files\Microsoft SQL Server\120\Tools\Samples\Adventure Works 2014 OLTP Script
// (depending on version) then run something like
// sqlcmd.exe -E -S "(localdb)\ProjectV12" -i instawdb.sql
// (from that directory, running in a PowerShell or cmd prompt as admin).

/// Use the SQL Server Type Provider to define a type-safe query for the top products (by $ amount)
/// summary order amount statistics by date.
type OrderCmd = SqlCommandProvider<"
select p.Name, p.ProductID, o.OrderDate, sum(od.LineTotal) Total, 
       min(od.LineTotal) MinAmt, max(od.LineTotal) MaxAmt, 
       avg(od.LineTotal) AvgAmt, coalesce(stdev(od.LineTotal),0.0) StDevAmt, 
       (max(od.LineTotal) - min(od.LineTotal)) / 2 + min(od.LineTotal) MedianAmt
  from Production.Product p
  join Sales.SalesOrderDetail od
    on p.ProductID = od.ProductID
  join Sales.SalesOrderHeader o
    on od.SalesOrderID = o.SalesOrderID
 where p.ProductID in (select top 5 pod.ProductID from Sales.SalesOrderDetail pod 
       group by pod.ProductID order by sum(pod.LineTotal) desc)
   and o.OrderDate between '2014-03-01' and '2014-03-31'
 group by p.ProductID, p.Name, o.OrderDate
 order by p.ProductID, o.OrderDate
","server=(localdb)\ProjectsV12;database=AdventureWorks2014;integrated security=SSPI">
/// An instance of the query type.
let getOrders = new OrderCmd()

/// Converts a product name, record sequence tuple into a product name, sum total amount tuple.
let productTotal (n,p:OrderCmd.Record seq) = 
    n, Seq.sumBy (fun (d:OrderCmd.Record) -> d.Total.Value) p
/// Converts a record into a date, max amount tuple if it differs from the average amount.
let dateMax = 
    function 
    | (d:OrderCmd.Record) when d.MaxAmt.Value <> d.AvgAmt.Value 
        -> Some (d.OrderDate, d.MaxAmt.Value) 
    | _ -> None
/// Converts a record into a date, standard deviation of amount tuple if one exists.
let dateStDev = 
    function 
    | (d:OrderCmd.Record) when d.StDevAmt.Value <> 0.0 
        -> Some (d.OrderDate, float d.AvgAmt.Value + d.StDevAmt.Value) 
    | _ -> None
/// Converts a record into a date, average amount tuple.
let dateAvg (d:OrderCmd.Record) = d.OrderDate, d.AvgAmt.Value

/// Collects the records by product name, caching to prevent re-reading from a closed data reader.
let products = getOrders.Execute() |> Seq.cache |> Seq.groupBy (fun o -> o.Name)

/// Saves a chart to a temporary file, and returns a LinkedResource object for emailing.
let saveChart (c:ChartTypes.GenericChart) = 
    let file = IO.Path.ChangeExtension(IO.Path.GetTempFileName(),"png")
    // Needed to render to file. This is a workaround for bug #38
    // see https://github.com/fsharp/FSharp.Charting/issues/38
    // (This will display a window with the chart.)
    c.ShowChart() |> ignore
    c.SaveChartAs(file,ChartTypes.ChartImageFormat.Png)
    new LinkedResource(file,"image/png")

// For each product, build a compound chart, then append those to a list containing the 
// summary pie chart, and save them all for emailing.
/// The collection of charts, as LinkResource objects for emailing.
let charts =
    products 
        |> Seq.toList
        |> List.map 
            (fun (p,o) -> 
                Chart.Combine(charts=
                    [ Chart.Point(Title=p,Color=Drawing.Color.Red,
                        data=Seq.choose dateMax o)
                      Chart.Point(Color=Drawing.Color.DarkOrange,
                        data=Seq.choose dateStDev o)
                      Chart.Column(Color=Drawing.Color.Blue,
                        data=Seq.map dateAvg o) ]))
        |> List.append 
            [Chart.Pie(Title="Top 5 Products, March 2014",
                data=Seq.map productTotal products)]
        |> List.map saveChart

/// Sends an email to the given address, with the given subject, using the LinkedResource 
/// objects as inline images in the body of the message.
let sendCharts (t:string) s c =
    /// The email message, initialized with the subject value.
    /// The default from address is set in machine.config's
    /// /configuration/system.net/mailSettings/smtp/@from
    let email = new MailMessage(Subject=s)
    email.To.Add(t)
    /// The HTML body of the email: inline images of the provided LinkedResource objects.
    let body = 
        List.map (fun (i:LinkedResource) -> 
                    sprintf "<div><img src='cid:%s' /></div>" i.ContentId) c 
            |> String.concat "\n"
    /// Create the body as an AlternateView object, to support inline images.
    use view = 
        AlternateView.CreateAlternateViewFromString(body, 
            ContentType MediaTypeNames.Text.Html)
    Seq.iter view.LinkedResources.Add c
    email.AlternateViews.Add(view)
    /// The SMTP sender, configured in the machine.config's
    /// /configuration/system.net/mailSettings element.
    use send = new SmtpClient()
    send.Send(email)

// Send the email.
sendCharts "test@example.net" "Top Products Charts" charts

Thursday, August 07, 2014

Macro to set Outlook email expiration with subject hashtag

When you return from vacation, you probably have a ton of irrelevant spam for events that happened while you were gone but are no longer useful, like:
  • people leaving early/coming in late
  • lunch/available food
  • server reboots
  • weather/traffic warnings
One way to reduce this noise is to set an expiration on emails of this nature. Sadly, Microsoft Outlook has deeply hidden the UI to do this, so most people don't remember, can't figure it out, or can't be bothered.
Here is a macro that lets you set the expiration date for an email by just adding certain hashtags to your subject. Adding #today sets the expiration for the end of the day, otherwise you can use ISO 8601 durations as hashtags, like #PT2H for two hours, or #P1W for a week.

Public WithEvents Item As Outlook.MailItem

Private Function DateUnit(ByVal isoUnit, ByVal isYmwdPart) As String
    Select Case isoUnit
        Case "Y": DateUnit = "yyyy"
        Case "W": DateUnit = "ww"
        Case "M":
            If isYmwdPart Then
                DateUnit = "m"
            Else
                DateUnit = "n"
            End If
        Case Else: DateUnit = isoUnit
    End Select
End Function

Private Function AddIsoDuration(ByVal start As Date, ByVal isoDuration As String) As Date
    Dim value, durationMatch, nextPart
    value = start
    Set durationMatch = New RegExp
    durationMatch.Pattern = "^(P((\d+[YMWD])*)(T((\d+[HMS])+))?)$"
    Set nextPart = New RegExp
    nextPart.Pattern = "^(\d+)([YMWDHMS])"
    Set matched = durationMatch.Execute(isoDuration)(0)
    ymwd = matched.SubMatches(1)
    hms = matched.SubMatches(4)
    Do Until Len(ymwd) = 0
        Set part = nextPart.Execute(ymwd)(0)
        value = DateAdd(DateUnit(part.SubMatches(1), True), CInt(part.SubMatches(0)), value)
        ymwd = Mid(ymwd, Len(part) + 1)
    Loop
    Do Until Len(hms) = 0
        Set part = nextPart.Execute(hms)(0)
        value = DateAdd(DateUnit(part.SubMatches(1), False), CInt(part.SubMatches(0)), value)
        hms = Mid(hms, Len(part) + 1)
    Loop
    AddIsoDuration = value
End Function

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim durationMatch
    Set durationMatch = New RegExp
    durationMatch.Pattern = "#(P(\d+[YMWD])*(T(\d+[HMS])+)?)\b"
    ExpiryTime = Item.ExpiryTime
    If InStr(1, Item.Subject, "#today", vbTextCompare) > 0 Then
        If ExpiryTime = #1/1/4501# Then
            Item.ExpiryTime = DateAdd("d", 1, Date)
        End If
    ElseIf durationMatch.Test(Item.Subject) Then
        If ExpiryTime = #1/1/4501# Then
            Item.ExpiryTime = AddIsoDuration(Now, durationMatch.Execute(Item.Subject)(0).SubMatches(0))
        End If
    End If
    Item.Save
End Sub

Wednesday, October 30, 2013

PowerShell script to parse ASP.NET errors from the event log

Here is a script that exhaustively parses out the ASP.NET details from event log entries.

Get-AspNetEvents.ps1

<#
.Synopsis
Parses ASP.NET errors from the event log on the given server.
.Parameter ComputerName
The name of the server on which the error occurred.
.Parameter EntryType
Gets only events with the specified entry type. Valid values are Error, Information, and Warning. The default is all events.
.Parameter After
Skip events older than this datetime.
.Parameter Before
Skip events newer than this datetime.
.Parameter Newest
The maximum number of the most recent events to return.
#>

#requires -version 3
[CmdletBinding()] Param(
[Parameter(Mandatory=$true,Position=0)][Alias('CN','Server')][string[]]$ComputerName,
[ValidateSet('Information','Warning','Error')][string[]]$EntryType,
[DateTime]$After,
[DateTime]$Before,
[int]$Newest
)
$FieldNames= @(
    @('EventCode','EventMessage','EventTime','EventTimeUtc','EventId','EventSequence','EventOccurrence',
        'EventDetailCode','AppDomain','TrustLevel','AppPath','AppLocalPath','MachineName','_','ProcessId','ProcessName',
        'AccountName','ExceptionType','ExceptionMessage','RequestUrl','RequestPath','UserHostAddress','User','IsAuthenticated',
        'AuthenticationType','ReqThreadAccountName','ThreadId','ThreadAccountName','IsImpersonating','StackTrace','CustomEventDetails'),
    @('EventCode','EventMessage','EventTime','EventTimeUtc','EventId','EventSequence','EventOccurrence',
        'EventDetailCode','AppDomain','TrustLevel','AppPath','AppLocalPath','MachineName','_','ProcessId','ProcessName',
        'AccountName','RequestUrl','RequestPath','UserHostAddress','User','IsAuthenticated','AuthenticationType',
        'ThreadAccountName','CustomEventDetails')
) |sort Length
$RemoveFields= '_','ThreadAccountName','ReqThreadAccountName' # blank or redundant fields
$BoolFields= 'IsAuthenticated','IsImpersonating'
$IntFields= 'EventOccurrence','EventSequence','EventCode','EventDetailCode','ProcessId','ThreadId'
$EventQuery = @{
    ComputerName = $ComputerName
    LogName      = 'Application'
    Source       = 'ASP.NET 4.0.30319.0','ASP.NET 2.0.50727.0','ASP.NET 1.1.4322.0'
}
if($After){$EventQuery.After=$After}
if($Before){$EventQuery.Before=$Before}
if($Newest){$EventQuery.Newest=$Newest}
if($EntryType){$EventQuery.EntryType=$EntryType}
Get-EventLog @EventQuery |
    ? {1017,1019,1023,1025 -notcontains $_.EventID} | # don't want ASP.NET registration events
    % {
        [string]$type = $_.EntryType
        $fields = @{EntryType=$type;Source=$_.Source;EventTime=$_.TimeGenerated}
        if($type -eq 'Error')
        { # errors aren't structured nicely
            if($_.Message -match '(?m)^Application ID: (?.+)$'){$fields.AppId=$Matches.AppId.TrimEnd()}
            if($_.Message -match '(?m)^Process ID: (?.+)$'){$fields.ProcessId=[int]$Matches.ProcessId.TrimEnd()}
            if($_.Message -match '(?m)^Exception: (\w+\.)*(?\w+)\s*$'){$fields.ExceptionType=$Matches.ExceptionType}
            if($_.Message -match '(?m)^Message: (?.+)$'){$fields.ExceptionMessage=$Matches.ExceptionMessage.TrimEnd()}
            if($_.Message -match '(?ms)^StackTrace: (?.+)$'){$fields.StackTrace=$Matches.StackTrace.TrimEnd()}
        }
        elseif($_.ReplacementStrings.Length)
        {
            $values = $_.ReplacementStrings
            $names = $FieldNames |? Length -ge $values.Length |select -f 1
            if($values.Length -gt $names.Length) { Write-Warning ('Unexpected field values: {0} > {1}' -f $values.Length,$names.Length) }
            for($i=0; $i -lt $values.Length; $i++) {$fields[$names[$i]]= $values[$i].TrimEnd()}
            $RemoveFields |% {$fields.Remove($_)}
            $BoolFields |% {$fields[$_]=[bool]$fields[$_]}
            $IntFields |% {$fields[$_]=[int]$fields[$_]}
            $fields.RequestUrl= [uri]$fields.RequestUrl
            $fields.EventTime= [datetime]::Parse($fields.EventTime,$null,[Globalization.DateTimeStyles]::AssumeLocal)
            $fields.EventTimeUtc= [datetime]::Parse($fields.EventTimeUtc,$null,[Globalization.DateTimeStyles]::AssumeUniversal)
            if($fields.ExceptionMessage -and $fields.StackTrace)
            { $fields.ExceptionMessage= $fields.ExceptionMessage.Replace($fields.StackTrace,'').TrimEnd() } # don't need stack trace twice
        }
        $event = New-Object PSObject -p $fields
        $event.PSObject.TypeNames.Insert(0,'AspNetApplicationEventLogEntry')
        $event
    }

Tuesday, May 07, 2013

Find-Lines.ps1

<#
.Synopsis
Searches files for pattern, displays matches, opens in text editor.
.Parameter Pattern
Specifies the text to find. Type a string or regular expression. 
If you type a string, use the SimpleMatch parameter.
.Parameter Filters
Specifies wildcard filters that files must match one of.
.Parameter Path
Specifies a path to one or more locations. Wildcards are permitted. 
The default location is the current directory (.).
.Parameter Include
Wildcard patterns files must match one of (slower than Filter).
.Parameter Exclude
Wildcard patterns files must not match any of.
.Parameter CaseSensitive
Makes matches case-sensitive. By default, matches are not case-sensitive. 
.Parameter List
Returns only the first match in each input file. 
By default, Select-String returns a MatchInfo object for each match it finds.
.Parameter NotMatch
Finds text that does not match the specified pattern.
.Parameter SimpleMatch
Uses a simple match rather than a regular expression match. 
In a simple match, Select-String searches the input for the text in the Pattern parameter. 
It does not interpret the value of the Pattern parameter as a regular expression statement.
.Parameter NoRecurse
Disables searching subdirectories.
.Example
C:\PS> Find-Lines 'using System;' *.cs "$env:USERPROFILE\Documents\Visual Studio*\Projects" -CaseSensitive -List

This command searches all of the .cs files in the Projects directory (or directories) and subdirectories,
displays the first matching line of each file with a match, then opens the file to the correct line in the editor.
#>
[CmdletBinding()]Param(
  [Parameter(Position=0,Mandatory=$true)][string[]]$Pattern,
  [Parameter(Position=1)][string[]]$Filters,
  [Parameter(Position=2)][string[]]$Path,
  [string[]]$Include,
  [string[]]$Exclude,
  [switch]$CaseSensitive,
  [switch]$List,
  [switch]$NotMatch,
  [switch]$SimpleMatch,
  [switch]$NoRecurse
)
# set up splatting
$lsopt = @{Recurse=!$NoRecurse}
if($Path) { $lsopt.Path=$Path }
if($Include) { $lsopt.Include=$Include }
if($Exclude) { $lsopt.Exclude=$Exclude }
$ssopt = @{'Pattern'=$Pattern}
if($CaseSensitive) { $ssopt.CaseSensitive=$true }
if($List) { $ssopt.List=$true }
if($NotMatch) { $ssopt.NotMatch=$true }
if($SimpleMatch) { $ssopt.SimpleMatch=$true }
# the filter parameter is much faster than the include parameter
Select-String -Path ($( if($Filters) { $Filters|% {ls @lsopt -Filter $_} } else { ls @lsopt } ) |
    ? {Test-Path $_.FullName -PathType Leaf}) @ssopt |
  ogv -p -t "Search: '$Pattern' $Filters" |
  % {emeditor $_.Path /l $_.LineNumber} #TODO: customize editor

Thursday, April 11, 2013

TLS (HTTPS) query strings: encrypted

Wednesday, April 10, 2013

Copy-SchTasks.ps1

A simple PowerShell script to copy scheduled tasks from one machine (often much older) to another.
<#
.Synopsis
Copy scheduled jobs from another computer to this one, using a GUI list to choose jobs.
.Parameter ComputerName
The name of the computer to copy jobs from.
.Parameter DestinationComputerName
The name of the computer to copy jobs to (local computer by default).
#>
[CmdletBinding()]Param(
[Parameter(Mandatory=$true,Position=0)][string]$ComputerName,
[Parameter(Position=1)][Alias('To','Destination')][string]$DestinationComputerName = $env:COMPUTERNAME
)
$TempXml= [io.path]::GetTempFileName()
$CredentialCache = @{}
function Get-CachedCredentials([Parameter(Mandatory=$true,Position=0)][string]$UserName)
{
    if(!$CredentialCache.ContainsKey($UserName))
    { $CredentialCache.Add($UserName,(Get-Credential -Message "Enter credentials for $UserName tasks" -UserName $UserName)) }
    $CredentialCache[$UserName]
}
function ConvertFrom-Credential([Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]$Credential)
{ $Credential.GetNetworkCredential().Password }
schtasks /query /s $ComputerName /v /fo csv |
    ConvertFrom-Csv |
    ogv -p -t 'Select jobs to copy' |
    select TaskName,'Run As User' -Unique |
    % {
        schtasks /query /s $ComputerName /tn $_.TaskName /xml ONE |Out-File -Encoding unicode $TempXml
        schtasks /create /s $DestinationComputerName /tn $_.TaskName /ru ($_.'Run As User') `
            /rp (Get-CachedCredentials $_.'Run As User' |
            ConvertFrom-Credential) /xml $TempXml
        rm $TempXml
    }
$CredentialCache.Clear()

Wednesday, January 23, 2013

Installing to the GAC



Note
In earlier versions of the .NET Framework, the Shfusion.dll Windows shell extension enabled you to install assemblies by dragging them in File Explorer. Beginning with the .NET Framework 4, Shfusion.dll is obsolete.

Note
Gacutil.exe is only for development purposes and should not be used to install production assemblies into the global assembly cache.

(Also: Yikes, there are multiple GACs (by CLR, and by 32/64-bit processor architecture): .NET 4.0 has a new GAC, why? - Stack Overflow)


Replacement for the alternative: http://wixtoolset.org/ (not http://www.wix.com/) ("Standard Custom Actions"?):