Logging and Reporting on Print Jobs with PaperCut

28 Apr

Some time back I wrote a couple of posts on using the free PaperCut software (this one and this one)

I haven’t used it for quite some time, but somewhere else I needed to log and report on Print jobs and so I’ve gone a little further this time.

So the network is all split into VLANs and so only the print server can access the printers. Users talk to the print server, it talks to the printer, so this stops anyone circumventing the logging. All printers are restricted to certain users groups, and all deployed with Group Policy.

First I have a daily PowerShell script to copy the previous days log file over to my SQL server. A scheduled task runs this at 00:01.

#Move daily log file

$logpath = "C:\Program Files (x86)\PaperCut Print Logger\logs\csv\daily\"
$yesterdaysdate = (Get-Date).AddDays(-1).ToString("yyyy-MM-dd")
$logfile = "papercut-print-log-$yesterdaysdate.csv"
$logitem = $logpath + $logfile
$destitem = "\\SQLserver\folder\PrintLogs\log.csv"

#filename example: papercut-print-log-2017-04-21

Copy-Item $logitem $destitem
exit

Then from the SQL server a job runs at 00:05. This job opens the log file, modifies the Document Name entries to remove any comma’s (as these were causing me a headache) and then re-writes the new CSV file. It calls the SQL command to import this into the database, waits 10 seconds and then removes the CSV files:

#PowerShell to import the print logs

$logfile = "C:\folder\PrintLogs\log.csv"
$logfilemod = "C:\folder\PrintLogs\logmod.csv"

if (Test-Path $logfile){

$data = Import-Csv -Delimiter "," -Header @("Time","User","Pages","Copies","Printer","Document Name","Client","Paper Size","Language","Height","Width","Duplex","Grayscale","Size") -Path $logfile

foreach($row in $data){
$row.'Document Name' = $row.'Document Name' -replace ',',' '
}

$data | ConvertTo-Csv -NoTypeInformation | % {$_.Replace('"','')} | Out-File $logfilemod

Invoke-Sqlcmd -InputFile "C:\folder\SQL\ImportPrintJobs.sql" -Database PrintLog

Start-Sleep -s 10

remove-item "C:\folder\PrintLogs\log.csv" -Confirm:$false
remove-item "C:\folder\PrintLogs\logmod.csv" -Confirm:$false

}
USE PrintLog
GO
BULK INSERT "log"
FROM 'C:\folder\PrintLogs\logmod.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 4
)
GO

The SQL table for this can be found in my previous post.

I then have another SQL table which holds the print costs for each printer:

CREATE TABLE [dbo].[PrintCost](
[ID] [int] NULL,
[PrinterName] [varchar](255) NULL,
[ColourCost] [real] NULL,
[GreyscaleCost] [real] NULL
) ON [PRIMARY]

I have have a link to AD within SQL so I can query each user and return their department for more advanced reporting.

I have a few views to pull data from the last 7 days:

vw_ReportByUser

CREATE VIEW [dbo].[vw_ReportByUser]
AS
SELECT TOP (100) PERCENT AD.dbo.vw_Users.givenName + ' ' + AD.dbo.vw_Users.sn + ' (' + AD.dbo.vw_Users.department + ')' AS 'Name', SUM(dbo.[log].pages * dbo.[log].copies) AS Pages, COUNT(dbo.[log].greyscale)
AS 'Number of Jobs', CASE WHEN greyscale = 'GRAYSCALE' THEN 'No' ELSE 'Yes' END AS 'Colour', dbo.[log].printername AS 'Printer Name', CASE WHEN greyscale = 'GRAYSCALE' THEN CAST(round
((((SELECT TOP 1 ColourCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) ELSE CAST(round
((((SELECT TOP 1 GreyscaleCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) END AS 'Cost (GBP ex. VAT)'
FROM dbo.[log] INNER JOIN
AD.dbo.vw_Users ON AD.dbo.vw_Users.sAMAccountName = dbo.[log].username
WHERE (dbo.[log].datetime >= DATEADD(day, - 7, GETDATE()))
GROUP BY AD.dbo.vw_Users.givenName + ' ' + AD.dbo.vw_Users.sn + ' (' + AD.dbo.vw_Users.department + ')', dbo.[log].greyscale, dbo.[log].printername
ORDER BY name, 'Printer Name', colour DESC

GO

vw_ReportByDepartment

CREATE VIEW [dbo].[vw_ReportByDepartment]
AS
SELECT TOP (100) PERCENT AD.dbo.vw_Users.department AS 'Department', SUM(dbo.[log].pages * dbo.[log].copies) AS Pages, COUNT(dbo.[log].greyscale) AS 'Number of Jobs',
CASE WHEN greyscale = 'GRAYSCALE' THEN 'No' ELSE 'Yes' END AS 'Colour', dbo.[log].printername AS 'Printer Name', CASE WHEN greyscale = 'GRAYSCALE' THEN CAST(round
((((SELECT TOP 1 GreyscaleCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) ELSE CAST(round
((((SELECT TOP 1 ColourCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) END AS 'Cost (GBP ex. VAT)'
FROM dbo.[log] INNER JOIN
AD.dbo.vw_Users ON AD.dbo.vw_Users.sAMAccountName = dbo.[log].username
WHERE (dbo.[log].datetime >= DATEADD(day, - 7, GETDATE()))
GROUP BY AD.dbo.vw_Users.department, dbo.[log].greyscale, dbo.[log].printername
ORDER BY 'Department', 'Printer Name', colour DESC

GO

vw_ReportByPrinter

CREATE VIEW [dbo].[vw_ReportByPrinter]
AS
SELECT TOP (100) PERCENT dbo.[log].printername AS 'Printer Name', SUM(dbo.[log].pages * dbo.[log].copies) AS Pages, COUNT(dbo.[log].greyscale) AS 'Number of Jobs',
CASE WHEN greyscale = 'GRAYSCALE' THEN 'No' ELSE 'Yes' END AS 'Colour', CASE WHEN greyscale = 'GRAYSCALE' THEN CAST(round
((((SELECT TOP 1 GreyscaleCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) ELSE CAST(round
((((SELECT TOP 1 ColourCost
FROM PrintLog.dbo.PrintCost
WHERE PrinterName = log .printername) * SUM(pages * copies)) / 100), 2) AS numeric(36, 2)) END AS 'Cost (GBP ex. VAT)'
FROM dbo.[log] INNER JOIN
AD.dbo.vw_Users ON AD.dbo.vw_Users.sAMAccountName = dbo.[log].username
WHERE (dbo.[log].datetime >= DATEADD(day, - 7, GETDATE()))
GROUP BY dbo.[log].printername, dbo.[log].greyscale
ORDER BY 'Printer Name', colour DESC

GO

And of course with a little grouping, you can then pull a costs report for each of these:

select Name, sum(pages) as 'Total Pages', sum("Cost (GBP ex. VAT)") as "Cost (GBP ex. VAT)"
from Printlog.dbo.vw_ReportByUser
group by name
order by "Cost (GBP ex. VAT)" desc

So now onto another job that runs to run all the reports and then take the CSV files, attach them to an email and send them out every Monday morning:

$startDate = (get-date (Get-Date).AddDays(-7) -UFormat "%Y-%m-%d")
$endDate = (get-date (Get-Date).AddDays(-1) -UFormat "%Y-%m-%d")

Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query User.sql" | Export-CSV "c:\folder\Print Report by User, $startdate to $enddate.csv" -NoTypeInformation
Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query Department.sql" | Export-CSV "c:\folder\Print Report by Department, $startdate to $enddate.csv" -NoTypeInformation
Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query Printer.sql" | Export-CSV "c:\folder\Print Report by Printer, $startdate to $enddate.csv" -NoTypeInformation
Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query Costs User.sql" | Export-CSV "c:\folder\Print Costs User Report, $startdate to $enddate.csv" -NoTypeInformation
Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query Costs Department.sql" | Export-CSV "c:\folder\Print Costs Department Report, $startdate to $enddate.csv" -NoTypeInformation
Invoke-Sqlcmd -InputFile "C:\folder\SQL\Print Query Costs Printer.sql" | Export-CSV "c:\folder\Print Costs Printer Report, $startdate to $enddate.csv" -NoTypeInformation

$emailbodytext = "Please find attached the printer log reports for the last 7 days, $startDate to $enddate."
Send-MailMessage -To "[email protected]" -From "[email protected]" -Subject "Print Log Report $startdate to $enddate" -smtpserver x.x.x.x -body $emailbodytext -BodyAsHtml -Attachments "c:\folder\Print Report by User, $startdate to $enddate.csv", "c:\temp\Print Report by Department, $startdate to $enddate.csv", "c:\temp\Print Report by Printer, $startdate to $enddate.csv", "c:\temp\Print Costs User Report, $startdate to $enddate.csv", "c:\temp\Print Costs Department Report, $startdate to $enddate.csv", "c:\temp\Print Costs Printer Report, $startdate to $enddate.csv"

All automated, fantastic. Some room for improvement I’m sure, let me know if you have any feedback!