locked
SCCM SQL Views Data Flow RRS feed

  • Question

  • Hello Folks,

    In SCCM SQL we have "v_R_system view" and "v_gs_workstation_status".

    I have total workstations in v_R_system =6750 while in v_gs_workstation_status = 5879

    What is the criteria being followed here ? how SCCM\SQL decide which device should be part of v_GS_workstation_status or should be removed ? Any definite calculation behind this ?.

    Many Thanks for the responses in advance.


    Tuesday, December 10, 2019 12:01 PM

Answers

All replies

  • V_r_system is based on discovery and also includes non clients. The other view is based on hw inventory.

    Garth Jones

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

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

    Tuesday, December 10, 2019 12:49 PM
  • Thanks Jones for your response.

    Can we check somehow what is the criteria behind v_GS_workstation_status. which tables it refers to update itself ?.

    I am in serious need of finding actual calculations behind this.

    Thank You.



    • Edited by SourabhK Tuesday, December 10, 2019 5:58 PM
    Tuesday, December 10, 2019 5:57 PM
  • Thanks Jones for your response.

    Can we check somehow what is the criteria behind v_GS_workstation_status. which tables it refers to update itself ?.

    I am in serious need of finding actual calculations behind this.

    Thank You.



    it is dbo.workstationstatus_data.

    what are you trying to find for calculations? 

    you can refer to Microsoft docs for information about inventory and VIEWS https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/sample-queries-hardware-inventory-configuration-manager


    Eswar Koneti | Configmgr Blog: http://www.eskonr.com | Linkedin: eskonr | Twitter: @eskonr

    Tuesday, December 10, 2019 6:24 PM
  • Thanks Jones for your response.

    Can we check somehow what is the criteria behind v_GS_workstation_status. which tables it refers to update itself ?.

    I am in serious need of finding actual calculations behind this.

    Thank You.



    The table will have basically the same data and it is not supported to query them. So what exactly do you mean by calculation behind this? 



    Garth Jones

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

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

    Tuesday, December 10, 2019 6:33 PM
  • Hi,
     
    Agree with Eswar, for the v_GS_workstation_status , the data is summary from dbo.WorkstationStatus_DATA. It lists workstation status information for Configuration Manager clients.

    https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/hardware-inventory-views-configuration-manager

     
    For v_R_System view, the data is from dbo.vSMS_R_System.  It lists all discovered system resources. So they will be different.
     
    Hope it can help.
     
    Best regards.
    Crystal

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

    Wednesday, December 11, 2019 1:56 AM
  • Hi Eswar

    Thank you for your response.

    We are working on a activity wherein ServiceNow fetches device list from v_gs_computer_system.

    There are some computers which are not in v_gs_computer_system but present in v_r_system which is clear.

    objective is to identify that suppose a computer agent is not sending HW inventory to SCCM, when SCCM will decide OK remove this computer agent information from v_gs_computer_system ? OR if HW inventory is before specific days, then remove this entry from v_gs_computer_system.

    Thanks.

    Wednesday, December 11, 2019 2:41 AM
  • Hi Jones

    Thank you for your response.

    We are working on a activity wherein ServiceNow fetches device list from v_gs_computer_system.

    There are some computers which are not in v_gs_computer_system but present in v_r_system which is clear.

    objective is to identify that suppose a computer agent is not sending HW inventory to SCCM, when SCCM will decide OK remove this computer agent information from v_gs_computer_system ? OR if HW inventory is before specific days, then remove this entry from v_gs_computer_system.

    Thanks.

    Wednesday, December 11, 2019 2:42 AM
  • Hi,
     
    Based as I know: v_GS_COMPUTER_SYSTEM is updated every time the hardware inventory is run whereas v_R_System  is updated during the last Network Discovery (PC) or Heartbeat Discovery.  So the information updated depends on the hardware inventory cycle and the discovery schedule we set. Hope it can help.
     
    Best regards.
    Crystal

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

    Wednesday, December 11, 2019 7:17 AM
    • Marked as answer by SourabhK Wednesday, December 18, 2019 9:25 AM
    Wednesday, December 11, 2019 10:46 AM
  • when SCCM will decide OK remove this computer agent information from v_gs_computer_system

    ConfigMgr never does this. If there is no data in this view (and underlying table) for a system then that system has never successfully submitted any hardware inventory information and yo need to troubleshoot hardware inventory for that system per Garth's blog.


    Jason | https://home.configmgrftw.com | @jasonsandys


    Wednesday, December 11, 2019 1:13 PM
  • HI,

    How's everything going? If there's any update, please let us know.

    Best regards.

    Crystal


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

    Monday, December 16, 2019 1:25 AM
  • Thanks Jones.

    Your explanation helped much on understanding the HW inventory flow within SCCM.

    such a great article it is. :)

    Wednesday, December 18, 2019 9:26 AM
  • Hi Crystal

    The frequency of updates in v_gs_computer_system depends on the schedule of MT in SCCM.

    So this answers my question, as there is no fix calculation behind this.

    Thanks for your responses on this topic. Much appreciated.

    Wednesday, December 18, 2019 9:30 AM
  • Hi,

    Thanks for your reply. I am glad that our information can help. if there's anything else we can help in the future, feel free to post in our forum.

    Thanks for your time and have a nice day.

    Best regards.

    Crystal


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

    Thursday, December 19, 2019 2:50 AM
  • Hi Garth.

    I followed your link to troubleshoot the issue with HW inventory from client devices. I need your expert inputs here.

    Inventory Agent log - shows below line which confirms HW inventory scan is done and uploaded.

    Inventory: Successfully sent report. Destination:mp:MP_HinvEndpoint, ID: {BA67CEBF-8D96-4E8C-9171-17588A32DC33}, Timeout: 80640 minutes MsgMode: Signed, Not Encrypted

    ccmmessaging.log shows below lines :

    Failed in WinHttpSendRequest API, ErrorCode = 0x2ee2 CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    [CCMHTTP] ERROR: URL=http://MPSERVER/ccm_system/request, Port=80, Options=224, Code=12002, Text=ERROR_WINHTTP_TIMEOUT CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    [CCMHTTP] ERROR INFO: StatusCode=600 StatusText= CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    Raising event:
    instance of CCM_CcmHttp_Status
    {
    ClientID = "GUID:af006a97-a7c5-4173-a1c3-a275f9ed0215";
    DateTime = "20200203052914.033000+000";
    HostName = "MPSERVER";
    HRESULT = "0x80072ee2";
    ProcessID = 8712;
    StatusCode = 600;
    ThreadID = 9092;
    };
    CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    Successfully submitted event to the Status Agent. CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    Successfully queued event on HTTP/HTTPS failure for server 'MPSERVER'. CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    Post to http://MPSERVER/ccm_system/request failed with 0x87d00231. CcmMessaging 3/02/2020 3:29:14 PM 9092 (0x2384)
    Endpoint '{750D84E5-20F6-47B7-A5E3-66C16B193049}' of application 'ConfigMgr_Client' is supported. CcmMessaging 3/02/2020 3:39:53 PM 6504 (0x1968)
    OutgoingMessage(Queue='mp_[http]mp_policymanager', ID={2B21920E-81C7-4AF6-AF71-F8EC09EA66A7}): Will be discarded (expired). CcmMessaging 3/02/2020 3:53:21 PM 6148 (0x1804)

    Can you please provide any inputs here, How to fix it?. Thanks in advance.

    Monday, February 3, 2020 10:29 AM
  • 0x87d00231 = "Transient error"

    0x80072ee2 = "The operation timed out"

    There is something outside the scope of control or visibility preventing the communication between the client and MP from successfully completing. This is often network related but could be something else in that path like IIS.

    Start by reviewing the IIS logs on the MP to see if it is seeing the connection and traffic from the client at all.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Monday, February 3, 2020 2:22 PM
  • Thanks Jason for your inputs, I am going to check IIS logs for any probable issue.

    Few other machines does correct entry in inventoryagent.log, ccmmessaging.log shows upload successful, IIS log shows successfully completed BITS etc. But there is no entry in MP_hinv.log. also no entry for computer name in dataldr.log on primary site server.

    What could be the issue?. 


    • Edited by SourabhK Tuesday, February 4, 2020 6:14 AM
    Tuesday, February 4, 2020 6:08 AM
  • Without troubleshooting, I have no idea. Are you sure that the MIF isn't being rejected by the data loader and moved to the one of the BADMIFS sub-folders? If this is the case, the name of the system won't appear in the dataldr.log file and you need to track the process based on the MIF file name. Keep in mind that you can view MIF files in a text editor like notepad to see the data they contain and the system that generated them.

    Jason | https://home.configmgrftw.com | @jasonsandys

    Tuesday, February 4, 2020 4:31 PM