Miscellaneous

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,

Shane