/build/static/layout/Breadcrumb_cap_w.png

Creating software reports.

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?


2 Comments   [ + ] Show comments
  • Please post your query so that we can provide assistance. - chucksteel 5 years ago
  • 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 - fleminsa 5 years ago

Answers (1)

Answer Summary:
Posted by: worzie 5 years ago
Fourth Degree Brown Belt
1

Top Answer

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.


Comments:
  • That worked. Thank you very much for this. - fleminsa 5 years ago

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