Аудит печати

```powershell

<#
icinga2scripts
Version 1.0
Description: Скрипт для Icinga 2 - Сбор логов заданий печати

Pavel Satin (c) 2017
pslater.ru@gmail.com
#>
#[Console]::OutputEncoding = [System.Text.Encoding]::UTF8

$returnStateOK = 0
$returnStateWarning = 1
$returnStateCritical = 2
$returnStateUnknown = 3

#$ErrorActionPreference = "SilentlyContinue"

$connString = "Server=192.168.0.209;Uid=dbuser;Pwd=password;database=printaudit;charset=utf8"

$connString0 = "Server=192.168.0.209;Uid=dbuser;Pwd=password;database=inventory;charset=utf8"

$recordCount = 0


#Проверка аргументов
# 0 - Сервер печати

if ( $args[0] -ne $Null) {
    $ComputerName = $args[0]
} else {
    $ComputerName = "localhost"
}

# 1 - За сколько последних часов сделать выборку (с начала часа до конца часа периода)
if ( $args[1] -ne $Null) {
    $QrHours = $args[1]
} else {
    $QrHours = 1
}



#Функция выполнения MySQL запроса
function run-MySQLQuery {
    Param(
        [Parameter(
            Mandatory = $true,
            ParameterSetName = '',
            ValueFromPipeline = $true)]
            [string]$query,
        [Parameter(
            Mandatory = $true,
            ParameterSetName = '',
            ValueFromPipeline = $true)]
            [string]$connectionString
        )

        try {
            # load MySQL driver and create connection
            Write-Verbose "Create Database Connection"
            # You could also could use a direct Link to the DLL File
            $mySQLDataDLL = "C:\ProgramData\icinga2\Scripts\icinga2\MySQL.Data.dll"
            [void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
            #[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
            $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
            $connection.ConnectionString = $ConnectionString
            Write-Verbose "Open Database Connection"
            $connection.Open()

            # Run MySQL Querys
            Write-Verbose "Run MySQL Querys"
            $command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connection)
            $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
            $dataSet = New-Object System.Data.DataSet
            $recordCount = $dataAdapter.Fill($dataSet, "data")
            $dataSet.Tables["data"] # | Format-Table
        }
        catch {
            Write-Host "Could not run MySQL Query" $Error[0]
        }
        Finally {
            Write-Verbose "Close Connection"
            $connection.Close()
        }

} #Конец функции Run-MySQLQuery


#########################################################################################
#Замер времени исполнения скрипта
$watch = [System.Diagnostics.Stopwatch]::StartNew()
$watch.Start() #Запуск таймера

$StartDate_hour = [datetime]::Now.AddHours(-$QrHours)
$StartDate_hour = $StartDate_hour.AddMinutes(-$StartDate_hour.Minute)
$StartDate_hour = $StartDate_hour.AddSeconds(-$StartDate_hour.Second)


$StartDate = $StartDate_hour
$EndDate = $StartDate_hour.AddHours($QrHours)

# the main print job entries are event ID 307 (use "-ErrorAction SilentlyContinue" to handle the case where no event log messages are found)
$PrintEntries = Get-WinEvent -ErrorAction SilentlyContinue -ComputerName $ComputerName -FilterHashTable @{ProviderName="Microsoft-Windows-PrintService"; StartTime=$StartDate; EndTime=$EndDate; ID=307}
# the by-job number of copies are in event ID 805 (use "-ErrorAction SilentlyContinue" to handle the case where no event log messages are found)
$PrintEntriesNumberofCopies = Get-WinEvent -ErrorAction SilentlyContinue -ComputerName $ComputerName -FilterHashTable @{ProviderName="Microsoft-Windows-PrintService"; StartTime=$StartDate; EndTime=$EndDate; ID=805}






#####
# loop to parse ID 307 event log entries

ForEach ($PrintEntry in $PrintEntries) {

    # retreive the remaining fields from the event log contents
    $entry = [xml]$PrintEntry.ToXml()

    $ComputerTargetName = $entry.Event.UserData.DocumentPrinted.Param4

    #Write-Host $ComputerTargetName

    if ($ComputerTargetName.StartsWith("\\")) {
        $ComputerTargetName = $ComputerTargetName.Substring(2)
        $ComputerTargetName = $ComputerTargetName.ToLower()
        $ComputerTargetName = $ComputerTargetName + "." + (Get-WmiObject win32_computersystem).Domain
    } else {
        $ComputerTargetName = $ComputerTargetName.ToLower()
        $ComputerTargetName = $ComputerTargetName + "." + (Get-WmiObject win32_computersystem).Domain
    }


    #Write-Host $ComputerTargetName


    $sQLquery = "SELECT Name, ComputerTargetId FROM tbComputerTarget WHERE Name='$ComputerTargetName'"
    $rQLquery = run-MySQLQuery -connectionString $connString0 -query $sQLquery



    # get date and time of printjob from TimeCreated
    $StartDate_Time = $PrintEntry.TimeCreated
    ## retreive the remaining fields from the event log contents
    #$entry = [xml]$PrintEntry.ToXml()
    $PrintJobId = $entry.Event.UserData.DocumentPrinted.Param1
    $DocumentName = $entry.Event.UserData.DocumentPrinted.Param2
    $UserName = $entry.Event.UserData.DocumentPrinted.Param3
    $ClientPCName = $rQLquery.ComputerTargetId
    $PrinterName = $entry.Event.UserData.DocumentPrinted.Param5
    $PrinterPort = $entry.Event.UserData.DocumentPrinted.Param6
    $PrintSizeBytes = $entry.Event.UserData.DocumentPrinted.Param7
    $PrintPagesOneCopy = $entry.Event.UserData.DocumentPrinted.Param8

    # get the user's full name from Active Directory
    if ($UserName -gt "") {
        $DirectorySearcher = New-Object System.DirectoryServices.DirectorySearcher
        $LdapFilter = "(&(objectClass=user)(samAccountName=${UserName}))"
        $DirectorySearcher.Filter = $LdapFilter
        $UserEntry = [adsi]"$($DirectorySearcher.FindOne().Path)"
        $ADName = $UserEntry.displayName
    }

    # get the print job number of copies by correlating with event ID 805 records
    #  the ID 805 record always is logged immediately before (that is, earlier in time) its related 307 record
    #  the print job ID number restarts after a certain interval, so we need to check both for a matching job ID and a very close logging time (within 5 seconds) to its related event ID 307 record
    $PrintEntryNumberofCopies = $PrintEntriesNumberofCopies | Where-Object {$_.TimeCreated -le $StartDate_Time -and $_.TimeCreated -ge ($StartDate_Time - (New-Timespan -second 5)) -and $_.Message -eq "Подготовка задания $PrintJobId."}
    # check for found data and extract the number of copies if a matching event log ID 805 record was found
    if ($PrintEntryNumberofCopies) {
        # retrieve the remaining fields from the event log contents
        $entry = [xml]$PrintEntryNumberofCopies.ToXml()
        $NumberOfCopies = $entry.Event.UserData.RenderJobDiag.Copies
        # some flawed printer drivers always report 0 copies for every print job; output a warning so this can be investigated further and set copies to be 1 in this case as a guess of what the actual number of copies was
        if ($NumberOfCopies -eq 0) {
            $NumberOfCopies = 1
            $Message = "WARNING: Printer $PrinterName recorded that print job ID $PrintJobId was printed with 0 copies. This is probably a bug in the print driver. Upgrading or otherwise changing the print driver may help. Guessing that 1 copy of the job was printed and continuing on..."
            Write-Host $Message
            #Write-Output $Message | Out-File -FilePath $OutputFilenameByPrintJob -Encoding UTF8 -Append
        }
    }
    # if no matching event log ID 805 record was found, exit with errorlevel 1 -- a matching record should always be present
    else {
        $Message = "ERROR: There were no print job event ID 805 entries found in the specified time range from $StartDate to $EndDate that matched print job id $PrintJobId. A matching record should always be present. Exiting script."
        Write-Host $Message
        #Write-Output $Message | Out-File -FilePath $OutputFilenameByPrintJob -Encoding UTF8 -Append
        Exit 1
    }

    # calculate the total number of pages for the whole print job
    $TotalPages = [int]$PrintPagesOneCopy * [int]$NumberOfCopies


    $PrintEntryDateTime = ($PrintEntry.TimeCreated).ToString("yyyy-MM-dd HH:mm:ss")


    $sQLquery = "INSERT INTO tbPrintJob ( PrintJobDateTime, PrintJobId, UserLogin, UserName, ComputerTargetId, PrinterName, PrinterPort, DocumentName, PrintJobSize, PageCountForOneCopy, NumberOfCopies, TotalPages ) VALUES ( '$PrintEntryDateTime', '$PrintJobId', '$UserName', '$ADName', '$ClientPCName', '$PrinterName', '$PrinterPort', '$DocumentName', '$PrintSizeBytes', '$PrintPagesOneCopy', '$NumberOfCopies', '$TotalPages' )"

    #Write-Host $sQLquery

    $rQLquery = run-MySQLQuery -connectionString $connString -query $sQLquery

    $recordCount++

}









Write-Host "Inserted $recordCount entries in the database"
Write-Host "Request completed from $StartDate to $EndDate."
$watch.Stop() #Остановка таймера
Write-Host $watch.Elapsed #Время выполнения
Write-Host (Get-Date)

#[System.Environment]::Exit($returnStateOK)



```

```sql

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `tbPrintJob`;
CREATE TABLE `tbPrintJob` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `PrintJobDateTime` datetime NOT NULL,
  `PrintJobId` int(5) NOT NULL,
  `UserLogin` varchar(256) NOT NULL,
  `UserName` varchar(256) NOT NULL,
  `ComputerTargetId` varchar(256) NOT NULL,
  `PrinterName` varchar(256) NOT NULL,
  `PrinterPort` varchar(256) NOT NULL,
  `DocumentName` varchar(256) NOT NULL,
  `PrintJobSize` int(20) NOT NULL,
  `PageCountForOneCopy` int(5) NOT NULL,
  `NumberOfCopies` int(5) NOT NULL,
  `TotalPages` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



```