/build/static/layout/Breadcrumb_cap_w.png
01/09/2019 106 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

Share