/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Custom Ticket Field Query "Discovered Software in a Label"

06/24/2020 200 views

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

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

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



Answered 06/26/2020 by: chucksteel
Red Belt

  • Excellent chucksteel! Exactly what I needed. Thank you most kindly.

All Answers

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

Answered 06/25/2020 by: Hobbsy
Red Belt

  • 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 :)
  • FYI, This would return software inventory titles, not software catalog titles.
 
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