«

»

Jan 13 2015

vRealize Automation Data collection doesn’t work

“VMTurbo"
vRealize Automation (vRA), formerly called vCloud Automation Center (vCAC) vSphere Data collection is the process where vRA reaches out to its infrastructure source endpoints and their compute resources to collect information. Below is the number of data collection types:

  • Infrastructure Source Endpoint Data Collection
  • Inventory Data Collection
  • State Data Collection
  • Performance Data Collection (vSphere compute resources only)
  • vCNS inventory data collection (vSphere compute resources only)
  • WMI data collection (Windows compute resources only)
  • Cost data collection (compute resources managed by vRealize Business Standard Edition only)


The data collection process happens of a regular basis and can also be started manually from the vRA UI:

vRA -> Infrastructure -> Endpoints -> Endpoints -> Select Compute resource

You can read more about the vRA data collection process here.

Yesterday, 2015-01-12, i got a question about a vRA data collection issue. The following vRA log entry with severity Error could be found:

DatabaseStatsService, ignoring exception, Error executing query usp_SelectAgent Inner Exception, Error  executing query usp_SelectAgentCapabilities
Screen Shot 2015-01-12 at 07.00.41

This error usually occurs because of a communication problem between the vRA Infrastructure as a Service (IaaS) component(s) and or its database.

After verifying the virtual machines (VMs) firewall settings (actually turned off) i loked into the Microsoft Distributed Transaction Coordinator Service (MS DTC). This service is responsible for coordinating transactions that span multiple systems and must be enabled on all the servers in the vRA deployment. Port 135 over TCP is used for communication and also a random port between 1 024 – 65 535

Open Component Services by either typing dcomcnfg.exe in Run or a command prompt or window or use Administrative Tools to manage the MS DTC settings.

  • Component Services –> Computers –> My Computer –> Distributed Transaction Coordinator.
  • Right click on Local DTC & select Properties
  • Select Security tab

My customers configuration followed the VMware recommendation

Screen Shot 2015-01-12 at 07.15.26

System restart/reboot didn’t solve the problem so MSDTC uninstall and install was the next step since i know these steps has solved similar problems in the past.

  • msdtc -uninstall
    Verify the action result via e.g Event Viewer since the command does not give you any output
    Screen Shot 2015-01-12 at 08.25.55
  • msdtc -install
    Verify the action result via e.g Event Viewer since the command does not give you any output
    Screen Shot 2015-01-12 at 08.26.07

A MS DTC uninstall and install should be performed if the VMs used by any component in vRA has been cloned from a template or another VM. This is because the MS DTC Globally unique identifier (GUID) configuration does not change while running sys prep meaning you still have the original VMs MS DTC configuration.

When finished the data collection could be manually started and vRA Reservations could be setup.

Screen Shot 2015-01-12 at 08.35.35

As mentioned by Adam Bohle in the comments field below another indication of this MS DTC problem is any errors referring to “Failed Transactions” with the DB instance in the All.log or Error.log files in $installation_pathvmwarevcacserverlogs.
Follow the uninstall, install procedure in these cases.

29 comments

Skip to comment form

  1. bohleadam

    Another symptom of this issue is the presence of any information in the logs which refers to “Failed Transactions” occurring with the DB instance. The minute you see anything in the Manager Service All.log or Error.log (c:program files (x86)vmwarevcacserverlogs) saying this, then follow the procedure above.

    Also be aware that if you have cloned your IaaS server from another template machine and you have configured MSDTC correctly, you may still have issues and might need to reinstall MSDTC as it might conflict with other machines on the network running MSDTC from the same image. Custom Specs don’t clear this out in vSphere machines.

    One last thing, if you are communicating with a DB instance in a different domain, set authentication to No Authentication rather than Mutual Authentication. Not a common use case but worth noting.

  2. magander3

    Thanks for your comment. Have updated the blog post with some of the information.

    //Magnus

  3. hj

    Hi,

    I have exactly the same problem, Fabric Group set correctly, endpoints added with admin acct (same acct used to installed and configured everything) – even I tried different accounts etc but still when I go to Reservations I can see my (vSphere – vCenter) cluster lists in “Compute resource” (under “Reservation Information” tab) but it shows “0” values for everything when I go to “Resources” tab, no Storage listed either.

    Any helps will be appreciated guys?

    Cheers

  4. magander3

    Hi,
    what error messages are you seeing? The Fabric group does not include any potential vSphere clusters?

  5. Pardha Nallan

    I have the same problem. I uninstalled and reinstalled DTC on the servers, and they are all fresh builds..no clones done on any of the IaaS component machines. I’m still facing this issue. Pls help!!

    Pardha

  6. magander3

    What error do you get in vRA, same as in blog post?

  7. hj

    Hi mate,

    Actually I can select the vSphere Cluster listed on Fabric Group, but when I go to reservation I still can select my vSphere -vCenter clsuter from the drop-down list in Compute Resource under Reservation Information tab, but when I go to “Resources” tab, there is “0” resources and even there is no Storage listed?

    Hope there is solution for this issue?

    Thanks for your help mate,

  8. magander3

    Ok and you can’t select the cluster via the fabric group or has that already been done? What errors do you see in the vSphere Proxy Agent and in the vRA UI?

  9. hj

    Hello,

    Yes cluster is selected in the fabric group already. vSphere Agent status is ok and there is no error appears anywhere on the vRA UI – it just shows “0” value on the Resources tab (under Reservation)?

    Cheers

  10. Mohamed

    Hi
    I am getting the same error as you posted in the Blog, All my iaas servers are fresh build, but still I am getting the same error, tried your solution, but no luck.

    error description
    1. DataBaseStatsService: ignoring exception: Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities
    2. Error processing ping response Error executing query usp_SelectAgent Inner Exception: Error executing query usp_SelectAgentCapabilities

    I am using a service account which has local admin rights and admin rights in VC as well.
    Please suggest what else I can try next?
    Thanks in advance

  11. magander3

    Hi,
    did you successfully ran the pre check script before the installation so all the privileges necessary are configured?
    Have you tried starting the service with another account?

    //Magnus

  12. Pardha Nallan

    I ran into this error recently.. MSDTC was running on all my IaaS web servers but for some reason the same service was in disabled state in the SQL server where my IaaS DB is hosted.. Once I set the service to automatic and started it the problem disappeared..

  13. magander3

    Good catch Pardha.

    //Magnus

  14. Mohamed

    Hi

    Thanks for the reply.
    Yes I ran the pre-check successfully and I had no issue with installation.I checked the Iaas services, SQL services and MSDTC services all were running and good.

    After lots of digging I found from event viewer in the SQL server, that It was refusing / closing the connection from Iaas server. (Not sure why)

    I did few things different this time and issue got resolved
    1. I used different ISO files for vRA installation, Windows machine and Different SQL ISO.
    2. Installed a new sql server and I installed Iaas manager service on top of the SQL server.
    3. Never gave up 🙂

    data collection Issue got resolved

    But still not sure what was causing the previous mentioned error

    Thanks for you support

  15. magander3

    Hi, awesome. i have seen a lot of these issues cause by MSDTC

    thanks

    //Magnus

  16. Jose

    Thank you all for this information. You save my day!

    I had exactly the same issue as reported by HJ above:

    “I have exactly the same problem, Fabric Group set correctly, endpoints added with admin acct (same acct used to installed and configured everything) – even I tried different accounts etc but still when I go to Reservations I can see my (vSphere – vCenter) cluster lists in “Compute resource” (under “Reservation Information” tab) but it shows “0” values for everything when I go to “Resources” tab, no Storage listed either.”

    My setup is simple: Vsphere 6, Windows 2012 DC, SQL server on Windows 2012, IaaS on Windows 2012 and vRA appliance.
    I am using vRA v7 and I did a minimal setup by using the vRA Installation Wizard.
    My IaaS had a number of missing requisites but I used the Fix option to resolve them.

    Issue:
    All went fine until I tried to create Reserves: it was not possible because the vCenter Endpoint was not able to do data collection.
    The vRA logs showed exactly what is reported above.

    Solution:
    After configuring the MSDTC at the SQL server system the issue was gone, although I had to reboot the IaaS Windows server and vRA appliance after setting the SQL MSDTC.

  17. magander3

    ok so same applies to vRA 7. good to know.

    thanks
    Magnus

  18. simon sung

    you can try to login to VC and enable the “CIM” service

  19. Joseph

    what resolved this for me was actually on the sql server side

    network dts access was disabled. did the changes and boom…success

  20. magander3

    good stuff

    //Magnus

  21. Chris Smallwood

    vRA 7 wasn’t seeing any compute resources in my inventory. I configured DCOM as shown above, uninstalled and reinstalled MSDTC on every involved server, rebooted those servers, and it is fixed! I have a feeling that this was caused by the above mentioned issue with regards to cloning servers and having conflicting IDs.

    Thank you for this!

  22. magander3

    Glad it worked..

    //Magnus

  23. Stephan

    Thanks! Error Message in vRA 7.1:

    DataBaseStatsService: ignoring exception:
    Error executing query usp_SelectAgent
    Inner Exception: Error executing query usp_SelectAgentCapabilities

  24. Oleg

    I have the same error in 7.2 but can’t solve this by solution which is explained in this article.
    Could you contact with me please for a paid outsourcing to solve this problem ?
    my skype is coolmanfn
    Thank you

  25. Sukumar

    With 7.2 it is not working. I tried everything. I’m not able to get the Inventory.

  26. Rommel

    I finally managed to get this working by performing the following aside from the steps mentioned in this blog:

    1. Ensure firewall ports are open for DTC communications, port 135 and the dynamic RPC ports
    2. set DTC authentication to No authentication since my DB and my IaaS are on different domains
    3. Added host file entries for both IaaS and the DB server so that they can resolve short hostname.

  27. magander3

    Really good, thanks for sharing.

    //Magnus

  28. kamlesh

    Hi ,Nice conversation from all of you really very helpful, Can anyone please help me to understand datacollection flow between different component in vRA/vRO.

    Example:1. How it is communicating with the different vRA/vRO component?
    2.Where is being stored after datacollection process?
    etc.
    2.what agent or service it use to communicate with hypervisor or connected endpoints?

    Thanks in advance.

  29. magander3

    Hi, check out the latest vRealize Automation Reference Architecture. There are some really nice pictures with ports included that describes almost all communication flows.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">