/build/static/layout/Breadcrumb_cap_w.png

Does anyone know a SQL query report to display System Name, Asset Location, IP Address, OS Name, and Specific Software information (example: Trend Micro)?

Does anyone know a SQL query report to display System Name, Asset Location, IP Address, OS Name, and Specific Software information (example: Trend Micro)?

I've tried it several times but it keeps failing. 

HzXmrVrHWeVBAAAAAElFTkSuQmCC


0 Comments   [ + ] Show comments

Answers (1)

Posted by: sven.hain 4 weeks ago
Green Belt
0

Hi,

please try one of the queries below that I created in the past and just added the fields that you want to see. In my example it is for 7-ZIP under a specific version. Query 1 will only show 7-ZIP, Query 2 would be under a specific version. Choose one of them and change it for your needs.

One hint here. You had in your where clause '%trend%micro%'. The percent in the middle is not needed. Just type it like '%Trend Micro%'.

Query 1 Check only for specific software:

Select

    MACHINE.NAME as NAME,

    MACHINE.IP as IP,

    MACHINE.OS_NAME as OSName,

    ASSET_LOCATION.NAME as Location,

    SOFTWARE.DISPLAY_NAME as SoftwareName,

    SOFTWARE.DISPLAY_VERSION as VERSION

From

    SOFTWARE Inner Join

    MACHINE_SOFTWARE_JT On SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID Inner Join

    MACHINE On MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID

    LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID

    Where

    SOFTWARE.DISPLAY_NAME Like '%7-zip%'

Query 2 Check software under specific version:

Select

    MACHINE.NAME as NAME,

    MACHINE.IP as IP,

    MACHINE.OS_NAME as OSName,

    ASSET_LOCATION.NAME as Location,

    SOFTWARE.DISPLAY_NAME as SoftwareName,

    SOFTWARE.DISPLAY_VERSION as VERSION

From

    SOFTWARE Inner Join

    MACHINE_SOFTWARE_JT On SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID Inner Join

    MACHINE On MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID

    LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID

    Where

    SOFTWARE.DISPLAY_NAME Like '%7-zip%' And

    SOFTWARE.DISPLAY_VERSION < 19


Comments:
  • Hi, this is really helpful! Thankyou so much - Nzw 4 weeks 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