Query not working in the system console?

asked 2020-09-11T02:08:12.733+00:00
Dominique DUCHEMIN 821 Reputation points

Hello,

I have a query:

select SMS_R_System.Name, SMS_G_System_INSTALLED_SOFTWARE.ProductName, SMS_G_System_INSTALLED_SOFTWARE.ProductVersion,

vgsis2.ProductName, vgsis2.ProductVersion
from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId
Inner join SMS_G_System_INSTALLED_SOFTWARE vgsis2 on vgsis2.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceId = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ProductName like "%IBM Spectrum%" and SMS_G_System_SoftwareProduct.ProductName like "%IBM Spectrum%"
and vgsis2.ProductName like '%Microsoft Visual C++%'


But apparebtly it does not displayed the columns from the second link to vgsis2...
Any limitation in the query design or is it a mistake I did?

Thanks,
Dom

Microsoft Configuration Manager
No comments
{count} votes

3 answers

Sort by: Most helpful
  1. answered 2020-09-11T08:41:01.073+00:00
    AllenLiu-MSFT 21,936 Reputation points Microsoft Employee

    @Dominique DUCHEMIN
    Thank you for posting in Microsoft Q&A forum.
    The vgsis2 is an alias of SMS_G_System_INSTALLED_SOFTWARE, the query result only show the column once if there is any duplicate data.
    You may try to use a SQL query to get the required results.

    select v_R_System.Name0, v_GS_INSTALLED_SOFTWARE.ProductName0, v_GS_INSTALLED_SOFTWARE.ProductVersion0,   
    vgis2.ProductName0, vgis2.ProductVersion0  
    from v_R_System inner join v_GS_INSTALLED_SOFTWARE on v_GS_INSTALLED_SOFTWARE.ResourceID = v_R_System.ResourceId   
    Inner join v_GS_INSTALLED_SOFTWARE vgis2 on vgis2.ResourceID = v_R_System.ResourceId   
    where v_GS_INSTALLED_SOFTWARE.ProductName0 like '%c++%'   
    and vgis2.ProductName0 like '%microsoft%'  
    

    24047-38.jpg


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments

  2. answered 2020-09-11T10:57:45.333+00:00
    Garth 4,486 Reputation points

    Wql queries are not designed for this type of thing. Sql queries are. Is their a reason why you are trying to create a collection query and not a Sql query?

    No comments

  3. answered 2020-09-14T19:08:57.943+00:00
    Duchemin, Dominique 1,311 Reputation points

    Hi Garth,

    This is the point \Monitoring\Overview\Queries\ where the Installers Teams are usually going.
    They don't have access to the SQL Server. I will do it through a report.

    Thanks,
    Dom

    No comments