Printer status monitoring via SNMP to Excel

Problem:

All modern printers with a TCP/IP port allow monitoring of the printer's status using the SNMP interface. There is a wide range of values (e.g. color and tray level, uptime, statuses, etc.), but each manufacturer may implement the limited and necessary set of values.

This 'how to' shows how to automate data reading from several printers and collect data in Excel in real-time, where one row in Excel contains all values for one printer. The program overwrites values in that row with the latest values. You can see the resulting XLSX file example for color Canon printers.

FYI: We use a data example from Canon or Zebra printers in this article.

Fig. 1: Canon printer values

Fig. 2: Zebra printer values

Fig. 3: Main window

2. We should always write data for a specific printer to one fixed row. Therefore, we should have some rules to define a start cell in Excel. We can use the Expressions plugin for this task and use a printer's IP address to distinguish data rows for different printers.

Fig. 4: Select a data query plugin

Fig. 5: Defining rules in the Expressions plugin

3. Enable the "Direct Excel Connection" plugin and configure destination file options. This example uses a template with column headers for all new files.

Fig. 6: Enabling the data export plugin

Fig. 7: Excel connection settings

Fig. 8: Excel file settings

Fig. 8: Excel sheet settings

4. Bind SNMP printer status values to Excel columns.

Here, you can assign SNMP printer values to the necessary column. The start cell for the first value is defined as a placeholder for the calculated value from the Expressions plugin. For all other values, we use relational positions by selecting "Last write cell+NNN" as "Left top cell".

Fig. 9: Binding SNMP printer values to Excel columns

8. Finally, you download a configuration example (load it in the File menu in the main window), or visit the following tutorials about exporting parsed data to other targets like a database, OPC, CSV, DDE.