82 lines
3.5 KiB
PowerShell
82 lines
3.5 KiB
PowerShell
# Drucklog_MySQL.ps1
|
|
# Install-Module MySQLCmdlets
|
|
# Nyomtatási napló export MySQL adatbázisba
|
|
# Aufgabeplannung: Taglich um 8.00 Uhr
|
|
# Name: Drucklog_MySQL_0.1.ps1
|
|
# powershell.exe -ExecutionPolicy Bypass -File "-ExecutionPolicy Bypass -File "C:\Tools\drucklog_export_0.1.ps1"
|
|
|
|
# Beállítások
|
|
$logName = "Microsoft-Windows-PrintService/Operational"
|
|
$mysqlHost = "10.101.0.82"
|
|
$mysqlUser = "druckloguser"
|
|
$mysqlPassword = "Test123#"
|
|
$mysqlDatabase = "drucklog"
|
|
$quelleServer = $env:COMPUTERNAME # vagy: "Szerver01"
|
|
|
|
# MySQL .NET csomag betöltése (előzetesen szükséges: MySql.Data.dll)
|
|
# Add-Type -Path "C:\Tools\MySql.Data.dll"
|
|
# Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector NET 9.4\MySql.Data.dll"
|
|
[Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector NET 9.4\MySql.Data.dll") | Out-Null
|
|
|
|
# Kapcsolódás
|
|
$connectionString = "server=$mysqlHost;user id=$mysqlUser;password=$mysqlPassword;database=$mysqlDatabase;SslMode=none"
|
|
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
|
|
$connection.Open()
|
|
|
|
# Lekérés a logból
|
|
$events = Get-WinEvent -LogName $logName -ErrorAction SilentlyContinue | Where-Object { $_.Id -eq 307 }
|
|
|
|
# Kulturális beállítás a heti számításhoz
|
|
$calendar = [System.Globalization.CultureInfo]::CurrentCulture.Calendar
|
|
$weekRule = [System.Globalization.CalendarWeekRule]::FirstFourDayWeek
|
|
$firstDay = [System.DayOfWeek]::Monday
|
|
|
|
$ujBejegyzes = 0
|
|
|
|
foreach ($event in $events) {
|
|
$msg = $event.Message
|
|
|
|
if ($msg -match "im Besitz von (.+?) auf (.+?) wurde auf (.+?) über Port") {
|
|
$benutzer = $matches[1]
|
|
$computer = $matches[2]
|
|
$drucker = $matches[3]
|
|
} else {
|
|
continue
|
|
}
|
|
|
|
$dokument = if ($msg -match "Dokument (.+?), Dokument drucken") { $matches[1] } else { "Unbekannt" }
|
|
$seiten = if ($msg -match "Gedruckte Seiten: (\d+)") { [int]$matches[1] } else { 0 }
|
|
$woche = $calendar.GetWeekOfYear($event.TimeCreated, $weekRule, $firstDay)
|
|
$datum = $event.TimeCreated.ToString("yyyy-MM-dd HH:mm:ss")
|
|
$tag = $event.TimeCreated.ToString("yyyy-MM-dd")
|
|
$id = $event.RecordId
|
|
|
|
# Duplikáció ellenőrzés
|
|
$checkCmd = $connection.CreateCommand()
|
|
$checkCmd.CommandText = "SELECT COUNT(*) FROM drucklog WHERE event_id = @id AND quelle_server = @qs"
|
|
$checkCmd.Parameters.AddWithValue("@id", $id)
|
|
$checkCmd.Parameters.AddWithValue("@qs", $quelleServer)
|
|
$exists = $checkCmd.ExecuteScalar()
|
|
|
|
if ($exists -eq 0) {
|
|
$insertCmd = $connection.CreateCommand()
|
|
$insertCmd.CommandText = "INSERT INTO drucklog (event_id, datum, tag, woche, benutzer, computer, dokument, drucker, seiten, quelle_server)
|
|
VALUES (@id, @datum, @tag, @woche, @benutzer, @computer, @dokument, @drucker, @seiten, @qs)"
|
|
$insertCmd.Parameters.AddWithValue("@id", $id)
|
|
$insertCmd.Parameters.AddWithValue("@datum", $datum)
|
|
$insertCmd.Parameters.AddWithValue("@tag", $tag)
|
|
$insertCmd.Parameters.AddWithValue("@woche", $woche)
|
|
$insertCmd.Parameters.AddWithValue("@benutzer", $benutzer)
|
|
$insertCmd.Parameters.AddWithValue("@computer", $computer)
|
|
$insertCmd.Parameters.AddWithValue("@dokument", $dokument)
|
|
$insertCmd.Parameters.AddWithValue("@drucker", $drucker)
|
|
$insertCmd.Parameters.AddWithValue("@seiten", $seiten)
|
|
$insertCmd.Parameters.AddWithValue("@qs", $quelleServer)
|
|
$insertCmd.ExecuteNonQuery()
|
|
$ujBejegyzes++
|
|
}
|
|
}
|
|
|
|
$connection.Close()
|
|
Write-Host "Feldolgozott események: $($events.Count) | Új bejegyzés: $ujBejegyzes"
|