/build/static/layout/Breadcrumb_cap_w.png

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,

Shane


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 4 years ago
Red Belt
1

Top Answer

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




Comments:
  • Excellent chucksteel! Exactly what I needed. Thank you most kindly. - Moncus 4 years ago
Posted by: Hobbsy 4 years ago
Red Belt
0

For the first query try this

QUERY: SELECT SOFTWARE.DISPLAY_NAME FROM SOFTWARE SOFTWARE INNER JOIN SOFTWARE_LABEL_JT SOFTWARE_LABEL_JT ON (SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID) WHERE SOFTWARE_LABEL_JT.LABEL_ID = 153)

Adjust the label ID number for your software label


Comments:
  • That throws the error: "An SQL error occurred in generating the list: SELECT SOFTWARE.DISPLAY_NAME FROM SOFTWARE INNER JOIN SOFTWARE_LABEL_JT SOFTWARE_LABEL_JT ON (SOFTWARE.ID = SOFTWARE_LABEL_JT.SOFTWARE_ID) WHERE SOFTWARE_LABEL_JT.LABEL_ID = 68)"

    Thank you for the quick reply though :) - Moncus 4 years ago
  • FYI, This would return software inventory titles, not software catalog titles. - chucksteel 4 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ