In my last post, I discussed a script that I wrote that pulls the Login Timers into an Excel spreadsheet. Well, when I ran this script it was VERY slow. For one pod, it took about 16 hours to process 1 day’s worth of logins. The inital pull from SQL wasn’t all that long. The delay was mostly when I ‘transformed’ the data, placing the different timers into colums in the same row.
So, I took a look on the web and realized that I should/could do this with the PIVOT function, either in Excel or in SQL. In all honesty, I have struggled with creating Pivot tables in Excel for many years, as I just don’t quite grasp the mechanics of it. So, I decided to try and perform the pivot in SQL.
Not that using SQL for the pivot was that straightforward, thought it seemed like it was. After reading many articles, watching YouTube tutorials and just trial and error, I found a statement that worked. And the best thing is that I can process 7 days of data for all 6 pods in about 30 minutes!
SQL Query
First, let’s look at the SQL query with the Pivot function.
As a reminder, the SQL query I used in the previous version was:
select LoginEnterprise.Measurements.UserSessionId,LoginEnterprise.Measurements.Timestamp,FORMAT (LoginEnterprise.Measurements.Timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 'MM/dd/yyyy, hh:mm:ss tt') as Timestamp1,LoginEnterprise.Measurements.MeasurementID,LoginEnterprise.Measurements.Duration/1000 as Duration,LoginEnterprise.UserSessions.Target,LoginEnterprise.UserSessions.LauncherName,Management.Accounts.Username
from LoginEnterprise.Measurements
Left JOIN LoginEnterprise.UserSessions
ON LoginEnterprise.Measurements.UserSessionId = LoginEnterprise.UserSessions.UserSessionId
Left JOIN Management.Accounts
ON LoginEnterprise.UserSessions.AccountId = Management.Accounts.[Key]
WHERE LoginEnterprise.Measurements.Timestamp >= DATEADD(n,-60,getutcdate()) AND (MeasurementId IN ('connection') OR MeasurementId IN ('group_policies') OR MeasurementId IN ('user_profile') OR MeasurementId IN ('total_login_time'))
Order By Timestamp Desc, UserSessionId;This produced a dataset like this:
To perform the pivot, we use the above query as the source for the pivot query. So, we wind up with code like this:
Select * From
(
select LoginEnterprise.Measurements.UserSessionId,LoginEnterprise.Measurements.Timestamp,FORMAT (LoginEnterprise.Measurements.Timestamp AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Eastern Standard Time’, ‘MM/dd/yyyy, hh:mm:ss tt’) as Timestamp1,LoginEnterprise.Measurements.MeasurementID,LoginEnterprise.Measurements.Duration/1000 as Duration,LoginEnterprise.UserSessions.Target,LoginEnterprise.UserSessions.LauncherName,Management.Accounts.Username
from LoginEnterprise.Measurements
Left JOIN LoginEnterprise.UserSessions
ON LoginEnterprise.Measurements.UserSessionId = LoginEnterprise.UserSessions.UserSessionId
Left JOIN Management.Accounts
ON LoginEnterprise.UserSessions.AccountId = Management.Accounts.[Key]
WHERE LoginEnterprise.Measurements.Timestamp >= DATEADD(d,-7,getutcdate()) AND (MeasurementId IN (‘connection’) OR MeasurementId IN (‘group_policies’) OR MeasurementId IN (‘user_profile’) OR MeasurementId IN (‘total_login_time’))
) t
PIVOT
(
Max(Duration)
For MeasurementID in ([connection],[group_policies],[user_profile],[total_login_time])
) p
So, from the dataset returned from the source query, we select all columns and rows (Select *). Then, we pivot on the values in the MeasurementID column, creating a new column for each of the measurements, connection, group_policies, user_profile, total_login_time.
NOTE: As mentioned, it took me a long time to get this to work. The bit that I was missing was the ‘t’ and ‘p’ after the Source Query and the Pivot. None of the articles that I read or videos that I watched explained why these were there. One of my colleagues explained that it applies an alias that can be referenced.
So, now that I have the new query, this simplies the Powershell code itself.
Powershell
First, as I still want to output this to an Excel workbook and I no longer needed to address each row of data, I decided to use the Export-Excel function. This is not a built-in cmdlet, but is included in the Import-Excel module. You can find the GIT page on it at GitHub – dfinke/ImportExcel: PowerShell module to import/export Excel spreadsheets, without Excel. Thus, I no longer need to include the Excel COM object in the Powershell script directly.
So, the beginning of the script now looks like this, which sets the variables for performing the SQL query.:
$global:BSQLServer = "<SQL server>"
$global:db1 = "<database name>"
$global:queryLoginTimers = "Select * From
(
select LoginEnterprise.Measurements.UserSessionId,LoginEnterprise.Measurements.Timestamp,FORMAT (LoginEnterprise.Measurements.Timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 'MM/dd/yyyy, hh:mm:ss tt') as Timestamp1,LoginEnterprise.Measurements.MeasurementID,LoginEnterprise.Measurements.Duration/1000 as Duration,LoginEnterprise.UserSessions.Target,LoginEnterprise.UserSessions.LauncherName,Management.Accounts.Username
from LoginEnterprise.Measurements
Left JOIN LoginEnterprise.UserSessions
ON LoginEnterprise.Measurements.UserSessionId = LoginEnterprise.UserSessions.UserSessionId
Left JOIN Management.Accounts
ON LoginEnterprise.UserSessions.AccountId = Management.Accounts.[Key]
WHERE LoginEnterprise.Measurements.Timestamp >= DATEADD(d,-7,getutcdate()) AND (MeasurementId IN ('connection') OR MeasurementId IN ('group_policies') OR MeasurementId IN ('user_profile') OR MeasurementId IN ('total_login_time'))
) t
PIVOT
(
Max(Duration)
For MeasurementID in ([connection],[group_policies],[user_profile],[total_login_time])
) p"
For each pod, I run the SQL query and place the results in the $timers array. Then I select the properties that I want to add to the Excel workbook and place them in the $logintimers array. And finally, I export these to the Excel workbook, LoginTimers.xlsx, and in the worksheet, Pod1, since I’m pulling from six pods.
#===========================
# Pod 1
#===========================
$Timers1 = Invoke-Sqlcmd -ServerInstance $global:BSQLServer -Database $global:db1 -Query $global:queryLoginTimers -Username "srv_sql_loginvsi" -Password "ux(#M7iD2rgjPAbE)X2B" -Verbose -TrustServerCertificate
$LoginTimers1 = $Timers1 | Select-Object -Property Timestamp1, Target, LauncherName, Username, connection, group_policies, user_profile, total_login_time
$LoginTimers1 | Export-Excel LoginTimers.xlsx -WorksheetName "Pod1"Rinse and repeat for the six pods.
Now, I have a workbook that looks like this:
So, for troubleshooting why we see some long login times, I can sort this data by Time, Account, Target or Launcher. We can also compare the different pods. As I start filtering this data and creating charts, I’l post on how we do that and create meaningful charts for visualization and comparison.