Custom Ticket Field Query "Discovered Software in a Label"
I am attempting to craft a query for a custom field in the service desk to return discovered software titles that are part of a manual label (in this case label ID=68).
Being a SQL novice, I was able to "borrow" a query from a report created in the wizard to get me half way there, but I am failing to successfully add logic for the label.
The query returning discovered software titles (which I suspect could be better optimized if not built with the report wizard) is below:
QUERY: SELECT SC.PRODUCT_NAME FROM SAM_MACHINE_JT JOIN CATALOG.SAM_CATALOG SC ON SC.ID = SAM_MACHINE_JT.SAM_CATALOG_ID
JOIN SAM_COUNT ON SAM_COUNT.SAM_CATALOG_ID = SC.ID
LEFT JOIN SAM_METER_DATA SMD ON SMD.MACHINE_ID = SAM_MACHINE_JT.MACHINE_ID
AND SMD.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN SAM_METER_TITLED_APPLICATION SMTA ON SMTA.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN SAM_NOT_ALLOWED SNA ON SNA.TITLED_APPLICATION_ID = SC.ID
LEFT JOIN MACHINE M ON M.ID = SAM_MACHINE_JT.MACHINE_ID
LEFT JOIN KBSYS.SAM_TITLE_REQUEST STR ON STR.SAM_APPLICATION_FILE_ID = SC.ID WHERE (SC.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND (SC.SOFTWARE_CATEGORY_ID <> 32) AND SAM_COUNT.SAM_CATALOG_ID NOT IN (select SAM_CATALOG_ID from REPORT_SOFTWARE_CATALOG_EXCEPTION)) AND ((SAM_COUNT.INSTALLED_ON > '1') OR (SAM_COUNT.INSTALLED_ON = '1')) GROUP BY SAM_MACHINE_JT.SAM_CATALOG_ID ORDER BY PRODUCT_NAME
I was also able to get the below query working to pull users with a specific manual label (ID=38) but I am having difficulty manipulating the syntax to apply to the SAM_CATALOG:
query: SELECT FULL_NAME, USER.ID, USER_LABEL_JT.LABEL_ID FROM ORG1.USER USER INNER JOIN ORG1.USER_LABEL_JT USER_LABEL_JT ON (USER.ID = USER_LABEL_JT.USER_ID) WHERE USER_LABEL_JT.LABEL_ID = "38" ORDER BY FULL_NAME ASC
Any guidance is greatly appreciated.
Thank you most kindly,
Answer Chosen by the Author
This should work for you:
SELECT NAME FROM CATALOG.SAM_CATALOG
JOIN ORG1.SAM_CATALOG_LABEL_JT on ORG1.SAM_CATALOG_LABEL_JT.SAM_CATALOG_ID = SAM_CATALOG.ID
WHERE LABEL_ID = 68
ORDER BY NAME