How to run a usage report for PrinterOn Enterprise against the SQL DB - c06642326

1. To begin, download SQL Management Studio and install it on a machine that can connect to the SQL DB instance your deployment is using. NOTE: If you are using the included SQL Express instance, this is best done from the same server.
2. Install & launch SQL Management Studio
3. Connect to the SQL instance. If this is a local SQL Express, use the credentials of the user running most of the PrinterOn Windows services. These are the same user credentials entered when prompted during installation.
   NOTE:
   If the deployment uses an alternate SQL server, connect to that and carry on to step 4 below.
4. After the connection is established, expand the SQL Server and Databases.
5. Right click on the cpsdb and select New Query
6. Copy the below query and Execute it.

SELECT DISTINCT pr.alias_tail , pds.PRINT_ALIAS , pwc.COLOUR , pwc.ptid , isnull ( pds.DATESTAMP , pwc.DATESTAMP ) as DATESTAMP, isnull ( pds.PG_COUNT , pwc.PG_COUNT ) as PG_COUNT, isnull ( pds.user_email , pwc.USER_EMAIL ) as user_email , pwc.DUPLEX_OPTION , pq.STATUS FROM printer pr, PRINT_TRAN_LOG pwc left outer JOIN PRINT_TRAN_LOG pds ON pds.PTID = pwc.PTID and pds.CLIENT_SW NOT IN ( 'PWC', 'ENC', 'PDH') join vw_print_queue pq on pwc.PTID = pq.ptid left outer join printer p on p.id = pwc.printer_id WHERE pwc.CLIENT_SW IN ( 'PWC', 'ENC' ) and isnull ( pds.datestamp , pwc.datestamp ) between '2017-01-01' and '2020-01-01' and ( isnull ( pds.job_state , '0') = 9 or ( pwc.job_state = 9 and p.printer_class = 'IPP' )) and pds.print_alias = pr.alias_num

This will produce a usage report for all Printed jobs in the time frame specified. it will include the job's colour setting, the date/time it was released, number of pages, job owner and duplex option.