locked
SCCM Query not listing all computers in collection RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Experts,

    I am using SCCM Queries under monitoring to get Client activity, status, OS, Model, Lastlogon user, chasis type,etc.. all workstations collection have 3000+ computers but query results only for 2800+ computers. I understand query will not show up computers missing certain fields but I want to have all the computers in the list so please advice on how to get all 3000+ computers in the query result with or without the queried information. 

    Please let me know if this is possible with SCCM report ?

    Thanks in advance

    Monday, April 8, 2019 9:17 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    try this.

    Select 
	RV.Netbios_Name0, 
	OS.Caption0, 
	RV.Operating_System_Name_and0, 
	RV.Client0,
	CHCS.ClientActiveStatus, 
	CS.Model0, 
	RV.User_Name0, 
	CS.Manufacturer0, 
	CSP.IdentifyingNumber0
from  
	dbo.v_r_system RV
	left outer join dbo.v_CH_ClientSummary CHCS on RV.ResourceID = CHCS.ResourceId 
	left outer join dbo.v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.ResourceId 
	left outer join dbo.v_GS_COMPUTER_SYSTEM_PRODUCT CSP on RV.ResourceID = CSP.ResourceId 
	left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceId

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    • Proposed as answer by Sherry Kissinger Monday, April 8, 2019 2:38 PM
    • Marked as answer by IN-RMJ Tuesday, April 9, 2019 7:18 AM
    Monday, April 8, 2019 1:54 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello IN_RMJ,
     
    What does your query look like? If you are querying from multiple tables/classes, you could use left join/right join instead of inner join if there are no corresponding records in one table/class.
     
    For example, change "select ... from SMS_R_System inner join SMS_G_System_CH_ClientSummary on ... where ..." to  "select ... from SMS_R_System left join SMS_G_System_CH_ClientSummary on ... where ...".
     
    Hope my answer could help you and look forward to your feedback.
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Monday, April 8, 2019 10:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Personally, I wouldn't use the Console, query node at all. it sounds like you want to query sql (with is a report in the Reporting node), and not WQL (which is the query node or for making collections).  To me, those are very distinctly different needs.  SQL can be much, much more robust for reporting than wql could ever be.

    Standardize. Simplify. Automate.

    Monday, April 8, 2019 12:27 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Ray,

    Here is my query which pulls the result but computers without client status, activity or OS are not listing in the result.

    select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.Client, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_R_System.LastLogonUserName, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber, SMS_R_System.LastLogonTimestamp from  SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM_PRODUCT on SMS_G_System_COMPUTER_SYSTEM_PRODUCT.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId

    Monday, April 8, 2019 1:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Sherry,

    I would like to get the below information for all the computers. please let me know the query to pull from SQL.

    SCCM Queries:

    select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.Client, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_R_System.LastLogonUserName, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber, SMS_R_System.LastLogonTimestamp from  SMS_R_System inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM_PRODUCT on SMS_G_System_COMPUTER_SYSTEM_PRODUCT.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId

    Monday, April 8, 2019 1:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    try this.

    Select 
	RV.Netbios_Name0, 
	OS.Caption0, 
	RV.Operating_System_Name_and0, 
	RV.Client0,
	CHCS.ClientActiveStatus, 
	CS.Model0, 
	RV.User_Name0, 
	CS.Manufacturer0, 
	CSP.IdentifyingNumber0
from  
	dbo.v_r_system RV
	left outer join dbo.v_CH_ClientSummary CHCS on RV.ResourceID = CHCS.ResourceId 
	left outer join dbo.v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.ResourceId 
	left outer join dbo.v_GS_COMPUTER_SYSTEM_PRODUCT CSP on RV.ResourceID = CSP.ResourceId 
	left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceId

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    • Proposed as answer by Sherry Kissinger Monday, April 8, 2019 2:38 PM
    • Marked as answer by IN-RMJ Tuesday, April 9, 2019 7:18 AM
    Monday, April 8, 2019 1:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    Try changing inner join to left join like I mentioned above, then check the results.

    Best Regards,

    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, April 8, 2019 3:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote


    Thank you for your reply, changing left join makes no change in the result.

    Tuesday, April 9, 2019 7:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you Garth Jones. Its working well in SQL MS, please share if you have report with this Information.
    Tuesday, April 9, 2019 7:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello IN-RMJ,
     
    It should not be. Have you changed all the inner to left? Try the following query.
     
    select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.Client, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_R_System.LastLogonUserName, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber, SMS_R_System.LastLogonTimestamp from  SMS_R_System left join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_COMPUTER_SYSTEM_PRODUCT on SMS_G_System_COMPUTER_SYSTEM_PRODUCT.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
     
    Best Regards,
    Ray

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, April 9, 2019 8:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I am getting same result.
    Tuesday, April 9, 2019 10:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello,
     
    Well, compare the record numbers of the above query with the following query.
     
    select * from SMS_R_System
     
    Are they the same? Are they the numbers of all systems?
     
    Best Regards,
    Ray 

    Please remembers to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Tuesday, April 9, 2019 11:50 AM