Share via

Query not working in the system console?

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

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
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 42,026 Reputation points Microsoft Vendor
    2020-09-11T08:41:01.073+00:00

    @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.

    1 person found this answer helpful.
    0 comments
  2. Garth 5,801 Reputation points
    2020-09-11T10:57:45.333+00:00

    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?

    1 person found this answer helpful.
    0 comments
  3. Duchemin, Dominique 2,006 Reputation points
    2020-09-14T19:08:57.943+00:00

    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

    0 comments