/build/static/layout/Breadcrumb_cap_w.png

Software Question


Creating software reports.

01/09/2019 440 views

Anybody have best practices on creating software reports.  I wanted a report where it would show the device name, name of the primary user on the machine,  the software name specified in the filter and the version number.  Whenever I run the report it goes into an endless loop and no results show up.  Any ideas?

Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Please post your query so that we can provide assistance.
  • SELECT LAST_INVENTORY, MACHINE.NAME AS SYSTEM_NAME, USER_FULLNAME, CHASSIS_TYPE, CS_MANUFACTURER, CS_MODEL, (SELECT GROUP_CONCAT(PUBLISHER order by PUBLISHER separator '\n') FROM SOFTWARE S2 INNER JOIN MACHINE_SOFTWARE_JT MSJT ON S2.ID = MSJT.SOFTWARE_ID WHERE MSJT.MACHINE_ID = MACHINE.ID) AS PUBLISHER, (SELECT GROUP_CONCAT(DISPLAY_NAME order by DISPLAY_NAME separator '\n') FROM SOFTWARE S2 INNER JOIN MACHINE_SOFTWARE_JT MSJT ON S2.ID = MSJT.SOFTWARE_ID WHERE MSJT.MACHINE_ID = MACHINE.ID) AS DISPLAY_NAME, (SELECT GROUP_CONCAT(DISPLAY_VERSION order by DISPLAY_VERSION separator '\n') FROM SOFTWARE S2 INNER JOIN MACHINE_SOFTWARE_JT MSJT ON S2.ID = MSJT.SOFTWARE_ID WHERE MSJT.MACHINE_ID = MACHINE.ID) AS DISPLAY_VERSION, (SELECT GROUP_CONCAT(DISTINCT CATALOG.SAM_CATALOG.PUBLISHER ORDER BY CATALOG.SAM_CATALOG.PUBLISHER separator '\n') FROM CATALOG.SAM_CATALOG JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID WHERE MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID AND CATALOG.SAM_CATALOG.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY_ID <> 32) AS SAM_PUBLISHER, (SELECT GROUP_CONCAT(DISTINCT CONCAT(CATALOG.SAM_CATALOG.PUBLISHER, '-', CATALOG.SAM_CATALOG.NAME)
    ORDER BY CATALOG.SAM_CATALOG.PUBLISHER, CATALOG.SAM_CATALOG.NAME separator '
    ')
    FROM CATALOG.SAM_CATALOG
    JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID
    WHERE MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID AND CATALOG.SAM_CATALOG.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY_ID <> 32) AS SAM_PUB_PROD, (SELECT GROUP_CONCAT(DISTINCT CATALOG.SAM_CATALOG.NAME ORDER BY CATALOG.SAM_CATALOG.NAME separator '\n') FROM CATALOG.SAM_CATALOG JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID WHERE MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID AND CATALOG.SAM_CATALOG.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND CATALOG.SAM_CATALOG.SOFTWARE_CATEGORY_ID <> 32) AS SAM_TITLE FROM MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) LEFT JOIN SAM_MACHINE_JT on MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID LEFT JOIN CATALOG.SAM_CATALOG ON CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID WHERE ((( exists (select 1 from CATALOG.SAM_CATALOG JOIN SAM_MACHINE_JT on CATALOG.SAM_CATALOG.ID = SAM_MACHINE_JT.SAM_CATALOG_ID where MACHINE.ID = SAM_MACHINE_JT.MACHINE_ID and CATALOG.SAM_CATALOG.NAME like 'Jenzabar%')) )) GROUP BY MACHINE.ID ORDER BY USER_FULLNAME

Answer Chosen by the Author

1

Here is an example I whipped up from a stock wizard example for rounding up all the Java SE's installed:

I think if this were a device report, you could sort first by devices with Java SE.  This is a software report, sorted by the software name.

Answered 01/10/2019 by: worzie
Third Degree Brown Belt

  • That worked. Thank you very much for this.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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