################################*******Notes*******##################################
#                               
# This script writes the the logs from a print server to a SQL database, specifically event ID 307
# It takes the message part of the logged Windows event and parses it into a table within a SQL database
# You can then create reports off from Job size to who is printing...
#
# I used the following as two websites as a base for this script
# https://rcmtech.wordpress.com/2015/12/04/send-windows-event-logs-to-sql-database/
# https://blogs.technet.microsoft.com/ashleymcglone/2013/08/28/powershell-get-winevent-xml-madness-getting-details-from-event-logs/
#
# 
################################********SQL********##################################
#
# Before you can run this script you will need a SQL database, with the table created
#
#
################################*****Database******##################################
#
#USE [master]
#GO
# 
#CREATE DATABASE [EventCollection]
#
# ON  PRIMARY
#( NAME = N'EventCollection', FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\EventCollection.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
# LOG ON
#( NAME = N'EventCollection_log', FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\EventCollection_log.ldf' , FILEGROWTH = 10%)
#GO
# 
#ALTER DATABASE [EventCollection] SET COMPATIBILITY_LEVEL = 100
#GO
# 
#IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
#begin
#EXEC [EventCollection].[dbo].[sp_fulltext_database] @action = 'enable'
#end
#GO 
#
#################################***Permissions****##################################
#
# Grant the computers group in Active directory the following permissions to the database
#    Insert
#    Select
#
# SQL Management Studio
#    Security -> Logins -> New Login -> Search -> Object Type (Group) / Location (Domain) -> Check Name (Domain Computers) -> Default Database (EventCollection)
#    Database -> Security -> Logins -> Right Click (Domain\Domain Computers) -> Properties -> Securables -> Search (All Object Types...) -> Tables (Insert, Select)
#
################################*****Database******##################################
#
################################*******Table*******##################################
#
#USE [EventCollection]
#GO
#
#/* Microsoft-Windows-PrintService/Operational */
#CREATE TABLE [dbo].[MicrosoftWindowsPrintService_Operational](
#    /*[TimeCreated] [datetime] NULL,
#    [MachineName] [varchar](50) NULL,
#    [UserId] [varchar](50) NULL,
#    [Id] [int] NULL,
#    [Message] [varchar](500) NULL, */
#    [Server] [varchar](8) NULL,
#    [PrinterName] [varchar](30) NULL,
#    [Computer] [varchar](18) NULL,
#    [UserName] [varchar](25) NULL,
#    [PrintSize] [varchar](12) NULL,
#    [Pages] [varchar] (20) NULL,
#    [Document] [varchar](150) NULL,
#    [Date] [varchar](25) NULL
#) ON [PRIMARY]
# 
#GO
#
#
################################*******Table*******##################################
#
################################********SQL********##################################
#
################################***Scheduled*Task***#################################
#
# Craete a scheduled task with the following settings
#
# General
#    Run whether user is logged in or not
# Triggers
#    Settings: Daily 6AM
#    Advanced: Random delay 30 minutes
#    Advanced: Repeat 1 hour for 12 hours
# Actions
#    Start a program
#      C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
#      C:\Admin\PS\PrintLogs2SQL.ps1
# Settings
#    Stop the task if it runs for longer than 1 hour
#    
################################***Scheduled*Task***#################################
#
################################*******Notes*******##################################

######
# Cutom Definable Paramiters
#
# Windows Log Name
$LogName = "Microsoft-Windows-PrintService/Operational"
# SQL Database that holds the table for the events
$SQLServer = "<SQLServerName>.<DomainName>.COM"
$SQLDatabase = "EventCollection"
$PrintServer = "<PrintServerName>"
# Windows log event ID
$WinEventID = 307
#
###

######
# Write the date to SQL 
# This called at the end of the script
#
Function Write2SQL {
        
    $SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Data Source=$SQLServer;Integrated Security=SSPI;Database=$SQLDatabase"
    $SQLConnection.Open()
    $SQLBulkCopy = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy -ArgumentList $SQLConnection
    $SQLBulkCopy.DestinationTableName = "dbo.$LogNameSimplified"
    $SQLBulkCopy.BulkCopyTimeout = 60
    $SQLBulkCopy.WriteToServer($Datatable)
    
    # Create/update the LastRun value - assuming all the above has worked - in ISO 8601 format
    New-ItemProperty -Path $RegKey -Name $LogNameSimplified -Value (Get-Date -Format s) -Force | Out-Null
}
#
###

######
# Create a simplified version of the log name for use elsewhere in the script
#
$LogNameSimplified = $LogName.Replace("/","_")
$LogNameSimplified = $LogNameSimplified.Replace(" ","")
$LogNameSimplified = $LogNameSimplified.Replace("-","")
Write-Host "SQL table name: $LogNameSimplified"
#
###

######
# Registry key to store last run date & time
#
$RegKey = "HKCU:\Software\<CustomRegEntry>\EventCollector"
 
function Get-UserFromSID ($SID){
    # Does what it says on the tin
    $SIDObject = New-Object -TypeName System.Security.Principal.SecurityIdentifier($SID)
    $User = $SIDObject.Translate([System.Security.Principal.NTAccount])
    $User.Value
}
 
# Initialise LastRun variable, make it old enough that all events will be collected on first run
# Always use ISO 8601 format
[datetime]$LastRunExeDll = "1977-01-01T00:00:00"
 
if(Test-Path $RegKey){
    # Registry key exists, check LastRun value
    $LastRunValue = (Get-ItemProperty -Path $RegKey -Name $LogNameSimplified -ErrorAction SilentlyContinue).$LogNameSimplified
    if($LastRunValue -ne $null){
        $LastRunExeDll = $LastRunValue
    }
}else{
    # Registry key does not exist, create it, then set the NewsID value and run full script
    Write-Host "Registry key not present"
    New-Item -Path $RegKey -Force | Out-Null
}
#
###

######
# Get the events logged since LastRun date & time
#
Write-Host ("Collecting events from "+(Get-Date -Date $LastRunExeDll -Format s))
$Events = Get-WinEvent -FilterHashtable @{logname=$LogName; starttime=$LastRunExeDll; Id=$WinEventID} -ErrorAction SilentlyContinue
Write-Host ("Found "+$Events.Count+" events")
Write-Host ''
#
###

######
# Process the Log and upload the data to SQL
#        
if($Events.Count -gt 0){
        $DataTable = New-Object System.Data.DataTable
        $DataTable.TableName = $LogNameSimplified
        
        # Define Columns
        $Column1 = New-Object system.Data.DataColumn Server,([string])
        $Column2 = New-Object system.Data.DataColumn PrinterName,([string])
        $Column3 = New-Object system.Data.DataColumn Computer,([string])
        $Column4 = New-Object system.Data.DataColumn UserName,([string])
        $Column5 = New-Object system.Data.DataColumn PrintSize,([string])
        $Column6 = New-Object system.Data.DataColumn Pages,([string])
        $Column7 = New-Object system.Data.DataColumn Document,([string])
        $Column8 = New-Object system.Data.DataColumn Date,([string])
    
        # Add the Columns
        $DataTable.Columns.Add($Column1)
        $DataTable.Columns.Add($Column2)
        $DataTable.Columns.Add($Column3)
        $DataTable.Columns.Add($Column4)
        $DataTable.Columns.Add($Column5)
        $DataTable.Columns.Add($Column6)
        $DataTable.Columns.Add($Column7)
	    $DataTable.Columns.Add($Column8)

    foreach ($Event in $Events) {
    
        #Get print job details
        $time = $Event.TimeCreated
        # Convert the event to XML
        $eventXML = [xml]$Event.ToXml()
        $docName = $eventXML.Event.UserData.DocumentPrinted.Param2
        $Username = $eventXML.Event.UserData.DocumentPrinted.Param3
        $Computer = $eventXML.Event.UserData.DocumentPrinted.Param4
        $PrinterName = $eventXML.Event.UserData.DocumentPrinted.Param5
        $PrintSize = $eventXML.Event.UserData.DocumentPrinted.Param7
        $PrintPages = $eventXML.Event.UserData.DocumentPrinted.Param8
        


    # Parse out the event message data    

    
        # Process event data into a DataTable ready for upload to SQL Server
        # Create DataTable

            $Row = $DataTable.NewRow()
            $Row.Server = $PrintServer
            $Row.PrinterName = $PrinterName
            $Row.Computer = $Computer
            $Row.UserName = $Username
            $Row.PrintSize = $PrintSize
            $Row.Pages = $PrintPages
            $Row.Document = $docName
            $Row.Date = $time.ToString()
            $DataTable.Rows.Add($Row)
        }
        try{

        Write2SQL

            }
       catch{
        Write-Host "Problem uploading data to SQL Server"
        Write-Error $error[0]
    }
    }
    
    
    [System.GC]::Collect()