PROBLEM STATEMENT: We need to create custom report for a customer that shows:

Printer policies (name/path/liste of printers by policy) in relationship with workstation or group of workstation.

The report need to display name of workstation or group of worksation,name of printer policy, list of printer path or printer name like in the workstation side.

AD HOC Fields on police category and on inventory category can not allow link between those two categories.

PLATFORM / CONFIGURATION: ZCM 11.3.2
STEPS TO REPRODUCE: /
RESULTS: /
EXPECTED RESULTS: Printer policies (name/path/liste of printers by policy) in relationship with workstation or group of workstation.

WORKAROUND:
We have provided SQL query to customer to fetch data from xml from db and now its working fine. Report is created in Ireport. Below query is used:
SELECT "policies_contentObject1"."NAME" AS "policies_contentObject1_NAME",
"consumertab1"."NAME" AS "consumertab1_NAME",
CASE
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'WinGroupPolicy')
THEN 'Windows Group Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'browserbookmarkspolicy')
THEN 'Browser Bookmarks Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'dlu policy')
THEN 'Dynamic Local User Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'launcher configuration policy')
THEN 'ZENworks Explorer Configuration Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'local file rights policy')
THEN 'Local File Rights Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'printer policy')
THEN 'Printer Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'remote management policy')
THEN 'Remote Management Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'roamingprofile policy')
THEN 'Roaming Profile Policy'
WHEN(CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50)) = 'snmp policy')
THEN 'SNMP Policy'
ELSE CAST("policies_contentObject"."SUBTYPE" AS VARCHAR(50))
END AS Policy_Tye ,
CASE
WHEN ZPOLICY.policyData LIKE '%Printer%'
THEN SUBSTR(ZPOLICY. policyData,(INSTR( ZPOLICY.policyData,'<Printer name') + 15),
( INSTR( ZPOLICY.policyData,'type=') -(INSTR( ZPOLICY.policyData,'<Printer name') + 17)))
ELSE EMPTY_CLOB()
END AS PrinterName
FROM
(SELECT consumertab1.Name,
consumertab1.PrimaryType,
consumertab1.PATH,
consumertab1.ZUID
FROM ZZENOBJECT consumertab1
WHERE (consumertab1.PrimaryType='Device'
OR consumertab1.PrimaryType ='External Reference')
) "consumertab1"
LEFT OUTER JOIN "ZRS_ASSIGNMENT" "zrs_assignment"
ON ("consumertab1"."ZUID" = "zrs_assignment"."CONSUMERUID")
RIGHT OUTER JOIN "ZZENOBJECT" "contentobject"
ON ("zrs_assignment"."CONTENTUID" = "contentobject"."ZUID")
INNER JOIN "ZZENOBJECT" "policies_contentObject"
ON ("contentobject"."ZUID" = "policies_contentObject"."ZUID")
INNER JOIN
(SELECT bundleZenObject.ZUID,
bundleZenObject.name,
bundleZenObject.PrimaryType,
assignableContentObject.Version
FROM ZZENOBJECT bundleZenObject
INNER JOIN ZASSIGNABLECONTENTOBJECT assignableContentObject
ON (bundleZenObject.ZUID =assignableContentObject.ZUID
AND (assignableContentObject.Version! = -1)
AND assignableContentObject.releaseuid IS NULL)
) "policies_contentObject1" ON ("contentobject"."ZUID" = "policies_contentObject1"."ZUID")
LEFT OUTER JOIN ZPOLICY
ON "policies_contentObject".zuid =ZPOLICY.zuid
WHERE ("policies_contentObject"."PRIMARYTYPE" ='Policy')
AND ("policies_contentObject1"."PRIMARYTYPE" ='Policy'
AND "policies_contentObject1"."VERSION" != -1)
AND "policies_contentObject".SUBTYPE ='printer policy'

by: Marc L. | over a year ago | Reporting