In my career, relying on good logging has been key to validating solutions are working, but more importantly when trying to troubleshoot issues. With a complex system like Login Enterprise, there are a number of places to look at logs, the appliance, the launchers, the targets, etc.
I’m intending this post to be the first in a series of how we use the event logs at Rightworks.
To add a little color and give some background on what has brought us to this point. In the past 2 months, we have been overwhelmed with the number of errors we have been receiving. Keep in mind that we are running 3 separate LE environments, one running 822 Continuous Tests, one running 316 Continuous Tests and one running 133 Continuous Tests. In addition, we are running the Windows Event Logger tool against each appliance that pulls the events so that we could bring them into Splunk. Additionally, we also have a service that is pulling the measurements into an Azure database which is used with PowerBI for our management reports.
The point in telling you all of that is that our appliances are BUSY, especially the one running 822 tests. So, when we try to look at the Events in the GUI, we may be waiting 10-15 minutes for it to paint the screen. Many times, the GUI logs us out before the screen paints.
So, we turned to the API to pull the events using a Powershell script. This was working for the most part until the time 2 months ago when we started having the plethora of errors. BTW, these events were everything from Login Failures (with a number of different descriptions) Session Failures, Connection Initialization Failures, etc. This was on top of the normal events that we received “Unable to find a process/screen/what have you from our scripts. Add to that, our applications scripts are also logging events including validations of drive mappings, etc.
So, there is a huge amount of data to churn through. So much so, that the API was giving up on as and we receive 504 Gateway Timeout errors.
So, we decided to find another approach. Again, we turned to the SQL database, which is discouraged by Login VSI for a number of (valid) reasons. But, we really had no other recourse.
The Microsoft SQL Database
I have not tried this with the built-in Postgres database. As you can see, our environment is way to large to use that and all of our environments have and will have an External SQL Database.
DISCLAIMER: I am by no means a SQL expert. Most of what of what I am sharing has been trial and error with a lot of collaboration with my teammates, LVSI support/Dev and some of my friends who are SQL experts. But, it’s been a great education!
Looking at the database is quite daunting, especially for a noob like me. I will dive deeper into my approach in another article and for now, will just focus on the data I wanted to retrieve and the script that we came up with.
There are a lot of tables in the database and they seem to be grouped in a few different groups. (not sure if that’s the right terms). You’ll see the following groups:
- Accounts
- Applications
- ContentDelivery
- Environments
- Launchers
- LoadTests
- LoginEnterprise
- Management
- RawDataExports
- Reports
- SessionMetrics
- Statistics
- SystemConfigurations
The Data
As we were focusing on retrieving the Events, we went to the LoginEnterprise.Events table, which has the following columns:
As you can see, some of the valuable data is actually referenced by and ID, such as the User and Application. These will require looking up that value in another table. (The joys of relational databases!). This requires using JOINs in the SQL query that retrieves the data.
I’m going to jump into the Powershell code and then break down the login the SQL query.
The Powershell Script
Again, I am still a noob when it comes to writing Powershell scripts and I have had a lot of guidance, so of which I follow and others I don’t, either because I have a hard time wrapping my head around things, or I’m just stubborn. Either, take what you want from this and make it better. Comments are always welcome!
This, it turns out, is a fairly simple script (as compared to my API scripts), as most of the logic is being done in the SQL query. With all of my reporting scripts, I create the $report object that holds the name of the output file. This includes the data/time stamp in the file name.
$timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }
$report="./Events-East-$timestamp.csv"Next, I create objects for the Invoke-SQLcmd cmdlet parameters. These include $SQLServer, $db1 and $qcd. The first two are obvious and $qcd contains the SQL query as a string. $SQLuser and $SQLpwd hold the credentials used to connect to the DB. We are using SQL Authentication and the same credentials configured in the Login Enterprise.
$SQLServer = "SQLServerl"
$db1 = "LoginEnterprise"
$SQLUser = "<username>"
$SQLpwd = "<passsword>"
$qcd = "SELECT TOP 1000 Events.Created,Events.Title,LoginEnterprise.EventProperties.Value,Management.Applications.Name,LoginEnterprise.UserSessions.LauncherName,Management.Accounts.Username,LoginEnterprise.UserSessions.Target
FROM LoginEnterprise.Events
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.EventType <> 11
Order By LoginEnterprise.Events.Created Desc;"If you are conversant in SQL, you can probably make sense of this fairly easily. For others, I’ll break it down.
The SELECT TOP 1000 says that we are want to retrieve the first 1000 records from the dataset being retrieved. This is combined with the last command ORDER BY to ensure that we are pulling the most recent 1000 events.
The SELECT command also indicates with columns of data to retrieve from the table(s) specified in the FROM command. You might be used to seeing this as SELECT * FROM which retrieves all columns of data. As there are a lot of extraneous data that I don’t need (at this point), I am specifying to retrieve the following data:
- Events.Created
- Events.Title
- LoginEnterprise.EventProperties.Value
- Management.Applications.Name
- LoginEnterprise.UserSessions.LauncherName
- Management.Accounts.Username
- LoginEnterprise.UserSessions.Target
But, as these data are in various tables, we need to use JOINs. These tell SQL how each table relates to each other.
The primary table that we want to retrieve is LoginEnterprise.Events. Thus, the command:
FROM LoginEnterprise.EventsThen we specify a JOIN to retrieve the corresponding data from the LoginEnterprise.EventProperties table. The data set to match is the EventID. So, in other words, when SQL retrieves a record from LoginEnterprise.Events, it’ll lookup up the record in LoginEnterprise.EventProperties that matches the EventID.
LEFT JOIN LoginEnterprise.EventProperties
ON LoginEnterprise.Events.EventId = LoginEnterprise.EventProperties.EventIdWe do a JOIN for each table that we need to retrieve data from, UserName, LauncherName, ApplicationName.
The WHERE command is used to filter (or this case, filter out) the events that we do not want. Event Type 11 is Script Events, so this will not retrieve events of that type. This eliminates a lot of the noise when combing through the logs.
WHERE LoginEnterprise.Events.EventType <> 11Finally, the ORDER BY command ensures that we are retrieving the most recent records by specifying the Created column and ordering it in Descending order.
Order By LoginEnterprise.Events.Created Desc;"The last two lines run the SQL query and place it into the $Events object and then export that array to a CSV file using the $Reports object for the file name.
$events = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Username $SQLuser -Password $SQLpwd -Verbose
$events | Export-CSV $reportHere is the complete code:
$timestamp = Get-Date -Format o | ForEach-Object { $_ -replace ":", "." }
$report="./Events-$timestamp.csv"
$SQLServer = "SQLServerl"
$db1 = "LoginEnterprise"
$qcd = "SELECT TOP 1000 Events.Created,Events.Title,LoginEnterprise.EventProperties.Value,Management.Applications.Name,LoginEnterprise.UserSessions.LauncherName,Management.Accounts.Username,LoginEnterprise.UserSessions.Target
FROM LoginEnterprise.Events
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.EventType <> 11
Order By LoginEnterprise.Events.Created Desc;"
$events = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db1 -Query $qcd -Username $SQLuser -Password $SQLpwd -Verbose
$events | Export-CSV $reportThis is what satisfied our needs at this time. The script runs much faster than using the API, as the Appliance is removed from the data path. Also, we are no longer limited to 10,000 items retrieved that the API imposes (even though we overcame that using the Offset parameter).
Again, this is not a recommended way, but it has greatly improved our ability to troubleshoot.
In subsequent posts, I plan to show you how we discovered the table relationships (and the pain it was before we figured out how to do that), and some scripts that we are working on now to gather all the logs into one place, including Launcher and engine logs as well as Windows Event logs from the launcher and that target.
As always feedback is welcome. Let me know if you find this helpful, hate it or just want to make fun of me.