<# .NOTES =========================================================================== Created with: SAPIEN Technologies, Inc., PowerShell Studio 2015 v4.2.81 Created on: 28.08.2017 13:39 Created by: Constantin Lotz Organization: Druckzentrum Rhein Main GmbH & Co. KG Filename: VMWareLogonMonitor2MSSQL.ps1 =========================================================================== .DESCRIPTION Die Software liest die Log-Dateien des VMware Login Monitor aus und trägt die relevanten Daten in eine SQL-Datenbank This software reads out the logfiles produced by VMware Logon Monitor which is included in VMware Horizon (View) 7.2 and pushs them into an Microsoft SQL Database .CHANGELOG 28.02.2018 # Switch onlyTextOutput does not work when Database connection is invalid. Skipped the connection startup when the switch is set # Thanks to Richard The needed MSSQL Schema for storing Data: ### Start ### /****** Object: Table [dbo].[VmwareLogonMonitorStats] Script Date: 08/29/2017 15:46:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[VmwareLogonMonitorStats]( [UniqueID] [varchar](100) NOT NULL, [LogonTimeSec] [float] NOT NULL, [ProfileSyncTimeSec] [float] NULL, [TotalLogonScriptSec] [float] NULL, [MachinePolicyApplySec] [float] NULL, [LogonStartHiveSec] [float] NULL, [LogonStartClassesHiveSec] [float] NULL, [FreeDiskSpaceGB] [int] NULL, [ShellLoadTimeSec] [float] NULL, [GPOSoftwareInstallSec] [float] NULL, [UserPolicyApplySec] [float] NULL, [WindowsFolderRedirectionSec] [float] NULL, [LogonDateTime] [datetime] NULL, [UserName] [varchar](50) NULL, [ProfileSizeMB] [float] NULL, [ProfileSizeFiles] [int] NULL, [ProfileSizeFolders] [int] NULL, CONSTRAINT [PK_VmwareLogonMonitorStats] PRIMARY KEY CLUSTERED ( [UniqueID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ### End ### #> param ( [Parameter(Position = 0, ValueFromPipeline = $true)] [String]$logonMonitorFolder, [Switch]$onlyTextOutput ) # Display Help if (!$logonMonitorFolder) { Write-Host "######################################" Write-Host "## VMWareLogonMonitor2MSSQL.ps1" Write-Host "## Version: 0.1" Write-Host "## (c) Constantin Lotz" Write-Host "## www.constey.de | VLM@constey.de" Write-Host "######################################" Write-Host "## - Requieres Powershell v3 - Write-Host "## To Scan a complete folder with multiple Logfiles" Write-Host "## .\VMwareLogonMonitor2MSSQL.ps1 " Write-Host "## To Scan only one specific Logfile" Write-Host "## .\VMwareLogonMonitor2MSSQL.ps1 " Write-Host "## Change MSSQL Login Data within Script under 'Open SQL Connection'" Write-Host "## If you only want Textoutput without writing to MSSQL add -onlyTextOutput Parameter" Write-Host "########### exiting... ############" exit (0) } # Maybe not needed if ($logonMonitorFolder.EndsWith("\") -eq $true) { $logonMonitorFolder = $logonMonitorFolder + "*.txt" } elseif ($logonMonitorFolder.EndsWith(".txt") -eq $false) { $logonMonitorFolder = $logonMonitorFolder + "\*.txt" } # Parameters $logfile = $PSScriptRoot + "\VmwareLogonMonitor.log" $logtime = get-date -format 'dd.MM.yyyy;HH:mm:ss' $dataArray = New-Object System.Collections.ArrayList # Open SQL Connection $dataSource = "hostname" # Hostname or IP-Adress $user = "sql-username" # SQL Username $pwd = "password" # SQL Password $database = "Databasename" # Database name $connectionString = "Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;" if ($onlyTextOutput -eq $true) { # Do not connect when onlyTextOutput is set } else { $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() } # Logging Function function Write-Feedback() { param ( [Parameter(Position = 0, ValueFromPipeline = $true)] [string]$msg ) Write-Host $msg; $msg | Out-File $logfile -Append } # Regex Grabbing the Logfile function ExtractDataFromLogfile () { param ( [Parameter(Position = 0, ValueFromPipeline = $true)] [string]$logonfile ) # Check if File exists if (Test-Path $logonfile) { # Check if File is from VMware Logon Monitor $content = Get-Content $logonfile | select-String -Pattern "VMWLogSettings" if ($content.length -gt 0) { $uniqueID = gci $logonfile | % Name $timeInSeconds = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Logon Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $username = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Session Summary \(User: (.*)," | ForEach-Object { $_.Matches.Groups[1].Value } $computername = Select-String -path $logonfile -Pattern "\[VMWLogSettings\] Computer Name: (.*)" | ForEach-Object { $_.Matches.Groups[1].Value } $logonStartHive = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Logon Start To Hive Loaded Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $logonStartClassesHive = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Logon Start To Classes Hive Loaded Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $profileSyncTime = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Profile Sync Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $windowsFolderRedirection = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Windows Folder Redirection Apply Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $shellLoadTime = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Shell Load Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $totalLogonScript = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Total Logon Script Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $userPolicyApply = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] User Policy Apply Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $machinePolicyApply = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Machine Policy Apply Time:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $gpoSoftwareInstall = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Group Policy Software Install Time: \s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $freeDiskSpace = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogSummary\] Free Disk Space Available To User:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) $logonDateTime = Select-String -path $logonfile -Pattern "([^\s]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | Select-Object -first 1 $profileSize = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogProfileSize\] Profile Size:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % ToString([cultureinfo]::GetCultureInfo('en-US')) [int]$profileSizeFiles = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogProfileSize\] Profile Size: .* Files:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } [int]$profileSizeFolders = Select-String -path $logonfile -Pattern "\[LogonMonitor::LogProfileSize\] Profile Size: .* Files: .* Folders:\s*([\d|.]+)" | ForEach-Object { $_.Matches.Groups[1].Value } | % trim (".") $values = @{ "UniqueID" = $uniqueID; "LogonDateTime" = $logonDateTime; "LogonTime" = $timeInSeconds; "UserName" = $username; "ComputerName" = $computername; "LogonStartHive" = $logonStartHive; "LogonStartClassesHive" = $logonStartClassesHive; "ProfileSyncTime" = $profileSyncTime; "WindowsFolderRedirection" = $windowsFolderRedirection; "ShellLoadTime" = $shellLoadTime; "TotalLogonScript" = $totalLogonScript; "UserPolicyApply" = $userPolicyApply; "MachinePolicyApply" = $machinePolicyApply; "GPOSoftwareInstall" = $gpoSoftwareInstall; "FreeDiskSpace" = $freeDiskSpace; "ProfileSize" = $profileSize; "ProfileSizeFiles" = $profileSizeFiles; "ProfileSizeFolders" = $profileSizeFolders; } # Write to Variable for no text output $temp = $dataArray.Add($values) } } else { # File does not exist Write-Feedback "$logtime - Fehler: Datei existiert nicht. - Datei: $($logonfile)" } } # Check if runs for a folder or only for a File if (($logonMonitorFolder.EndsWith(".txt") -eq $true) -and ($logonMonitorFolder.EndsWith("*.txt") -eq $false)) { ExtractDataFromLogfile $logonMonitorFolder } else { foreach ($file in Get-ChildItem $logonMonitorFolder) { ExtractDataFromLogfile $file } } if ($onlyTextOutput -eq $true) { # Just Output the Data $dataArray } else { # Push Data into MSSQL Database foreach ($entry in $dataArray) { # #Write to SQL $cmd = New-Object System.Data.SqlClient.SqlCommand $cmd.connection = $connection try { $cmd.commandtext = "INSERT INTO dbo.VmwareLogonMonitorStats (UniqueID,LogonDateTime,UserName,LogonTimeSec,ProfileSyncTimeSec,TotalLogonScriptSec,MachinePolicyApplySec,LogonStartHiveSec,LogonStartClassesHiveSec,FreeDiskSpaceGB,ShellLoadTimeSec,GPOSoftwareInstallSec,UserPolicyApplySec,WindowsFolderRedirectionSec,ProfileSizeMB,ProfileSizeFiles,ProfileSizeFolders) VALUES('{0}','{1}','{2}',{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16})" -f $entry.UniqueID, $entry.LogonDateTime, $entry.UserName, $entry.LogonTime, $entry.ProfileSyncTime, $entry.TotalLogonScript, $entry.MachinePolicyApply, $entry.LogonStartHive, $entry.LogonStartClassesHive, $entry.FreeDiskSpace, $entry.ShellLoadTime, $entry.GPOSoftwareInstall, $entry.UserPolicyApply, $entry.WindowsFolderRedirection, $entry.ProfileSize, $entry.ProfileSizeFiles, $entry.ProfileSizeFolders # Write-Host $cmd.CommandText $result = $cmd.executenonquery() if ($result -eq 1) { Write-Feedback "$logtime - Info: Die Meldung: $($entry.UniqueID) - wurde eingetragen." } } catch { Write-Feedback "$logtime - Fehler: $($entry.UniqueID)" # More detailed #Write-Feedback "$logtime - Fehler: $($cmd.commandtext) - Datei: $($error)" } } } # Closing open Database connections if ($onlyTextOutput -eq $true) { # Do not close when onlyTextOutput is set } else { $connection.Close() } # End