HealthCheck - Identify MFDs where print jobs are not accounted, list details about such jobs

YSoft SafeQ marks job as PRINTED as soon as it is delivered to MFD. However in some cases accounting information does not come and job is not accounted. There may be various reasons for this behavior, for example:

  • accounting is not set up or does not work properly

  • job was canceled by user or automatically by the MFD from MFD joblist

  • MFD is asking for resources and user does not have instructions how to do it so he leaves the MFD without clearing the job from MFD job list

The consequences may vary but the most common ones are the missing accounting data in the statistical reports and the warning on the MFD that user has insufficient credit (credit is locked for the previous session and it is not released until the accounting is finished, attempt for print on another MFD shows insufficient credit warning).

The document provides instructions how to detect such a print jobs using SQL queries along with the ideas how to resolve such a situation.

Resolution

Limitations:

  • As there is no indication from the MFD about the printing progress, the SQL query may list also correct print jobs which are still being processed by MFD (e.g. if the printing of 1000 pages document takes 30 minutes, job will be listed as not accounted by the query within five minutes from delivery to the MFD. If you launch the SQL query after one hour again, job will not be listed any more because it will be already accounted).

  • SQL query may take a longer time to process on a larger databases.

  • SQL query is suitable for environment with MFD specific accounting, it is not suitable in environments where accounting is disabled.

  • SQL query ignores the print jobs that were re-queued.

Usage:

Run the SQL query on the SQDB6 database.

Best practices:

  1. Run the second SQL query when somebody contacts IT about "Insufficient credit" issue. Verify if there is some non-accounted job for the person who contacted IT.

    1. If there is any print job listed for the person (User_login):

      • verify where the job has been sent to

      • open the web interface of MFD_IP displayed on the same row and find the status of the job

      • discuss with the job owner how to resolve the situation (e.g. delete the job via MFD panel, via MFD web interface or by reboot of the MFD)

      • instruct person (User_login) who initiated the print to always make sure to log off and do not leave any job on the MFD when leaving (security concern as well as possible trigger for a credit issue)

    2. If there is no job stuck for the particular person, verify the credit balance in the YSoft SafeQ web interface.

  2. Run the first and second SQL query on a regular basis. For the second query check the jobs where Last_Update_Time is considerably old.

    • connect to MFD_IP administrative interface and verify the job status. If the job is in error state (i.e. not in Printing state), contact person (User_login) who sent the job to print and ask whether you can delete the job, then delete the job via MFD web interface (or by reboot of MFD)

    • instruct person (User_login) who initiated the print to always make sure to log off and do not leave any job on the MFD when leaving (security concern as well as possible trigger for a credit issue)

  3. Run the first and second SQL query in case the statistics are showing suspiciously low numbers of accounted pages. Search for the history of jobs that were returned by the query on the MFD web interface and YSoft SafeQ job list.

    1. in case the job was printed on MFD but accounting was not made, try to diagnose the cause (e.g. see the accounting method chosen for this MFD or review the log files).

First SQL query - identify problematic MFD

This query will list accounting/printing (if older than 30min) records on the MFD that was identified as problematic, that means where print jobs are delivered to the MFD but no accounting data follow after that. It is recommended to review accounting health on every single MFD that is reported by the query.

YSoft SafeQ 6 - MS SQL
SQL
SET NOCOUNT ON
DECLARE @MyVar TABLE(Val BIGINT)
DECLARE @I INT

INSERT INTO @MyVar(Val) SELECT (ID) from tenant_1.devices where status = 'ACTIVE'

WHILE (SELECT count(1) from @MyVar) >= 1
BEGIN
 DECLARE @dev BIGINT
 SET @dev = (SELECT top 1 Val from @MyVar)
 DECLARE @tab TABLE(id BIGINT, job_id BIGINT, queue_id BIGINT, device_id BIGINT, devaddr NVARCHAR(256), devname NVARCHAR(64), date datetime2(7), message_id NVARCHAR(64), note NVARCHAR(100))

 INSERT INTO @tab
   SELECT top 10 sjl.id, job_id, queue_id, device_id, d.network_address, d.name, date, CASE WHEN (message_id = 119) THEN 'DELIVERED' ELSE 'ACCOUNTED' END, note from tenant_1.smartq_jobs_log sjl
   LEFT JOIN tenant_1.devices d on d.id = sjl.device_id
   WHERE (message_id in (101,111) or (message_id in (119) and date < (SELECT DATEADD(mi,30,GETDATE())))) and device_id = @dev
   ORDER BY id DESC

 IF  (not exists (SELECT 1 FROM @tab WHERE message_id in ('ACCOUNTED')) 
	 and exists (SELECT 1 FROM @tab))
	SELECT * FROM  @tab

 DELETE FROM @tab
 DELETE FROM @MyVar WHERE Val = @dev
END
YSoft SafeQ 6 - PostgreSQL
SQL
SELECT id INTO TEMP TABLE activeMFDs FROM tenant_1.devices WHERE status = 'ACTIVE';
CREATE TEMP TABLE parseddata (
	id BIGINT
	,job_id BIGINT
	,queue_id BIGINT
	,device_id BIGINT
	,devaddr VARCHAR(256)
	,devname VARCHAR(64)
	,date timestamp without time zone NOT NULL 
	,message_id VARCHAR(64)
	,note VARCHAR(100)
);
CREATE TEMP TABLE affectedMFDs (
	id BIGINT
	,job_id BIGINT
	,queue_id BIGINT
	,device_id BIGINT
	,devaddr VARCHAR(256)
	,devname VARCHAR(64)
	,date timestamp without time zone NOT NULL 
	,message_id VARCHAR(64)
	,note VARCHAR(100)
);

DO $$
DECLARE _dev bigint;
BEGIN
 WHILE (SELECT count(1) from activeMFDs) >= 1 LOOP
 _dev := (SELECT id from activeMFDs limit 1);
 
 INSERT INTO parseddata (
   SELECT sjl.id, job_id, queue_id, device_id, d.network_address, d.name, date, CASE WHEN (message_id = 119) THEN 'DELIVERED' ELSE 'ACCOUNTED' END, note from tenant_1.smartq_jobs_log sjl
   LEFT JOIN tenant_1.devices d on d.id = sjl.device_id
   WHERE (message_id in (101,111) or (message_id in (119) and date < (SELECT CURRENT_TIMESTAMP -interval '30 minutes'))) and device_id = _dev
   ORDER BY id DESC
   LIMIT 10);
	
  IF (not exists (SELECT 1 FROM parseddata WHERE message_id in ('ACCOUNTED'))
     and exists (SELECT 1 FROM parseddata))
     THEN INSERT INTO affectedMFDs (SELECT * FROM parseddata); 
     END IF;
 
 TRUNCATE TABLE parseddata;
 DELETE FROM activeMFDs WHERE id = _dev;
 END LOOP;
END $$;

SELECT * FROM affectedMFDs;

Second SQL query - list details about problematic print jobs

This query will provide detailed information about print jobs that were delivered to the MFD but were not accounted. To prevent high amount of false positive reports only the print jobs sent for print more that 30 minutes ago (since launching the query) are reviewed and reported.

  • Last_Update_Time is the time of the last change for this job, the very same time can be visible in the job list of YSoft SafeQ interface

  • Job_Title shows title of the print job

  • Job_Id shows internal ID of the print job in YSoft SafeQ database

  • MFD_name shows the name of MFD where user selected the job for print

  • MFD_IP shows ip address of MFD where user selected the job for print

  • User_login shows the name of user who sent the job for print

    https://ysoft.atlassian.net/wiki/plugins/servlet/confluence/placeholder/unknown-macro?name=talk&locale=en_GB&version=2
YSoft SafeQ 6 - uncomment line with cur_status_time based on SQL version
SQL
select distinct sjl.date, sj.cur_status_time TimeInJoblistOnWeb, sj.title Job_Title, sj.id Job_Id, sd.name MFD_name, sd.id MFD_id, sd.network_address MFD_IP, u.login User_login, 'false' as accounted
from tenant_1.smartq_jobs_log sjl
left outer join tenant_1.devices sd on (sjl.device_id = sd.id)
left outer join tenant_1.smartq_jobs sj on (sjl.job_id = sj.id)
left outer join tenant_1.users u on (sj.user_id = u.id)
left outer join tenant_1.smartq_jobs_log sjlm on (sjl.job_id = sjlm.job_id and (sjlm.message_id in (101,111) or sjlm.status in (32)) and sjl.device_id = sjlm.device_id)
where sjl.status = 2 and sjlm.id is null
-- and sj.cur_status_time < (SELECT DATEADD(mi,-30,GETDATE())) -- uncomment this line in case of MS SQL
-- and sj.cur_status_time < (SELECT CURRENT_TIMESTAMP -interval '30 minutes') -- uncomment this line in case of PostgreSQL
order by sjl.date desc

Automatic e-mail notification for the MS SQL query

If the automated notification from the query result is required, description below demonstrates one of examples how to accomplish it. The other means of configuration are possible at administrator's discretion. As the query may list also the jobs which are not problematic (they just take longer to process as described above), we rather recommend to use the query as described above in the Best Practices.

Risks: The SQL query may be time consuming operation. Make sure you define a proper delay between the schedules.

How to automate the output of the SQL query to the email

  1. Configure SQL server for Database Mail
        http://technet.microsoft.com/en-us/library/ms175951.aspx

  2.  Create job for SQL server agent that will be launching the query

    1. Description for job creation is available at:
      http://technet.microsoft.com/en-us/library/ms190268.aspx
      http://technet.microsoft.com/en-us/library/ms187910.aspx
      http://technet.microsoft.com/en-us/library/ms191439.aspx

    2. Example of SQL query to be used in the job:

      SQL
      -- YSoft SafeQ 5
USE [SAFEQDB];
IF (select count(1) from smartq_jobs_log sjl left outer join smartq_devices sd on (sjl.device_id = sd.id) left outer join smartq_jobs sj on (sjl.job_id = sj.id) left outer join users u on (sj.user_id = u.id) left outer join smartq_jobs_log sjlm on (sjl.job_id = sjlm.job_id and (sjlm.message_id in (101,111) or sjlm.status in    (32))) where sjl.status = 2 and sjlm.id is null) > 0
BEGIN
EXEC
msdb.dbo.sp_send_dbmail
@recipients=N'admin.email@test.abc',
@body= 'This message is automatically sent by the MS SQL server',
@subject = 'YSoft SafeQ: List of non-accounted print jobs that were delivered to MFD',
@profile_name = 'NameOfProfileFromPoint1',
@query = 'USE [SAFEQDB];select distinct sj.cur_status_time Last_Update_Time, sj.title Job_Title, sj.id Job_Id, sd.name MFD_name, sd.ip_address MFD_IP, u.login User_login from smartq_jobs_log sjl left outer join smartq_devices sd on (sjl.device_id = sd.id) left outer join smartq_jobs sj on (sjl.job_id = sj.id) left outer join users u on (sj.user_id = u.id) left outer join smartq_jobs_log sjlm on (sjl.job_id = sjlm.job_id and (sjlm.message_id in (101,111) or sjlm.status in (32))) where sjl.status = 2 and sjlm.id is null and sj.cur_status_time < (SELECT DATEADD(mi,-30,GETDATE())) order by Last_Update_Time asc',
@query_result_separator= ';',
@query_result_no_padding=1,
@attach_query_result_as_file = 1
END

      SQL query has to be optimized for your environment prior using it in the job:

      • put email address of administrator to @recipients parameter

      • put the name of profile from Step 2 to @profile_name parameter

      • replace 2x the name of database SAFEQDB by the name from your environment

      • alter the query that generates the email notification according to your needs as described at:
        http://technet.microsoft.com/en-us/library/ms190307.aspx

  3. Use the modified SQL query and add it to the SQL Server Agent job

  4. Verify the functionality