<# .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 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 "## --- Requires 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;" $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)" } } } # Ending $connection.Close()