Login Enterprise Continuous Tests’ Event Viewer

At Rightworks, we use the Continuous Testing feature of Login Enterprise extensively and almost exclusively. We run 1400+ RDP Desktop connections every 10 or 15 minutes, across 6 separate pods. As you can imagine, this generates and enormous amount of data, including login measurements and application timers. But, also, since we are hosting/testing applications that are not well written, we also receive an enormoous amount of application errrors and connection errors (including, Login Failures, Connection Initialization Timeouts and Threshold exceedings.

Whilte Login Enterprise is great at gathering this data, the built-in Event Viewer is woefully inadequate. You can only view data from a single 60-minute period. You cannot filter the events by any field such as type of error, by launcher, target, test, or account.

This makes using this data to actually troubleshoot issues impossible.

Creating a usable Event Viewer

If you have been reading my series on creating a Login Enterprise Status Board, you’ll see that I have been doing a lot of with Powershell to pull data from Login Enterprise and, now, creating WinForm UIs to interact iwth this data. This series will document how I am creating an Event Viewer for Login Enterprise that is actually useful, especially when managing multiple environments.

The Approach

As usual, I will be accessing the data directly from the SQL database. This has the benefit of being much faster to pull data and not putting additional load on our extremely busy LE appliances. The downside is that the date/time stamp that LE writes into the tables is all written in UTC time, so you will have to translate these to your local timezone. I am writing this in Powershell (and my usual disclaimer that I am NOT a coder and this code is by no means elegant. But, it works! 😊 ).

The code

First, I created a form in Visual Studio Community Edition and used the ConvertForm Powershell Module to convert this form to a Powershell Script. See Don Ruggeri’s excellent 2 page article on how to do this.
Creating Extensive PowerShell GUI Applications – PART 1 and Creating Extensive PowerShell GUI Applications – PART 2.

I setuyp a basic Form with a Label object for the title and a DataGridView object with 7 columns. I added a Refresh button which will be used to pull a fresh set of data.

Once I have the form that I wanted, I saved the solution. Once the ConvertFrom module was installed, I simply ran the command to generate the Powershell Script.

$Source = "C:\Users\ssun\source\repos\LEEventViewer\LEEventViewer\Form1.Designer.cs"
$Destination = "C:\Users\ssun\source\repos\LEEventViewer\LEEventViewer\"
Convert-Form -Path $Source -Destination $Destination -Encoding ascii -force

This generated that following code:

################################################################################ 
#
#  Name    : C:\Users\ssun\source\repos\LEEventViewer\LEEventViewer\\Form1.ps1  
#  Version : 0.1
#  Author  :
#  Date    : 9/5/2024
#
 #  Generated with ConvertForm module version 2.0.0
#  PowerShell version 5.1.20348.1366
#
#  Invocation Line   : Convert-Form -Path $Source -Destination $Destination -Encoding ascii -force
#  Source            : C:\Users\ssun\source\repos\LEEventViewer\LEEventViewer\Form1.Designer.cs
################################################################################

function Get-ScriptDirectory
{ #Return the directory name of this script
  $Invocation = (Get-Variable MyInvocation -Scope 1).Value
  Split-Path $Invocation.MyCommand.Path
}

$ScriptPath = Get-ScriptDirectory

# Loading external assemblies
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$Form1 = New-Object System.Windows.Forms.Form

$label1 = New-Object System.Windows.Forms.Label
$dataGridView1 = New-Object System.Windows.Forms.DataGridView
$button1 = New-Object System.Windows.Forms.Button
$Created = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$Event = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$Description = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$Application = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$Launcher = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$Account = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
$ASP = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
#
# label1
#
$label1.AutoSize = $true
$label1.Font = New-Object System.Drawing.Font("Microsoft Sans Serif", 24,[System.Drawing.FontStyle]::Bold,[System.Drawing.GraphicsUnit]::Point, 0)
$label1.Location = New-Object System.Drawing.Point(297, 9)
$label1.Name = "label1"
$label1.Size = New-Object System.Drawing.Size(474, 37)
$label1.TabIndex = 0
$label1.Text = "Login Enterprise Event Viewer"
#
# dataGridView1
#
$dataGridView1.ColumnHeadersHeightSizeMode = [System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode]::AutoSize
$dataGridView1.Columns.AddRange(@(
$Created,
$Event,
$Description,
$Application,
$Launcher,
$Account,
$ASP))
$dataGridView1.Location = New-Object System.Drawing.Point(12, 93)
$dataGridView1.Name = "dataGridView1"
$dataGridView1.RowHeadersVisible = $false
$dataGridView1.Size = New-Object System.Drawing.Size(1178, 516)
$dataGridView1.TabIndex = 1
#
# button1
#
$button1.Font = New-Object System.Drawing.Font("Microsoft Sans Serif", 8.25,[System.Drawing.FontStyle]::Bold,[System.Drawing.GraphicsUnit]::Point, 0)
$button1.Location = New-Object System.Drawing.Point(994, 44)
$button1.Name = "button1"
$button1.Size = New-Object System.Drawing.Size(75, 23)
$button1.TabIndex = 2
$button1.Text = "Refresh"
$button1.UseVisualStyleBackColor = $true
#
# Created
#
$Created.HeaderText = "Created"
$Created.Name = "Created"
$Created.Width = 125
#
# Event
#
$Event.HeaderText = "Event"
$Event.Name = "Event"
$Event.Width = 150
#
# Description
#
$Description.HeaderText = "Description"
$Description.Name = "Description"
$Description.Width = 400
#
# Application
#
$Application.HeaderText = "Application"
$Application.Name = "Application"
$Application.Width = 125
#
# Launcher
#
$Launcher.HeaderText = "Launcher"
$Launcher.Name = "Launcher"
$Launcher.Width = 125
#
# Account
#
$Account.HeaderText = "Account"
$Account.Name = "Account"
$Account.Width = 125
#
# ASP
#
$ASP.HeaderText = "ASP"
$ASP.Name = "ASP"
$ASP.Width = 125
#
# Form1
#
$Form1.ClientSize = New-Object System.Drawing.Size(1203, 621)
$Form1.Controls.Add($button1)
$Form1.Controls.Add($dataGridView1)
$Form1.Controls.Add($label1)
$Form1.Name = "Form1"
$Form1.Text = "Form1"

function OnFormClosing_Form1{ 
	# $this parameter is equal to the sender (object)
	# $_ is equal to the parameter e (eventarg)

	# The CloseReason property indicates a reason for the closure :
	#   if (($_).CloseReason -eq [System.Windows.Forms.CloseReason]::UserClosing)

	#Sets the value indicating that the event should be canceled.
	($_).Cancel= $False
}

$Form1.Add_FormClosing( { OnFormClosing_Form1} )

$Form1.Add_Shown({$Form1.Activate()})
$ModalResult=$Form1.ShowDialog()
# Release the Form
$Form1.Dispose()

When I ran this code, however, it produced the following error and the columns were not being displayed.

Cannot convert argument "dataGridViewColumns", with value: "System.Object[]", for "AddRange" to type "System.Windows.Forms.DataGridViewColumn": "Cannot convert the "System.Object[]" value of type 
"System.Object[]" to type "System.Windows.Forms.DataGridViewColumn"."
At C:\Users\ssun\source\repos\LEEventViewer\LEEventViewer\Form1.ps1:53 char:1
+ $dataGridView1.Columns.AddRange(@(
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

What I found was that the conversion tried to use an array to create the columns.

#
# dataGridView1
#
$dataGridView1.ColumnHeadersHeightSizeMode = [System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode]::AutoSize
$dataGridView1.Columns.AddRange(@(
$Created,
$Event,
$Description,
$Application,
$Launcher,
$Account,
$ASP))
$dataGridView1.Location = New-Object System.Drawing.Point(12, 93)
$dataGridView1.Name = "dataGridView1"
$dataGridView1.RowHeadersVisible = $false
$dataGridView1.Size = New-Object System.Drawing.Size(1178, 516)
$dataGridView1.TabIndex = 1

So, instead, I added the columns manually.

#
# dataGridView1
#
$dataGridView1.ColumnHeadersHeightSizeMode = [System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode]::AutoSize
$dataGridView1.ColumnCount = 8
$dataGridView1.ColumnHeadersVisible = $true
$dataGridView1.Columns[0].Name = "Pod"
$dataGridView1.Columns[1].Name = "Created"
$dataGridView1.Columns[2].Name = "Event"
$dataGridView1.Columns[3].Name = "Description"
$dataGridView1.Columns[4].Name = "Application"
$dataGridView1.Columns[5].Name = "Launcher"
$dataGridView1.Columns[6].Name = "Account"
$dataGridView1.Columns[7].Name = "ASP"
$dataGridView1.Columns[0].Width = 50
$dataGridView1.Columns[1].Width = 125
$dataGridView1.Columns[2].Width = 150
$dataGridView1.Columns[3].Width = 400
$dataGridView1.Columns[4].Width = 125
$dataGridView1.Columns[5].Width = 125
$dataGridView1.Columns[6].Width = 125
$dataGridView1.Columns[7].Width = 126

And now the form is built correctly.

Getting the event data

Next, I needed to create a SQL query that would pull the data that I wanted in the format that I wanted.

NOTE: The Login Enterprise database has a lot of bloat from architectural changes that have not been cleaned up over the years. Also, there are many tables with one or two values in them, so there are many joins that are required to reference details from related tables. I won’t go into how to navigate the LE database in this article, but if this is of interest, I may write an article on how to do this. BTW, this is greatly discouraged by LVSI.

The SQL query I wound up with pulls events that are only related to errors. This is controlled with the filter (LoginEnterprise.EventProperties.PropertyId = ‘Description’) and events from the past 60 minutes, as defined by the filter (LoginEnterprise.Events.Created > dateadd(n,-60,getutcdate()).

SELECT FORMAT (LoginEnterprise.Events.Created,  'dd/MM/yyyy, hh:mm:ss ') as Created,Events.Title as Event,LoginEnterprise.EventProperties.Value as Description,Management.Applications.Name as Application,LoginEnterprise.UserSessions.LauncherName as Launcher,Management.Accounts.Username as Account,Environments.Environments.Name as ASP 
FROM LoginEnterprise.Events
LEFT JOIN Environments.Environments
ON LoginEnterprise.Events.TestRunId = Environments.Environments.EnvironmentId 
LEFT JOIN LoginEnterprise.EventProperties
ON LoginEnterprise.Events.EventId = LoginEnterprise.EventProperties.EventId
Left JOIN LoginEnterprise.UserSessions
ON LoginEnterprise.Events.UserSessionId = LoginEnterprise.UserSessions.UserSessionId
LEFT JOIN LoginEnterprise.AppExecutions
ON LoginEnterprise.Events.AppExecutionId = LoginEnterprise.AppExecutions.AppExecutionId
LEFT JOIN Management.Applications
ON LoginEnterprise.AppExecutions.ApplicationId = Management.Applications.[Key]
LEFT JOIN Management.Accounts
ON LoginEnterprise.UserSessions.AccountID = Management.Accounts.[Key]
  Where (LoginEnterprise.Events.Created > dateadd(n,-60,getutcdate()) AND (LoginEnterprise.EventProperties.PropertyId = 'Description'))
Order By LoginEnterprise.Events.Created Desc;

To use this in my Powershell script, I included it in a set of global variables that define my communications with our 3 SQL servers and 6 databases.

# Setup variables for LE Databases
$global:BSQLServer = "<SQLServer1>"
$global:db1 = "<Pod1database>"
$global:db2 = "<Pod2database>"
$global:db3 = "<Pod3database>"
$global:db4 = "<Pod4database>"

$global:VSQLServer = "<SQLServer2>"
$global:dbV = "<PodVdatabase>"

$global:GSQLServer = "<SQLServer3>"
$global:dbG = "<Pod=Gdatabase>"
$global:Eventquery="SELECT FORMAT (LoginEnterprise.Events.Created,  'dd/MM/yyyy, hh:mm:ss ') as Created,Events.Title as Event,LoginEnterprise.EventProperties.Value as Description,Management.Applications.Name as Application,LoginEnterprise.UserSessions.LauncherName as Launcher,Management.Accounts.Username as Account,Environments.Environments.Name as ASP 
FROM LoginEnterprise.Events
LEFT JOIN Environments.Environments
ON LoginEnterprise.Events.TestRunId = Environments.Environments.EnvironmentId 
LEFT JOIN LoginEnterprise.EventProperties
ON LoginEnterprise.Events.EventId = LoginEnterprise.EventProperties.EventId
Left JOIN LoginEnterprise.UserSessions
ON LoginEnterprise.Events.UserSessionId = LoginEnterprise.UserSessions.UserSessionId
LEFT JOIN LoginEnterprise.AppExecutions
ON LoginEnterprise.Events.AppExecutionId = LoginEnterprise.AppExecutions.AppExecutionId
LEFT JOIN Management.Applications
ON LoginEnterprise.AppExecutions.ApplicationId = Management.Applications.[Key]
LEFT JOIN Management.Accounts
ON LoginEnterprise.UserSessions.AccountID = Management.Accounts.[Key]
  Where (LoginEnterprise.Events.Created > dateadd(n,-60,getutcdate()) AND (LoginEnterprise.EventProperties.PropertyId = 'Description'))
Order By LoginEnterprise.Events.Created Desc;"

Next, I created a function that pulls the events for each pod using the SQL connection info. I add a new column to specify which Pod the event is from and populate the DGV with it.

Function Get-LEEvents {

$Events1 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db1 -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$Events1 | Add-Member NoteProperty "Pod" -Value "Pod1"
$Events2 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db2 -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$Events2 | Add-Member NoteProperty "Pod" -Value "Pod2"
$Events3 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db3 -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$Events3 | Add-Member NoteProperty "Pod" -Value "Pod3"
$Events4 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db4 -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$Events4 | Add-Member NoteProperty "Pod" -Value "Pod4"
$EventsV = Invoke-Sqlcmd -ServerInstance $global:VSQLServer -Database $global:dbV -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$EventsV | Add-Member NoteProperty "Pod" -Value "PodV"
$EventsG = Invoke-Sqlcmd -ServerInstance $global:GSQLServer -Database $global:dbG -Query $global:Eventquery -Username "<SQL account>" -Password "<SQL password>" -Verbose -TrustServerCertificate
$EventsG | Add-Member NoteProperty "Pod" -Value "PodG"

$AllEvents = $($Events1; $Events2; $Events3; $Events4; $EventsG; $EventsV)


$dataGridView1.Rows.Clear()
ForEach ($Item in $AllEvents) {[void]$dataGridView1.Rows.Add(($Item).Pod,($Item).Created,($Item).Event,($Item).Description,($Item).Application,($Item).Launcher,($Item).Account,($Item).ASP)}
}

This is as far as I’ve taken it so far. Already, I have one view of all the events from all 6 Pods. I can sort on any column, which can help to see trending events. I’ve also removed all of the system events such as Data Retention Started.

Part 2 will talk about adding functionality to the Refresh button. Future articles will address adding filters or radio buttons to select specific pods or events.

Login Enterprise Continuous Tests’ Event Viewer – Part 1

Creating a usable Event Viewer

The Approach

The code

Getting the event data

Leave a ReplyCancel reply

Creating a usable Event Viewer

The Approach

The code

Getting the event data

Related Posts

Leave a ReplyCancel reply