Login Enterprise Continuous Tests’ Event Viewer

In part one of the LE Continuous Test Event Viewer series, I mentioned that I was going to add code to have the refresh button working. I’ve also updated the SQL query to convert the timestamps from UTC to US Eastern timezone. Since I have to apply this to all of my SQL scripts, I will also create a standalone post out-lining how to do that.

Refresh button

When I created the form for the LE Event Viewer, I added a button labeled ‘Refresh’ but did not add any functionality to it. To do this, I had to make a couple of small edits to the code.

The first edit was to move the code for the Get-LEevents function towards the beginning. This function needs to be defined before adding the Click action to the button. So, I placed it right after I defined the global: variables.

Next, I had to tell the button what to do when it is clicked. So, I added the line:

$button1.add_Click({Get-LEEvents})

So, now the entire code block for the button looks like this:

#
# 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
$button1.add_Click({Get-LEEvents})

Login Enterprise Timestamps in UTC

One of the downsides of querying the LE SQL database rather than using the API is that all of the timestamps are recorded in UTC, where the API converts it to you local timezone. So, after Googling a bit, I found this article on StackOverflow which details on how to do this.

The key code is:

AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'

Note: As the article states, you can view the full list of timezone names by executing the following query:

SELECT * FROM sys.time_zone_info

So, now the SQL query for the LE Event Viewer looks like this:

$global:Eventquery="SELECT FORMAT (LoginEnterprise.Events.Created AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time',  '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;"

The code for the full script now looks like this:

################################################################################ 
#
#  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
}

# Setup variables for LE Databases
$global:BSQLServer = "RNBSVSI-SQL01"
$global:db1 = "RNBSVSI-APL01"
$global:db2 = "RNBSVSI-APL02"
$global:db3 = "RNBSVSI-APL03"
$global:db4 = "RNBSVSI-APL04"

$global:VSQLServer = "RNVSVSI-SQL01"
$global:dbV = "RNVSVSI-APL01"

$global:GSQLServer = "RNGSVSI-SQL01"
$global:dbG = "RNGSVSI-APL01"
$global:Eventquery="SELECT FORMAT (LoginEnterprise.Events.Created AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time',  '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;"

$ScriptPath = Get-ScriptDirectory

Function Get-LEEvents {

$Events1 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db1 -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$Events1 | Add-Member NoteProperty "Pod" -Value "Pod1"
$Events2 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db2 -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$Events2 | Add-Member NoteProperty "Pod" -Value "Pod2"
$Events3 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db3 -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$Events3 | Add-Member NoteProperty "Pod" -Value "Pod3"
$Events4 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db4 -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$Events4 | Add-Member NoteProperty "Pod" -Value "Pod4"
$EventsV = Invoke-Sqlcmd -ServerInstance $global:VSQLServer -Database $global:dbV -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$EventsV | Add-Member NoteProperty "Pod" -Value "PodV"
$EventsG = Invoke-Sqlcmd -ServerInstance $global:GSQLServer -Database $global:dbG -Query $global:Eventquery -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$EventsG | Add-Member NoteProperty "Pod" -Value "PodG"

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


#$AllTests = Import-CSV "C:\Users\Cl002309\OneDrive - Rightworks\Documents\Projects\StatusBoard\DisabledTests.csv"
$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)}
}


# 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(444, 37)
$label1.TabIndex = 0
$label1.Text = "Login Enterprise Event Viewer"
#
# 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

$dataGridView1.Location = New-Object System.Drawing.Point(12, 93)
$dataGridView1.Name = "dataGridView1"
$dataGridView1.RowHeadersVisible = $false
$dataGridView1.Size = New-Object System.Drawing.Size(1228, 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
$button1.add_Click({Get-LEEvents})
#
# 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(1253, 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} )




Get-LEEvents


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

Login Enterprise Continuous Tests’ Event Viewer – Part 2

Refresh button

Login Enterprise Timestamps in UTC

Leave a ReplyCancel reply

Refresh button

Login Enterprise Timestamps in UTC

Related Posts

Leave a ReplyCancel reply