Raise your hand if you’ve never encountered that mythological, ancient, sworn enemy of digitalization, the unwitting lover of plastic gardens, who infests all offices: the compulsive printer!

The one who prints (almost) all emails to keep them in weathered file cabinet, the one who downloads a 150-page PDF document to have a look at a random chapter but on the screen “struggles to read” and therefore it’s better to print it (all) to read it on paper in positions that the orthopedist appreciates, the one who says “but you never know…” you get the idea!

So, hopefully, the moment arrives when the environmentally conscious Manager or more likely, whoever sees or pays the management costs, finally requests a tool for reporting and monitoring these poor, overused printers (the Amazon rainforest thanks you).

If you’re reading this article, you’ve probably already found some software more or less suitable for the purpose, there’s just one problem, they’re so nice and well-integrated that they have a commercial license to buy (fair enough).

So here’s a solution, in the Windows environment, that we can use as a starting point and then enrich it according to needs, we’ll use:

  • a PowerShell script to query client side data
  • a SQL database for storage, i.e. MSSQL instance
  • a Web Service receiving client data

The script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

<#
.SYNOPSIS
    Enable operational print log and push updates on print activity
.DESCRIPTION
    Enable operational print log and push updates on print activity
    Exit 0 and log with ID 0 if successfull, 1 if something wrong
.NOTES
    Author:  Lestoilfante
    Email:   [email protected]
    Date:    18NOV2021
    PSVer:   5.0
#>	

$serverUrl = "https://mioserver.contoso.local/api/PrintLog"
# Definiamo un nome da usare come Event Source in EventLog di Windows
$eventName = "Contoso_PrintReport"

If ([System.Diagnostics.EventLog]::SourceExists($eventName) -eq $False) {
    New-EventLog -LogName Application -Source $eventName
}

$printLog = New-Object System.Diagnostics.Eventing.Reader.EventLogConfiguration "Microsoft-Windows-PrintService/Operational" 
if ($printLog.IsEnabled -eq $False) {
    $printLog.IsEnabled = $True
    $printLog.MaximumSizeInBytes = 5242880
    $printLog.LogMode = Circular
    $printLog.SaveChanges()
}

$filterEvtScript = @{
    Logname='Application'
    ProviderName=$eventName
    ID=0
}
$evt = Get-WinEvent -filterHashTable $filterEvtScript -MaxEvents 1 -ErrorAction SilentlyContinue
if ($evt -eq $null){
    $evtStartTime = [datetime]::Today.AddDays(-30)
}
else {
    $evtStartTime = $evt.TimeCreated
}

$filterEvtPrint = @{
    LogName = 'Microsoft-Windows-PrintService/Operational'
    ID=307
    StartTime = $evtStartTime
}
[xml[]]$xml = Get-WinEvent -filterHashTable $filterEvtPrint -ErrorAction SilentlyContinue | ForEach-Object{ $_.ToXml() }

if ($xml.Count -eq 0) {
    Write-EventLog -LogName "Application" -Source $eventName -EventID 0 -EntryType Information -Message "0 events found" -Category 1
    exit 0
}

$collectionWithItems = New-Object System.Collections.ArrayList
Foreach ($event in $xml.Event)
{
    if ($event.Userdata.DocumentPrinted -ne $null){
        $temp = "" | select "UserName", "PageQty", "PrinterName", "Date"
        $temp.UserName = $event.Userdata.DocumentPrinted.Param3
        $temp.PageQty = $event.Userdata.DocumentPrinted.Param8
        $temp.PrinterName = $event.Userdata.DocumentPrinted.Param5
        $temp.Date = $event.System.TimeCreated.SystemTime
        $collectionWithItems.Add($temp) | Out-Null
    }
}

try {
    $json = ConvertTo-Json $collectionWithItems
}
catch {
    Write-EventLog -LogName "Application" -Source $eventName -EventID 1 -EntryType Information -Message "Error in json conversion" -Category 1
    exit 1
}

try
{
    $Response = Invoke-WebRequest -Method 'Post' -Uri $serverUrl -Body $json -ContentType "application/json" -TimeoutSec 15 -UseBasicParsing
    $StatusCode = $Response.StatusCode
    $Message = [String]::new($Response.Content)
    Write-EventLog -LogName "Application" -Source $eventName -EventID 0 -EntryType Information -Message "Report sent, response: $Message" -Category 1
    exit 0
}
catch
{
    $StatusCode = $_.Exception.Response.StatusCode.value__
    Write-EventLog -LogName "Application" -Source $eventName -EventID 1 -EntryType Information -Message "Http error $StatusCode" -Category 1
    exit 1
}

exit 0

The Database

We want to have a daily summary record for User and Printer to know Who/Where/When/How much.

Let’s make a “PrintLog” table then 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

USE [ContosoDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PrintLog](
	[Username] [nvarchar](10) NOT NULL,
	[Date] [date] NOT NULL,
	[Printer] [nvarchar](30) NOT NULL,
	[Pages] [int] NOT NULL,
	CONSTRAINT [PK_PrintLog] UNIQUE NONCLUSTERED 
	(
		[Username] ASC,
		[Date] ASC,
		[Printer] 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

and a Stored Procedure handling data input 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

USE [ContosoDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[printLog_update](
	@_Username AS nvarchar(10),
	@_Date AS date,
	@_Printer AS nvarchar(30),
	@_Pages AS int
)
AS
BEGIN
	DECLARE @_varPages AS int;
	SET @_varPages = (SELECT Pages FROM PrintLog WHERE Username = @_Username and Date = @_Date and Printer = @_Printer);
	IF (@_varPages IS NULL)
	BEGIN
		INSERT INTO PrintLog (Username,Date,Printer,Pages) VALUES (@_Username,@_Date,@_Printer,@_Pages)
	END
	ELSE
	BEGIN
		UPDATE
			PrintLog
		SET
			Pages = @_Pages + @_varPages
		WHERE
			Username = @_Username AND Date=@_Date AND Printer=@_Printer
	END
END;
GO

The Web Service

The rule of using what you know best or what is better supported in your infrastructure applies, in this case, an example in .NET.

First, let’s define a model for the data we will receive. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

public class PrintLogData
{
	public class LogCollection
    {
    	public List<LogEntry> Entry { get; set; }
	}
	public class LogEntry
	{
    	public string UserName { get; set; }
    	public int PageQty { get; set; }
        public string PrinterName { get; set; }
        public DateTime Date { get; set; }
	}
}

So let’s define our minimal Web Service that will call the stored procedure defined on the DB. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

public class PrintLogController : ApiController
{
    private readonly string sqlServer = "";
    private readonly string sqlDB = "ContosoDB";
    private readonly string sqlUser = "";
    private readonly string sqlPwd = "";

    // POST: api/PrintLog
    public string Post([FromBody] ICollection<PrintLogData.LogEntry> json)
    {
        // Raggruppiamo i dati ricevuti per user/data/stampante sommandone le pagine
        var newList = json.GroupBy(x => new { x.UserName, x.Date.Date, x.PrinterName })
            .Select(y => new PrintLogData.LogEntry()
            {
                UserName = y.Key.UserName,
                Date = y.Key.Date.Date,
                PrinterName = y.Key.PrinterName,
                PageQty = y.Sum(c => c.PageQty)
            });
        int count = newList.Count();
        int entriesProcessed = 0;

        try
        {
            using (var sqlCon = new SqlConnection("Data Source=" + sqlServer + ";Initial Catalog=" + sqlDB + "; User ID = " + sqlUser + "; Password = " + sqlPwd))
            {
                sqlCon.Open();
                foreach (var item in newList)
                {
                    SqlCommand sql_cmnd = new SqlCommand("printLog_update", sqlCon);
                    sql_cmnd.CommandType = CommandType.StoredProcedure;
                    sql_cmnd.Parameters.AddWithValue("@_Username", SqlDbType.NVarChar).Value = item.UserName;
                    sql_cmnd.Parameters.AddWithValue("@_Date", SqlDbType.Date).Value = item.Date;
                    sql_cmnd.Parameters.AddWithValue("@_Printer", SqlDbType.NVarChar).Value = item.PrinterName;
                    sql_cmnd.Parameters.AddWithValue("@_Pages", SqlDbType.Int).Value = item.PageQty;
                    entriesProcessed += sql_cmnd.ExecuteNonQuery();
                }
                sqlCon.Close();
            }
        }
        catch (Exception e)
        {
            return SendStructResponse(e.Message, HttpStatusCode.BadRequest);
        }

        return SendStructResponse(entriesProcessed.ToString(), HttpStatusCode.OK);
    }

    internal T SendStructResponse<T>(T response, HttpStatusCode statusCode = HttpStatusCode.OK)
    {
        if (statusCode != HttpStatusCode.OK)
        {
            var r =
                new HttpResponseMessage(statusCode)
                {
                    Content = new StringContent(JsonConvert.SerializeObject(response), Encoding.UTF8, "text/plain")
                };
            throw new HttpResponseException(r);
        }
        return response;
    }
}

Let’s put the pieces together

Now that we have all the components, how do we handle deployment on the clients?

Obviously, it depends on you and the tools you have available. Just keep in mind that the script must run at least the first time with elevated privileges to activate logging correctly. I would have a completely automatic solution in Windows Domain networks… let’s see if you reach the same solution too!