Sample Queries

The following are sample queries that can be run using the Spectrum Reporting Schema.

Note: A user account named LOFTUSER (Oracle) or loftuser (PostgreSQL) exists in the Spectrum database (LoftStore). This user account has the permissions necessary to see views and run reports using the Spectrum Reporting Schema. If you are using Loftware Spectrum Cloud, Loftware Cloud Operations may have assigned a unique username for your reporting user account. Contact Loftware Cloud Operations for details.

Active Printers

This query returns Devices (Physical Devices) that have not been deleted. You must run a join with Device Groups (Logical Devices), because the deleted flag is stored on that object.

SELECT PD.FULL_NAME FROM LOFTREPORTS.R_PHYSICAL_DEVICES PD, LOFTREPORTS.R_LOGICAL_DEVICES LD
WHERE PD.LOGICAL_DEVICE_NAME = LD.FULL_NAME AND LD.DELETED = 'N';

Existing Users

This query shows the full names of all the users in the Spectrum environment that have not been deleted.

SELECT U.FULL_NAME FROM LOFTREPORTS.R_USERS U WHERE DELETED = 'N';

Idle Printers for the Last Month

R_PRINTJOB_DETAIL returns which Device Groups (Logical Devices) were used in the past month. From Device Groups, you can get to the actual printers.

SELECT PD.FULL_NAME FROM LOFTREPORTS.R_PHYSICAL_DEVICES PD, LOFTREPORTS.R_LOGICAL_DEVICES LD
WHERE PD.LOGICAL_DEVICE_NAME = LD.FULL_NAME AND LD.DELETED = 'N' AND
LD.FULL_NAME NOT IN (SELECT UPPER(RESOLVED_DESTINATION) FROM LOFTREPORTS.R_PRINTJOB_DETAIL
WHERE MODIFIED BETWEEN ADD_MONTHS(SYSTIMESTAMP, -1) AND SYSTIMESTAMP);

Jobs Per Printer, Last Two Weeks

This query uses the Device Group (Logical Device) view to span Devices (Physical Devices) and Print Job.

SELECT PD.FULL_NAME, COUNT(JOB.ID) "JOBS"
FROM LOFTREPORTS.R_PHYSICAL_DEVICES PD,
LOFTREPORTS.R_LOGICAL_DEVICES LD,
LOFTREPORTS.R_PRINTJOB JOB
WHERE PD.LOGICAL_DEVICE_NAME = LD.FULL_NAME AND
UPPER(JOB.DESTINATION_FQN) = LD.FULL_NAME AND
JOB.MODIFIED > SYSTIMESTAMP - 14
GROUP BY PD.FULL_NAME ORDER BY 2 DESC;

Total Labels by Label Template, Last Month

Although a self-join with R_PRINTJOB_DETAIL would work, it is faster to join with R_DOCUMENTS.

SELECT DOC.FULL_NAME, SUM(PJD.QUANTITY * (1 + PJD.DUPLICATES)) "Labels"
FROM LOFTREPORTS.R_DOCUMENTS DOC, LOFTREPORTS.R_PRINTJOB_DETAIL PJD
WHERE DOC.FULL_NAME = UPPER(PJD.RESOLVED_DOC) AND
PJD.MODIFIED BETWEEN ADD_MONTHS(SYSTIMESTAMP, -1) AND SYSTIMESTAMP
GROUP BY DOC.FULL_NAME ORDER BY 2 DESC;

User with Most Modified Documents, Last Six Months

This query leverages the LAST_UPDATED_ISSUER field of objects that are managed by using Documents permissions. Note that this checks for information about all types of objects managed by using Documents permissions, not just label templates. To check only for information about label templates you must add a predicate for DOCTYPE.

SELECT U.FULL_NAME, COUNT(DOC.ID) "Docs"
FROM LOFTREPORTS.R_USERS U, LOFTREPORTS.R_DOCUMENTS DOC
WHERE U.FULL_NAME = DOC.LAST_UPDATED_ISSUER AND
DOC.MODIFIED BETWEEN ADD_MONTHS (SYSTIMESTAMP, -6) AND SYSTIMESTAMP
GROUP BY U.FULL_NAME ORDER BY 2 DESC;

Failed Jobs

This query returns full information about all failed jobs.

SELECT TJ.JOB_STATE, TPE.JOB_ID, TJ.NAME, TJ.FOLDER_FQN JOB_FOLDER, TJ.ERROR_MESSAGE, TJ.NAME, TPE.MESSAGE, TPE.STACK_TRACE, TPE.ERROR_CODE
FROM LOFTREPORTS.R_JOB TJ, LOFTREPORTS.R_PROCESS_ERRORS TPE
WHERE TPE.JOB_ID = TJ.ID;

Existing Labels under a Folder Hierarchy

This query uses the CONNECT BY/START WITH concept in Oracle to return a tree rather than a table. Although not shown in the following example, note that this query must also have an outer join on FOLDER_NAME = FULL_NAME to get every branch of the tree.

SELECT DISTINCT DOC.FULL_NAME
FROM LOFTREPORTS.R_DOCUMENTS DOC, LOFTREPORTS.R_FOLDERS F
WHERE DOC.FOLDER_NAME = F.FULL_NAME AND DOC.DOCTYPE = 'DOCUMENT'
CONNECT BY PRIOR F.FULL_NAME = F.PARENT_FOLDER_NAME
START WITH F.FULL_NAME = '/LOFTWARE INC';