Report WPM Unmanaged Transactions with PowerShell

Version 2

    Web Performance Monitor doesn't provide an easy way of viewing a list of transactions that are either currently unmanaged or scheduled to be unmanaged. However, PowerShell can be leveraged to provide a formatted list of unmanaged transactions.

     

    Search Thwack for the Orion SDK, download it and install on a Windows computer with PowerShell installed, then open a PowerShell command prompt. For convenience, I have it installed on both my NPM Server and Windows 7 desktop.

     

    Begin by adding the SDK Snapin

    Add-PSSnapin "SwisSnapin"

     

    For some reason, the snapin DLL registered on my workstation, but didn't on the NPM server, as I received an error when registering SwisSnapin. Run this command only if the Snapin doesn't register

    C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\InstallUtil.exe 'C:\Program Files (x86)\SolarWinds\Orion SDK\SWQL Studio\SwisPowerShell.dll'

     

    List commands that are available with the SwisSnapin

    get-command | where-object{$_.PSSnapin.Name -eq "SwisSnapin"}

     

    Connect to your Orion Server, supply domain credentials that have permissions to view your transactions

    $cred = Get-Credential -Message "Enter Solarwinds Credentials"

    $npmServer = "npmserver1.acme.local"

    $swis = Connect-Swis -Credential $cred -Hostname $npmServer

     

    This will provide a list of all WPM transactions

    Get-SwisData $swis "SELECT TransactionId, Name FROM Orion.SEUM.Transactions" | Format-Table -AutoSize -Wrap

     

    Filter all transactions by listing only transactions where the Name begins with the prefix ACME*

    Filter a set of transactions and output to a Grid-View

    $vWPM = Get-SwisData $swis "SELECT TransactionId, Name, UnManaged, UnManageFrom, UnManageUntil FROM Orion.SEUM.Transactions" | Where Name -Like "ACME*"

    $vWPM | Out-GridView

     

    Let's add a filter to only display transactions that are either currently or scheduled to be unmanaged by filtering on the UnManageFrom field if it's not NULL.

    $allTrans = Get-SwisData $swis "SELECT TransactionId, Name, UnManaged, UnManageFrom, UnManageUntil FROM Orion.SEUM.Transactions" `

              | Where {($_.UnManageFrom -ne $null) -or ($_.UnManageUntil -ne $null)}

    $allTrans | Out-GridView -Title "Transactions Scheduled for Un-Manage"


    Now if you notice, unmanged transactions are stored in the Orion database in UTC format, not your local timezone. Here is how to convert the time.


    This command displays information about your local timezone, we are interested in the BaseUtcOffset value.:

    [timeZoneInfo]::Local


    I am in Eastern timezone and we are 5 hours behind UTC, so we need to add -5 to the UTC time value.

    [string]$strBaseOffset = [TimeZoneInfo]::Local | select BaseUtcOffset

    $timeOffset = ($strBaseOffset -split '=|:')[1]

     

    Now let's define a few expressions, adding -5 to our UTC time

    $umFrom = @{Label="Unmanage From"; Expression = {($_.UnManageFrom).addhours($timeOffset)}}

    $umUntil = @{Label="Unmanage Until"; Expression = {($_.UnManageUntil).addhours($timeOffset)}}

     

    Output the results to grid view, the time has been converted to your local timezone

    $allTrans | Select Name, Unmanaged, $umFrom, $umUntil | Out-GridView -Title "Transactions Scheduled for Un-Manage"

     

    A sample PowerShell script has been attached, please let me know if you have any feedback.